When should I use database-migrations?

database-migrations is useful in the following scenarios: • Schema Evolution: Seamlessly manage database changes such as creating new tables, adding columns, or modifying existing schemas using standardized SQL templates. • Reversible Deployments: Implement safe database updates by defining both upgrade and downgrade paths, allowing for reliable rollbacks if issues arise. • Automated Migration Workflows: Integrate the provided TypeScript migration runner to automate the execution of pending SQL scripts and track migration history within an application. • Database Indexing: Optimize query performance by following patterns for creating and managing database indexes alongside table migrations.

When & Why to Use This Skill

This Claude skill provides standardized SQLite database migration patterns designed for SpecFlux, enabling robust schema management and version control. It facilitates the creation of reversible migrations with explicit UP and DOWN sections, ensuring data integrity and easy rollbacks during the development lifecycle.

Use Cases

Schema Evolution: Seamlessly manage database changes such as creating new tables, adding columns, or modifying existing schemas using standardized SQL templates.
Reversible Deployments: Implement safe database updates by defining both upgrade and downgrade paths, allowing for reliable rollbacks if issues arise.
Automated Migration Workflows: Integrate the provided TypeScript migration runner to automate the execution of pending SQL scripts and track migration history within an application.
Database Indexing: Optimize query performance by following patterns for creating and managing database indexes alongside table migrations.

name	database-migrations
description	SQLite database migration patterns for SpecFlux. Use when creating new tables, modifying schema, adding indexes, or running migrations. Ensures reversible migrations with UP and DOWN sections.

name

database-migrations

description

SQLite database migration patterns for SpecFlux. Use when creating new tables, modifying schema, adding indexes, or running migrations. Ensures reversible migrations with UP and DOWN sections.

Database Migration Patterns

Migration Files

Keep migrations simple and atomic:

-- migrations/003_add_notifications.sql
-- UP
CREATE TABLE notifications (
  id INTEGER PRIMARY KEY,
  project_id INTEGER NOT NULL,
  type TEXT NOT NULL,
  title TEXT NOT NULL,
  message TEXT,
  task_id INTEGER,
  is_read BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id),
  FOREIGN KEY (task_id) REFERENCES tasks(id)
);

CREATE INDEX idx_notifications_project_id ON notifications(project_id);
CREATE INDEX idx_notifications_is_read ON notifications(is_read);

-- DOWN
DROP INDEX IF EXISTS idx_notifications_is_read;
DROP INDEX IF EXISTS idx_notifications_project_id;
DROP TABLE notifications;

Migration Runner

// src/db/migrate.ts
import Database from 'better-sqlite3';
import fs from 'fs';
import path from 'path';

export async function runMigrations(db: Database.Database) {
  // Create migrations table
  db.exec(`
    CREATE TABLE IF NOT EXISTS migrations (
      id INTEGER PRIMARY KEY,
      name TEXT NOT NULL UNIQUE,
      applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `);

  // Get applied migrations
  const applied = db.prepare('SELECT name FROM migrations').all() as { name: string }[];
  const appliedNames = new Set(applied.map(m => m.name));

  // Read migration files
  const migrationsDir = path.join(__dirname, '../../migrations');
  const files = fs.readdirSync(migrationsDir).filter(f => f.endsWith('.sql')).sort();

  // Apply pending migrations
  for (const file of files) {
    if (appliedNames.has(file)) continue;

    console.log(`Applying migration: ${file}`);
    const sql = fs.readFileSync(path.join(migrationsDir, file), 'utf-8');
    const upSQL = sql.split('-- DOWN')[0].replace('-- UP', '').trim();

    db.exec(upSQL);
    db.prepare('INSERT INTO migrations (name) VALUES (?)').run(file);
  }
}

Testing Migrations

Always test UP and DOWN:

# Test UP
npm run migrate

# Test DOWN (rollback)
npm run migrate:rollback

# Test UP again
npm run migrate