What is Prisma?

Prisma is a type-safe ORM that acts as a middleware between your database and application. Prisma not only eliminates manual query writing (where you might have full control but face issues like manual connection handling, repetitive boilerplate, etc.), but it also eliminates problems found in traditional ORMs (discussed below).

Now you may ask, what is an ORM?

An Object-Relational Mapper (ORM) acts as a translator or middleware between two different languages. It helps you focus on building your application rather than worrying about writing efficient SQL queries, handling connections manually, and writing repetitive code. However, traditional ORMs have tradeoffs too, which is why many developers prefer Prisma.

How is Prisma better than other ORMs?

Traditional ORMs let you define your application models as classes. These classes are then mapped to tables, which is great because it eliminates the hassle of writing queries every time or messing up column names. But it comes with a catch. Your ORM model uses objects, but SQL uses tables. This different representation of data is often referred to as the "Object-Relational Impedance Mismatch," which raises problems like:

1. One object might contain other objects, but in SQL, you need to perform multiple joins of tables.
2. Objects have concepts of inheritance, but in SQL, we often have to create one giant table.
3. Relationships are directional with objects, but for SQL, they are bidirectional, so you need to map these foreign keys back into object references.

These might not seem like problems for a developer, but under the hood, the ORM performs multiple JOINS which can slow down application performance and raise the n+1 problem (hitting the database 100 times for 100 items).

Prisma solves this problem by using a schema (which they refer to as the "single source of truth") as middleware between your database and application. This eliminates the object-relational impedance mismatch, generates a TypeScript client specifically for your database, and even executes queries in batches (which eliminates the n+1 problem) to ensure efficiency.

How Prisma got even better than before (v6.0.0+)

Previously, Prisma used a Rust-based query engine as middleware between your database and application, which was bundled with Rust binaries. Later, Prisma (from version 6) moved away from Rust and rebuilt itself with a TypeScript/WASM compiler. This might sound like a downgrade in performance because Rust is considered fast, but getting rid of Rust not only removed the binary overhead, it actually resulted in up to 3.4x faster queries (by removing cross-language serialization) and a 90% smaller bundle size (from ~14mb to 1.6mb). This is a drastic improvement for both performance and size.

About Prisma

To interact with a Prisma project, first install the Prisma CLI, which is used to initialize new projects, generate the Prisma Client, and analyze existing databases.

Run this command in your project to download the Prisma CLI (use your desired package manager if you aren't using npm):

npm install prisma --save-dev
npx prisma
npm install @prisma/client

Prisma Schema

This is the main configuration file for your Prisma setup. Here we define:

• The data source (e.g., PostgreSQL or MongoDB)
• Generators, which specify the config for generating the Prisma Client and the output directory
• Data model definitions, which specify your application models and generate the Client based on them.

# In case you want to use PostgreSQL
npm install @prisma/adapter-pg pg dotenv
npx prisma init --db

Here is an example of a schema.prisma file:

// generator determines which assets are created
generator client {
    provider = "prisma-client"
    // generated client output directory
    output   = "../lib/generated/prisma"
}

// data source
datasource db {
    provider = "postgresql"
    // url is deprecated in the schema file directly; now you can put the database URL in your .env file
    // and Prisma will automatically add it for you (given that dotenv is configured).
}

// data enum
enum Role {
    USER
    ADMIN
}

// data models
model User {
    id             String     @id @default(cuid())
    name           String     @map("full_name")
    role           Role       @default(USER)
    posts          Post[]
    @@map("users")
}
// @@map maps the 'User' object to the "users" table in the underlying database.
// @map maps the 'name' field to the "full_name" column in the underlying database.

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  published Boolean  @default(false)
  title     String   @db.VarChar(255)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  String
}
// @id defines primary key

For more information on field types, modifiers, and attributes, visit the Official Documentation Page.

Prisma Client

This is the auto-generated (based on your schema file), type-safe query builder used in your application. It allows you to read and write data from your database using plain JavaScript and TypeScript objects.

The command below will generate the Prisma Client based on your schema file:

npx prisma generate

After the Prisma Client is generated:

1. Import Prisma Client in your application (for driver adapter, not edge):

import { PrismaClient } from "../lib/generated/prisma/client"
// import it from your specified output location in the prisma generator (schema file)
import { PrismaPg } from "@prisma/adapter-pg";
// import PostgreSQL driver adapter

const adapter = new PrismaPg({
    connectionString: process.env.DATABASE_URL,
});
const prisma = new PrismaClient({ adapter });

2. Insert user data into the database:

prisma.user.create({
    data: {
        name: "Soumik",
        posts: {
            create: {
                title: "About Prisma",
                published: true,
            },
        },
    },
});

3. Get posts from the database:

const userPost = prisma.post.findMany({
    where: { published: true },
})

Prisma Migrate

In case you want to change your schema file by adding new columns, new tables, or changing any attributes, Prisma Migrate will help you update your database while keeping a history of it (much like Git does).

Make sure you initialize it once at the start:

npx prisma migrate dev --name init

Let's say you add another field to your schema:

model User {
    id             String     @id @default(cuid())
    name           String     @map("full_name")
    email          String?    @unique  // new field added
    role           Role       @default(USER)
    posts          Post[]
    @@map("users")
}

In order to update your database (add email), you have to run:

npx prisma migrate dev --name added_email

This will generate a migration folder (at the path configured in prisma.config.ts) and generate a .sql file which runs to update your underlying database.

Originally prisma db push works as well, but it doesn't keep a history of changes, which can be a problem in production or collaboration.

Prisma Studio

This isn't a core functionality in Prisma, but it's useful since it is a GUI (Graphical User Interface) for viewing and editing data. There isn't much to talk about here, but you can find it in the Studio tab in your Prisma Console. If you are using VS Code, you can install the Prisma VS Code extension as well.

For in-depth information, you can follow the Prisma official documentation here.

NOTE: I have used npm as an example, but you can use pnpm, yarn, or bun as per your requirements. Here is how you can use them: Link.