Agent skill

drizzle

Drizzle ORM schema and database guide. Use when working with database schemas (src/database/schemas/*), defining tables, creating migrations, or database model code. Triggers on Drizzle schema definition, database migrations, or ORM usage questions.

View SKILL.md on GitHub Repository
Stars 75,065
Forks 14,902

Install this agent skill to your Project

npx add-skill https://github.com/lobehub/lobehub/tree/canary/.agents/skills/drizzle

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

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

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

Foreign Keys

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

Timestamps

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

Indexes

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

Type Inference

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

Example Pattern

typescript
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)

typescript
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

typescript
// ✅ 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

typescript
// ✅ 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

typescript
// ✅ 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::

typescript
// ✅ 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.

Recommended Agent Skills

Expand your agent's capabilities with these related and highly-rated skills.

lobehub/lobehub

task

75,065 14,902
Explore
lobehub/lobehub

i18n

Internationalization guide using react-i18next. Use when adding translations, creating i18n keys, or working with localized text in React components (.tsx files). Triggers on translation tasks, locale management, or i18n implementation.

75,065 14,902
Explore
lobehub/lobehub

data-fetching

Data fetching architecture guide using Service layer + Zustand Store + SWR. Use when implementing data fetching, creating services, working with store hooks, or migrating from useEffect. Triggers on data loading, API calls, service creation, or store data fetching tasks.

75,065 14,902
Explore
lobehub/lobehub

recent-data

Guide for using Recent Data (topics, resources, pages). Use when working with recently accessed items, implementing recent lists, or accessing session store recent data. Triggers on recent data usage or implementation tasks.

75,065 14,902
Explore
lobehub/lobehub

hotkey

Guide for adding keyboard shortcuts. Use when implementing new hotkeys, registering shortcuts, or working with keyboard interactions. Triggers on hotkey implementation or keyboard shortcut tasks.

75,065 14,902
Explore
lobehub/lobehub

debug

Debug package usage guide. Use when adding debug logging, understanding log namespaces, or implementing debugging features. Triggers on debug logging requests or logging implementation.

75,065 14,902
Explore

Didn't find tool you were looking for?

Be as detailed as possible for better results