Handle database schema changes, Drizzle DAO implementations, and migrations for Cloudflare D1 with Drizzle ORM. Use when the user asks to create database tables, implement DAOs, modify schema, generate migrations, or work with database infrastructure. This skill focuses ONLY on the infra/drizzle layer (schema, DAO implementations, migrations) and references app/dao for interface definitions. Triggers include requests like "add a database table for...", "implement the DAO for...", "create schema for...", "generate migrations", "add a column to...", or any database infrastructure work.
Handle database schema definition, DAO implementations, and migration generation for Cloudflare D1 using Drizzle ORM. This skill manages only the infrastructure layer (src/infra/drizzle/) and follows interfaces defined in src/app/dao/.
Resolve the API package root first (for example code/api or api) and run all file checks/commands there. Paths below are relative to that package root.
Ask the user to clarify:
src/app/dao/?Check existing tables:
cat src/infra/drizzle/schema.ts
Check existing DAO interfaces:
ls src/app/dao/
Check existing DAO implementations:
ls src/infra/drizzle/dao/
Based on requirements:
For a new table:
schema.tsdao/factories/DAOFactory.tssrc/app/dao/ firstFor modifying existing table:
schema.tsFor a new DAO implementation (interface already exists):
Drizzle{Entity}DAO.ts in dao/Edit src/infra/drizzle/schema.ts:
Table naming convention:
{entity}Table (camelCase){entity} (lowercase, snake_case for multi-word)todoTable → "todo"Required columns for all tables:
id: text("id").primaryKey().notNull()createdAt, updatedAt for audit trailColumn pattern:
export const entityTable = sqliteTable("entity", {
id: text("id").primaryKey().notNull(),
ownerId: text("owner_id")
.notNull()
.references(() => user.id),
name: text("name").notNull(),
description: text("description"), // optional
completed: integer("completed", { mode: "boolean" }).notNull().default(false),
createdAt: integer("created_at", { mode: "timestamp_ms" })
.notNull()
.$default(() => new Date()),
updatedAt: integer("updated_at", { mode: "timestamp_ms" })
.notNull()
.$default(() => new Date()),
});
Add relations if needed:
export const entityRelations = relations(entityTable, ({ one, many }) => ({
owner: one(user, {
fields: [entityTable.ownerId],
references: [user.id],
}),
}));
See drizzle_patterns.md for column types and patterns.
Create src/infra/drizzle/dao/Drizzle{Entity}DAO.ts:
Structure:
import { count, eq } from "drizzle-orm";
import type { CreateEntityParams, EntityDAO, UpdateEntityParams } from "../../../app/dao/EntityDAO";
import type { Entity } from "../../../app/domain/Entity";
import type { Connection } from "../connection";
import { entityTable } from "../schema";
import { generateULID } from "../utils/generateULID";
export class DrizzleEntityDAO implements EntityDAO {
constructor(private db: Connection) {}
async getById(id: string): Promise<Entity | null> {
const result = await this.db
.select()
.from(entityTable)
.where(eq(entityTable.id, id));
return result.length > 0 ? this.mapToEntity(result[0]) : null;
}
async create(entity: CreateEntityParams): Promise<Entity> {
const result = await this.db
.insert(entityTable)
.values({
id: generateULID(),
...entity,
})
.returning();
return this.mapToEntity(result[0]);
}
async update({ id, ...params }: UpdateEntityParams): Promise<Entity> {
const result = await this.db
.update(entityTable)
.set(params)
.where(eq(entityTable.id, id))
.returning();
return this.mapToEntity(result[0]);
}
async delete(id: string): Promise<void> {
await this.db.delete(entityTable).where(eq(entityTable.id, id));
}
private mapToEntity(row: typeof entityTable.$inferSelect): Entity {
return {
id: row.id,
ownerId: row.ownerId,
name: row.name,
description: row.description ?? undefined, // null → undefined
completed: row.completed,
};
}
}
Key patterns:
Connection via constructorgenerateULID() for IDs.returning() on insert/updatemapToEntitynull to undefined for optional fieldsSee drizzle_patterns.md for complete examples.
Edit src/infra/factories/DAOFactory.ts:
import type { EntityDAO } from "../../app/dao/EntityDAO";
import { DrizzleEntityDAO } from "../drizzle/dao/DrizzleEntityDAO";
export function getEntityDAO(db: Connection): EntityDAO {
const mock = ServiceLocator.get("entityDAO");
if (mock) return mock;
return new DrizzleEntityDAO(db);
}
Don't forget to:
After schema changes are complete:
bun run db:generate
This command:
drizzle/Review the generated migration:
cat drizzle/NNNN_*.sql
Verify:
Start dev server:
bun run dev
Test using:
bun run db:studiobun run test:integrationAfter completing the database work, inform the user:
✅ Completed:
schema.tssrc/infra/drizzle/dao/DAOFactory.tsdrizzle/⚠️ Next steps:
bun run dev and bun run db:studiodrizzle/ directory)bun run deployasync findByOwnerId(
params: ListEntitiesByOwnerIdParams,
): Promise<{ result: Entity[]; total: number }> {
const query = this.db
.select()
.from(entityTable)
.where(eq(entityTable.ownerId, params.ownerId))
.limit(params.limit)
.offset(params.offset);
const countQuery = this.db
.select({ count: count(entityTable.id) })
.from(entityTable)
.where(eq(entityTable.ownerId, params.ownerId));
const [results, countResult] = await Promise.all([query, countQuery]);
const total = countResult[0]?.count ?? 0;
return {
result: results.map((row) => this.mapToEntity(row)),
total
};
}
export const todoTable = sqliteTable("todo", {
// ...
ownerId: text("owner_id")
.notNull()
.references(() => user.id),
});
export const todoRelations = relations(todoTable, ({ one }) => ({
owner: one(user, {
fields: [todoTable.ownerId],
references: [user.id],
}),
}));
createdAt: integer("created_at", { mode: "timestamp_ms" })
.notNull()
.$default(() => new Date()),
updatedAt: integer("updated_at", { mode: "timestamp_ms" })
.notNull()
.$default(() => new Date()),