Prisma ORM with PostgreSQL

What is Prisma

I’ve come across Prisma for some time now and was eager to explore it as my preferred ORM for executing CRUD operations in my frontend application, all without the need for raw queries or any low-level ORM such as the Supabase JS Client, which allows you to programmatically create queries.

Essentially, Prisma includes a schema file named prisma.schema within your project. Inside this file, you’ll find all the models corresponding to your database tables. From the schema file, you can establish connections between entities and create types that define these entities and their relationships. Importantly, these types can be seamlessly imported into TypeScript, a significant advantage as it helps maintain synchronization between your backend and frontend models.

While I referred to it as an ORM, Prisma differs from the typical ORM pattern where you work with model classes and their associated methods. Instead, Prisma generates a comprehensive database schema that serves as the single source of truth for all your models, with everything meticulously defined in this file. For a more detailed explanation, you can explore further on their official website.

One of Prisma’s strengths is its ease of integration with various database systems, including:

PostgreSQL
MySQL
SQLite
CockroachDB
MSSQL

Installation

The first step when using Prisma Client is installing the @prisma/client npm package:

npm install @prisma/client

Follow the intregration steps as indicated on their website, in this case using a PostgeSQL hosted on Supabase.

Example Usage

Firstly we need to add the ENV vars required by Prisma which is the DATABASE_URL. This can be generated by creating a sample database on Supabase.
Secondly, create a Postgres DB and add 2 tables, book and author
Afterwards, we need to setup the prisma.schema file using introspection which creates a model schema from your existing DB.

datasource db {
  url      = env("DATABASE_URL")
  provider = "postgresql"
}

model Author {
  id          String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  name        String
  books  Book[]

  @@map("authors")
}

model Book {
  id          String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  name        String
  author_id   String?            @db.Uuid
  author      Image?             @relation(fields: [author_id], references: [id], onDelete: Restrict, onUpdate: Restrict)

  @@map("books")
}

Create a Book entity

import { PrismaClient } from '@prisma/client/edge'

const prisma = new PrismaClient()

const newBook = await prisma.book.create({
  data: {
    name: 'How to be a geek in 2023',
    author: {
         create: {
           title: 'W Mifsud Le Prevost',
         },
     },
  },
})

const users = await prisma.user.findMany()

The code above will create a book and also create an author on the fly and link it to the book

Read the book entity

import { PrismaClient } from '@prisma/client/edge'

const prisma = new PrismaClient()

const book = await prisma.book.findUnique({
  where: {
    id: 1,
  },
})

Update the book entity

import { PrismaClient } from '@prisma/client/edge'

const prisma = new PrismaClient()

const updatedBook = await prisma.book.update({
  where: {
    id: 1
  },
  data: {
    name: 'I love new tech',
  },
})

Deleting the book entity

import { PrismaClient } from '@prisma/client/edge'

const prisma = new PrismaClient()

await prisma.book.delete({
  where: {
    id: 1
  },
})

Pros of Prisma

Reverse Engineering for Existing Databases

Prisma makes it remarkably easy to create a schema file through reverse engineering from your existing database. This process, known as introspection, simplifies the setup.

Typescipt Models

Prisma generates a typescript model on build time using prisma generate in the node modules folder. Each time you change the schema file, be sure to run this command so that the types will be updated. More info can be found in their website.

type PostCreateInput = {
  id?: number
  title: string
  content?: string | null
  authorId: number
}

If you need to alter the auto generated models, you can always extend from them and add your own custom fields and types.

type MyNewPost = PostUncheckedCreateInput &  {
created_at: string
}

Migrations Made Easy

Prisma offers a robust migration mechanism, enabling developers to apply incremental updates to their database. By crafting migration scripts and committing them to their codebase, developers can effortlessly run pending migrations on their database using simple commands. For instance, npx prisma migrate dev is used during development, while npx prisma migrate is employed in production. Prisma maintains a record of applied migrations via a prisma_migrations table, ensuring seamless management of database changes.

User-Friendly Experience

Prisma provides comprehensive documentation that greatly simplifies code generation for fetching data. When dealing with complex queries, including referenced fields, I found ChatGPT to be immensely helpful in generating queries efficiently.

Built-In Validation

Prisma comes equipped with its validation functionality, allowing developers to connect it to validation libraries such as zod.

Prisma does not automatically include related fields like the Author’s name by default. Instead, these fields must be explicitly included using the include key when fetching data. This approach is sensible, as it avoids unnecessary processing unless specifically required, optimizing performance.

const book = await prisma.book.findUnique({
  where: {
    id: 1,
  },
  include: { author: true },
})

More info can be found here

Cons of Prisma

Performance Limitations

Like most Object-Relational Mapping (ORM) tools, Prisma doesn’t inherently generate optimized queries or consolidate logic into a single query for performance efficiency. Instead, it generates multiple internal queries, which are usually hidden from developers unless query logging is enabled. This approach can lead to performance bottlenecks, particularly when dealing with large datasets. In such cases, it’s advisable to avoid using an ORM and instead focus on crafting optimized, indexed, and aggregated queries or views, which can be executed either through Prisma or by leveraging the database package available.

Prisma with Data Proxy over Edge

Some users encounter issues when using Prisma with their Data Proxy service to run APIs over an Edge network. This is often related to connection pooling, which may not always perform optimally. Connectivity problems and resource exhaustion, such as running out of connections, can occur. Debugging such issues can be challenging. The upcoming tool called Accelerate from Prisma is promising and may address these problems. However, it’s worth noting that issues could also originate from external services, such as Supabase, used for hosting public PostgreSQL databases.

Many-to-Many Relationships

While Prisma is a powerful tool, handling many-to-many relationships can be less straightforward. Unlike document databases like MongoDB, relational databases like PostgreSQL don’t natively support many-to-many relationships. Instead, developers must create intermediary tables, often referred to as “matrix” or “associative” tables, to manage these relationships.

For example, if you have a scenario where a Book can have multiple Authors, you can define it in your schema. However, when generating the migration, Prisma automatically creates a table called _authors_book with columns named A and B to link the IDs together. Unfortunately, you cannot customize the table name or column names, which can be limiting. For more details, you can refer to Prisma’s documentation on conventions for relation tables in implicit many-to-many relations and this related GitHub issue.

model Author {
  id          String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  name        String
  
  @@map("authors")
}

model Book {
  id          String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  name        String

  authors Author[] @relation("authors_book")

  @@map("books")
}