Drizzle ORM

# ORM runtime — query builder, schema types, and relational query API
bun add drizzle-orm
# CLI tool for generating and running migrations, and Drizzle Studio
bun add -d drizzle-kit

src/db/
├── index.ts        # db connection
├── schema.ts       # table definitions
└── relations.ts    # relational query definitions (v2)
drizzle/            # generated migration files
drizzle.config.ts   # drizzle-kit config

import { drizzle } from "drizzle-orm/bun-sqlite";

export const db = drizzle(process.env.DB_FILE_NAME!); // file-based
// or: drizzle(':memory:') // in-memory

import * as schema from "./schema";

export const db = drizzle(process.env.DB_FILE_NAME!, { schema });

import { sqliteTable, int, text, real } from "drizzle-orm/sqlite-core";
import type { AnySQLiteColumn } from "drizzle-orm/sqlite-core";

export const users = sqliteTable("users", {
  id: int().primaryKey({ autoIncrement: true }),
  name: text().notNull(),
  email: text().notNull().unique(),
  // SQLite has no enum type — use $type to constrain in TypeScript
  role: text().$type<"guest" | "user" | "admin">().default("guest"),
  createdAt: int("created_at", { mode: "timestamp" })
    .notNull()
    .$defaultFn(() => new Date()),
  // Self-referencing FK
  invitedBy: int("invited_by").references((): AnySQLiteColumn => users.id),
});

export const posts = sqliteTable("posts", {
  id: int().primaryKey({ autoIncrement: true }),
  title: text().notNull(),
  content: text().notNull(),
  published: int({ mode: "boolean" }).default(false),
  authorId: int("author_id").references(() => users.id, { onDelete: "cascade" }),
});

import { index, uniqueIndex, primaryKey } from "drizzle-orm/sqlite-core";

export const usersToGroups = sqliteTable(
  "users_to_groups",
  {
    userId: int("user_id")
      .notNull()
      .references(() => users.id),
    groupId: int("group_id")
      .notNull()
      .references(() => groups.id),
  },
  (t) => [primaryKey({ columns: [t.userId, t.groupId] })],
);

export const posts = sqliteTable(
  "posts",
  {
    id: int().primaryKey({ autoIncrement: true }),
    slug: text().notNull(),
    title: text().notNull(),
  },
  (t) => [uniqueIndex("slug_idx").on(t.slug), index("title_idx").on(t.title)],
);

type NewUser = typeof users.$inferInsert;
type User = typeof users.$inferSelect;

import { eq, and, or, gt, lt, like, desc, asc, inArray, isNull } from "drizzle-orm";

// All rows
const all = await db.select().from(users);

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

// Filter
const admins = await db.select().from(users).where(eq(users.role, "admin"));

// Compound filter
await db
  .select()
  .from(users)
  .where(and(gt(users.id, 10), like(users.email, "%@example.com")));

// Order, limit, offset
await db.select().from(users).orderBy(desc(users.createdAt)).limit(20).offset(40);

// Single
await db.insert(users).values({ name: "Alice", email: "alice@example.com" });

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

// Returning (SQLite supports this)
const [newUser] = await db
  .insert(users)
  .values({ name: "Alice", email: "alice@example.com" })
  .returning();

// On conflict
await db
  .insert(users)
  .values({ name: "Alice", email: "alice@example.com" })
  .onConflictDoUpdate({ target: users.email, set: { name: "Alice" } });
// or: .onConflictDoNothing()

await db.update(users).set({ role: "admin" }).where(eq(users.email, "alice@example.com"));

const [updated] = await db.update(users).set({ role: "admin" }).where(eq(users.id, 1)).returning();

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

const [deleted] = await db.delete(users).where(eq(users.id, 1)).returning();

eq(users.id, 1); // =
ne(users.id, 1); // !=
gt(users.age, 18); // >
like(users.name, "%Alice%"); // LIKE
inArray(users.id, [1, 2, 3]);
isNull(users.deletedAt);
between(users.age, 18, 65);
and(eq(users.role, "admin"), gt(users.id, 0));
or(eq(users.role, "admin"), eq(users.role, "user"));

// Raw SQL
sql`lower(${users.email}) = ${"alice@example.com"}`;

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

// Left join with column selection
const result = await db
  .select({
    post: posts,
    authorName: users.name,
  })
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id));

import { defineRelations } from "drizzle-orm";
import * as schema from "./schema";

export const relations = defineRelations(schema, (r) => ({
  users: {
    posts: r.many.posts(),
    invitee: r.one.users({
      from: r.users.invitedBy,
      to: r.users.id,
    }),
  },
  posts: {
    author: r.one.users({
      from: r.posts.authorId,
      to: r.users.id,
    }),
  },
  // Many-to-many via junction table
  usersToGroups: {
    user: r.one.users({
      from: r.usersToGroups.userId,
      to: r.users.id,
    }),
    group: r.one.groups({
      from: r.usersToGroups.groupId,
      to: r.groups.id,
    }),
  },
}));

import * as schema from "./schema";
import { relations } from "./relations";

export const db = drizzle(process.env.DB_FILE_NAME!, { schema, relations });

// findMany with nested data
const result = await db.query.users.findMany({
  with: { posts: true },
});

// findFirst
const user = await db.query.users.findFirst({
  where: { id: 1 },
  with: { posts: true },
});

// Filter columns and nested relations
const result = await db.query.users.findMany({
  columns: { id: true, name: true },
  with: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: "desc" },
      limit: 5,
      columns: { id: true, title: true },
    },
  },
});

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: "Hello", content: "...", authorId: user.id });
});

// Manual rollback
await db.transaction(async (tx) => {
  const [account] = await tx.select().from(accounts).where(eq(accounts.id, 1));
  if (account.balance < 100) tx.rollback(); // throws, rolling back

  await tx
    .update(accounts)
    .set({ balance: sql`balance - 100` })
    .where(eq(accounts.id, 1));
});

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

const [{ total }] = await db.select({ total: count() }).from(users);
const [{ avg: avgAge }] = await db.select({ avg: avg(users.age) }).from(users);

// Group by
await db.select({ role: users.role, count: count() }).from(users).groupBy(users.role);

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  dialect: "sqlite",
  dbCredentials: {
    url: process.env.DB_FILE_NAME!,
  },
});

bunx drizzle-kit generate   # generate SQL migration files from schema changes
bunx drizzle-kit migrate    # apply pending migrations
bunx drizzle-kit push       # push schema directly to DB (dev shortcut, no migration files)
bunx drizzle-kit studio     # open Drizzle Studio browser UI

import { migrate } from "drizzle-orm/bun-sqlite/migrator";
import { db } from "./db";

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