Drizzle ORM Reference: Schema, Queries, Relational API, Migrations & Edge Runtimes

import { pgTable, serial, text, varchar, integer, boolean,
         timestamp, uuid, index, uniqueIndex } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

// Define tables in schema.ts:
export const users = pgTable("users", {
  id:        serial("id").primaryKey(),
  name:      varchar("name", { length: 100 }).notNull(),
  email:     text("email").notNull().unique(),
  age:       integer("age"),
  verified:  boolean("verified").notNull().default(false),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  uuid:      uuid("uuid").default(sql`gen_random_uuid()`),
}, (table) => ({
  emailIdx:  uniqueIndex("users_email_idx").on(table.email),
  nameIdx:   index("users_name_idx").on(table.name),
}));

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

// Infer TypeScript types from schema (no separate type definitions):
import type { InferSelectModel, InferInsertModel } from "drizzle-orm";
type User       = InferSelectModel<typeof users>;     // row shape for SELECT
type NewUser    = InferInsertModel<typeof users>;     // shape for INSERT (id optional)
type UpdateUser = Partial<Omit<NewUser, "id">>;      // shape for UPDATE

import { drizzle } from "drizzle-orm/node-postgres";
import { eq, and, or, like, gte, lte, isNull, desc, asc, count } from "drizzle-orm";
import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle(pool, { schema: { users, posts } });   // schema enables relational API

// SELECT:
const allUsers = await db.select().from(users);
const alice = await db.select().from(users).where(eq(users.email, "alice@example.com")).limit(1);

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

// WHERE with multiple conditions:
const results = await db.select().from(users).where(
  and(
    eq(users.verified, true),
    gte(users.age, 18),
    like(users.name, "%Alice%"),
  )
).orderBy(desc(users.createdAt)).limit(20).offset(40);

// JOIN:
const postsWithAuthors = await db
  .select({ post: posts, author: { name: users.name, email: users.email } })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(eq(posts.published, true));

// INSERT:
const [newUser] = await db.insert(users)
  .values({ name: "Alice", email: "alice@example.com", verified: true })
  .returning();   // returns the inserted row with generated id

// Bulk insert:
await db.insert(users).values([
  { name: "Bob", email: "bob@example.com" },
  { name: "Carol", email: "carol@example.com" },
]);

// UPDATE:
const [updated] = await db.update(users)
  .set({ name: "Alice Smith", verified: true })
  .where(eq(users.id, 1))
  .returning();

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

// COUNT:
const [{ total }] = await db.select({ total: count() }).from(users);

import { relations } from "drizzle-orm";

// Define relations (for the relational API only — not required for joins):
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));
export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
}));

// Relational API — nested queries (uses JOINs under the hood):
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: {
      where: eq(posts.published, true),
      orderBy: desc(posts.createdAt),
      limit: 5,
      columns: { title: true, createdAt: true },  // select specific columns
    },
  },
  where: eq(users.verified, true),
  orderBy: asc(users.name),
  limit: 10,
});
// usersWithPosts[0].posts is typed as array of { title: string, createdAt: Date }

// Find one (returns undefined if not found):
const user = await db.query.users.findFirst({
  where: eq(users.email, "alice@example.com"),
  with: { posts: true },
});

// Transactions (all queries share the same connection):
const result = await db.transaction(async (tx) => {
  const [user] = await tx.insert(users)
    .values({ name: "Alice", email: "alice@example.com" })
    .returning();
  await tx.insert(posts)
    .values({ title: "First Post", authorId: user.id });
  return user;
});
// If any query throws, the transaction is automatically rolled back

// npm install -D drizzle-kit

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

// Generate migration from schema changes:
// npx drizzle-kit generate
// Creates: drizzle/0001_add_users_table.sql + drizzle/meta/

// Apply migrations (programmatic — run at app startup or in CI):
import { migrate } from "drizzle-orm/node-postgres/migrator";
await migrate(db, { migrationsFolder: "./drizzle" });

// drizzle-kit push (dev only — bypasses migration files, syncs schema directly):
// npx drizzle-kit push
// Use for: rapid prototyping, local dev. Never in production.

// drizzle-kit studio (visual query browser):
// npx drizzle-kit studio

// Common commands:
// npx drizzle-kit generate   — generate SQL migration from schema diff
// npx drizzle-kit migrate    — apply pending migrations
// npx drizzle-kit push       — sync schema without migration files (dev only)
// npx drizzle-kit introspect — generate schema from existing database

// Turso (LibSQL) — SQLite at the edge:
import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";

const client = createClient({
  url: process.env.TURSO_DATABASE_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN,
});
const db = drizzle(client, { schema });

// Neon serverless (HTTP-based, works in edge functions):
import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";

const sql_client = neon(process.env.DATABASE_URL!);
const db = drizzle(sql_client, { schema });

// Neon serverless transactions require pool (not HTTP client):
import { drizzle } from "drizzle-orm/neon-serverless";
import { Pool } from "@neondatabase/serverless";

// Cloudflare D1 (SQLite in Workers):
// wrangler.toml: [[d1_databases]] binding = "DB"
import { drizzle } from "drizzle-orm/d1";
export default {
  async fetch(request: Request, env: Env) {
    const db = drizzle(env.DB, { schema });
    const users = await db.select().from(usersTable).all();
    return Response.json(users);
  },
};

// SQLite (local / tests):
import { drizzle } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";
const sqlite = new Database(":memory:");   // in-memory for tests
const db = drizzle(sqlite, { schema });