drizzle-orm

Drizzle ORM

File Structure

apps/server/
├── src/db/
│   ├── schema-sqlite.ts        # sqliteTable() definitions
│   ├── schema-mysql.ts         # mysqlTable() definitions
│   ├── schema-columns.ts       # Shared column name constants
│   ├── sqlite-client.ts        # drizzle(better-sqlite3) factory
│   └── mysql-client.ts         # drizzle(mysql2) factory
├── src/services/
│   ├── drizzle-session-store.ts    # Session CRUD
│   ├── drizzle-raw-store.ts        # Raw event append/query
│   ├── composite-raw-store.ts      # Multi-backend wrapper
│   └── composite-session-store.ts
├── src/scripts/
│   ├── migrate-sqlite.ts
│   └── migrate-mysql.ts
├── drizzle-sqlite.config.ts
├── drizzle-mysql.config.ts
└── drizzle/
    ├── sqlite/    # 10 migration files
    └── mysql/     # 10 migration files

Schema Pattern

Both dialects share column names via schema-columns.ts:

// schema-columns.ts
export const SESSION_COLUMNS = { id, provider, command, args, cwd, mode, role, parentId, sessionId, title, status, createdAt };
export const RAW_ENTRY_COLUMNS = { id, sessionId, promptId, dir, raw, seq, createdAt };

Schema consistency is verified by src/__tests__/schema-consistency.test.ts using getTableColumns().

Connection Setup

SQLite (sqlite-client.ts): drizzle(new Database(path), { schema }) — WAL mode + foreign keys enabled. MySQL (mysql-client.ts): drizzle(mysql.createPool(url), { schema, mode: 'default' }).

Query Patterns

// Select with pagination
db.select().from(sessions).where(ne(sessions.status, 'deleted')).orderBy(desc(sessions.createdAt)).limit(limit).offset(offset)

// Insert
db.insert(rawEntries).values({ sessionId, raw, seq, createdAt })

// Update
db.update(sessions).set({ title }).where(eq(sessions.id, id))

// Delete
db.delete(sessions).where(eq(sessions.id, id))

// Count
db.select({ count: count() }).from(sessions)

Operators: eq(), ne(), desc(), asc(), count().

Migration Workflow