Node.js ORM example application

The following tutorial implements a REST API server using the Prisma ORM. The scenario is that of an e-commerce application. Database access in this application is managed through the Prisma.

The source for this application can be found in the Using ORMs with YugabyteDB repository.

YugabyteDB Aeon requires SSL

Are you using YugabyteDB Aeon? Install the prerequisites.

Prerequisites

This tutorial assumes that you have installed YugabyteDB and created a cluster. Refer to Quick Start.

Clone the orm-examples repository

$ git clone https://github.com/YugabyteDB-Samples/orm-examples.git

Build the application

$ cd ./node/prisma/

$ npm install

Set up the database connection

From your local YugabyteDB installation directory, connect to the YSQL shell using the following command:

$ ./bin/ysqlsh

ysqlsh (11.2)
Type "help" for help.

yugabyte=#

Create the ysql_prisma database using the following command:

yugabyte=# CREATE DATABASE ysql_prisma;

Connect to the database using the following command:

yugabyte=# \c ysql_prisma;

Specify the configuration

Modify the DATABASE_URL in the .env file according to your cluster configuration:

DATABASE_URL="postgresql://<user>:<password>@<host>:<port>/<db_name>"

If you have a YugabyteDB Aeon cluster, do the following:

Download your cluster certificate.
Install OpenSSL, if not present.
Convert the certificate from .crt to .pem format using:
```
$ openssl x509 -in <root_crt_path> -out cert.pem
```
Modify the DATABASE_URL by including the cert_path as the relative path of cert.pem with respect to the /prisma folder:
```
DATABASE_URL="postgresql://<user>:<password>@<host>:<port>/<db_name>?sslmode=require&sslcert=<cert_path>"
```

Apply the migrations

Create the tables in YugabyteDB by applying the migration for the data models in the file prisma/schema.prisma, and generate the Prisma client using the following command:

$ npx prisma migrate dev --name first_migration

Note

To use the Prisma CLI without npx, install Prisma globally, as follows:

npm install -g prisma

Run the application

Start the Node.js API server at http://localhost:8080.

$ npm start

If port 8080 is already in use, change the port using the following command:

$ export PORT=<new_port>

Send requests to the application

Create 2 users.

$ curl --data '{ "firstName" : "John", "lastName" : "Smith", "email" : "jsmith@example.com" }' \
   -v -X POST -H 'Content-Type:application/json' http://localhost:8080/users

$ curl --data '{ "firstName" : "Tom", "lastName" : "Stewart", "email" : "tstewart@example.com" }' \
   -v -X POST -H 'Content-Type:application/json' http://localhost:8080/users

Create 2 products.

$ curl \
  --data '{ "productName": "Notebook", "description": "200 page notebook", "price": 7.50 }' \
  -v -X POST -H 'Content-Type:application/json' http://localhost:8080/products

$ curl \
  --data '{ "productName": "Pencil", "description": "Mechanical pencil", "price": 2.50 }' \
  -v -X POST -H 'Content-Type:application/json' http://localhost:8080/products

Create 2 orders.

$ curl \
  --data '{ "userId": "1", "products": [ { "productId": 1, "units": 2 } ] }' \
  -v -X POST -H 'Content-Type:application/json' http://localhost:8080/orders

$ curl \
  --data '{ "userId": "2", "products": [ { "productId": 1, "units": 2 }, { "productId": 2, "units": 4 } ] }' \
  -v -X POST -H 'Content-Type:application/json' http://localhost:8080/orders

Query results

Using ysqlsh

ysql_prisma=# SELECT count(*) FROM users;

 count
-------
     2
(1 row)

ysql_prisma=# SELECT count(*) FROM products;

 count
-------
     2
(1 row)

ysql_prisma=# SELECT count(*) FROM orders;

 count
-------
     2
(1 row)

Using the REST API

$ curl http://localhost:8080/users

{
  "content": [
    {
      "userId": 2,
      "firstName": "Tom",
      "lastName": "Stewart",
      "email": "tstewart@example.com"
    },
    {
      "userId": 1,
      "firstName": "John",
      "lastName": "Smith",
      "email": "jsmith@example.com"
    }
  ]
}

$ curl http://localhost:8080/products

{
  "content": [
    {
      "productId": 2,
      "productName": "Pencil",
      "description": "Mechanical pencil",
      "price": 2.5
    },
    {
      "productId": 1,
      "productName": "Notebook",
      "description": "200 page notebook",
      "price": 7.5
    }
  ]
}

$ curl http://localhost:8080/orders

{
  "content": [
    {
      "orderId": "999ae272-f2f4-46a1-bede-5ab765bb27fe",
      "userId": 2,
      "orderTotal": 25,
      "orderLines":[
        {
          "productId": 1,
          "quantity": 2
        },
        {
          "productId": 2,
          "quantity": 4
        }
      ]
    },
    {
      "orderId": "1598c8d4-1857-4725-a9ab-14deb089ab4e",
      "userId": 1,
      "orderTotal": 15,
      "orderLines":[
        {
          "productId": 1,
          "quantity": 2
        }
      ]
    }
  ]
}

Use Prisma Studio

Start Prisma Studio using the following command:

$ npx prisma studio

To view the tables and data created, go to http://localhost:5555.

Prisma studio