Migraciones en SQLite con PRAGMA user_version

Los esquemas cambian. Asúmelo desde el principio.

La primera versión de tu esquema nunca es la definitiva. Tarde o temprano agregas columnas, divides tablas o repiensas los índices. La pregunta no es si tu esquema va a cambiar, sino si ese cambio va a aplicarse limpiamente en cada portátil, servidor y dispositivo de usuario que ya tenga una copia anterior de la base de datos.

Para eso existen las migraciones sqlite: una secuencia de scripts pequeños y ordenados que llevan la base de datos de la versión N a la N+1. Si los ejecutas en orden, cualquier base de datos se pone al día. Si te saltas la disciplina, acabas con esos bugs de "en mi máquina funciona" que te queman la tarde entera depurando.

SQLite te da una sola herramienta nativa para esto: PRAGMA user_version. Es un entero de 32 bits que la base de datos guarda por ti y que SQLite no toca nunca. Tú decides qué significa ese número.

PRAGMA user_version;
PRAGMA user_version = 1;
PRAGMA user_version;

Una base de datos recién creada arranca en 0. Configúralo con el número de la última migración que hayas aplicado y léelo al iniciar la app para saber en qué punto estás.

Un bucle de migración mínimo

La idea es sencilla: cada migración es un script SQL numerado. Tu aplicación lee el user_version actual, ejecuta en orden todos los scripts con un número superior, y actualiza user_version tras cada uno.

Aquí tienes la migración 1, que crea el esquema inicial:

BEGIN;

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);

PRAGMA user_version = 1;

COMMIT;

Hay dos detalles que conviene mirar con calma. Todo el bloque va envuelto en BEGIN; ... COMMIT;, así que es atómico: si el CREATE TABLE falla, el user_version no se incrementa y puedes corregir y volver a ejecutarlo. Además, PRAGMA user_version = 1 es la última instrucción antes del commit, de modo que la versión solo cambia si todo lo demás salió bien.

Ahora imagina que necesitas añadir una columna created_at. Esa sería la migración 2:

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

BEGIN;

ALTER TABLE users ADD COLUMN created_at TEXT
    NOT NULL DEFAULT (datetime('now'));

PRAGMA user_version = 2;

COMMIT;

SELECT sql FROM sqlite_master WHERE name = 'users';

Una base de datos en la versión 0 ejecuta ambas. Una base de datos en la versión 1 ejecuta solo la segunda. Una base de datos en la versión 2 no ejecuta nada. El orden es el contrato.

Qué puede y qué no puede hacer ALTER TABLE

El ALTER TABLE de SQLite es deliberadamente limitado. Permite lo siguiente:

• ADD COLUMN — agregar una nueva columna con un valor por defecto opcional.
• DROP COLUMN — eliminar una columna (desde la 3.35).
• RENAME COLUMN — renombrar una columna (desde la 3.25).
• RENAME TO — renombrar la propia tabla.

Y hasta ahí. No puedes cambiar el tipo de una columna, modificar un NOT NULL, alterar una restricción CHECK ni agregar una FOREIGN KEY a una columna existente.

-- No soportado:
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);
ALTER TABLE users ADD CONSTRAINT users_email_check CHECK (email LIKE '%@%');

Cuando necesitas un cambio que SQLite no puede hacer directamente, la receta oficial es "reconstruir la tabla". Es más larga, pero funciona siempre sin sorpresas.

Reconstruir una tabla para cambios mayores

El patrón es el siguiente: creas una tabla nueva con la forma que quieres, copias los datos, eliminas la vieja y renombras la nueva para que ocupe su lugar. Todo dentro de una misma transacción.

BEGIN;

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);
INSERT INTO users (email, name) VALUES ('a@x.com', 'Ada'), ('b@x.com', 'Boris');

-- Ahora queremos una restricción CHECK en email. Reconstruir:
CREATE TABLE users_new (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE CHECK (email LIKE '%@%'),
    name  TEXT NOT NULL
);

INSERT INTO users_new (id, email, name)
    SELECT id, email, name FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

SELECT * FROM users;

La documentación oficial de SQLite llama a esto la receta de 12 pasos y añade algunas advertencias extra sobre triggers, vistas y referencias de claves foráneas — vale la pena leerla al menos una vez antes de aplicarla sobre un esquema en producción. Para la mayoría de los casos, la versión de cuatro pasos de arriba es más que suficiente.

Un aviso importante: si tienes claves foráneas que apuntan a la tabla que vas a reconstruir, ejecuta PRAGMA foreign_keys = OFF antes de la migración y PRAGMA foreign_keys = ON al terminar. De lo contrario, el DROP TABLE puede romper la integridad referencial a mitad del proceso.

Cómo ejecutar migraciones SQLite desde tu aplicación

La lógica de control es lo bastante sencilla como para escribirla tú mismo. En Python, usando solo la librería estándar:

-- Conceptualmente, tu aplicación hace esto al iniciar:
--
--   current = SELECT user_version FROM PRAGMA;
--   for i in range(current + 1, len(MIGRATIONS) + 1):
--       ejecutar MIGRATIONS[i] dentro de una transacción
--       PRAGMA user_version = i;
--
-- El SQL que ejecuta cada paso se parece a las migraciones anteriores.

PRAGMA user_version;

Las invariantes clave:

• Las migraciones se numeran de forma consecutiva empezando en 1. Sin saltos ni reordenamientos.
• Cada migración va envuelta en una transacción junto con el incremento de PRAGMA user_version = N.
• Una vez que una migración se ha confirmado y desplegado, no se toca nunca más. Cualquier cambio nuevo va en una migración nueva.

Esa última regla es la que más equipos se saltan. Si editas la migración 3 después de que un colega ya la haya aplicado en su base de datos, la suya queda desincronizada con la tuya para siempre, y sin avisar.

Registrar un historial de auditoría

user_version te dice en qué punto está una base de datos. Pero no te dice cuándo se ejecutó cada paso ni qué hizo. Una pequeña tabla de control soluciona eso:

CREATE TABLE schema_migrations (
    version    INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    applied_at TEXT NOT NULL DEFAULT (datetime('now'))
);

BEGIN;

CREATE TABLE posts (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    body   TEXT NOT NULL
);

INSERT INTO schema_migrations (version, name)
    VALUES (1, 'create_posts');
PRAGMA user_version = 1;

COMMIT;

SELECT * FROM schema_migrations;

Ahora tienes una fila por migración con su nombre y marca de tiempo, algo muy útil cuando estás depurando y te preguntas: "¿por qué esta base de datos tiene una columna que el código no espera?".

PRAGMA user_version sigue siendo la fuente de verdad para el bucle; la tabla está pensada para humanos.

Rollback de migraciones: qué te dan las transacciones y qué no

El DDL de SQLite es transaccional. Si la migración 5 empieza a crear una tabla, copiar datos y subir user_version, y la copia falla a medio camino, ROLLBACK deshace todo, incluido el CREATE TABLE. La base de datos queda exactamente como estaba antes del BEGIN.

BEGIN;

CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL);
INSERT INTO accounts (id, balance) VALUES (1, 100);

-- Imagina que algo sale mal aquí:
ROLLBACK;

-- La tabla no existe:
SELECT name FROM sqlite_master WHERE type='table' AND name='accounts';

Hasta aquí hemos visto las migraciones fallidas. Pero falta hablar de las migraciones que se aplicaron correctamente y de las que ahora te arrepientes. Para esos casos se escribe una migración de bajada aparte: un script que deshace el cambio. SQLite no tiene una marcha atrás automática. Si la migración 7 añadió una columna, la versión de bajada la elimina. Si la migración 7 eliminó una columna, la versión de bajada no puede recuperar los datos; lo máximo que puede hacer es volver a crear la columna vacía.

En la práctica, muchos proyectos pequeños prescinden por completo de las migraciones de bajada y se apoyan en copias de seguridad para "deshacer". Es una decisión válida, siempre y cuando hagas esas copias.

Buenas costumbres que te ahorran disgustos

• Una migración por cambio lógico. Una migración que añade tres columnas sin relación entre sí cuesta más de revisar y de revertir que tres migraciones independientes.
• Prueba las migraciones contra una copia de producción. Los cambios de esquema pueden ser lentos en tablas grandes; descubrirlo en producción no tiene ninguna gracia.
• Nunca edites una migración ya publicada. Crea una nueva.
• Haz copia de seguridad antes. Un .backup rápido desde la CLI o copiar el fichero con la base de datos cerrada es un seguro barato antes de cualquier migración no trivial.
• Cuidado con PRAGMA foreign_keys. Desactívalo mientras reconstruyes tablas y vuélvelo a activar al terminar.

Para proyectos más grandes, tira de una herramienta dedicada: Alembic con SQLAlchemy, golang-migrate, Knex, Flyway. Se encargan del orden, de la ejecución concurrente y de las convenciones de equipo que, si no, acabarías reinventando. El principio es el mismo que el del bucle anterior; la herramienta solo te quita el código repetitivo.

Lo que viene: modo WAL y concurrencia

Las migraciones normalmente se ejecutan con la aplicación parada o manteniendo un bloqueo exclusivo. El resto del tiempo, tu base de datos está atendiendo lecturas y escrituras desde varias conexiones a la vez, y el modo de journal por defecto de SQLite no siempre es la mejor opción. La siguiente página explica el modo WAL, qué cambia y cuándo conviene activarlo.