Prisma vs Drizzle: Which ORM Should You Choose for Your SaaS?

// Prisma Schemadatasource db {  provider = "postgresql"  url      = env("DATABASE_URL")}generator client {  provider = "prisma-client-js"}model User {  id        String   @id @default(cuid())  email     String   @unique  name      String?  createdAt DateTime @default(now())  updatedAt DateTime @updatedAt  posts     Post[]  profile   Profile?}model Post {  id        String   @id @default(cuid())  title     String  content   String?  published Boolean  @default(false)  createdAt DateTime @default(now())  author    User     @relation(fields: [authorId], references: [id])  authorId  String}model Profile {  id     String @id @default(cuid())  bio    String  user   User   @relation(fields: [userId], references: [id])  userId String @unique}

import { relations } from 'drizzle-orm';import { boolean, pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';export const users = pgTable('users', {  id: text('id')    .primaryKey()    .$defaultFn(() => crypto.randomUUID()),  email: text('email').notNull().unique(),  name: text('name'),  createdAt: timestamp('created_at').defaultNow().notNull(),  updatedAt: timestamp('updated_at').defaultNow().notNull()});export const posts = pgTable('posts', {  id: text('id')    .primaryKey()    .$defaultFn(() => crypto.randomUUID()),  title: text('title').notNull(),  content: text('content'),  published: boolean('published').default(false).notNull(),  createdAt: timestamp('created_at').defaultNow().notNull(),  authorId: text('author_id')    .notNull()    .references(() => users.id)});export const profiles = pgTable('profiles', {  id: text('id')    .primaryKey()    .$defaultFn(() => crypto.randomUUID()),  bio: text('bio').notNull(),  userId: text('user_id')    .notNull()    .unique()    .references(() => users.id)});// Define relations separatelyexport const usersRelations = relations(users, ({ many, one }) => ({  posts: many(posts),  profile: one(profiles)}));export const postsRelations = relations(posts, ({ one }) => ({  author: one(users, { fields: [posts.authorId], references: [users.id] })}));export const profilesRelations = relations(profiles, ({ one }) => ({  user: one(users, { fields: [profiles.userId], references: [users.id] })}));

import { PrismaClient } from '@prisma/client';const prisma = new PrismaClient();// Find a user with their postsconst userWithPosts = await prisma.user.findUnique({  where: { email: 'john@example.com' },  include: {    posts: {      where: { published: true },      orderBy: { createdAt: 'desc' }    },    profile: true  }});// Create a user with a postconst newUser = await prisma.user.create({  data: {    email: 'jane@example.com',    name: 'Jane',    posts: {      create: {        title: 'My First Post',        content: 'Hello World!'      }    }  },  include: { posts: true }});// Update with nested writesconst updatedUser = await prisma.user.update({  where: { id: 'user-id' },  data: {    name: 'Updated Name',    posts: {      updateMany: {        where: { published: false },        data: { published: true }      }    }  }});// Complex filteringconst filteredPosts = await prisma.post.findMany({  where: {    OR: [      { title: { contains: 'prisma', mode: 'insensitive' } },      { content: { contains: 'prisma', mode: 'insensitive' } }    ],    AND: {      published: true    }  }});

import { and, desc, eq, ilike, or } from 'drizzle-orm';import { db } from './db';import { posts, profiles, users } from './schema';// Find a user by emailconst user = await db  .select()  .from(users)  .where(eq(users.email, 'john@example.com'))  .limit(1);// Join users with postsconst usersWithPosts = await db  .select({    userId: users.id,    userName: users.name,    postTitle: posts.title  })  .from(users)  .leftJoin(posts, eq(users.id, posts.authorId))  .where(eq(posts.published, true))  .orderBy(desc(posts.createdAt));// Insert a userconst newUser = await db  .insert(users)  .values({    email: 'jane@example.com',    name: 'Jane'  })  .returning();// Update usersawait db  .update(users)  .set({ name: 'Updated Name' })  .where(eq(users.id, 'user-id'));// Complex filteringconst filteredPosts = await db  .select()  .from(posts)  .where(    and(      or(ilike(posts.title, '%prisma%'), ilike(posts.content, '%prisma%')),      eq(posts.published, true)    )  );

import { db } from './db';// Drizzle's relational queries - similar to Prisma's includeconst userWithPosts = await db.query.users.findFirst({  where: eq(users.email, 'john@example.com'),  with: {    posts: {      where: eq(posts.published, true),      orderBy: desc(posts.createdAt)    },    profile: true  }});// Find many with relationsconst allUsersWithPosts = await db.query.users.findMany({  with: {    posts: true  }});

# Generate migration from schema changesnpx prisma migrate dev --name add_user_table# Apply migrations in productionnpx prisma migrate deploy# Reset database (dev only)npx prisma migrate reset

CREATE TABLE "User" (    "id" TEXT NOT NULL,    "email" TEXT NOT NULL,    "name" TEXT,    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,    "updatedAt" TIMESTAMP(3) NOT NULL,    CONSTRAINT "User_pkey" PRIMARY KEY ("id"));CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

# Generate migrationnpx drizzle-kit generate --name add_user_table# Push schema directly (dev only)npx drizzle-kit push# Apply migrationsnpx drizzle-kit migrate

import { defineConfig } from 'drizzle-kit';export default defineConfig({  dialect: 'postgresql',  schema: './src/db/schema.ts',  out: './drizzle',  dbCredentials: {    url: process.env.DATABASE_URL!  }});

CREATE TABLE "users" (  "id" text PRIMARY KEY NOT NULL,  "email" text NOT NULL,  "name" text,  "created_at" timestamp DEFAULT now() NOT NULL,  "updated_at" timestamp DEFAULT now() NOT NULL,  CONSTRAINT "users_email_unique" UNIQUE("email"));

// Optimizing Prisma for serverlessimport { PrismaClient } from '@prisma/client';const globalForPrisma = globalThis as unknown as {  prisma: PrismaClient | undefined;};export const prisma =  globalForPrisma.prisma ??  new PrismaClient({    log: process.env.NODE_ENV === 'development' ? ['query'] : []  });if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

// Drizzle with Neon serverlessimport { neon } from '@neondatabase/serverless';import { drizzle } from 'drizzle-orm/neon-http';import * as schema from './schema';const sql = neon(process.env.DATABASE_URL!);export const db = drizzle(sql, { schema });// Or with node-postgres for traditional serversimport { Pool } from 'pg';import { drizzle } from 'drizzle-orm/node-postgres';const pool = new Pool({ connectionString: process.env.DATABASE_URL });export const db = drizzle(pool, { schema });

import { Post, Prisma, User } from '@prisma/client';// Generated typestype UserWithPosts = Prisma.UserGetPayload<{  include: { posts: true };}>;// Input types for queriestype UserCreateInput = Prisma.UserCreateInput;type UserWhereInput = Prisma.UserWhereInput;// Use the validator for reusable query objectsconst userWithPostsQuery = Prisma.validator<Prisma.UserDefaultArgs>()({  include: { posts: true }});

import { InferInsertModel, InferSelectModel } from 'drizzle-orm';import { posts, users } from './schema';// Inferred typestype User = InferSelectModel<typeof users>;type NewUser = InferInsertModel<typeof users>;type Post = InferSelectModel<typeof posts>;// Use with queriesconst user: User = await db  .select()  .from(users)  .limit(1)  .then((r) => r[0]);

// Prisma excels at complex nested operationsconst result = await prisma.user.create({  data: {    email: 'user@example.com',    profile: { create: { bio: 'Hello!' } },    posts: {      createMany: {        data: [{ title: 'Post 1' }, { title: 'Post 2' }]      }    }  },  include: {    profile: true,    posts: true  }});

// Drizzle excels at SQL-like operationsconst result = await db  .select({    category: posts.category,    count: sql<number>`count(*)`.as('count'),    avgLength: sql<number>`avg(length(${posts.content}))`.as('avg_length')  })  .from(posts)  .where(eq(posts.published, true))  .groupBy(posts.category)  .having(sql`count(*) > 5`)  .orderBy(desc(sql`count(*)`));