blockmatic-icebox/Drizzle ORM

Skill: drizzle-orm

Scope

• Applies to: Drizzle ORM v0.44+ for PostgreSQL, MySQL, SQLite - schema definitions, type-safe queries, migrations, relations
• Does NOT cover: Database driver setup, connection pooling configuration, other ORMs

Assumptions

• Drizzle ORM v0.44+
• Drizzle Kit v0.31+ (dev dependency) for migrations
• PostgreSQL, MySQL, or SQLite database
• TypeScript v5+ with strict mode
• ESM module system

Principles

• Schemas defined using table builders (pgTable, mysqlTable, sqliteTable) with typed columns
• Column types match database constraints (varchar with length, timestamp with mode)
• Indexes defined in table definition second parameter using index() helper
• Identity columns (generatedAlwaysAsIdentity) preferred over serial in PostgreSQL
• Query helpers (eq, and, or, like) provide type-safe SQL construction
• Relational query builder (db.query.*) preferred for complex relations
• Type inference via $inferSelect and $inferInsert eliminates manual types
• Migrations generated with drizzle-kit generate (not push in production)
• Prepared statements optimize frequently executed queries
• Schemas organized by domain (one file per entity/table)
• Transactions (db.transaction) ensure atomic multi-step operations

Constraints

MUST

• Use Drizzle Kit for migrations (drizzle-kit generate, drizzle-kit migrate)
• Define column types matching database constraints
• Use query helpers instead of raw SQL

SHOULD

• Use relations for type-safe joins
• Use relational query builder for complex relations
• Use transactions for multi-step operations
• Use prepared statements for frequently executed queries
• Export types via $inferSelect and $inferInsert
• Handle DrizzleQueryError for structured error handling
• Organize schemas by domain (one file per entity)
• Use selective field loading (not full rows)
• Use identity columns over serial in PostgreSQL
• Specify length for varchar columns
• Use index() helper in table definitions
• Use PGLite for testing PostgreSQL schemas

AVOID

• Raw SQL unless necessary
• Manual type assertions (use inferred types)
• Skipping migration generation
• serial in new PostgreSQL tables (use identity columns)
• Over-indexing (index only where queries justify)
• Fetching full rows when only few columns needed
• push in production (use generate + migrate)
• String-based timestamp mode when DB supports date/time types

Interactions

• Works with nextjs Server Components and API routes
• Complements fastify for API development

Patterns

Schema Definition

import { index, pgTable, text, timestamp, varchar } from 'drizzle-orm/pg-core'

export const users = pgTable(
  'users',
  {
    id: text('id').primaryKey(),
    email: varchar('email', { length: 255 }).notNull().unique(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().notNull(),
  },
  table => [index('users_email_idx').on(table.email)],
)

export type User = typeof users.$inferSelect
export type NewUser = typeof users.$inferInsert

Identity Columns

import { pgTable, integer, generatedAlwaysAsIdentity } from 'drizzle-orm/pg-core'

export const posts = pgTable('posts', {
  id: integer('id').primaryKey().generatedAlwaysAsIdentity(),
})

Query Builder

import { eq } from 'drizzle-orm'

const user = await db
  .select()
  .from(users)
  .where(eq(users.id, userId))
  .limit(1)

const userWithPosts = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: { posts: true },
})

const userEmail = await db
  .select({ email: users.email })
  .from(users)
  .where(eq(users.id, userId))

Transactions

await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values(userData).returning()
  await tx.insert(profiles).values({ userId: user.id, ...profileData })
})

Prepared Statements

import { placeholder } from 'drizzle-orm'

const getUserByEmail = db
  .select()
  .from(users)
  .where(eq(users.email, placeholder('email')))
  .prepare('get_user_by_email')

const user = await getUserByEmail.execute({ email: '[email protected]' })

Error Handling

import { DrizzleQueryError } from 'drizzle-orm'

try {
  const user = await db.select().from(users).where(eq(users.id, userId))
} catch (error) {
  if (error instanceof DrizzleQueryError) {
    if (error.cause?.code === '23505') {
      throw new Error('User already exists')
    }
  }
  throw error
}

Relations

import { relations } from 'drizzle-orm'

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}))

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}))

Database Connection

import { drizzle } from 'drizzle-orm/node-postgres'
import { Pool } from 'pg'
import * as schema from './schema'

const pool = new Pool({ connectionString: process.env.DATABASE_URL })
export const db = drizzle(pool, { schema })

Drizzle Kit Config

import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  dialect: 'postgresql',
  schema: './src/db/schema/index.ts',
  out: './src/db/migrations',
  dbCredentials: { url: process.env.DATABASE_URL! },
  migrations: {
    table: '__drizzle_migrations',
    schema: 'public',
  },
  verbose: true,
  strict: true,
})

References

• Query Patterns - CRUD operations, joins, aggregations
• PostgreSQL Patterns - PostgreSQL-specific patterns