D1 Drizzle

Initialize DB in a route

import { createDb } from "../db";

const db = createDb(c.env.DB);

Common Query Patterns

Find one:

const item = await db.query.users.findFirst({
  where: eq(users.id, id),
  columns: { passwordHash: false }, // exclude sensitive fields
});

Find many with pagination:

const results = await db.query.tracks.findMany({
  where: and(eq(tracks.userId, userId), eq(tracks.isPublic, true)),
  orderBy: desc(tracks.createdAt),
  limit, offset,
});

Insert:

await db.insert(users).values({ id: nanoid(), username, email, passwordHash });

Update:

await db.update(tracks)
  .set({ plays: sql`${tracks.plays} + 1` })
  .where(eq(tracks.id, id));

Delete:

await db.delete(likes).where(
  and(eq(likes.userId, userId), eq(likes.targetId, id))
);

Raw count:

const [{ count }] = await db
  .select({ count: sql<number>`count(*)` })
  .from(tracks)
  .where(eq(tracks.userId, userId));

Adding a New Table

1. Add table definition to src/db/schema.ts
2. Run npx drizzle-kit generate → creates SQL migration
3. Run npm run db:migrate for local, npm run db:migrate:prod for prod

Migration Commands

cd d:/Code/hiu/api
npx drizzle-kit generate          # generate from schema diff
npm run db:migrate                 # apply local
npm run db:migrate:prod            # apply production
npx drizzle-kit studio             # visual DB browser

Schema Conventions

• IDs: text("id").primaryKey() using nanoid()
• Timestamps: text("created_at").default(sql\(datetime('now'))`)`
• Booleans: integer("is_public", { mode: "boolean" }).default(true)
• Foreign keys: always add .references(() => table.id, { onDelete: "cascade" })
• Add indexes for all FK columns and frequently filtered columns

See references/schema.md for full schema reference.