Construye un sistema de migraciones SQL simple con Node.js y SQLite

Proyecto práctico: un pequeño runner de migraciones que te permite aplicar y revertir archivos SQL (.up.sql / .down.sql), mantener el historial en la BD y ver el estado. Ideal para proyectos pequeños o para entender cómo funcionan las migraciones por debajo de herramientas como Flyway o Liquibase.

Prerequisitos

• Node.js v14+ y npm
• Conocimientos básicos de SQL
• SQLite (no es necesario instalar aparte, usaremos la librería sqlite3 que crea el archivo .sqlite)

Estructura de carpetas

mi-migrator/
├─ migrations/
│  ├─ 001_create_users.up.sql
│  ├─ 001_create_users.down.sql
│  ├─ 002_add_email.up.sql
│  └─ 002_add_email.down.sql
├─ db.js
├─ migrate.js
└─ package.json

Instalación rápida

mkdir mi-migrator && cd mi-migrator
npm init -y
npm install sqlite3
# luego crea las carpetas y archivos según la estructura

Archivo: package.json (ejemplo mínimo)

{
  "name": "mi-migrator",
  "version": "1.0.0",
  "main": "migrate.js",
  "scripts": {
    "migrate": "node migrate.js"
  },
  "dependencies": {
    "sqlite3": "^5.0.0"
  }
}

Archivo: db.js

Un wrapper pequeño que expone promesas para ejecutar SQL y transacciones.

const sqlite3 = require('sqlite3').verbose();
const { open } = require('sqlite');

// Abrimos la BD usando sqlite (promises API)
async function openDb () {
  // database.sqlite se crea en el mismo directorio
  return open({
    filename: './database.sqlite',
    driver: sqlite3.Database
  });
}

module.exports = { openDb };

Archivo: migrate.js

Script CLI: node migrate.js <up|down|status|create <name>>

#!/usr/bin/env node
const fs = require('fs').promises;
const path = require('path');
const { openDb } = require('./db');

const MIGRATIONS_DIR = path.join(__dirname, 'migrations');

function parseArgs () {
  const [,, cmd, ...rest] = process.argv;
  return { cmd, rest };
}

async function ensureMigrationsTable(db) {
  await db.exec(`CREATE TABLE IF NOT EXISTS migrations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    run_at TEXT NOT NULL
  );`);
}

async function listMigrationFiles() {
  try {
    const files = await fs.readdir(MIGRATIONS_DIR);
    // buscamos archivos .up.sql y .down.sql, agrupamos por prefijo
    const groups = new Map();
    for (const f of files) {
      const full = path.join(MIGRATIONS_DIR, f);
      const stat = await fs.stat(full);
      if (!stat.isFile()) continue;
      const m = f.match(/^([0-9]+_[0-9A-Za-z_\-]+)\.(up|down)\.sql$/);
      if (!m) continue;
      const key = m[1];
      const kind = m[2];
      if (!groups.has(key)) groups.set(key, {});
      groups.get(key)[kind] = f;
    }
    // devolver array ordenado por prefijo (numérico si empiezan con número)
    return Array.from(groups.entries())
      .map(([name, files]) => ({ name, up: files.up, down: files.down }))
      .sort((a,b) => a.name.localeCompare(b.name, undefined, {numeric: true}));
  } catch (err) {
    if (err.code === 'ENOENT') return [];
    throw err;
  }
}

async function status() {
  const db = await openDb();
  await ensureMigrationsTable(db);
  const applied = await db.all('SELECT name, run_at FROM migrations ORDER BY id');
  const files = await listMigrationFiles();
  console.log('Applied migrations:');
  applied.forEach(a => console.log('  ', a.name, a.run_at));
  console.log('\nAvailable migrations:');
  files.forEach(f => console.log('  ', f.name, f.up ? '(up)' : '(missing up)', f.down ? '(down)' : '(missing down)'));
  await db.close();
}

async function up() {
  const db = await openDb();
  await ensureMigrationsTable(db);
  const files = await listMigrationFiles();
  const applied = await db.all('SELECT name FROM migrations');
  const appliedSet = new Set(applied.map(a => a.name));

  const pending = files.filter(f => !appliedSet.has(f.name));
  if (pending.length === 0) {
    console.log('No hay migraciones pendientes.');
    await db.close();
    return;
  }

  for (const m of pending) {
    if (!m.up) {
      console.warn(`Skipp: migración ${m.name} sin archivo .up.sql`);
      continue;
    }
    const sql = await fs.readFile(path.join(MIGRATIONS_DIR, m.up), 'utf8');
    try {
      // Ejecutamos en transacción
      await db.exec('BEGIN');
      await db.exec(sql);
      const now = new Date().toISOString();
      await db.run('INSERT INTO migrations (name, run_at) VALUES (?, ?)', [m.name, now]);
      await db.exec('COMMIT');
      console.log(`Aplicada: ${m.name}`);
    } catch (err) {
      await db.exec('ROLLBACK');
      console.error(`Error aplicando ${m.name}:`, err.message);
      await db.close();
      process.exit(1);
    }
  }
  await db.close();
}

async function down() {
  const db = await openDb();
  await ensureMigrationsTable(db);
  const last = await db.get('SELECT name FROM migrations ORDER BY id DESC LIMIT 1');
  if (!last) {
    console.log('No hay migraciones aplicadas para revertir.');
    await db.close();
    return;
  }
  const files = await listMigrationFiles();
  const m = files.find(x => x.name === last.name);
  if (!m || !m.down) {
    console.error(`No se encontró archivo .down.sql para ${last.name}. Abortando.`);
    await db.close();
    process.exit(1);
  }
  const sql = await fs.readFile(path.join(MIGRATIONS_DIR, m.down), 'utf8');
  try {
    await db.exec('BEGIN');
    await db.exec(sql);
    await db.run('DELETE FROM migrations WHERE name = ?', [m.name]);
    await db.exec('COMMIT');
    console.log(`Revertida: ${m.name}`);
  } catch (err) {
    await db.exec('ROLLBACK');
    console.error(`Error revertiendo ${m.name}:`, err.message);
    await db.close();
    process.exit(1);
  }
  await db.close();
}

async function create(nameParts) {
  if (!nameParts || nameParts.length === 0) {
    console.error('Usa: node migrate.js create ');
    process.exit(1);
  }
  const name = `${String(Date.now())}_${nameParts.join('_')}`;
  const upFile = path.join(MIGRATIONS_DIR, `${name}.up.sql`);
  const downFile = path.join(MIGRATIONS_DIR, `${name}.down.sql`);
  await fs.mkdir(MIGRATIONS_DIR, { recursive: true });
  await fs.writeFile(upFile, '-- Escribe aquí el SQL para aplicar la migración\n');
  await fs.writeFile(downFile, '-- Escribe aquí el SQL para revertir la migración\n');
  console.log(`Creados: ${path.basename(upFile)} y ${path.basename(downFile)}`);
}

(async () => {
  const { cmd, rest } = parseArgs();
  if (!cmd || cmd === 'help') {
    console.log('Comandos: up | down | status | create ');
    process.exit(0);
  }
  if (cmd === 'status') return await status();
  if (cmd === 'up') return await up();
  if (cmd === 'down') return await down();
  if (cmd === 'create') return await create(rest);
  console.error('Comando desconocido');
  process.exit(1);
})();

Ejemplos de migraciones

-- migrations/001_create_users.up.sql
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- migrations/001_create_users.down.sql
DROP TABLE IF EXISTS users;

-- migrations/002_add_email.up.sql
ALTER TABLE users ADD COLUMN email TEXT;

-- migrations/002_add_email.down.sql
-- SQLite no soporta DROP COLUMN; en producción harías una migración más completa.
-- Aquí lo hacemos simple: recrearemos la tabla sin la columna 'email'.
PRAGMA foreign_keys=off;
BEGIN TRANSACTION;
CREATE TABLE users_new (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL
);
INSERT INTO users_new (id, username, created_at) SELECT id, username, created_at FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
COMMIT;
PRAGMA foreign_keys=on;

Por qué esto funciona (y por qué está diseñado así)

• Archivos separados .up.sql y .down.sql: claridad en aplicar y revertir, y compatibilidad con muchas prácticas de migraciones.
• Tabla migrations: permite saber qué migraciones ya se ejecutaron y evitar re-ejecutarlas.
• Ejecutar cada migración dentro de una transacción: evita estados parciales si falla algo.
• Formato de nombre: timestamp_descripcion.up.sql garantiza orden y evita colisiones en entornos concurrentes.
• Uso de SQLite: simplifica el ejemplo; en entornos reales puedes adaptar db.js para Postgres/MySQL usando sus clientes y la misma lógica de runner.

Limitaciones y puntos de mejora

• Esta implementación es simple: no maneja migraciones paralelas en múltiples máquinas ni conflictos (conviene usar locks o un servicio central para eso).
• SQLite tiene limitaciones (p. ej. ALTER TABLE limitado). Para migraciones complejas, ajusta los scripts .down.sql con migraciones destructivas controladas.
• No hay validaciones sobre el SQL. Podrías añadir checksum en la tabla migrations para detectar cambios en archivos ya aplicados.
• En entornos CI/CD, añade un step para ejecutar las migraciones antes del deploy y fallar si no coinciden.

Cómo usarlo

1. Crear nuevas migraciones: node migrate.js create add_users_table
2. Editar los .up.sql / .down.sql generados
3. Aplicar: node migrate.js up
4. Revertir la última: node migrate.js down
5. Ver estado: node migrate.js status

Consejo avanzado: para entornos con varios despliegues paralelos, implementa un lock a nivel de BD (p. ej. CREATE TABLE migration_lock(...) y usa INSERT OR IGNORE para adquirir el lock) o migra a Postgres y usa advisory locks; así previenes race conditions al aplicar migraciones concurrentes.

Advertencia de seguridad: nunca ejecutes SQL dinámico no auditado desde migraciones en producción. Revisa y versiona cada script, y realiza backups antes de migraciones que modifiquen o eliminen datos.

Siguiente paso sugerido: adapta db.js para Postgres (pg) y añade checksums por migración para detectar cambios no deseados en archivos ya aplicados.