drizzle-migrations

name	drizzle-migrations
description	Manage database schema with Drizzle ORM and SQLite migrations. Use when adding tables, modifying columns, creating indexes, or running migrations. Activates for database schema changes, migration generation, and Drizzle query patterns.
allowed-tools	Read,Write,Edit,Bash(npm:*,npx:*)
category	Data & Analytics
tags	["database","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., {
    : ,
  }),
  : ().(),
  : ().(),
  : (),
  : (),
  : ().().(sql),
});


  auditLog = (, {
  : ().(),
  : ().(),
  : ().(),
  : (),
  : (),
  : (),  
  : ().().(sql),
},  ({
  : ().(table., table.),
  : ().(table.),
}));

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(, { :  }),

  
  : (, { : [, , ] }),
});

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))
  .();

Insert

// Single insert
const [newUser] = await db
  .insert(users)
  .values({
    id: generateId(),
    email: '[e-mail]',
    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: '[e-mail]' })
  .onConflictDoUpdate({
    target: users.id,
    set: { email: '[e-mail]' },
  });

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