SKILL.md

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.

drizzle