Drizzle ORM

Drizzle ORM is a modern, type-safe SQL ORM for TypeScript.
It's built to be lightweight, explicit, and compatible with multiple databases,
while giving you full control over SQL.

Drizzle Client

Drizzle doesn't generate a client like Prisma - instead, you define your schema in TypeScript and pass it to a drizzle() instance that manages queries.

Copyimport { drizzle } from "drizzle-orm/postgres-js"
import postgres from "postgres"
 
import { env } from "@/env"
 
import * as schema from "./schema"
 
const globalForDb = globalThis as unknown as {
  conn: postgres.Sql | undefined
}
 
const conn = globalForDb.conn ?? postgres(env.DATABASE_URL)
if (env.NODE_ENV !== "production") globalForDb.conn = conn
 
export const db = drizzle(conn, { schema })

Here:

• postgres is the database driver
• schema is imported from your Drizzle schema definition
• db is your Drizzle instance - similar to Prisma's PrismaClient

Defining a Schema

Unlike Prisma's .prisma file, Drizzle schemas are written directly in TypeScript. Below is the default schema for a Lx2 application when initialized with Drizzle:

Copyimport { sql } from "drizzle-orm"
import { index, pgTableCreator } from "drizzle-orm/pg-core"
 
export const createTable = pgTableCreator((name) => `drizzle-test_${name}`)
 
export const post = createTable(
  "post",
  (d) => ({
    id: d.integer().primaryKey().generatedByDefaultAsIdentity(),
    name: d.varchar({ length: 255 }).notNull(),
    createdAt: d
      .timestamp({ withTimezone: true })
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: d.timestamp({ withTimezone: true }).$onUpdate(() => new Date()),
  }),
  (t) => [index("post_name_idx").on(t.name)],
)

Key Concepts:

• pgTableCreator is a custom table creator function used to prefix table names
• Column types (integer, varchar, timestamp) map to SQL types
• Constraints (primaryKey, notNull) enforce rules
• No separate migration file is required for schema definition - migrations are generated from these TypeScript files

Basic CRUD Operations

• Create:

Copyimport { post as postTable } from "@/server/db/schema"
 
const newPost = await db
  .insert(postTable)
  .values({
    id: 1,
    name: "New Post",
  })
  .returning()
console.log(newPost)

• Read:

Copyimport { post as postTable } from "@/server/db/schema"
 
const posts = await db.query.post.findMany()
console.log(posts)
 
const post = await db.query.post.findFirst({
  where: eq(postTable.id, 1),
})
console.log(post)

• Update:

Copyimport { post as postTable } from "@/server/db/schema"
 
const updatedPost = await db
  .update(postTable)
  .set({
    name: "Updated Post",
  })
  .where(eq(postTable.id, 1))
  .returning()
console.log(updatedPost)

• Delete:

Copyimport { post as postTable } from "@/server/db/schema"
 
const deletedPost = await db
  .delete(postTable)
  .where(eq(postTable.id, 1))
  .returning()
console.log(deletedPost)

Migrations

Drizzle migrations are code-based and generated using the CLI.

Common Commands:

• pnpm db:generate -> Generate SQL migrations from your TypeScript schema
• pnpm db:migrate -> Apply pending migrations to the database
• pnpm db:push -> Apply migrations directly to the database
• pnpm db:studio -> Visual database browser

Workflow:

1. Modify your TypeScript schema (schema.ts)

2. Run:

   Copypnpm db:generate

3. Apply the migrations:

   Copypnpm db:migrate

4. Your changes are not in sync with the database

Drizzle Studio

Drizzle Studio is a browser UI for your database. To access it, run:

Copypnpm db:studio

The command will return a URL to access Drizzle Studio in your browser, where you can:

• Browse tables
• View data
• Run queries
• Edit records

Useful Resources