Prisma for beginners

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.

1. Introspect (SQL → Data Model)
2. 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.