docs/content/stable/develop/drivers-orms/nodejs/prisma.md
Prisma is an open-source Object Relational Mapping (ORM) tool for Node.js or TypeScript applications. Prisma has various components including Prisma Client , which is used as a query builder to communicate with databases, Prisma Migrate, which acts as a migration tool, and Prisma Studio, which is a GUI-based tool to manage data in the database.
The Prisma client can be a REST API, a GraphQL API, a gRPC API, or anything else that needs a database.
Because YugabyteDB is PostgreSQL-compatible, Prisma supports the YugabyteDB YSQL API.
This page provides details for getting started with Prisma for connecting to YugabyteDB using the PostgreSQL database connector.
This section describes how to use data models (domain objects) to store and retrieve data from a YugabyteDB cluster.
Prisma has a main file called schema.prisma in which the configurations and data models are defined. The data models are also called Prisma models which represent the entities of your application, and map to the tables in the database. Prisma models also form the basis of the queries available in the generated Prisma Client API.
Learn how to establish a connection to YugabyteDB database and begin basic CRUD operations using the steps on the Node.js ORM example application page.
The following steps break down the example to demonstrate how to perform common tasks required for Node.js application development using Prisma.
Before proceeding with the next steps, you need to have Node.js installed on your machine. Refer to Downloading and installing Node.js and npm.
To create a basic Node.js project and install the prisma package, do the following:
Create a new directory.
mkdir nodejs-prisma-example && cd nodejs-prisma-example
Create a package.json file:
echo {} > package.json
Install the Prisma package:
npm install [email protected]
Install the Yugabyte prisma-adapter package:
npm install @yugabytedb/[email protected]
{{< note title="Note" >}}
To use the Prisma CLI without npx, install Prisma globally using the following command:
npm install -g prisma
{{< /note >}}
Create an empty example.js file:
touch example.js
Initialize Prisma in your project using the following command:
npx prisma init
This will create files named prisma/schema.prisma and .env.
schema.prisma consists of configurations and data models for the project..env defines the environment variable DATABASE_URL which is used in configuring the database.Update the generator-client block in your schema to include the driverAdapters Preview feature:
generator client {
provider = "prisma-client-js"
previewFeatures = ["driverAdapters"]
}
Configure the DATABASE_URL environment variable in the .env file to connect with a YugabyteDB cluster.
DATABASE_URL="postgresql://<user>:<password>@<host>:<port>/<db_name>?ybServersRefreshInterval=10&loadBalance=true"
If you have a YugabyteDB Aeon cluster, modify the DATABASE_URL using the following steps:
Download your cluster certificate.
Install OpenSSL, if not present.
Convert the certificate from .crt to .pem format:
openssl x509 -in <root_crt_path> -out cert.pem
Modify the DATABASE_URL by including the cert_path as the relative path to cert.pem with respect to the /prisma folder:
DATABASE_URL="postgresql://<user>:<password>@<host>:<port>/<db_name>?sslmode=require&sslcert=<cert_path>&ybServersRefreshInterval=10&loadBalance=true"
Create a data model in the prisma/schema.prisma file and migrate it to YugabyteDB using Prisma Migrate.
Create a data model called Employee in prisma/schema.prisma file by specifying the following in the file:
model Employee {
emp_id Int @id
emp_name String
emp_age Int
emp_email String @unique
@@map("employee")
}
Migrate this data model to YugabyteDB:
$ npx prisma migrate dev --name first_migration
The prisma migrate command creates a migration.sql file with the folder name <unique-id>_first_migration under prisma/migrations, applies those to the database, and generates a Prisma client, which can be used as a query builder to communicate with the database.
Use the Prisma client to create a few records in the employee table and fetch those records from the database.
Add the following code in the example.js file to create three employee records in the employee table, and fetch those employee details in the order of their employee IDs:
const { PrismaClient } = require('@prisma/client')
const { PrismaPg } = require('@yugabytedb/prisma-adapter')
const connectionString = `${process.env.DATABASE_URL}`
const adapter = new PrismaPg({ connectionString })
const prisma = new PrismaClient({ adapter })
async function example() {
const employee1 = {
emp_id: 1,
emp_name: "Jake",
emp_age: 24,
emp_email: "[email protected]"
}
const employee2 = {
emp_id: 2,
emp_name: "Sam",
emp_age: 30,
emp_email: "[email protected]"
}
const employee3 = {
emp_id: 3,
emp_name: "Tom",
emp_age: 22,
emp_email: "[email protected]"
}
await prisma.employee
.createMany({
data: [employee1, employee2, employee3,]
})
.then(async (res) => {
console.log("Created",res.count,"employees.");
await prisma.employee
.findMany({
orderBy: {
emp_id: 'asc'
},
})
.then((res) => {
console.log("Fetched employee details sorted by employee IDs -");
console.log(res);
})
.catch((err) => {
console.log("Error in fetching employees: ",err);
})
})
.catch((err)=>{
console.log("Error in creating employees: ",err);
})
}
example()
.then (() => {
console.log("Ran the Prisma example successfully.")
})
.catch((e) => {
throw e
})
.finally(async () => {
await prisma.$disconnect()
})
Run the example:
$ node example.js
Expect output similar to the following:
Created 3 employees.
Fetched employee details sorted by employee IDs -
[
{
emp_id: 1,
emp_name: 'Jake',
emp_age: 24,
emp_email: '[email protected]'
},
{
emp_id: 2,
emp_name: 'Sam',
emp_age: 30,
emp_email: '[email protected]'
},
{
emp_id: 3,
emp_name: 'Tom',
emp_age: 22,
emp_email: '[email protected]'
}
]
Ran the Prisma example successfully.
Run the following command to start Prisma Studio:
npx prisma studio
Open http://localhost:5555 in your browser to see the table and data created.