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 Managed requires SSL
Are you using YugabyteDB Managed? 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 Managed 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 thecert_path
as the relative path ofcert.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.