Create a CAP Business Service with Node.js Using Visual Studio Code

You will add plain CSV files in folder db/data to fill your database tables with initial data. In the command line window execute the following:

cds add data

This adds csv files with a single header line for all entities to the db/data/ folder. The name of the files matches the entities’ namespace and name, separated by -.

1. Open the file db/data/my.bookshop-Authors.csv and add the following sample data to the file:

   CSV Copy

   101,Emily Brontë
   107,Charlote Brontë
   150,Edgar Allen Poe
   170,Richard Carpenter

2. Open the file db/data/my.bookshop-Books.csv and add the following sample data to the file:

   CSV Copy

   201,Wuthering Heights,101,12
   207,Jane Eyre,107,11
   251,The Raven,150,333
   252,Eleonora,150,555
   271,Catweazle,170,22

   Remember to save your files choosing Ctrl + S.

   For now, you can ignore the additional files that cds add data generated for you.

   After you added these files, cds watch restarts the server with an output, telling that the files have been detected and their content been loaded into the database automatically:

   Shell/Bash Copy

   [cds] - connect using bindings from: { registry: '~/.cds-services.json' }
   [cds] - connect to db > sqlite { database: ':memory:' }
    > init from ./db/csv/my.bookshop-Books.csv
   /> successfully deployed to sqlite in-memory db
   
   [cds] - using auth strategy { kind: 'mocked' }
   [cds] - serving CatalogService {
       at: [ '/odata/v4/catalog' ],
       decl: 'srv/cat-service.cds:3',
       impl: 'srv/cat-service.js'
   }
   [cds] - server listening on { url: 'http://localhost:4004' }
   [cds] - server v9.7.1 launched in 419 ms
   [cds] - [ terminate with ^C ]

3. Remove the code with mock data in cat-service.js, because you want to see the data loaded from the csv files.

4. To test your service, open a web browser and go to:

   http://localhost:4004/odata/v4/catalog/Books

   http://localhost:4004/odata/v4/catalog/Authors

   As you now have a fully capable SQL database with some initial data, you can send complex OData queries, served by the built-in generic providers.

   http://localhost:4004/odata/v4/catalog/Authors?$expand=books($select=ID,title)

   You should see a book titled Jane Eyre. If not, make sure you’ve removed the mock data from cat-service.js.

Before you continue, make sure that you’ve completed the prerequisites and installed SQLite (for Windows users only).

Instead of using in-memory, you can also use persistent databases.

1. If cds watch is running, choose Ctrl + C in the command line to stop the service.

2. Configure the database in your package.json in the cds.requires.db section.

   JSON Copy

   {
     "name": "my-bookshop",
     "version": "1.0.0",
     "description": "A simple CAP project.",
     "repository": "<Add your repository here>",
     "license": "UNLICENSED",
     "private": true,
     "dependencies": {
       "@sap/cds": "^7",
       "express": "^4"
     },
     "devDependencies": {
       "@cap-js/sqlite": "^1"
     },
     "scripts": {
       "start": "cds-serve"
     },
     "cds": { "requires": {
      "db": {
         "kind": "sqlite",
         "credentials": { "url": "db/my-bookshop.sqlite" }
      }
   }}
   }

3. Deploy the data model to an SQLite database:

   Shell/Bash Copy

   cds deploy

   You’ve now created an SQLite database file under db/my-bookshop.sqlite.

   The difference to the automatically provided in-memory database is that you now get a persistent database stored in the local file.

4. Open SQLite and view the newly created database:

   Shell/Bash Copy

   sqlite3 db/my-bookshop.sqlite -cmd .dump

   If this doesn’t work, check if you have SQLite installed. On Windows, you might need to enter the full path to SQLite, for example: C:\sqlite\sqlite3 db/my-bookshop.db -cmd .dump. Find the steps how to install it in the Troubleshooting guide in section How Do I Install SQLite in the CAP documentation for more details.

5. To stop SQLite and go back to your project directory, choose Ctrl + C.

6. Run your service.

   Shell/Bash Copy

   cds watch

   Shell/Bash Copy

   [cds] - using bindings from: { registry: '~/.cds-services.json' }
   [cds] - connect to db > sqlite { url: 'db/my-bookshop.sqlite' }
   [cds] - using auth strategy { kind: 'mocked' }
   [cds] - serving CatalogService {
       at: [ '/odata/v4/catalog' ],
       decl: 'srv/cat-service.cds:3',
       impl: 'srv/cat-service.js'
   }
   [cds] - server listening on { url: 'http://localhost:4004' }
   [cds] - server v9.7.1 launched in 296 ms
   [cds] - [ terminate with ^C ]

You can now see the generic handlers shipped with CAP in action.

In the root of your project, create a file called test.http and copy the following requests in it. This file can be used with the REST client to make requests against your service. The generic handlers CAP provides sent the responses to your requests.

###
#
# Browse Books
#
GET http://localhost:4004/odata/v4/catalog/Books?
  # &$select=title,stock
  # &$expand=currency
  # &sap-language=de

###
#
# Get Author wit ID 101
#
GET http://localhost:4004/odata/v4/catalog/Authors(101)

###
#
# Update Author with ID 101
#
POST http://localhost:4004/odata/v4/catalog/Authors
Content-Type: application/json

{"ID": 101, "name": "Some Author"}


###
#
# Order a Book
#
POST http://localhost:4004/odata/v4/catalog/Orders
Content-Type: application/json;IEEE754Compatible=true

{"book_ID": 201, "amount": 5}

Click on Send Request inside the test.http file, to execute requests against your service.

This Send Request button is provided by the REST client. It appears for every single request. This is important for the following step, when you execute, for example, the Order a Book request.

The REST client gives you the response of your service and you see immediately if the request was successful.

Use the following request to answer the next question:

POST http://localhost:4004/odata/v4/catalog/Books
Content-Type: application/json

{"ID": 201, "title": "Some Title"}

1. In VS Code open the file cat-service.js and replace the existing code with:

   JavaScript Copy

     module.exports = (srv) => {
   
     const {Books} = cds.entities ('my.bookshop')
   
     // Reduce stock of ordered books
     srv.before ('CREATE', 'Orders', async (req) => {
       const order = req.data
       if (!order.amount || order.amount <= 0)  return req.error (400, 'Order at least 1 book')
       const tx = cds.transaction(req)
       const affectedRows = await tx.run (
         UPDATE (Books)
           .set   ({ stock: {'-=': order.amount}})
           .where ({ stock: {'>=': order.amount},/*and*/ ID: order.book_ID})
       )
       if (affectedRows === 0)  req.error (409, "Sold out, sorry")
     })
   
     // Add some discount for overstocked books
     srv.after ('READ', 'Books', each => {
       if (each.stock > 111)  each.title += ' -- 11% discount!'
     })
   
   }

   Remember to save your files choosing Ctrl + S.

   Whenever orders are created, this code is triggered. It updates the book stock by the given amount, unless there aren’t enough books left.

2. In the test.http file, execute the Browse Books request.

   Look at the stock of book 201.

   

3. Execute the Order a Book request.

   This triggers the logic above and reduces the stock.

4. Execute the Browse Books request again.

   The stock of book 201 is lower than before. In the response you also see, that overstocked books get a discount now.