Drizzle ORM

bun add drizzle-orm                # + a driver (below)
bun add -d drizzle-kit             # CLI: migrations, studio
bun add drizzle-zod zod            # optional: zod schemas
# npm i drizzle-orm pg  ·  pnpm add -D drizzle-kit

import { drizzle } from "drizzle-orm/bun-sql";
import * as schema from "./schema";
 
const url = process.env.DATABASE_URL;
if (!url) throw new Error("DATABASE_URL is not set");
 
export const db = drizzle({
  connection: { url, max: 10 },
  schema,                  // enables db.query.*
  casing: "snake_case",    // authorId -> author_id
  logger: process.env.NODE_ENV !== "production",
});
export type DB = typeof db;

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import * as schema from "./schema";
 
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
});
export const db = drizzle({ client: pool, schema });
 
// postgres.js: drizzle({ client: postgres(url), schema })

import { defineConfig } from "drizzle-kit";
 
export default defineConfig({
  dialect: "postgresql",
  schema: "./src/db/schema/index.ts", // file, dir or glob
  out: "./drizzle",                  // migrations folder
  casing: "snake_case",
  dbCredentials: { url: process.env.DATABASE_URL! },
  strict: true,      // push asks before every statement
  verbose: true,     // push prints the SQL
});

import { sql } from "drizzle-orm";
import {
  boolean, check, index, integer, jsonb, pgEnum,
  pgTable, primaryKey, text, uuid, varchar,
} from "drizzle-orm/pg-core";
import { timestamps } from "./columns";
 
export const role = pgEnum("role", ["admin", "member"]);
 
export const users = pgTable("users", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  publicId: uuid().notNull().unique().defaultRandom(),
  email: varchar({ length: 320 }).notNull().unique(),
  name: text().notNull(),
  role: role().notNull().default("member"),
  prefs: jsonb().$type<{ theme: "light" | "dark" }>(),
  ...timestamps,
}, (t) => [
  index("users_name_idx").on(sql`lower(${t.name})`),
]);
 
export const posts = pgTable("posts", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  authorId: integer().notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  title: text().notNull(),
  slug: text().notNull()
    .$defaultFn(() => crypto.randomUUID().slice(0, 8)),
  published: boolean().notNull().default(false),
  views: integer().notNull().default(0),
  ...timestamps,
}, (t) => [
  index("posts_author_idx")
    .on(t.authorId, t.createdAt.desc()),
  index("posts_live_idx").on(t.slug)
    .where(sql`${t.deletedAt} is null`),
  check("posts_views_ck", sql`${t.views} >= 0`),
]);
 
export const tags = pgTable("tags", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: text().notNull().unique(),
});
 
export const postTags = pgTable("post_tags", {
  postId: integer().notNull()
    .references(() => posts.id, { onDelete: "cascade" }),
  tagId: integer().notNull()
    .references(() => tags.id, { onDelete: "cascade" }),
}, (t) => [primaryKey({ columns: [t.postId, t.tagId] })]);

import { relations } from "drizzle-orm";
import { postTags, posts, tags, users } from "./tables";
 
export const usersRelations = relations(
  users,
  ({ many }) => ({ posts: many(posts) }),
);
 
export const postsRelations = relations(
  posts,
  ({ one, many }) => ({
    author: one(users, {
      fields: [posts.authorId],
      references: [users.id],
    }),
    postTags: many(postTags),
  }),
);
 
export const postTagsRelations = relations(
  postTags,
  ({ one }) => ({
    post: one(posts, {
      fields: [postTags.postId],
      references: [posts.id],
    }),
    tag: one(tags, {
      fields: [postTags.tagId],
      references: [tags.id],
    }),
  }),
);

import { defineRelations } from "drizzle-orm";
 
export const relations = defineRelations(
  { users, posts },
  (r) => ({
    users: { posts: r.many.posts() },
    posts: {
      author: r.one.users({
        from: r.posts.authorId,
        to: r.users.id,
      }),
    },
  }),
);
// db.query.users.findMany({ where: { id: 1 },
//   orderBy: { id: "desc" }, with: { posts: true } })

bunx drizzle-kit generate --name add_posts # read the SQL
bunx drizzle-kit migrate
bunx drizzle-kit push --strict --verbose     # dev only
bunx drizzle-kit studio --port 4983

import { drizzle } from "drizzle-orm/bun-sql";
import { migrate } from "drizzle-orm/bun-sql/migrator";
 
const db = drizzle(process.env.DATABASE_URL!);
await migrate(db, { migrationsFolder: "./drizzle" });
await db.$client.close();

import {
  and, asc, desc, eq, ilike, isNull,
} from "drizzle-orm";
 
const all = await db.select().from(users);  // User[]
 
const [one] = await db
  .select({ id: users.id, email: users.email })
  .from(users)
  .where(eq(users.email, "ada@example.com"))
  .limit(1);                  // { id; email } | undefined
 
const page = await db
  .select()
  .from(posts)
  .where(
    and(
      eq(posts.published, true),
      isNull(posts.deletedAt),
      q ? ilike(posts.title, `%${q}%`) : undefined,
    ),
  )
  .orderBy(desc(posts.createdAt), asc(posts.id))
  .limit(20)
  .offset(40);

const [ada] = await db
  .insert(users)
  .values({ email: "ada@example.com", name: "Ada" })
  .returning();                     // full row
 
await db.insert(users).values([     // multi-row
  { email: "bob@example.com", name: "Bob" },
  { email: "cy@example.com", name: "Cy", role: "admin" },
]);
 
const bumped = await db
  .update(posts)
  .set({ views: sql`${posts.views} + 1` })
  .where(eq(posts.id, 1))
  .returning({ id: posts.id, views: posts.views });
 
await db.delete(posts).where(inArray(posts.id, [4, 5]));

await db.insert(tags).values({ name })
  .onConflictDoNothing({ target: tags.name });
 
await db
  .insert(users)
  .values(rows)                       // one or many
  .onConflictDoUpdate({
    target: users.email,              // or [a, b]
    set: { name: sql`excluded.name` },
    setWhere: sql`${users.deletedAt} is null`,
  })
  .returning({ id: users.id });

import { alias } from "drizzle-orm/pg-core";
 
const rows = await db
  .select({ title: posts.title, author: users.name })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .where(eq(users.role, "admin"));
 
const all = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id));
// { users: User; posts: Post | null }[]
 
const t = alias(tags, "t");           // self-join / rename
const tagged = await db
  .select({ post: posts.title, tag: t.name })
  .from(posts)
  .innerJoin(postTags, eq(postTags.postId, posts.id))
  .innerJoin(t, eq(t.id, postTags.tagId));

const authors = await db.query.users.findMany({
  columns: { id: true, name: true },
  where: (u, { eq }) => eq(u.role, "admin"),
  orderBy: (u, { asc }) => [asc(u.name)],
  limit: 10,
  with: {
    posts: {
      columns: { title: true },
      where: (p, { eq }) => eq(p.published, true),
      with: { postTags: { with: { tag: true } } },
    },
  },
});
// authors[0]?.posts[0]?.postTags[0]?.tag.name
 
const post = await db.query.posts.findFirst({
  where: eq(posts.slug, "hello"),
  with: { author: true },
});           // (Post & { author: User }) | undefined

import {
  avg, count, countDistinct, gt, max, sql, sum,
} from "drizzle-orm";
 
const perAuthor = await db
  .select({
    authorId: posts.authorId,
    n: count(),                  // number
    views: sum(posts.views),     // string | null
    avgViews: avg(posts.views),  // string | null
    last: max(posts.createdAt),
  })
  .from(posts)
  .groupBy(posts.authorId)
  .having(({ n }) => gt(n, 5));
 
const live = await db.$count(
  posts,
  eq(posts.published, true),
);                               // number
const authors = countDistinct(posts.authorId);

const ranked = await db
  .select({
    id: posts.id,
    rank: sql<number>`ts_rank(
      to_tsvector('english', ${posts.title}),
      websearch_to_tsquery('english', ${q})
    )`.mapWith(Number).as("rank"),
  })
  .from(posts)
  .orderBy(desc(sql`rank`));
 
const res = await db.execute(sql`select now() as now`);

const postById = db
  .select()
  .from(posts)
  .where(eq(posts.id, sql.placeholder("id")))
  .prepare("post_by_id");            // named: server-side
 
const byRole = db.query.users
  .findMany({
    where: (u, { eq }) =>
      eq(u.role, sql.placeholder("role")),
    limit: sql.placeholder("limit"),
  })
  .prepare("users_by_role");
 
const [p] = await postById.execute({ id: 1 });
const admins = await byRole.execute({
  role: "admin",
  limit: 10,
});

const result = await db.transaction(async (tx) => {
  const [src] = await tx
    .select({ views: posts.views })
    .from(posts)
    .where(eq(posts.id, from))
    .for("update");                   // row lock
  if (!src || src.views < 10) tx.rollback(); // throws
  await tx.update(posts)
    .set({ views: sql`${posts.views} - 10` })
    .where(eq(posts.id, from));
  return tx.transaction(async (sp) => // SAVEPOINT
    sp.$count(posts),
  );
}, { isolationLevel: "serializable" });

import type {
  InferInsertModel, InferSelectModel,
} from "drizzle-orm";
import {
  createInsertSchema, createSelectSchema, createUpdateSchema,
} from "drizzle-zod";
import { z } from "zod";
 
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
type Same = InferSelectModel<typeof users>; // User
type Role = User["role"];      // "admin" | "member"
 
export const insertUser = createInsertSchema(users, {
  email: (s) => s.email(),
  name: (s) => s.trim().min(1).max(100),
}).pick({ email: true, name: true, role: true });
export const selectUser = createSelectSchema(users);
export const patchUser =
  createUpdateSchema(users);    // all optional
export type InsertUser = z.infer<typeof insertUser>;

export async function listPosts(page = 1, size = 20) {
  const where = and(
    eq(posts.published, true),
    isNull(posts.deletedAt),
  );
  const [items, [{ total } = { total: 0 }]] =
    await Promise.all([
      db.select().from(posts).where(where)
        .orderBy(desc(posts.createdAt), desc(posts.id))
        .limit(size).offset((page - 1) * size),
      db.select({ total: count() }).from(posts).where(where),
    ]);
  return { items, total, pages: Math.ceil(total / size) };
}

import { timestamp } from "drizzle-orm/pg-core";
 
export const timestamps = {
  createdAt: timestamp({ withTimezone: true })
    .notNull()
    .defaultNow(),
  updatedAt: timestamp({ withTimezone: true })
    .notNull()
    .defaultNow()
    .$onUpdate(() => new Date()),
  deletedAt: timestamp({ withTimezone: true }),
};
// pgTable("x", { id: ..., ...timestamps })

import type { PgColumn } from "drizzle-orm/pg-core";
 
export const live = (deletedAt: PgColumn): SQL =>
  isNull(deletedAt);
 
export async function softDelete(id: number) {
  await db.update(posts)
    .set({ deletedAt: new Date() })
    .where(and(eq(posts.id, id), live(posts.deletedAt)));
}
 
export const listLive = () =>
  db.select().from(posts).where(live(posts.deletedAt));

export async function upsertUsers(
  rows: { email: string; name: string }[],
) {
  if (rows.length === 0) return [];
  return db.insert(users).values(rows)
    .onConflictDoUpdate({
      target: users.email,
      set: { name: sql`excluded.name` },
    })
    .returning({ id: users.id, email: users.email });
}

import { TransactionRollbackError } from "drizzle-orm";
 
try {
  await db.transaction(async (tx) => {
    const [u] = await tx.insert(users)
      .values({ email, name }).returning();
    if (!u) return tx.rollback();
    await tx.insert(posts)
      .values({ authorId: u.id, title: "Welcome" });
  });
} catch (err) {
  if (!(err instanceof TransactionRollbackError)) throw err;
}

import { sql } from "drizzle-orm";
import { db } from "./index";
import { posts, users } from "./schema";
 
if (process.env.NODE_ENV === "production") {
  throw new Error("refusing to seed production");
}
await db.transaction(async (tx) => {
  await tx.execute(sql`truncate ${users}, ${posts}
    restart identity cascade`);
  const people = await tx.insert(users).values(
    Array.from({ length: 10 }, (_, i) => ({
      email: `user${i}@example.com`,
      name: `User ${i}`,
    })),
  ).returning({ id: users.id });
  await tx.insert(posts).values(
    people.map(({ id }) => ({
      authorId: id,
      title: `Post ${id}`,
    })),
  );
});

import { createInsertSchema } from "drizzle-zod";
 
const insertUser = createInsertSchema(users, {
  email: (s) => s.email(),
  name: (s) => s.trim().min(1).max(100),
}).pick({ email: true, name: true });
 
export async function createUser(input: unknown) {
  const parsed = insertUser.safeParse(input);
  if (!parsed.success) {
    const error = parsed.error.issues;
    return { ok: false as const, error };
  }
  const [row] = await db.insert(users)
    .values(parsed.data).returning();
  return { ok: true as const, user: row! };
}