About Prisma
Prisma is an ORM with an introspect and migrations feature that reflects changes across SQL definitions and GraphQL Types.
When you're working with GraphQL , it can become tedious to make changes to your Type Schema and subsequently make those very same changes to your Database Schema. Therefore it becomes a necessity to find a tool that can persist these changes.
However , Prisma needn't always be used with GraphQL. It's a great tool on its own even if you're building standard REST APIs.
Prerequisites
- PostGRES Installed locally (psql)
- NodeJS Installed
- npm Installed
Setting up Prisma
Initialise a Node Project using npm
.
npm init -y
Install the Prisma CLI Tool that is used to generate the schema.prisma
file.
npm install @prisma/cli --save-dev
Install the Prisma Client that is used to interface with the Database
npm install @prisma/client
Let's generate a Prisma project next.
npx prisma init
This generates a prisma folder containing the schema.prisma
file.
Prisma differs in the sense that it offers its very own schema.prisma
file with a specialised syntax for defining your schema. However , we'll not be editing this schema file now. We'll generate a schema using SQL first.
We've also generated a .env
file. You can edit the DATABASE_URL
variable to connect to your PostGRES Database.
#.env
DATABASE_URL="postgresql://username:password@localhost:5432/dbname?schema=public"
Replace username
, password
with your PostGRES credentials and replace dbname
with any name you wish to give to your Database. (Database does not have to be created yet) I will name this Database as 'test'.
Migrations and Introspection
Prisma offers two approaches to persist changes across Type Schema and Database Schema.
- Introspect (SQL → Data Model)
- Migration (Data Model → SQL)
Migration
Let's generate a Data Model in our schema.prisma
file first , using SQL.
Login to psql. Create the Database first.
-- Create a Database
CREATE DATABASE test
Connect to the database using \c test
. Proceed to execute the following SQL commands to generate our users
and posts
table along with our custom user type.
-- Create a custom type
CREATE TYPE "user_role_enum" AS ENUM ('user', 'admin', 'superadmin');
-- Create a table
CREATE TABLE "users"(
"id" BIGSERIAL PRIMARY KEY NOT NULL,
"name" VARCHAR(255) NOT NULL,
"email" VARCHAR(255) UNIQUE NOT NULL,
"role" user_role_enum NOT NULL DEFAULT('user')
);
-- Create a table
CREATE TABLE "posts"(
"id" BIGSERIAL PRIMARY KEY NOT NULL,
"title" VARCHAR(255) NOT NULL,
"body" TEXT,
"userId" INTEGER NOT NULL,
FOREIGN KEY ("userId") REFERENCES "users"("id")
);
Generating Data Models using Introspect
Once the table has been succesfully created , run the introspect command to generate Prisma Models.
npx prisma introspect
This will generate an equivalent Data Model in the schema.prisma file.
//schema.prisma
model posts {
id Int @id @default(autoincrement())
title String
body String?
userId Int
users users @relation(fields: [userId], references: [id])
}
model users {
id Int @id @default(autoincrement())
name String
email String @unique
role user_role_enum @default(user)
posts posts[]
}
enum user_role_enum {
user
admin
superadmin
}
As you can see , with the introspect
feature , we've managed to convert our SQL commands into an equivalent Data Model.
Prisma also provides a GUI tool called prisma studio to view and interact with our Database.
npx prisma studio
This will open the GUI tool in your browser.
Migrations in Prisma
We've seen how we can convert our SQL commands to Data Models. Now let's look at how we can do the opposite. Delete your Database.
DROP DATABASE test;
This would have deleted the Database. Enter \l
into psql to check if it has been successfully deleted.
Migrations are used to generate SQL commands for given Prisma Data Models. Describe your model in the schema.prisma
file as necessary.
model User {
@@map(name: "users")
id Int @default(autoincrement()) @id
uuid String @default(uuid()) @unique
email String @unique
name String?
role UserRole @default(USER)
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
posts Post[]
}
model Post {
@@map(name: "posts")
id Int @default(autoincrement()) @id
uuid String @default(uuid()) @unique
title String
body String?
user User @relation(fields: [userId], references: [id])
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
userId Int
}
enum UserRole {
USER
ADMIN
SUPER_ADMIN
}
You can read more about the Data Model's syntax here
The Model above is a slightly modified version of the model we generated previously.
Let's generate a table and its equivalent SQL commands next.
Run the following command to generate a Prisma migration.
npx prisma migrate dev --preview-feature
You can give your migration any name you wish to. This will generate a migrations
folder with all the necessary documentation and commands that were run in sequence to generate the SQL tables.
Migration can create a Database if the Database doesn't exist already
We can check if our tables were successfully created using psql
.
Therefore we've looked at the two main approaches to persisting changes in schema.
Finally , Let's look at the Prisma Client.
Prisma Client
Run the following command to generate a Prisma Client
npx prisma generate
The Prisma Client helps us interact with our PostGRES Database.
const { PrismaClient } = require("@prisma/client");
const prisma = new PrismaClient();
async function main() {
const users = await prisma.users.findMany();
console.log(users);
}
main();
We can send queries to perform CRUD
operations on our Database. Read more about these operations here.
Note: Don't forget to generate the Prisma Client every time an introspection or migration is performed.
Top comments (0)