drizzle-migrations

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),
}));