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(sqlCURRENT_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(sqlCURRENT_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(sqlCURRENT_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(sqlCURRENT_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(sqlCURRENT_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

• Always use transactions for related changes

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

• Always include WHERE on DELETE/UPDATE

• Use indexes for frequently queried columns

• Store dates as ISO strings for SQLite

• Use returning() to get inserted/updated rows

• Generate migrations, don't push to production

References

• Drizzle ORM Docs

• Drizzle SQLite

• Drizzle Migrations