Drizzle ORM Migrations

This skill helps you manage database schema changes using Drizzle ORM with SQLite.

When to Use

✅ USE this skill for:

• Adding new tables or modifying existing columns
• Generating and running database migrations
• Drizzle-specific query patterns and relations
• SQLite schema best practices with Drizzle
• Setting up Drizzle configuration

❌ DO NOT use for:

• Supabase/PostgreSQL → use supabase-admin skill
• Raw SQL without Drizzle → use standard SQL resources
• Prisma ORM → different syntax and patterns
• General database design theory → use database architecture resources

Project Setup

Configuration: drizzle.config.ts

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'sqlite',
  dbCredentials: {
    url: './data/app.db',
  },
});

Commands:

npm run db:generate  # Generate migration files
npm run db:push      # Push schema directly (dev only)
npm run db:studio    # Open Drizzle Studio GUI

Schema Definition

Location: src/db/schema.ts

Table Definition

import { sqliteTable, text, integer, real, blob } from 'drizzle-orm/sqlite-core';
import { relations } from 'drizzle-orm';

// Basic table
export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(),
  username: text('username').notNull(),
  passwordHash: text('password_hash'),
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text('updated_at'),
});

// Table with foreign key
export const checkIns = sqliteTable('check_ins', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull().references(() => users.id, {
    onDelete: 'cascade',
  }),
  mood: integer('mood').notNull(),
  cravingLevel: integer('craving_level').notNull(),
  sleepHours: real('sleep_hours'),
  notes: text('notes'),
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
});

// Table with composite index
export const auditLog = sqliteTable('audit_log', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull(),
  action: text('action').notNull(),
  targetType: text('target_type'),
  targetId: text('target_id'),
  details: text('details'),  // JSON string
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
}, (table) => ({
  userActionIdx: index('idx_audit_user_action').on(table.userId, table.action),
  createdAtIdx: index('idx_audit_created').on(table.createdAt),
}));

Relations

export const usersRelations = relations(users, ({ many }) => ({
  checkIns: many(checkIns),
  sessions: many(sessions),
  journalEntries: many(journalEntries),
}));

export const checkInsRelations = relations(checkIns, ({ one }) => ({
  user: one(users, {
    fields: [checkIns.userId],
    references: [users.id],
  }),
}));

Column Types

SQLite Types in Drizzle

import {
  sqliteTable,
  text,           // TEXT - strings, JSON, dates
  integer,        // INTEGER - numbers, booleans (0/1)
  real,           // REAL - floating point
  blob,           // BLOB - binary data
} from 'drizzle-orm/sqlite-core';

const examples = sqliteTable('examples', {
  // Strings
  name: text('name').notNull(),
  description: text('description'),

  // Numbers
  count: integer('count').notNull().default(0),
  rating: real('rating'),

  // Booleans (stored as 0/1)
  isActive: integer('is_active', { mode: 'boolean' }).default(true),

  // Dates (stored as ISO strings)
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  expiresAt: text('expires_at'),

  // JSON (stored as TEXT)
  metadata: text('metadata', { mode: 'json' }),

  // Enums (stored as TEXT)
  status: text('status', { enum: ['pending', 'active', 'archived'] }),
});

Migration Strategies

Strategy 1: Push (Development Only)

npm run db:push

• Directly applies schema changes
• Fast for development
• Never use in production

Strategy 2: Generate & Migrate (Production)

# 1. Generate migration file
npm run db:generate

# 2. Review generated SQL in /drizzle folder

# 3. Apply migration (in code or manually)

Applying Migrations in Code

import { drizzle } from 'drizzle-orm/better-sqlite3';
import { migrate } from 'drizzle-orm/better-sqlite3/migrator';
import Database from 'better-sqlite3';

const sqlite = new Database('./data/app.db');
const db = drizzle(sqlite);

// Run migrations
migrate(db, { migrationsFolder: './drizzle' });

Common Schema Changes

Adding a New Table

// 1. Add to schema.ts
export const newFeature = sqliteTable('new_feature', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull().references(() => users.id),
  name: text('name').notNull(),
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
});

// 2. Add relations
export const newFeatureRelations = relations(newFeature, ({ one }) => ({
  user: one(users, {
    fields: [newFeature.userId],
    references: [users.id],
  }),
}));

// 3. Generate migration
// npm run db:generate

Adding a Column

// In schema.ts, add the new column
export const users = sqliteTable('users', {
  // existing columns...
  newColumn: text('new_column'),  // Add this
});

// Generate migration
// npm run db:generate

Adding an Index

export const messages = sqliteTable('messages', {
  id: text('id').primaryKey(),
  conversationId: text('conversation_id').notNull(),
  createdAt: text('created_at').notNull(),
}, (table) => ({
  // Add index
  convCreatedIdx: index('idx_messages_conv_created')
    .on(table.conversationId, table.createdAt),
}));

Renaming (Requires Manual SQL)

SQLite doesn't support direct column renames in older versions. For complex changes:

-- drizzle/XXXX_rename_column.sql
-- Manual migration for column rename

-- 1. Create new table with desired schema
CREATE TABLE users_new (
  id TEXT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,  -- renamed from username
  created_at TEXT NOT NULL
);

-- 2. Copy data
INSERT INTO users_new SELECT id, email, username, created_at FROM users;

-- 3. Drop old table
DROP TABLE users;

-- 4. Rename new table
ALTER TABLE users_new RENAME TO users;

Query Patterns

Basic Queries

import { db } from '@/db';
import { eq, and, or, desc, asc, like, gte, lte } from 'drizzle-orm';
import { users, checkIns } from '@/db/schema';

// Select all
const allUsers = await db.select().from(users);

// Select with conditions
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.isActive, true));

// Select specific columns
const userEmails = await db
  .select({ id: users.id, email: users.email })
  .from(users);

// Complex where clause
const results = await db
  .select()
  .from(checkIns)
  .where(
    and(
      eq(checkIns.userId, userId),
      gte(checkIns.createdAt, startDate),
      lte(checkIns.createdAt, endDate)
    )
  )
  .orderBy(desc(checkIns.createdAt))
  .limit(30);

Insert

// Single insert
const [newUser] = await db
  .insert(users)
  .values({
    id: generateId(),
    email: 'user@example.com',
    username: 'newuser',
  })
  .returning();

// Bulk insert
await db.insert(checkIns).values([
  { id: '1', userId, mood: 7, cravingLevel: 2 },
  { id: '2', userId, mood: 8, cravingLevel: 1 },
]);

// Upsert (insert or update)
await db
  .insert(users)
  .values({ id: 'user-1', email: 'new@example.com' })
  .onConflictDoUpdate({
    target: users.id,
    set: { email: 'new@example.com' },
  });

Update

await db
  .update(users)
  .set({ username: 'newname', updatedAt: new Date().toISOString() })
  .where(eq(users.id, userId));

Delete

// Always use WHERE clause!
await db
  .delete(checkIns)
  .where(eq(checkIns.id, checkInId));

// Delete with multiple conditions
await db
  .delete(sessions)
  .where(
    and(
      eq(sessions.userId, userId),
      lte(sessions.expiresAt, new Date().toISOString())
    )
  );

Joins

const userWithCheckIns = await db
  .select({
    user: users,
    checkIn: checkIns,
  })
  .from(users)
  .leftJoin(checkIns, eq(users.id, checkIns.userId))
  .where(eq(users.id, userId));

Aggregations

import { count, avg, sum, max, min } from 'drizzle-orm';

const stats = await db
  .select({
    totalCheckIns: count(),
    avgMood: avg(checkIns.mood),
    maxStreak: max(checkIns.streak),
  })
  .from(checkIns)
  .where(eq(checkIns.userId, userId));

Best Practices

1. Always use transactions for related changes

await db.transaction(async (tx) => {
  await tx.insert(users).values(userData);
  await tx.insert(profiles).values(profileData);
});

2. Always include WHERE on DELETE/UPDATE
3. Use indexes for frequently queried columns
4. Store dates as ISO strings for SQLite
5. Use returning() to get inserted/updated rows
6. Generate migrations, don't push to production

References

• Drizzle ORM Docs
• Drizzle SQLite
• Drizzle Migrations