Cómo versionar el esquema de la base de datos de una aplicación empresarial — Migraciones que evitan que «cada cliente tenga una base de datos distinta»

«En la base de datos que instalamos en la empresa A existe esta columna, pero en la de la empresa B no está. Y ya ni siquiera sabemos en qué versión se añadió» — cuando se hereda el mantenimiento de una aplicación empresarial instalada en distintos clientes, es muy probable encontrarse con esta situación.

El procedimiento de actualización dice «ejecute este SQL en la base de datos». Pero solo la persona que hizo el trabajo in situ sabe si realmente se ejecutó, y con el tiempo se van mezclando clientes en los que se olvidó ejecutarlo, clientes en los que quedó a medias tras un error y clientes a los que, por haber actualizado saltándose versiones, les falta algún ALTER TABLE intermedio. Años después, buena parte del esfuerzo de mantenimiento se termina consumiendo en investigar «errores que solo ocurren en un cliente concreto».

En este blog ya presentamos, en «Cómo elegir dónde guardar los datos de una aplicación de Windows», una versión mínima de código para numerar el esquema, y en «Usar SQLite en aplicaciones empresariales con C#» explicamos el diseño operativo de SQLite. Este artículo continúa desde ahí y profundiza en cómo versionar los cambios del esquema de la base de datos y cómo aplicarlos con seguridad a las numerosas bases de datos dispersas entre los clientes. Tomaremos SQLite como material principal, pero organizando el diseño de forma que también sea válido para SQL Server (Express).

1. La conclusión, primero

• Los cambios de esquema no deben ser un procedimiento SQL, sino código (migraciones numeradas) incluido en la propia aplicación, que se aplica automáticamente al iniciarla. El modelo en el que una persona ejecuta un procedimiento se rompe en cuanto la base de datos se dispersa entre clientes.
• La base de datos debe registrar por sí misma su versión de esquema actual. En SQLite, PRAGMA user_version es precisamente el espacio reservado para este uso.¹ En SQL Server, el historial de aplicación se guarda en una tabla dedicada.
• Las migraciones son solo hacia adelante y solo por adición. El SQL de un número ya publicado no se reescribe; cualquier corrección se hace con un número nuevo. Así, incluso una actualización que salta de v1.2 a v1.5 se reduce a «aplicar en orden lo que falte».
• Los cambios destructivos (eliminar o renombrar columnas) se hacen mediante un lanzamiento en dos fases de tipo expand-contract. Expand-contract es una técnica de lanzamiento en dos fases en la que primero se añade la nueva estructura sin tocar la existente (expand) y, una vez completada la migración del lado de la aplicación, se elimina la estructura antigua (contract). Primero se publica una versión que solo añade, y solo cuando ya no queda ninguna referencia al formato antiguo se publica la versión que elimina (sección 5.1).
• El riesgo de que una versión antigua de la aplicación abra una base de datos nueva se controla con una comprobación de versión mínima. Aquí, «el número de esquema actual» y «el límite inferior de exclusión» se mantienen como dos valores independientes. Si se usa el mismo valor para ambas cosas, la aplicación antigua queda excluida en el instante en que se aplica el expand, y el periodo de coexistencia descrito arriba no puede darse (sección 5.2).
• Antes de aplicar los cambios, se toma una copia de seguridad automática. En SQLite, una sola sentencia VACUUM INTO produce una copia consistente², de modo que la recuperación ante un fallo se reduce a sustituir el archivo.
• Una migración equivale a una transacción, e incluso la actualización del número de versión va dentro de esa misma transacción. En SQLite, incluso el DDL puede revertirse dentro de una transacción.³ SQL Server tiene DDL que constituye una excepción, así que esas operaciones se separan en una migración propia.⁴

2. Por qué se produce el problema de «cada cliente con una base de datos distinta»

Si se descompone el origen del problema, todos los caminos llevan a lo mismo: una operación que presupone que lo hace una persona.

• Olvidos en la aplicación manual de ALTER. En ningún lugar de la base de datos queda constancia de si se ejecutó el SQL del procedimiento, y en cuanto el único medio de comprobación es «mirar visualmente la definición de la tabla», los olvidos son inevitables.
• Fallos a mitad de camino que se dejan sin resolver. Cuando el tercero de cinco SQL del procedimiento falla, quien lo ejecuta no puede decidir si continuar o revertir, y termina dejándolo así porque «la aplicación funciona». Esa base de datos queda entonces con un esquema único en el mundo, que ya no coincide con ninguna versión.
• Actualizaciones que saltan versiones. En un cliente que pasa de v1.2 directamente a v1.5, es necesario aplicar correctamente y en conjunto los cambios de esquema de v1.3 y v1.4, algo difícil de lograr con una operación basada en procedimientos.
• Parches locales de emergencia. Se producen casos de «a este cliente en concreto le añadimos la columna antes», que después provocan un error de doble aplicación cuando llega la actualización oficial.

En un sistema web con un solo servidor hay una única base de datos, y su estado siempre se puede conocer. La dificultad esencial de las aplicaciones empresariales de escritorio es que la base de datos de una misma aplicación se dispersa en decenas o cientos de PC de clientes y sedes, y no todas están necesariamente en la misma versión. Una operación en la que una persona atiende máquina por máquina se rompe en proporción al número de equipos, así que solo hay una conclusión posible: dotar a la propia aplicación de la capacidad de inspeccionar su base de datos y llevarla hasta el esquema más reciente.

3. El patrón básico: versión de esquema + migraciones progresivas

El esqueleto del mecanismo consta de solo tres elementos.

1. La base de datos tiene su propio número de versión de esquema (un entero exclusivo para el esquema, distinto de la versión del producto de la aplicación).
2. Los cambios de esquema se van añadiendo al código de la aplicación como una lista de migraciones numeradas.
3. Al iniciarse (justo después de conectar con la base de datos), la aplicación aplica en transacción, en orden, las migraciones con número mayor que la versión actual.

El flujo en el arranque, representado en un diagrama, queda así. Todas las salvaguardas que se irán añadiendo en los capítulos 5 y 6 encajan en algún punto de este flujo.

flowchart TD
    accTitle: Flujo de aplicación de migraciones en el arranque
    accDescr: Diagrama de flujo que muestra cómo la aplicación lee la versión de esquema y el número mínimo compatible al conectar con la base de datos, decide si debe interrumpir el arranque, omitir la aplicación por tratarse de un esquema futuro compatible, o aplicar en orden las migraciones pendientes dentro de una copia de seguridad y transacciones, hasta llegar al arranque normal
    S["Arranque de la aplicación y conexión a la BD"] --> R["Leer PRAGMA user_version y el número mínimo compatible"]
    R --> Q1{"¿El número mínimo compatible<br/>es mayor que el máximo que conoce la aplicación?"}
    Q1 -->|"Mayor"| STOP["Interrumpir el arranque (sección 5.2)"]
    Q1 -->|"No"| Q2{"¿El número actual<br/>es mayor que el máximo que conoce la aplicación?"}
    Q2 -->|"Mayor"| FUT["Esquema futuro compatible.<br/>No se aplica nada, arranque normal (sección 5.2)"]
    Q2 -->|"No"| Q3{"¿Hay migraciones sin aplicar?"}
    Q3 -->|"No"| OK["Arranque normal"]
    Q3 -->|"Sí"| BK["Tomar copia de seguridad antes de aplicar<br/>(VACUUM INTO, sección 5.3)"]
    BK --> LOOP["Aplicar los números pendientes de uno en uno, en orden ascendente (sección 6.1)<br/>BEGIN TRANSACTION → cambio de esquema y conversión de datos →<br/>PRAGMA user_version = ese número → COMMIT"]
    LOOP -.->|"Si falla a mitad de camino"| FAIL["Solo esa migración se revierte,<br/>y queda en el número anterior"]
    LOOP --> DONE["Al llegar a la última, arranque normal"]

Figura 1: flujo de aplicación en el arranque. Todas las salvaguardas añadidas en los capítulos 5 y 6 encajan en algún punto de este flujo.

En el caso de SQLite, PRAGMA user_version sirve como lugar donde guardar el número de versión. Es un entero almacenado en la cabecera de la base de datos (desplazamiento 60), y la documentación oficial indica explícitamente que «la aplicación puede usarlo libremente, y el propio SQLite no utiliza este valor».¹ Sin necesidad de crear una tabla dedicada, el propio archivo de base de datos puede declarar su versión.

Una implementación propia en C# resulta funcional con apenas unas pocas decenas de líneas, como las siguientes.

using Microsoft.Data.Sqlite;

public static class SchemaMigrator
{
    // Lista de solo-adición. El SQL de un número ya publicado nunca se reescribe
    private static readonly (int Version, string Sql)[] Migrations =
    {
        (1, """
            CREATE TABLE customer (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
            -- Tabla donde vive el límite inferior de exclusión. Se crea dentro
            -- del número 1 y se le da un valor inicial.
            -- Si se separa en otro lugar, nadie llega a ejecutarla:
            -- GetMinCompatibleVersion siempre devuelve 0, la exclusión no
            -- funciona, y el primer contract falla porque UPDATE schema_meta
            -- encuentra que la tabla no existe (sección 5.2)
            CREATE TABLE schema_meta (key TEXT PRIMARY KEY, value INTEGER NOT NULL);
            INSERT INTO schema_meta (key, value) VALUES ('min_compatible_version', 0);
            """),
        (2, "ALTER TABLE customer ADD COLUMN phone TEXT"),
        (3, """
            CREATE TABLE invoice (
                id          INTEGER PRIMARY KEY,
                customer_id INTEGER NOT NULL REFERENCES customer(id),
                issued_at   TEXT    NOT NULL,  -- se guarda en UTC con formato fijo
                amount      INTEGER NOT NULL   -- el importe es un entero en la unidad monetaria mínima
            )
            """),
    };

    public static void Migrate(SqliteConnection conn)
    {
        // Un error al añadir (número duplicado o fuera de orden) provoca una
        // doble aplicación u omisión silenciosa, así que se detecta y se
        // detiene antes de aplicar nada
        for (int i = 1; i < Migrations.Length; i++)
            if (Migrations[i].Version <= Migrations[i - 1].Version)
                throw new InvalidOperationException(
                    "Los números de migración deben ser ascendentes y únicos.");

        int current = GetUserVersion(conn);
        int latest = Migrations[^1].Version;
        int minCompatible = GetMinCompatibleVersion(conn);

        // La exclusión se decide con el "número mínimo compatible", no con el
        // "número de esquema". Si aquí se comparara con current > latest, en
        // el instante en que la aplicación nueva aplica un expand subiría
        // user_version, y la aplicación antigua se quedaría sin poder abrir
        // la base de datos en ese mismo momento -- lo que anularía por completo
        // el diseño acordado en 5.1, donde "durante la coexistencia escriben
        // tanto la versión nueva como la antigua". El número mínimo compatible
        // solo se sube en el momento del contract
        if (minCompatible > latest)
            throw new InvalidOperationException(
                $"Esta base de datos requiere una aplicación que entienda el " +
                $"esquema v{minCompatible} o posterior (esta aplicación solo " +
                $"conoce hasta v{latest}). Actualice la aplicación.");

        if (current > latest)
            // Es un esquema futuro que esta aplicación no conoce, pero está
            // declarado como compatible. No hay nada que aplicar (todo es
            // <= current), así que se continúa con el arranque normal sin
            // tocar columnas desconocidas (detalles en la sección 5.2)
            return;

        foreach (var (version, sql) in Migrations)
        {
            if (version <= current) continue;

            using var tx = conn.BeginTransaction();
            using var cmd = conn.CreateCommand();
            cmd.Transaction = tx;
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            // La actualización de versión también se confirma en la misma
            // transacción. Así desaparece el estado de "el cambio entró pero
            // el número sigue siendo el antiguo"
            cmd.CommandText = $"PRAGMA user_version = {version}";
            cmd.ExecuteNonQuery();
            tx.Commit();
        }
    }

    private static int GetUserVersion(SqliteConnection conn)
    {
        using var cmd = conn.CreateCommand();
        cmd.CommandText = "PRAGMA user_version";
        return Convert.ToInt32(cmd.ExecuteScalar());
    }

    // El "límite inferior de aplicaciones que pueden abrir esta BD". Lo
    // importante es mantenerlo separado de user_version: si se comparte el
    // mismo valor, la aplicación antigua queda excluida en el instante en
    // que se aplica el expand. Solo se sube en las migraciones de contract
    // (secciones 5.1 y 5.2)
    private static int GetMinCompatibleVersion(SqliteConnection conn)
    {
        using var exists = conn.CreateCommand();
        exists.CommandText =
            "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'schema_meta'";
        if (exists.ExecuteScalar() is null) return 0;   // BD antigua sin la tabla. Sin límite inferior

        using var cmd = conn.CreateCommand();
        cmd.CommandText =
            "SELECT value FROM schema_meta WHERE key = 'min_compatible_version'";
        var value = cmd.ExecuteScalar();
        return value is null or DBNull ? 0 : Convert.ToInt32(value);
    }
}

Como se ve arriba, schema_meta se crea dentro de la migración número 1. Si se extrae a otro script, nadie llega a ejecutarlo y GetMinCompatibleVersion siempre devuelve 0, con lo que la exclusión no funciona en absoluto. Además, el primer contract falla porque UPDATE schema_meta encuentra que la tabla no existe.

Al añadir este mecanismo a una base de datos ya existente, las bases de datos donde ya se aplicó el número 1 no tienen schema_meta. En la migración de introducción, recréela con CREATE TABLE IF NOT EXISTS e INSERT OR IGNORE (basta con añadir un número más). El motivo por el que GetMinCompatibleVersion comprueba primero si la tabla existe es precisamente este periodo de transición.

Este valor solo se eleva en las migraciones de contract.

-- Se sube en la migración que elimina la columna antigua, dentro de la misma transacción
UPDATE schema_meta SET value = 7 WHERE key = 'min_compatible_version';
ALTER TABLE customer DROP COLUMN old_name;

Con esto, el problema del capítulo 2 queda resuelto estructuralmente. No se producen olvidos de aplicación (se comprueba en cada arranque), los fallos a mitad de camino se revierten (capítulo 6), y saltar versiones tampoco es un problema (si la base de datos de v1.2 está en el esquema v2, la aplicación v1.5 simplemente aplica 3, 4 y 5 en orden). Incluso la pregunta «¿en qué estado está la base de datos de este cliente?» se responde leyendo PRAGMA user_version una sola vez.

Solo hay dos reglas operativas que se deben cumplir sin excepción.

• No se reescribe un número ya publicado. Aunque el SQL de v3 tenga un error, se corrige en v4. Reescribirlo crearía una nueva dispersión: bases de datos con «la v3 antigua aplicada» y bases de datos con «la v3 nueva aplicada».
• La conversión de datos también forma parte de la migración. No basta con añadir columnas: el traslado de los datos existentes (UPDATE) se hace dentro del mismo número. Si desde el principio se unifica el formato y la zona horaria de las columnas de fecha y hora a UTC con formato fijo, como se explica en «El datetime y las zonas horarias en aplicaciones empresariales», las migraciones posteriores resultan más simples.

Hay una tarea más, que solo aplica al añadir este mecanismo a un sistema ya existente. Una base de datos que se ha operado con parches manuales puede encontrarse en un estado en el que user_version sigue en 0 mientras el esquema real ya ha avanzado parcialmente (precisamente el caso de los parches de emergencia del capítulo 2). Si se sube tal cual a esta cadena, el ALTER TABLE correspondiente a un cambio ya aplicado falla con un error de «la columna ya existe». En la primera versión de la introducción del mecanismo, como tratamiento de referencia único, se inspecciona el esquema real (en SQLite, comprobando la existencia de columnas con PRAGMA table_info), se graba el número de versión correspondiente en las bases de datos que ya tienen aplicado algún parche manual conocido, y a partir de ahí se deja el resto en manos de las migraciones progresivas. Este paso solo se puede omitir si el mecanismo se incorpora desde el lanzamiento inicial.

SQL Server no tiene un equivalente de user_version, así que se inserta una fila por cada aplicación en una tabla dedicada (por ejemplo, schema_version) con el número de versión, la fecha y hora de aplicación, y la versión de la aplicación en ese momento. Al quedar el historial como filas, resulta más sólido para investigaciones posteriores.

4. Usar una herramienta o hacerlo a mano — tabla de decisión

Existen tres familias de medios para lograr lo mismo: EF Core Migrations, una biblioteca de migraciones (como DbUp) y la implementación propia del capítulo anterior. Como el nombre por sí solo no aclara qué hace cada herramienta, va primero una breve descripción de cada una.

• EF Core (Entity Framework Core) — el mapeador objeto-relacional de Microsoft para .NET (una biblioteca que asocia objetos con tablas y genera SQL automáticamente). Su función incorporada, EF Core Migrations, detecta los cambios en el modelo (las definiciones de clase) escrito en C# y genera automáticamente el código que cambia el esquema.
• DbUp — una biblioteca de .NET de código abierto especializada en gestionar la aplicación de scripts SQL. El SQL lo escribe uno mismo; DbUp solo se encarga de registrar qué scripts ya se aplicaron y de ejecutar los pendientes.⁵

Según la situación, la recomendación es la siguiente.

DbUp es «una biblioteca de .NET que ayuda a desplegar cambios en bases de datos SQL Server»: registra los scripts ya ejecutados en una tabla de bitácora y ejecuta solo los pendientes. También admite SQLite, PostgreSQL, MySQL y otros motores.⁵ Como camino de migración para «convertir el SQL de los procedimientos en una aplicación automática con registro de ejecución», es el más corto.

4.1 Precauciones al usar EF Core Migrations en una aplicación distribuida

Durante el desarrollo, EF Core se aplica con dotnet ef database update, pero en el PC del cliente no hay ni SDK ni código fuente. El método realista de aplicación es context.Database.Migrate() en el arranque de la aplicación.

Lo que conviene saber aquí es que la documentación de Microsoft advierte explícitamente contra la aplicación en el arranque como método de gestión de una base de datos de producción. Los motivos son cinco: (1) fallos o daños por aplicación simultánea desde varias instancias (antes de EF Core 9); (2) que otra aplicación acceda a la base de datos mientras se está aplicando puede causar problemas graves; (3) la aplicación necesita permisos elevados para cambiar el esquema; (4) los medios de reversión son escasos; y (5) no se puede revisar ni corregir de antemano el SQL que se va a ejecutar. La recomendación es generar scripts SQL y aplicarlos en el proceso de despliegue.⁷

Ahora bien, esta recomendación parte del supuesto de un sistema de servidor con «una única base de datos y un proceso de despliegue». En una aplicación de escritorio con una base de datos local en cada PC del cliente, ir con los scripts a cada sitio a aplicarlos manualmente es exactamente el problema del capítulo 2, así que Migrate() en el arranque se convierte, en la práctica, en la solución estándar. Lo que queda es atender las preocupaciones restantes.

• Ejecución simultánea: desde EF Core 9, Migrate() obtiene un bloqueo automáticamente y evita que varios procesos ejecuten la migración al mismo tiempo.⁷ En versiones anteriores, hay que serializarlo manualmente, como en el capítulo 6. Sin embargo, este bloqueo solo serializa las ejecuciones de migración entre sí: no impide que una versión antigua de la aplicación lea y escriba con normalidad mientras se está aplicando. En una base de datos compartida, hay que combinarlo con la comprobación de versión mínima (sección 5.2) o con una franja de mantenimiento.
• Revisión previa del SQL: revise siempre la migración generada y ensáyela antes del lanzamiento con una base de datos equivalente a los datos reales (sección 6.3).
• No combinar con EnsureCreated(): el esquema se construiría sin historial de migraciones, y Migrate() fallaría más adelante. Use Migrate() de forma exclusiva desde el principio.⁷

En el proveedor de SQLite, una migración que cambia el tipo de una columna o la elimina se ejecuta como una reconstrucción de tabla («crear tabla nueva → copiar datos → eliminar tabla antigua → renombrar»), y tampoco se pueden generar scripts idempotentes.⁶ La decisión de si usar o no EF Core en sí ya se organizó en el capítulo 8 de «Usar SQLite en aplicaciones empresariales con C#».

5. Cómo escribir migraciones que no rompan nada

El principio de cada migración individual es: no realizar en un único lanzamiento un cambio que rompa la compatibilidad hacia atrás.

5.1 Los cambios destructivos, con expand-contract (lanzamiento en dos fases)

Añadir una columna es seguro, pero eliminarla, renombrarla o cambiar su tipo rompe cualquier cosa que dé por sentado el formato antiguo. Incluso con SQLite local y una relación de uno a uno entre aplicación y base de datos, casi siempre existe alguno de estos tres factores: (a) la posibilidad de volver a una versión antigua de la aplicación si aparece un fallo; (b) otras herramientas que leen la base de datos directamente (herramientas de informes, exportadores a CSV, integraciones con Access); o (c) una configuración de SQL Server en la que clientes nuevos y antiguos consultan la base a la vez. Por eso, los cambios destructivos se dividen en dos fases: expand (ampliar) y contract (reducir).

Visto en una línea de tiempo, lo esencial es intercalar entre ambas un periodo de coexistencia en el que funcionan tanto el formato antiguo como el nuevo.

flowchart TB
    accTitle: Línea de tiempo del lanzamiento en dos fases expand-contract
    accDescr: Diagrama que muestra la secuencia entre el lanzamiento A de tipo expand, el periodo de coexistencia en el que conviven la estructura antigua y la nueva, el momento en que la comprobación de versión mínima permite excluir a la aplicación antigua, y el lanzamiento B de tipo contract que elimina la estructura antigua
    A["Lanzamiento A (expand: ampliar)<br/>Se añade la nueva estructura y se conserva tal cual la antigua<br/>La aplicación escribe en ambas, y en la lectura la estructura antigua es la referencia"]
    B["Periodo de coexistencia<br/>La aplicación y las herramientas antiguas siguen funcionando tal cual (ambas estructuras están vivas)<br/>Durante este periodo se actualizan todos los clientes a la nueva versión"]
    C["Con la comprobación de versión mínima (sección 5.2)<br/>se llega al punto en que se puede excluir a la aplicación antigua"]
    D["Lanzamiento B (contract: reducir)<br/>Se copia / convierte de forma definitiva el último valor de la estructura antigua a la nueva<br/>Se cambia la lectura a la nueva estructura y se elimina la antigua"]
    A --> B --> C --> D

Figura 2: se intercala un periodo de coexistencia entre expand y contract. Lo esencial es no publicar el contract hasta poder excluir a la aplicación antigua.

Renombrar una columna tiene muchas ramificaciones y no cabe en una sola celda de la tabla. Desglosando el procedimiento, queda así.

1. expand: se añade la columna nueva y se copia el valor de la antigua. Dentro del mismo número de migración se ejecutan ALTER TABLE ... ADD COLUMN y UPDATE.
2. Periodo de coexistencia: la aplicación nueva escribe en ambas columnas, y la lectura toma la columna antigua como referencia. El motivo de dejar la lectura en la columna antigua es que, en una base de datos compartida donde la aplicación antigua y la nueva conviven, la aplicación antigua solo escribe en la columna antigua. Si se leyera la columna nueva, se pasarían por alto las actualizaciones que hizo la aplicación antigua. También existe la opción de sincronizar de la columna antigua a la nueva mediante un trigger en la base de datos.
3. Se excluye a la aplicación antigua. Con la comprobación de versión mínima (sección 5.2), se llega a un estado en el que la aplicación antigua ya no puede abrir esa base de datos. Hasta este punto no se excluye a nadie: en el paso 1 user_version sube, pero el número mínimo compatible se mantiene igual, así que la aplicación antigua puede seguir abriendo la base de datos y escribiendo en la columna antigua. Si estos dos valores se compartieran, el periodo de coexistencia del paso 2 desaparecería en el instante en que se aplica el paso 1.
4. contract: se hace la copia final del último valor de la columna antigua a la nueva, luego se cambia la lectura a la columna nueva y se elimina la antigua. Este orden es crucial: si se cambia la lectura a la columna nueva antes de excluir a la aplicación antigua, se pierden las actualizaciones que esta escribió solo en la columna antigua.

El lanzamiento del lado contract (reducir) es seguro publicarlo solo después de que la comprobación de versión mínima (siguiente sección) permita excluir a la aplicación antigua.

Como particularidad propia de SQLite, ALTER TABLE solo admite renombrar la tabla, renombrar columnas, añadir columnas y eliminar columnas, y la eliminación de columnas tiene además muchas restricciones («no se puede si la columna tiene PRIMARY KEY o una restricción UNIQUE», «no se puede si la columna la referencia un índice, una restricción CHECK, una clave foránea o una vista»). El resto de los cambios se realizan siguiendo el procedimiento que define la documentación oficial: «dentro de una transacción, crear la tabla nueva, trasladar los datos con INSERT INTO new_X SELECT ... FROM X, eliminar la tabla antigua y renombrar».³ En tablas grandes esto implica copiar todos los registros, así que hay que prever el tiempo de aplicación y el espacio libre en disco.

5.2 Protección contra el retroceso de versión — comprobación de versión mínima

En un diseño basado únicamente en migraciones progresivas no se escriben scripts en sentido de retroceso (no hay ocasión de usarlos en el cliente, y el código sin probar solo es peligroso). Lo que hace falta en su lugar es un mecanismo que detenga la ejecución cuando una versión antigua de la aplicación llega a abrir una base de datos nueva.

Aquí lo esencial es separar el «número de esquema» del «límite inferior de exclusión». En el código del capítulo 3 se manejan dos valores:

• user_version — el número de esquema actual, que sube cada vez que se aplica una migración.
• min_compatible_version en schema_meta — el límite inferior de aplicaciones que pueden abrir esta base de datos, que solo se sube en el momento del contract.

y la exclusión se decide únicamente con el segundo. Si se excluyera con user_version, el periodo de coexistencia de la sección 5.1 no podría darse. En cuanto la aplicación nueva aplica el expand, user_version sube, así que con un criterio de «rechazar si es mayor que el máximo que conozco», la aplicación antigua se quedaría sin poder abrir la base de datos desde ese mismo instante. Con eso, el propio diseño de «durante la coexistencia escriben tanto la aplicación nueva como la antigua» dejaría de funcionar.

Manteniéndolos separados, la evolución es la siguiente.

Desde el punto de vista de la aplicación antigua, esta funciona en un estado de «esquema con un número que no conoce, pero declarado compatible». Aquí se asume como premisa que no toca las columnas que no conoce. Precisamente por eso, los cambios que se introducen en el lado del expand se limitan a añadir columnas, sin cambiar el significado de las ya existentes.

Además, si se establece de antemano que «en un lanzamiento con posibilidad de reversión no se introducen cambios destructivos (solo expand)», que la aplicación antigua lea la base de datos nueva es en sí mismo seguro. También se puede optar por un diseño más flexible, en el que la exclusión se relaje a «mostrar una advertencia y arrancar en modo de solo lectura». Cuál de las dos opciones elegir depende de cuánto se pueda permitir detener la operación del negocio.

5.3 Copia de seguridad automática antes de aplicar

Una migración es una intervención quirúrgica sobre «datos de producción que están en el PC de otra persona». Hay que automatizar el «tomar una copia de seguridad antes de ejecutar». En SQLite, VACUUM INTO es lo más adecuado: con una sola sentencia crea, incluso a partir de una base de datos en funcionamiento, una instantánea consistente en otro archivo.²

// conn      … el SqliteConnection ya abierto (el mismo que se pasa a Migrate en el capítulo 3)
// latest    … el último número de Migrations (igual que latest en el capítulo 3)
// backupDir … dónde se guardan las copias. Si se coloca en la misma carpeta
//             que la BD principal, un fallo de disco las perdería a la vez,
//             así que se recomienda otra unidad o una carpeta compartida
var backupDir = Path.Combine(
    Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData),
    "MyApp", "db-backup");

// Solo cuando hace falta aplicar algo, se toma justo antes una copia de esa generación
if (GetUserVersion(conn) < latest)
{
    Directory.CreateDirectory(backupDir);

    // Aquí se limpian de verdad los archivos de trabajo que quedaron de un
    // fallo anterior. Aunque queden ahí, normalmente no estorban la próxima
    // vez (el nombre incluye la hora), pero si no se borran, cada fallo deja
    // acumulada una cantidad de basura equivalente a una base de datos.
    // Y si se vuelve a ejecutar en el mismo segundo, el nombre coincide, y
    // VACUUM INTO exige que "el destino no exista (o esté vacío)", así que
    // se detiene ahí.
    //
    // Solo se borra lo "suficientemente antiguo". Este bloque presupone que
    // se ejecuta dentro del mutex de 6.2, pero aun así puede haber otra
    // versión u otra herramienta usando la misma carpeta, y si se borra el
    // archivo de trabajo de una ejecución que está corriendo ahora mismo,
    // esa ejecución termina en un FileNotFoundException justo antes de File.Move
    DateTime staleBefore = DateTime.UtcNow - TimeSpan.FromHours(1);
    foreach (var stale in Directory.EnumerateFiles(backupDir, "*.db.tmp"))
    {
        try
        {
            if (File.GetLastWriteTimeUtc(stale) < staleBefore) { File.Delete(stale); }
        }
        catch (IOException) { }                  // otro proceso lo tiene abierto; se deja para la próxima vez
        catch (UnauthorizedAccessException) { }
    }

    var backupPath = Path.Combine(backupDir,
        $"app_schema_v{GetUserVersion(conn)}_{DateTime.Now:yyyyMMdd_HHmmss}.db");
    // Para que un archivo incompleto, producido por un corte de energía o
    // una terminación forzada del proceso a mitad de la ejecución, no pase
    // por "copia de seguridad completa", se crea con un nombre temporal y
    // se renombra solo tras el éxito
    var tempPath = backupPath + ".tmp";
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "VACUUM INTO $path";
    cmd.Parameters.AddWithValue("$path", tempPath);
    cmd.ExecuteNonQuery();  // VACUUM se ejecuta fuera de una transacción
    File.Move(tempPath, backupPath);
}

Ejecute este bloque dentro de la exclusión mutua que se prepara en 6.2. La copia de seguridad forma parte de la migración, no es un paso aparte. Si no se envuelve de un tirón la secuencia «comprobar la versión → tomar la copia → aplicar», en la rutina diaria de una aplicación empresarial donde todo el mundo la abre a la vez a primera hora de la mañana, dos procesos llegan al mismo tiempo a la misma comprobación. Uno limpia el archivo de trabajo del otro, y justo antes de File.Move salta un FileNotFoundException: un fallo difícil de explicar, en el que la copia se tomó correctamente pero solo falla el arranque. Que el código anterior borre «solo lo suficientemente antiguo» es un seguro para reducir el daño si alguien olvida envolver el bloque en la exclusión. Es un seguro, no un sustituto de la exclusión mutua.

Escriba la limpieza de los .tmp como parte de este mismo proceso, no como algo para «hacer después». Si VACUUM INTO se interrumpe por un corte de energía o una terminación forzada del proceso, deja un archivo de salida a medias y dañado.² Como se crea con un nombre temporal, nunca parecerá una «copia de seguridad completa», pero si no se borra, cada fallo va acumulando en el PC del usuario una cantidad de basura equivalente a una base de datos entera. Eso consume espacio en el destino de las copias de forma silenciosa y añade, en el momento de la recuperación, una decisión más sobre «cuál es la copia real».

Y VACUUM INTO exige que el archivo de destino no exista (o esté vacío).² Como el nombre anterior incluye la hora, normalmente no hay colisión, pero si el usuario reinicia de inmediato una aplicación que se cayó durante la migración (o lo hace un servicio de supervisión), puede caer en el mismo segundo, coincidir el nombre y detenerse ahí. Es el tipo de incidencia más difícil de explicar: «no se puede arrancar porque falló la copia de seguridad».

Incluir la versión de esquema en el nombre del archivo permite ver de un vistazo, en el momento de la recuperación, «hasta dónde se retrocede». Para los detalles de la copia de seguridad, incluyendo por qué copiar directamente el archivo de una base de datos en funcionamiento es un foco de corrupción, consulte el capítulo 7 de «Usar SQLite en aplicaciones empresariales con C#». En SQL Server, la idea es la misma, ejecutando BACKUP DATABASE antes de aplicar los cambios.

6. Trampas operativas

6.1 Fallos a mitad de camino y transacciones — conozca las diferencias entre motores

El código del capítulo 3 envuelve cada migración en una transacción, e incluye en esa misma transacción la actualización de user_version. Esto funciona porque SQLite puede ejecutar el DDL (CREATE TABLE, ALTER TABLE, etc.) dentro de una transacción y revertirlo si falla. El propio procedimiento oficial de reconstrucción de tablas tiene esta estructura: «iniciar una transacción, hacer CREATE/INSERT/DROP/RENAME y confirmar».³ Aunque se corte la energía a mitad de camino, la base de datos que se encuentra en el siguiente arranque queda en un estado consistente, el de «justo antes de esa migración».

SQL Server también puede ejecutar la mayoría del DDL dentro de una transacción, pero tiene excepciones. Por ejemplo, ALTER DATABASE no se puede usar dentro de una transacción explícita, y CREATE FULLTEXT INDEX tampoco se puede colocar dentro de una transacción de usuario.⁴ EF Core, por su parte, envuelve automáticamente cada migración en una transacción cuando es posible, pero también deja escrito que «algunas operaciones no se pueden ejecutar dentro de una transacción según la base de datos».⁸ La regla práctica se reduce a esto: no mezclar en la misma migración una operación que no entra en transacción con un cambio de esquema normal. Al cambiar de motor de base de datos, compruebe siempre si el DDL participa o no en las transacciones.

Un accidente clásico es dejar «solo la actualización de versión en una transacción aparte». Si el cambio principal tiene éxito pero el proceso se cae antes de actualizar el número, en el siguiente arranque se reintenta la misma migración y esta falla para siempre con «la tabla ya existe». Incluyendo la actualización del número en la misma transacción, esto no puede ocurrir en principio.

6.2 Arranque simultáneo de varios procesos — serializar la aplicación

Una aplicación empresarial es un software que «todo el mundo arranca a la vez por la mañana». Varios clientes que consultan una base de datos compartida (SQL Server) o múltiples ejecuciones en el mismo PC pueden llegar a ejecutar la migración de manera simultánea.

• Desde EF Core 9, Migrate() obtiene automáticamente un bloqueo sobre toda la base de datos y evita la aplicación simultánea (en versiones anteriores no existe esta protección). El bloqueo del proveedor de SQLite se implementa con una tabla de bloqueo, y la documentación oficial advierte de que, si el proceso que está aplicando la migración termina de forma anómala, la tabla puede quedar residual.⁷ Si la aplicación se queda esperando el bloqueo sin poder arrancar, tras confirmar que no hay ningún otro proceso ejecutando la migración, se puede recuperar eliminando (DROP) la tabla de bloqueo residual (__EFMigrationsLock).
• En una implementación propia, para una base de datos local, serializar con un Mutex con nombre es sencillo.

// using System.Threading;   (Mutex / AbandonedMutexException)
// conn … el SqliteConnection ya abierto. La conexión se deja lista antes de
//        ejecutar la migración, y se llama a Migrate después de tomar el Mutex
using var conn = new SqliteConnection(connectionString);
conn.Open();

// Se antepone Global\ para que, aunque se arranque desde varias sesiones de
// inicio de sesión (RDP o cambio de usuario), la serialización cubra todo
// el PC (Local\ solo cubre la misma sesión)
using var mutex = new Mutex(false, @"Global\MyApp.SchemaMigration");
try
{
    mutex.WaitOne();
}
catch (AbandonedMutexException)
{
    // Caso en el que el proceso propietario anterior terminó de forma
    // anómala sin llamar a Release. Aunque salte la excepción, la propiedad
    // ya se ha obtenido, así que se puede continuar sin problema.
    // La posibilidad de que la aplicación anterior quedara a medias se
    // cubre con la nueva comprobación de versión que sigue y con la
    // transacción de cada migración
}
try
{
    SchemaMigrator.Migrate(conn);
}
finally
{
    mutex.ReleaseMutex();
}

El proceso que quedó esperando vuelve a comprobar la versión tras obtener el bloqueo (el código del capítulo 3 revisa version <= current cada vez antes de aplicar), así que no se produce doble aplicación. Tenga en cuenta que los objetos con nombre bajo Global\ tienen, por defecto, una ACL derivada del usuario que los creó, así que abrir el mismo Mutex desde la sesión de otra cuenta de Windows puede provocar un UnauthorizedAccessException. Si se prevé el uso con varias cuentas, cree el Mutex con MutexAcl de System.Threading.AccessControl, concediendo permisos de sincronización y modificación a los usuarios implicados, o incline el diseño hacia el bloqueo del lado de la base de datos que se describe a continuación. En una base de datos compartida, el Mutex no cruza entre máquinas, así que conviene inclinarse por la serialización del lado de la base de datos: «completar la aplicación en el servidor antes de distribuir la actualización», «tomar un bloqueo del lado de la base de datos al iniciar la aplicación (BEGIN IMMEDIATE en SQLite, un bloqueo de aplicación en SQL Server)», etc.

6.3 Ensayo — probar la aplicación de un tirón desde «la base de datos más antigua»

Los errores de las migraciones casi nunca se descubren en la máquina de desarrollo, porque su base de datos siempre está en el esquema más reciente y con datos limpios. Lo que se rompe es la base de datos del cliente: antigua, grande y con datos inesperados dentro. Hay tres cosas que, como mínimo, deben hacerse antes del lanzamiento.

• Guardar como fixtures de prueba un archivo de base de datos de cada versión de esquema, y automatizar una prueba que aplique de un tirón desde cada una hasta la más reciente. Patrones que saltan versiones, como «de v1 a v5» o «de v3 a v5», son precisamente la realidad de los clientes. En SQLite basta con guardar los archivos de base de datos en el repositorio, así que este tipo de prueba es relativamente fácil de escribir.
• Probar con una cantidad y una calidad de datos equivalente a la real. Columnas llenas de NULL, duplicados inesperados o el tiempo de reconstrucción en tablas enormes (sección 5.1) no salen a la luz si los datos no se parecen a los reales. Si es posible, ensaye con una base de datos de cliente anonimizada.
• Probar los casos de fallo. Mate el proceso a mitad de la aplicación y confirme que, en el siguiente arranque, se recupera correctamente (se reaplica desde la versión a la que se revirtió).

6.4 Procedimiento de comprobación in situ y reversión

Una vez implantado el mecanismo, conviene dejar escrito, como procedimiento, «cómo comprobar si salió bien» y «qué hacer si salió mal». Tarde o temprano llegará el momento de guiar por teléfono a alguien en el sitio, así que es práctico dejarlo en forma de comandos.

Comprobar el resultado de la aplicación. Si se dispone del shell de línea de comandos oficial de SQLite (sqlite3), se puede leer la versión de esquema actual con una sola línea. Lo que devuelve es un único entero.

sqlite3 "C:\ProgramData\MyApp\app.db" "PRAGMA user_version;"

Muchas veces no se puede dejar sqlite3.exe en el PC del cliente, así que si la pantalla de información de versión de la aplicación muestra tanto la versión del producto como la versión del esquema, se puede comprobar el estado con una sola llamada telefónica. Basta con invocar directamente el GetUserVersion del capítulo 3. En SQL Server, SELECT MAX(version) FROM schema_version; cumple el mismo papel.

Revertir cuando algo falla. La copia de seguridad de la sección 5.3 se restaura con el siguiente procedimiento.

1. Cierre por completo la aplicación. Incluyendo cualquier instancia múltiple y cualquier otro equipo que consulte la misma base de datos.
2. Ponga a resguardo el archivo original. Mueva el archivo de base de datos actual, junto con los archivos -wal / -shm del mismo nombre si está en modo WAL, a otra carpeta. No lo borre: será necesario para investigar la causa.
3. Copie el archivo de la copia de seguridad con el nombre original. Como en la sección 5.3 se incluyó la versión de esquema en el nombre del archivo, el propio nombre indica a qué punto se está volviendo.
4. Arranque la aplicación y confirme que PRAGMA user_version tiene el número al que quería volver. A partir de ahí, hasta que se distribuya una versión de la aplicación con la causa corregida, continúe operando con la versión antigua.

Que este procedimiento esté por escrito o no cambia el tiempo de recuperación el día del incidente. Añada esta página al manual operativo en el mismo lanzamiento en que implemente las migraciones.

7. Resumen

• «Cada cliente con una base de datos distinta» no es un problema de la atención del encargado, sino la consecuencia estructural de una operación en la que una persona ejecuta un procedimiento SQL. En una aplicación empresarial de escritorio con la base de datos dispersa, no queda otra opción que hacer que la propia aplicación actualice su base de datos.
• El esqueleto consiste en el número de versión de esquema que guarda la propia base de datos (PRAGMA user_version en SQLite¹) y la aplicación en el arranque de migraciones progresivas numeradas. En C#, se logra con unas pocas decenas de líneas de implementación propia.
• Los medios se dividen en tres familias: EF Core Migrations, bibliotecas como DbUp, e implementación propia. Se elige según si ya se usa EF Core o cuánto SQL crudo se tiene acumulado (tabla de decisión del capítulo 4). Como la documentación oficial enumera precauciones sobre Migrate() en el arranque de EF Core,⁷ úselo junto con medidas contra la ejecución simultánea y con ensayos.
• Los cambios destructivos se realizan mediante el lanzamiento en dos fases de expand-contract, y el riesgo de que la aplicación antigua abra una base de datos nueva se detiene con la comprobación de versión mínima. Las restricciones de ALTER TABLE en SQLite y el procedimiento de reconstrucción siguen la documentación oficial.³
• El principio es que una migración equivale a una transacción, y la actualización de versión va en la misma transacción. Como SQL Server tiene DDL que no entra en transacciones,⁴ las operaciones excepcionales se separan. Solo cuando se incluyen, además, la copia de seguridad con VACUUM INTO antes de aplicar² y el ensayo de aplicación de un tirón desde la versión más antigua, la migración está realmente lista para «enviarse al cliente».

Si le suena familiar la operación con procedimientos de ALTER, pruebe a incorporar en el próximo lanzamiento, aunque solo sea, «el registro del número de versión» y «la aplicación en el arranque». Con esa base, el lanzamiento en dos fases y las copias de seguridad se pueden ir añadiendo poco a poco más adelante.

Artículos relacionados

• Usar SQLite en aplicaciones empresariales con C# — modo WAL, control de exclusión, protección contra daños y cuándo usar EF Core
• Cómo elegir dónde guardar los datos de una aplicación de Windows — tabla de decisión SQLite / JSON / registro / Access
• Más allá de appsettings.json — la gestión de configuración en aplicaciones empresariales de Windows
• El datetime y las zonas horarias en aplicaciones empresariales — de las trampas de DateTime al principio de guardar en UTC y el diseño de pruebas

Áreas de consultoría relacionadas

En KomuraSoft LLC nos encargamos del diseño de bases de datos e implementación de infraestructura de migraciones para aplicaciones empresariales instaladas en distintos clientes, de la investigación y normalización de esquemas dispersos por una operación basada en procedimientos, y del diseño de distribución de actualizaciones tanto en configuraciones con EF Core como con SQL crudo.

• Desarrollo de aplicaciones Windows
• Modernización y mantenimiento de software Windows existente
• Consultoría técnica y revisión de diseño
• Contacto

Referencias

Artículos relacionados

Artículos recientes con las mismas etiquetas para profundizar en temas cercanos.

Temas relacionados

Estas páginas sitúan el tema en un contexto más amplio de servicios y decisiones.

Servicios relacionados con este tema

El artículo está directamente relacionado con los siguientes servicios.

Preguntas frecuentes

Preguntas habituales en las consultas sobre el tema del artículo.

¿Cómo se debe gestionar el cambio de esquema de la base de datos de una aplicación empresarial?

En lugar de que una persona ejecute manualmente un procedimiento SQL, conviene incluir migraciones numeradas (código que cambia el esquema) dentro de la propia aplicación y aplicarlas automáticamente al iniciarla. La base de datos debe registrar por sí misma su versión de esquema actual (con PRAGMA user_version en SQLite, o con una tabla dedicada en SQL Server), y la aplicación aplica en transacción, en orden, únicamente los números aún no aplicados. Con este esquema, incluso una actualización que salta versiones, por ejemplo de v1.2 a v1.5, aplica todos los cambios de esquema intermedios, de modo que la situación de «cada cliente con una base de datos distinta» deja de poder producirse estructuralmente.

¿Se puede llamar a Migrate() de EF Core al iniciar la aplicación?

Es una opción realista, con condiciones. La documentación de Microsoft advierte sobre la aplicación en el arranque para entornos de producción, por razones como la aplicación simultánea desde varias instancias, la necesidad de dar a la aplicación permisos para cambiar el esquema o la imposibilidad de revisar el SQL de antemano, y para aplicaciones de servidor recomienda generar scripts SQL para aplicarlos aparte. Sin embargo, en aplicaciones de escritorio empresariales con una base de datos local por cada PC cliente, no es viable ejecutar scripts in situ, por lo que llamar a Migrate() en el arranque se convierte, en la práctica, en la solución estándar. Aun así, combínelo siempre con medidas contra ejecuciones simultáneas (el bloqueo automático de EF Core 9 o posterior, o un Mutex propio) y con una copia de seguridad previa a la aplicación.

¿Qué ocurre con la base de datos si una migración falla a mitad de camino?

Si cada migración se envuelve en su propia transacción y la actualización del número de versión se incluye en esa misma transacción, un fallo revierte al estado anterior al inicio de esa migración y no queda ningún esquema a medio terminar. SQLite puede ejecutar DDL como CREATE TABLE o ALTER TABLE dentro de una transacción, y el propio procedimiento oficial de reconstrucción de tablas está escrito asumiendo una transacción. SQL Server también puede ejecutar la mayoría del DDL dentro de una transacción, pero tiene excepciones, como ALTER DATABASE o los índices de texto completo, por lo que esas operaciones excepcionales deben separarse en una migración propia. Además, si existe una copia de seguridad automática previa a la aplicación, en el peor de los casos la recuperación se reduce a sustituir el archivo.

Si el esquema de la base de datos ya está disperso entre distintos clientes, ¿cómo se normaliza?

Primero hay que decidir un único esquema «correcto» de referencia, investigar la base de datos de cada cliente y detectar las diferencias respecto al estado actual. Después, para las bases de datos sin número de versión, se escribe una migración inicial que detecte cada patrón realmente existente y lo lleve a la forma normalizada, y en el momento en que esa migración termina se graba el número de versión. En SQLite, sqlite_master o PRAGMA table_info permiten determinar mecánicamente si una columna existe, lo que se puede absorber con SQL defensivo del tipo «si la columna no existe, añádela». Si a partir de entonces todos los cambios se incorporan como migraciones numeradas, la dispersión no vuelve a producirse.

Perfil del autor

Página de presentación del autor del artículo.

Go Komura

Representante de KomuraSoft LLC

Especializado en desarrollo de software para Windows, consultoría técnica e investigación de fallos, sobre todo en proyectos con sistemas existentes y errores difíciles de reproducir.