--- name: drizzle-migrations description: Drizzle ORM schema management and SQLite migrations — adding tables, modifying columns, creating indexes, generating and running migrations, Drizzle query patterns. NOT for Prisma, TypeORM, Sequelize, or raw SQL migration tools. allowed-tools: Read,Write,Edit,Bash(npm:*,npx:*) metadata: category: Data & Analytics tags: - database - drizzle - migrations pairs-with: - skill: database-design-patterns reason: Schema migration implementation follows database design pattern decisions - skill: supabase-admin reason: Drizzle migrations run against Supabase PostgreSQL databases with RLS considerations - skill: fullstack-debugger reason: Migration failures and schema mismatches are common fullstack debugging scenarios --- # 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` ```typescript import { defineConfig } from 'drizzle-kit'; export default defineConfig({ schema: './src/db/schema.ts', out: './drizzle', dialect: 'sqlite', dbCredentials: { url: './data/app.db', }, }); ``` **Commands**: ```bash 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 ```typescript 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 ```typescript 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 ```typescript 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) ```bash npm run db:push ``` - Directly applies schema changes - Fast for development - **Never use in production** ### Strategy 2: Generate & Migrate (Production) ```bash # 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 ```typescript 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 ```typescript // 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 ```typescript // 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 ```typescript 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: ```sql -- 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 ```typescript 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 ```typescript // 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 ```typescript await db .update(users) .set({ username: 'newname', updatedAt: new Date().toISOString() }) .where(eq(users.id, userId)); ``` ### Delete ```typescript // 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 ```typescript const userWithCheckIns = await db .select({ user: users, checkIn: checkIns, }) .from(users) .leftJoin(checkIns, eq(users.id, checkIns.userId)) .where(eq(users.id, userId)); ``` ### Aggregations ```typescript 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** ```typescript 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](https://orm.drizzle.team/docs/overview) - [Drizzle SQLite](https://orm.drizzle.team/docs/get-started-sqlite) - [Drizzle Migrations](https://orm.drizzle.team/docs/migrations) --- Source attribution: erichowens Original project: https://github.com/erichowens/some_claude_skills/blob/f49825290077e5fb46bb3e867dfa8a5b0161152b/.claude/skills/drizzle-migrations/SKILL.md License: MIT License information: https://opensource.org/license/mit Original upstream instructions; downloaded from Dave AI. ## Upstream license notice MIT License Copyright (c) 2025 Erich Owens Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. # Dave AI resource metadata Creator / publisher: erichowens Original project: https://github.com/erichowens/some_claude_skills/blob/f49825290077e5fb46bb3e867dfa8a5b0161152b/.claude/skills/drizzle-migrations/SKILL.md Attribution basis: Recovered original package and original project license at a pinned revision Fields of work: Development & Engineering Roles: Backend Developer, Data Engineer, Database Administrator Specialties: Database Tools: Drizzle, SQLite Country applicability: Country not specified Classification basis: Source catalog labels Instruction SHA-256: e4a1d882cc6212e4e6fbe81ebf015c6437a752a6f0672a569db2df164ab58bd0 Metadata reviewed: 2026-10-27 This metadata supersedes earlier Dave AI catalog attribution. Original instruction and supporting files are preserved.