Drizzle ORM — Better than Prisma?

import { pgTable, serial, varchar, boolean, timestamp, integer } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: varchar("email", { length: 255 }).unique().notNull(),
  password: varchar("password", { length: 255 }).notNull(),
  name: varchar("name", { length: 255 }),
  isAdmin: boolean("is_admin").default(false),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow().onUpdateNow(),
});

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  content: text("content"),
  published: boolean("published").default(false),
  authorId: integer("author_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").defaultNow(),
});

import { db } from "./db";
import { users, posts } from "./schema";
import { eq, and, or, like, desc, asc } from "drizzle-orm";

// Select all
const allUsers = await db.select().from(users);

// Select specific columns
const userNames = await db
  .select({ id: users.id, name: users.name })
  .from(users);

// Where clause
const admin = await db
  .select()
  .from(users)
  .where(eq(users.isAdmin, true));

// Complex where
const result = await db
  .select()
  .from(users)
  .where(
    and(
      eq(users.isAdmin, true),
      or(
        like(users.email, "%gmail%"),
        like(users.email, "%example%")
      )
    )
  );

// Ordering and limiting
const recent = await db
  .select()
  .from(posts)
  .orderBy(desc(posts.createdAt))
  .limit(10)
  .offset(0);

// Distinct
const distinctEmails = await db
  .selectDistinct({ email: users.email })
  .from(users);

// Inner join
const postsWithAuthors = await db
  .select()
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id));

// Left join
const usersWithPosts = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

// Multiple joins
const postsWithDetails = await db
  .select()
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .leftJoin(comments, eq(posts.id, comments.postId));

// Insert single
const newUser = await db
  .insert(users)
  .values({
    email: "alice@example.com",
    password: "hashed",
    name: "Alice",
  })
  .returning();

// Insert multiple
await db.insert(users).values([
  { email: "bob@example.com", password: "hashed", name: "Bob" },
  { email: "charlie@example.com", password: "hashed", name: "Charlie" },
]);

// Update
await db
  .update(users)
  .set({ name: "Alice Updated" })
  .where(eq(users.id, 1));

// Delete
await db.delete(users).where(eq(users.id, 1));

// Update with returning
const updated = await db
  .update(users)
  .set({ name: "New Name" })
  .where(eq(users.id, 1))
  .returning();

import { count, sum, avg, max, min, sql } from "drizzle-orm";

// Count
const userCount = await db
  .select({ count: count() })
  .from(users);

// Group by
const postsByAuthor = await db
  .select({
    authorId: posts.authorId,
    postCount: count(posts.id),
  })
  .from(posts)
  .groupBy(posts.authorId);

// Having
const activePlaces = await db
  .select({
    authorId: posts.authorId,
    postCount: count(posts.id).as("cnt"),
  })
  .from(posts)
  .groupBy(posts.authorId)
  .having((row) => count(posts.id).gt(5));

// Subqueries
const userWithMostPosts = await db
  .select()
  .from(users)
  .where(
    eq(
      users.id,
      sql`(SELECT ${posts.authorId} FROM ${posts} GROUP BY ${posts.authorId} ORDER BY COUNT(*) DESC LIMIT 1)`
    )
  );

await db.transaction(async (tx) => {
  // All queries in transaction
  const newUser = await tx.insert(users).values({
    email: "alice@example.com",
    password: "hashed",
  }).returning();

  await tx.insert(posts).values({
    title: "First Post",
    authorId: newUser[0].id,
  });

  // If anything throws, all is rolled back
});

// Execute raw SQL
const result = await db.execute(
  sql`SELECT * FROM users WHERE id = ${userId}`
);

// Type-safe raw SQL
const users = await db.execute<User>(
  sql`SELECT * FROM users WHERE email = ${email}`
);

// Mix SQL and schema
const data = await db.execute(
  sql`
    SELECT ${users.id}, ${users.name}, COUNT(${posts.id}) as post_count
    FROM ${users}
    LEFT JOIN ${posts} ON ${users.id} = ${posts.authorId}
    GROUP BY ${users.id}
  `
);

// drizzle/0001_initial.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  name VARCHAR(255),
  is_admin BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  published BOOLEAN DEFAULT false,
  author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT NOW()
);

// Apply migrations programmatically
import { migrate } from "drizzle-orm/node-postgres/migrator";

await migrate(db, { migrationsFolder: "./drizzle" });

// User repository
export const userRepository = {
  async findById(id: number) {
    return db.select().from(users).where(eq(users.id, id));
  },

  async findByEmail(email: string) {
    return db.select().from(users).where(eq(users.email, email));
  },

  async create(userData: { email: string; password: string; name?: string }) {
    return db.insert(users).values(userData).returning();
  },

  async update(id: number, updates: Partial<typeof users.$inferInsert>) {
    return db.update(users).set(updates).where(eq(users.id, id)).returning();
  },

  async delete(id: number) {
    return db.delete(users).where(eq(users.id, id));
  },

  async getAllWithPostCount() {
    return db
      .select({
        ...users,
        postCount: count(posts.id).as("post_count"),
      })
      .from(users)
      .leftJoin(posts, eq(users.id, posts.authorId))
      .groupBy(users.id);
  },
};