Drizzle DAO & Schema Management

Check existing DAO interfaces:

ls src/app/dao/

Check existing DAO implementations:

ls src/infra/drizzle/dao/

3. Determine Work Scope

Based on requirements:

For a new table:

• ✅ Add table definition to schema.ts
• ✅ Add relations if needed
• ✅ Create DAO implementation in dao/
• ✅ Add factory function in factories/DAOFactory.ts
• ✅ Generate migration
• ⚠️ User must have created DAO interface in src/app/dao/ first

For modifying existing table:

• ✅ Update table definition in schema.ts
• ✅ Update DAO implementation if methods change
• ✅ Generate migration
• ⚠️ Warn about destructive changes (dropping columns, changing types)

For a new DAO implementation (interface already exists):

• ✅ Create Drizzle{Entity}DAO.ts in dao/
• ✅ Implement all interface methods
• ✅ Add factory function

4. Define/Update Schema

Edit src/infra/drizzle/schema.ts:

Table naming convention:

• Table constant: {entity}Table (camelCase)
• SQL table name: {entity} (lowercase, snake_case for multi-word)
• Example: todoTable → "todo"

Required columns for all tables:

• id: text("id").primaryKey().notNull()
• Consider: createdAt, updatedAt for audit trail

Column 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.

5. Implement DAO

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:

1. Inject Connection via constructor
2. Use generateULID() for IDs
3. Always use .returning() on insert/update
4. Map database rows to domain models in mapToEntity
5. Convert null to undefined for optional fields

See drizzle_patterns.md for complete examples.

6. Add Factory Function

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:

• Import the DAO class
• Import the DAO interface type
• Check ServiceLocator for mocks (test support)

7. Generate Migration

After schema changes are complete:

bun run db:generate

This command:

1. Applies pending migrations to local D1
2. Compares schema.ts with database
3. Generates new migration SQL in drizzle/

Review the generated migration:

cat drizzle/NNNN_*.sql

Verify:

• Table names are correct
• Column types are correct
• Foreign keys are proper
• No unexpected changes

8. Test Locally

Start dev server:

bun run dev

Test using:

• Drizzle Studio: bun run db:studio
• Integration tests: bun run test:integration
• Manual API calls

9. Inform About Next Steps

After completing the database work, inform the user:

✅ Completed:

• Added/updated table definition in schema.ts
• Implemented DAO in src/infra/drizzle/dao/
• Added factory function in DAOFactory.ts
• Generated migration in drizzle/

⚠️ Next steps:

• Review generated migration SQL
• Test with bun run dev and bun run db:studio
• Commit migration files (drizzle/ directory)
• Deploy with bun run deploy

Common Patterns

Pagination Pattern

async 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
	};
}

Foreign Key Pattern

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],
	}),
}));

Timestamp Pattern

createdAt: integer("created_at", { mode: "timestamp_ms" })
	.notNull()
	.$default(() => new Date()),
updatedAt: integer("updated_at", { mode: "timestamp_ms" })
	.notNull()
	.$default(() => new Date()),

Boolean Pattern