Drizzle ORM Schema Style Guide

Configuration

• Config: drizzle.config.ts

• Schemas: src/database/schemas/

• Migrations: src/database/migrations/

• Dialect: postgresql with strict: true

Helper Functions

Location: src/database/schemas/_helpers.ts

• timestamptz(name) : Timestamp with timezone

• createdAt() , updatedAt() , accessedAt() : Standard timestamp columns

• timestamps : Object with all three for easy spread

Naming Conventions

• Tables: Plural snake_case (users , session_groups )

• Columns: snake_case (user_id , created_at )

Column Definitions

Primary Keys

id: text('id') .primaryKey() .$defaultFn(() => idGenerator('agents')) .notNull(),

ID prefixes make entity types distinguishable. For internal tables, use uuid .

Foreign Keys

userId: text('user_id') .references(() => users.id, { onDelete: 'cascade' }) .notNull(),

Timestamps

...timestamps, // Spread from _helpers.ts

Indexes

// Return array (object style deprecated) (t) => [uniqueIndex('client_id_user_id_unique').on(t.clientId, t.userId)],

Type Inference

export const insertAgentSchema = createInsertSchema(agents); export type NewAgent = typeof agents.$inferInsert; export type AgentItem = typeof agents.$inferSelect;

Example Pattern

export const agents = pgTable( 'agents', { id: text('id') .primaryKey() .$defaultFn(() => idGenerator('agents')) .notNull(), slug: varchar('slug', { length: 100 }) .$defaultFn(() => randomSlug(4)) .unique(), userId: text('user_id') .references(() => users.id, { onDelete: 'cascade' }) .notNull(), clientId: text('client_id'), chatConfig: jsonb('chat_config').$type<LobeAgentChatConfig>(), ...timestamps, }, (t) => [uniqueIndex('client_id_user_id_unique').on(t.clientId, t.userId)], );

Common Patterns

Junction Tables (Many-to-Many)

export const agentsKnowledgeBases = pgTable( 'agents_knowledge_bases', { agentId: text('agent_id') .references(() => agents.id, { onDelete: 'cascade' }) .notNull(), knowledgeBaseId: text('knowledge_base_id') .references(() => knowledgeBases.id, { onDelete: 'cascade' }) .notNull(), userId: text('user_id') .references(() => users.id, { onDelete: 'cascade' }) .notNull(), enabled: boolean('enabled').default(true), ...timestamps, }, (t) => [primaryKey({ columns: [t.agentId, t.knowledgeBaseId] })], );

Query Style

Always use db.select() builder API. Never use db.query.* relational API (findMany , findFirst , with: ).

The relational API generates complex lateral joins with json_build_array that are fragile and hard to debug.

Select Single Row

// ✅ Good const [result] = await this.db .select() .from(agents) .where(eq(agents.id, id)) .limit(1); return result;

// ❌ Bad: relational API return this.db.query.agents.findFirst({ where: eq(agents.id, id), });

Select with JOIN

// ✅ Good: explicit select + leftJoin const rows = await this.db .select({ runId: agentEvalRunTopics.runId, score: agentEvalRunTopics.score, testCase: agentEvalTestCases, topic: topics, }) .from(agentEvalRunTopics) .leftJoin(agentEvalTestCases, eq(agentEvalRunTopics.testCaseId, agentEvalTestCases.id)) .leftJoin(topics, eq(agentEvalRunTopics.topicId, topics.id)) .where(eq(agentEvalRunTopics.runId, runId)) .orderBy(asc(agentEvalRunTopics.createdAt));

// ❌ Bad: relational API with with: return this.db.query.agentEvalRunTopics.findMany({ where: eq(agentEvalRunTopics.runId, runId), with: { testCase: true, topic: true }, });

Select with Aggregation

// ✅ Good: select + leftJoin + groupBy const rows = await this.db .select({ id: agentEvalDatasets.id, name: agentEvalDatasets.name, testCaseCount: count(agentEvalTestCases.id).as('testCaseCount'), }) .from(agentEvalDatasets) .leftJoin(agentEvalTestCases, eq(agentEvalDatasets.id, agentEvalTestCases.datasetId)) .groupBy(agentEvalDatasets.id);

One-to-Many (Separate Queries)

When you need a parent record with its children, use two queries instead of relational with: :

// ✅ Good: two simple queries const [dataset] = await this.db .select() .from(agentEvalDatasets) .where(eq(agentEvalDatasets.id, id)) .limit(1);

if (!dataset) return undefined;

const testCases = await this.db .select() .from(agentEvalTestCases) .where(eq(agentEvalTestCases.datasetId, id)) .orderBy(asc(agentEvalTestCases.sortOrder));

return { ...dataset, testCases };

Database Migrations

See the db-migrations skill for the detailed migration guide.