Cómo usar SQLite en aplicaciones empresariales con C# — Modo WAL, control de exclusión, prevención de corrupción y cuándo usar EF Core

En el artículo anterior, «Cómo elegir dónde guardar los datos de una aplicación de Windows», escribimos que SQLite es la primera opción para almacenar datos empresariales e historiales que van creciendo con el tiempo. Si resumimos en una línea la conclusión que este artículo da por sentada, es la siguiente: «La ubicación va en %LOCALAPPDATA% si es por usuario, o en %PROGRAMDATA% si se comparte entre todos los usuarios. El formato es JSON para configuraciones pequeñas y SQLite para datos empresariales e historiales que crecen. Las contraseñas y las claves de API se tratan aparte, con DPAPI». Este artículo se ocupa únicamente de lo que viene «después de elegir SQLite», así que basta con tener presente esta línea aunque no haya leído el artículo anterior.

Con eso la decisión ya está tomada, pero al llegar al momento de integrarlo aparecen otras dudas. En las consultas escuchamos con frecuencia cosas como: «Al buscar SQLite en NuGet aparecen varios paquetes y no sé cuál instalar», «funciona, pero de vez en cuando aparece database is locked; lo estoy sorteando con reintentos, ¿es correcto?» o «¿basta con copiar el archivo de la base de datos para hacer una copia de seguridad?».

Todos estos son puntos por los que se pasa sin falta cuando se opera SQLite en una aplicación empresarial durante varios años, y son del tipo de cosas que no dan problemas después si se dejan resueltas desde el diseño inicial. En este artículo, partiendo de Microsoft.Data.Sqlite, repasamos de forma ordenada los puntos que revisamos en cada revisión de diseño: la elección de biblioteca, la cadena de conexión y el pooling, el funcionamiento del modo WAL, cómo enfrentar SQLITE_BUSY, las trampas del mapeo de tipos, la prevención de corrupción y las copias de seguridad, y hasta cuándo conviene usar EF Core.

1. Conclusión, primero

En una línea: la biblioteca es Microsoft.Data.Sqlite, el modo WAL se activa desde el principio y el camino de escritura se unifica en uno solo. A continuación, el detalle.

• Para el desarrollo nuevo, la biblioteca básica es Microsoft.Data.Sqlite (o el proveedor de SQLite para EF Core que se apoya en ella). Es un producto distinto de System.Data.SQLite, sin compatibilidad ni en la cadena de conexión ni en el comportamiento de detalle, así que tenga siempre presente cuál de las dos supone cada ejemplo que encuentre en internet. ¹
• Active el modo WAL desde la primera versión publicada. Aumenta la concurrencia entre lecturas y escrituras, y desaparece la mayor parte de los database is locked. La configuración queda persistida en el propio archivo de la base de datos, pero no funciona en una carpeta compartida por red. ²
• database is locked (SQLITE_BUSY) no es algo que se resuelva «añadiendo manejo de errores»: es una señal de que hay que revisar el diseño. Microsoft.Data.Sqlite reintenta automáticamente hasta el tiempo de espera (30 segundos por defecto)³, pero la solución de fondo es unificar el camino de escritura.
• Cuando hace muchos INSERT pequeños, basta con agruparlos en una transacción explícita para ganar dos o tres órdenes de magnitud de velocidad. Si le parece que «SQLite es lento», sospeche primero de la unidad de commit.
• SQLite tiene en la práctica solo cuatro tipos: INTEGER, REAL, TEXT y BLOB. DateTime, Guid y decimal se guardan como TEXT. En particular, con decimal la comparación y el ordenamiento siguen las reglas de las cadenas de texto, así que lo más seguro es guardar los importes como enteros en la unidad monetaria mínima. ⁴
• Para las copias de seguridad, está prohibido copiar el archivo directamente mientras la base de datos está en funcionamiento. Use VACUUM INTO o la Backup API (SqliteConnection.BackupDatabase). También está terminantemente prohibido borrar a mano los archivos -wal / -shm. ⁵
• El SQLite puro no admite cifrado. El parámetro Password de la cadena de conexión solo tiene efecto si se incorpora una biblioteca nativa de la familia SQLCipher⁶; para pequeñas cantidades de información confidencial, antes que cifrar toda la base de datos conviene evaluar primero la protección con DPAPI (Data Protection API, la API de cifrado que Windows ofrece como función del sistema operativo y que evita que la aplicación tenga que custodiar la clave). Puede encontrar más detalles en «Almacenamiento de información confidencial en aplicaciones de Windows: evite las configuraciones en texto plano con DPAPI».

2. Elección de biblioteca — nombres parecidos, contenidos distintos

Hay varios paquetes para usar SQLite desde .NET, y lo primero con lo que se tropieza es con lo confuso de sus nombres. Ordenados, quedan así:

Microsoft.Data.Sqlite lo mantiene el equipo de EF Core, y además es la base sobre la que se construye el proveedor de SQLite de EF Core. ¹ Como el paquete NuGet incluye el binario nativo (el propio SQLite), no hace falta ninguna tarea de distribución hacia los equipos cliente, y las diferencias entre x86, x64 y ARM64 las absorbe el propio NuGet.

Hay que tener cuidado porque la información pensada para System.Data.SQLite no se puede aplicar tal cual. Al llevar tanto tiempo en uso, en internet abundan los ejemplos que asumen System.Data.SQLite, y al seguirlos se topa con las siguientes incompatibilidades.

• La cadena de conexión no es compatible. Palabras clave como Version=3, UseUTF16Encoding o DateTimeFormat (que cambia el formato de fecha) no existen en Microsoft.Data.Sqlite, y si las especifica obtendrá una excepción. Las palabras clave se reducen a un puñado: Data Source, Mode, Cache, Password, Foreign Keys, Default Timeout, Pooling, entre otras pocas. ⁶
• El tratamiento de los tipos también difiere. Por ejemplo, un Guid se guarda como BLOB de forma predeterminada en System.Data.SQLite, pero como TEXT en Microsoft.Data.Sqlite. Durante un período de transición en el que ambas bibliotecas leen y escriben el mismo archivo de base de datos, esta diferencia aparece como inconsistencia de datos.

Microsoft.Data.Sqlite está deliberadamente hecho para ser delgado: al no tener conversiones de tipo propias ni funciones de conveniencia, lo que dice la documentación oficial de SQLite se aplica sin modificaciones. Si una aplicación existente funciona de forma estable con System.Data.SQLite, no hace falta migrarla a la fuerza, pero la norma es orientar el código nuevo hacia Microsoft.Data.Sqlite y, si se decide migrar, hacerlo solo después de haber identificado como elementos de la migración las incompatibilidades anteriores.

3. Conexión y cadena de conexión — el pooling retiene el archivo

La forma básica de la cadena de conexión es solo la ruta del archivo. En la práctica, lo que hay que tener presente son Mode y Pooling. ⁶

using Microsoft.Data.Sqlite;

var builder = new SqliteConnectionStringBuilder
{
    DataSource = dbPath,
    Mode = SqliteOpenMode.ReadWriteCreate  // Valor predeterminado. Si no existe, se crea
};
using var conn = new SqliteConnection(builder.ConnectionString);
conn.Open();

• Mode: el valor predeterminado es ReadWriteCreate (crea el archivo si no existe). En casos como la distribución de una base de datos maestra de solo lectura, especificar ReadOnly evita escrituras causadas por errores del programa u operaciones equivocadas.
• Cache: normalmente se deja en su valor predeterminado. La documentación indica expresamente que no se recomienda combinar Cache=Shared con el modo WAL, así que no tiene cabida en el enfoque de este artículo, que usa WAL. ⁶
• Password: si se especifica, se envía un PRAGMA key justo después de conectar, pero la biblioteca nativa estándar no admite cifrado, así que no ocurre nada. ⁶ Si el requisito es cifrar toda la base de datos, hace falta sustituirla por un paquete de la familia SQLCipher (por ejemplo, SQLitePCLRaw.bundle_e_sqlcipher), y guardar esa clave de cifrado termina requiriendo DPAPI de todos modos. DPAPI (Data Protection API) es la API de cifrado que Windows ofrece como función del sistema operativo, y se usa desde System.Security.Cryptography.ProtectedData. Un valor cifrado especificando DataProtectionScope.CurrentUser queda ligado a las credenciales de inicio de sesión de ese usuario, y en principio no se puede descifrar en otro usuario ni en otro equipo. Basta con entenderlo como una herramienta que traslada al sistema operativo el problema de «dónde se guarda, entonces, la propia clave de cifrado» (en el enlace del capítulo 1 se explica con más detalle).
• Default Timeout: el tiempo de espera de los comandos (30 segundos por defecto). Es el límite superior del tiempo de reintento del que se habla en el capítulo 5.

Un punto más sobre la API asíncrona. Como el propio SQLite no tiene E/S asíncrona, métodos async como ExecuteNonQueryAsync se ejecutan de forma síncrona por dentro. ⁷ Por lo tanto, no es cierto que «como es async, la interfaz no se congela»; para consultas pesadas hay que enviarlas explícitamente a un hilo de trabajo, por ejemplo con Task.Run. Los criterios para decidir entre async/await están ordenados en «Tabla práctica de decisión sobre async/await en C#».

3.1 La trampa del pooling — el archivo sigue abierto aunque se llame a Close

Desde la versión 6.0, Microsoft.Data.Sqlite tiene el pooling de conexiones activado de forma predeterminada. ⁶ Tiene la ventaja de no tener que reabrir el archivo cada vez que se llama a Open, pero, a cambio, aunque se llame a Close o a Dispose, la conexión nativa permanece en el pool y sigue reteniendo el identificador del archivo de la base de datos. Como consecuencia, operaciones como las siguientes fallan con un mensaje de «el archivo está en uso».

• La función «restablecer los datos», que borra el archivo de la base de datos y lo vuelve a crear
• Sustituir el archivo de la base de datos al restaurar una copia de seguridad
• Mover el archivo de la base de datos durante la desinstalación o un proceso de reubicación

La solución consiste en vaciar el pool justo antes de manipular el archivo.

// Destruye el pool correspondiente a esta cadena de conexión y libera el identificador del archivo
SqliteConnection.ClearPool(new SqliteConnection(connectionString));
File.Delete(dbPath);

Con el modo WAL (siguiente capítulo) es posible que también queden archivos -wal / -shm, así que hay que ocuparse de ellos al mismo tiempo. Si se quiere liberar todo al cerrar la aplicación, está SqliteConnection.ClearAllPools(); y si se trata de una herramienta puntual que de entrada no necesita pooling, también existe la opción de poner Pooling=False en la cadena de conexión. La consulta de «llamé a Close y aun así no puedo borrarlo» aparece con frecuencia después de migrar a SQLite, así que incluya ClearPool desde el principio en cualquier proceso utilitario que manipule el archivo de la base de datos.

4. Cómo funciona el modo WAL — úselo sabiendo qué ocurre

En el artículo anterior solo escribimos «active el modo WAL», así que ahora entramos en cómo funciona. En el método predeterminado del diario de reversión (rollback journal), las lecturas quedan bloqueadas mientras hay una escritura en curso, lo cual es la causa principal de database is locked. En el método WAL (Write-Ahead Logging), los cambios se escriben en un archivo de registro exclusivo para anexar, en lugar de en el cuerpo de la base de datos, de modo que las lecturas no bloquean las escrituras, ni las escrituras bloquean las lecturas. ² Esto permite tal cual la configuración típica de una aplicación empresarial en la que el hilo de la interfaz muestra el historial mientras, en segundo plano, se escriben valores medidos.

Al activar el modo WAL, junto al cuerpo de la base de datos (el contenido confirmado en el último checkpoint) aparecen otros dos archivos.

Si se dibuja la relación entre estos tres archivos y las operaciones de lectura y escritura, queda así.

flowchart TB
    W["Conexión de escritura<br/>Solo una a la vez"]
    R["Conexiones de lectura<br/>Se pueden abrir tantas como se quiera, en simultáneo"]
    WAL["app.db-wal（registro de cambios）<br/>Cambios confirmados pero aún no aplicados al cuerpo<br/>Si se borra a mano, se pierden los datos más recientes"]
    SHM["app.db-shm（wal-index）<br/>Memoria compartida. Coordina entre procesos<br/>hasta dónde debe leerse el WAL"]
    DB["app.db（cuerpo）<br/>Contenido confirmado en el último checkpoint"]
    CP["Checkpoint<br/>Se ejecuta automáticamente cuando el WAL llega a 1000 páginas（≈4 MB）por defecto<br/>Si una lectura larga se queda abierta, no avanza y el WAL crece"]

    W -->|"Agrega los cambios"| WAL
    R -->|"Lee la parte ya confirmada"| DB
    R -->|"Lee desde aquí los commits aún no aplicados"| WAL
    SHM -.->|"Indica el rango a leer"| R
    WAL --> CP
    CP -->|"Transfiere al cuerpo y vacía el WAL"| DB

Como la lectura consulta tanto el cuerpo como el WAL, la lectura no se detiene aunque haya una escritura en curso: ese es el efecto del WAL. Al mismo tiempo, en este mismo diagrama se leen directamente dos restricciones: que «copiar solo el cuerpo no incluye los datos más recientes» y que «-shm es memoria compartida, así que no funciona en una carpeta compartida por red».

Hay cuatro propiedades que conviene tener presentes.

• Checkpoint: es el proceso que transfiere el contenido de -wal al cuerpo, y por defecto se ejecuta automáticamente cuando el WAL alcanza las 1000 páginas (unos 4 MB). ² Si una transacción de lectura de larga duración se queda abierta, el checkpoint no puede avanzar y -wal crece, así que evite el diseño de «mantener una conexión de lectura abierta y reutilizarla»: ábrala y ciérrela cada vez que la use (gracias al pooling, volver a abrirla es rápido).
• La configuración queda persistida en la base de datos: una vez ejecutado PRAGMA journal_mode=WAL, queda registrado en el propio archivo de la base de datos, y a partir de entonces sigue en modo WAL sin importar con qué conexión se abra. ² No hace falta emitirlo en cada conexión.
• Las escrituras siguen siendo de una en una: lo que el WAL mejora es la concurrencia entre lectura y escritura, pero las escrituras entre sí siguen siendo excluyentes. Si se malinterpreta esto y se piensa que «con WAL ya se puede escribir libremente desde varios hilos», se termina topando con el SQLITE_BUSY del capítulo 5.
• No funciona en una carpeta compartida por red: como el wal-index presupone memoria compartida, no funciona entre procesos de máquinas distintas. ² Como ya se explicó en el artículo anterior, de entrada no se debe colocar SQLite en una carpeta compartida por red.

Como advertencia operativa, el archivo -wal contiene transacciones ya confirmadas que todavía no se aplicaron al cuerpo. Tanto «basta con copiar solo el .db del cuerpo» como «el -wal es un archivo temporal, se puede borrar» son ideas erróneas: se pierden los commits más recientes o, en el peor de los casos, la base de datos se corrompe. ⁵ Esto se conecta directamente con lo que se explica en el capítulo 7 sobre que «las copias de seguridad no se hacen copiando el archivo».

4.1 Cómo comprobar que la configuración surtió efecto

El cambio a WAL solo requiere ejecutar PRAGMA journal_mode=WAL una vez, pero para evitar el caso de «creía haberlo ejecutado y no surtió efecto», siempre hay que releer la configuración para confirmarla. Como un PRAGMA escrito sin especificar valor devuelve el valor actual, la comprobación se resuelve en pocas líneas.

using var cmd = conn.CreateCommand();
cmd.CommandText = "PRAGMA journal_mode";
var mode = (string)cmd.ExecuteScalar()!;      // Si el WAL está activo, devuelve "wal"
if (!string.Equals(mode, "wal", StringComparison.OrdinalIgnoreCase))
    logger.LogWarning("journal_mode sigue en {Mode}", mode);

A continuación resumimos los puntos que conviene revisar en el autodiagnóstico al iniciar la aplicación.

Lo importante es la diferencia que muestra la cuarta columna, «cómo se aplica». Como journal_mode queda registrado en el archivo de la base de datos, no hace falta emitirlo en cada conexión, pero el resto son configuraciones que se determinan cada vez que se abre una conexión. La consulta típica de «funcionaba en la máquina de desarrollo pero no en producción» suele deberse a que esto cambia por diferencias en la cadena de conexión o en la biblioteca nativa.

Cuando se quiere examinar solo el archivo de la base de datos sin arrancar la aplicación, se pueden ejecutar los mismos PRAGMA con sqlite3, la shell de línea de comandos oficial de SQLite. Tenerla a mano como herramienta de investigación en campo permite confirmar en un minuto si «esta base de datos realmente está en WAL».

5. Exclusión y SQLITE_BUSY — unifique la escritura en un solo camino

SQLITE_BUSY (que en el mensaje de la excepción aparece como database is locked) ocurre cuando otra conexión tiene el bloqueo de escritura. Lo primero que hay que saber es que, ante errores de busy/locked, Microsoft.Data.Sqlite reintenta automáticamente hasta el tiempo de espera del comando (30 segundos por defecto). ³ Es decir, normalmente no hace falta implementar un reintento propio con catch, espera y reejecución. Si aun así llega a la excepción por haber agotado el tiempo de espera, la causa es alguna de estas.

• Otra conexión (u otro proceso) mantiene abierta una transacción de más de 30 segundos
• Dentro de una transacción iniciada como lectura, se intentó pasar a escritura y hubo colisión con otra escritura; esperar no lo resuelve, así que falla de inmediato. Lo habitual es diseñar desde el principio como transacción de escritura cualquier proceso de «leer y luego escribir»
• Un volumen alto de escrituras pequeñas llega simultáneamente desde varios hilos y se produce una pugna por el bloqueo

En ninguno de estos casos se soluciona «aumentando el número de reintentos». La solución consiste en acortar las transacciones y en unificar el camino de escritura en uno solo.

5.1 Cree una cola de escritura con System.Threading.Channels

Con los datos que se originan en varios hilos (valores medidos, registros de operación, etc.), en lugar de que cada hilo escriba directamente en la base de datos, conviene enviarlos a una cola y dejar que un bucle de escritura dedicado los procese. En .NET, System.Threading.Channels se puede usar tal cual para esto.

using System.Globalization;
using System.Threading.Channels;
using Microsoft.Data.Sqlite;

public sealed record Measurement(string DeviceId, double Value, DateTime CreatedAtUtc);

public sealed class MeasurementWriter : IAsyncDisposable
{
    private readonly Channel<Measurement> _channel =
        Channel.CreateBounded<Measurement>(new BoundedChannelOptions(10_000)
        {
            FullMode = BoundedChannelFullMode.Wait  // Si se desborda, hace esperar al lado productor
        });
    private readonly string _connectionString;
    private readonly Task _loop;

    public MeasurementWriter(string connectionString)
    {
        _connectionString = connectionString;
        _loop = Task.Run(WriteLoop);
    }

    // Se puede llamar desde cualquier hilo. No toca la base de datos. Si el bucle
    // de escritura ya murió, el lado que encola se entera enseguida por ChannelClosedException
    public ValueTask EnqueueAsync(Measurement m) => _channel.Writer.WriteAsync(m);

    private async Task WriteLoop()
    {
        try
        {
            await WriteLoopCore();
        }
        catch (Exception ex)
        {
            // Comunica al lado que encola, a través del canal, que el escritor murió
            // (por ejemplo, por disco lleno). Si se omite esto, en cuanto la cola se
            // llene EnqueueAsync quedará esperando para siempre y nadie notará el fallo
            _channel.Writer.TryComplete(ex);
            throw;
        }
    }

    private async Task WriteLoopCore()
    {
        using var conn = new SqliteConnection(_connectionString);
        conn.Open();
        var buffer = new List<Measurement>(500);
        while (await _channel.Reader.WaitToReadAsync())
        {
            // Recoge hasta 500 elementos acumulados y los escribe en una sola transacción
            buffer.Clear();
            while (buffer.Count < 500 && _channel.Reader.TryRead(out var m))
                buffer.Add(m);

            using var tx = conn.BeginTransaction();
            using var cmd = conn.CreateCommand();
            cmd.Transaction = tx;
            cmd.CommandText =
                "INSERT INTO measurement (device_id, value, created_at) " +
                "VALUES ($device, $value, $at)";
            var pDevice = cmd.Parameters.Add("$device", SqliteType.Text);
            var pValue  = cmd.Parameters.Add("$value",  SqliteType.Real);
            var pAt     = cmd.Parameters.Add("$at",     SqliteType.Text);
            foreach (var m in buffer)
            {
                pDevice.Value = m.DeviceId;
                pValue.Value  = m.Value;
                // Se especifica InvariantCulture explícitamente para no depender del calendario ni de los números de la cultura predeterminada
                pAt.Value     = m.CreatedAtUtc.ToString(
                    "yyyy-MM-dd HH:mm:ss.fffffff", CultureInfo.InvariantCulture);
                cmd.ExecuteNonQuery();
            }
            tx.Commit();
        }
    }

    public async ValueTask DisposeAsync()
    {
        // No lanza una excepción aunque el bucle de escritura ya haya cerrado el canal
        // por un fallo (con Complete() se lanzaría por doble cierre, y ocultaría la excepción original)
        _channel.Writer.TryComplete();
        await _loop;  // Termina de escribir lo que quede. Si el bucle murió, la excepción original aparece aquí
    }
}

Con este diseño, los conflictos de escritura dejan de ocurrir de forma estructural y, además, lo acumulado en la cola se agrupa por lotes de manera natural, con lo que se obtiene de paso la mejora de velocidad de la siguiente sección. También son detalles que resultan efectivos en una aplicación empresarial: que al terminar, DisposeAsync deja escrito todo lo pendiente de la cola antes de cerrar; que FullMode deja explícito el comportamiento cuando la cola se llena; y que el fallo del bucle de escritura se propaga al lado que encola mediante TryComplete(ex) (si el único escritor muere en silencio, el fallo se manifiesta como una espera eterna cuando la cola se llena). La misma idea de «unificar el camino» es la que se comparte con el diseño de exclusión para la coordinación por archivos («Buenas prácticas de integración por archivos y bloqueo»).

5.2 Agrupar en transacciones cambia el orden de magnitud

SQLite realiza una escritura sincronizada al almacenamiento (fsync) en cada commit, así que si se hace INSERT uno a uno con commit implícito, el rendimiento se estanca en unos pocos cientos o miles por segundo incluso en SSD, y en unas pocas decenas por segundo en HDD. Basta con agrupar esos mismos INSERT en transacciones explícitas de 1000 en 1000 (encerrándolos entre BeginTransaction y un Commit final) para alcanzar un orden de decenas o cientos de miles por segundo. Es un cambio de tres líneas, al que llamarlo «ajuste de rendimiento» resulta hasta exagerado, y cambia dos o tres órdenes de magnitud.

Muchas de las consultas sobre «la importación de un CSV tarda 20 minutos» o «la migración de datos al iniciar no termina nunca» tienen esta causa, y se resuelven solo con convertirlo en transacciones y reutilizar los parámetros (la forma de código de la sección anterior). Por el contrario, si la transacción se alarga demasiado, esta vez es otras escrituras las que quedan esperando, así que el punto de equilibrio práctico está en confirmar «unos cientos o miles de registros, o el equivalente a unos cientos de milisegundos» por cada commit.

6. Trampas del mapeo de tipos — qué asignar a solo cuatro tipos

Los tipos que SQLite realmente puede almacenar son solo cuatro: INTEGER, REAL, TEXT y BLOB, y cada tipo de .NET se asigna a uno de ellos. El mapeo principal de Microsoft.Data.Sqlite es el siguiente. ⁴

Los tres que terminan en TEXT merecen especial atención.

• DateTime: mientras el formato y la zona horaria estén unificados, el formato tipo ISO 8601 en TEXT hace que el ordenamiento por cadena de texto coincida con el ordenamiento cronológico, así que en la práctica no hay problema. Dicho de otro modo, se rompe en el instante en que se mezclan UTC y hora local. La única solución es decidir desde el principio «se guarda en UTC, se convierte a hora local al mostrarla» y respetarlo en todos los flujos; funciones de SQLite como datetime('now') también devuelven UTC. Otro punto: si se convierte a texto manualmente, hay que pasarle siempre CultureInfo.InvariantCulture a ToString. Si se deja la cultura predeterminada, solo en los equipos que funcionan con una cultura distinta del calendario gregoriano (por ejemplo, el calendario japonés o el francés revolucionario) cambia la notación del año, y tanto el ordenamiento como la lectura se rompen (vea el ejemplo de código de la sección 5.1).
• Guid: se guarda como cadena de texto, así que la comparación es una coincidencia de cadenas. Si otra herramienta o biblioteca escribe con una notación distinta (mayúsculas/minúsculas, formato BLOB), el cruce de datos falla, así que si se accede desde varios lenguajes o herramientas conviene dejar la notación documentada como especificación.
• decimal: es la trampa más grande. Como con REAL se pierde precisión, se guarda como TEXT⁴, pero una comparación como WHERE amount > 1000 sobre una columna TEXT no funciona como se espera, porque en el orden de tipos de SQLite TEXT siempre es mayor que cualquier valor numérico. Las agregaciones como SUM también se convierten internamente a REAL, con lo que se pierde justamente la precisión que era la razón de elegir decimal.

La pauta práctica para decimal es sencilla: guarde los importes como enteros (INTEGER) en la unidad monetaria mínima. En yenes japoneses, se guarda como long en unidades de yen y se convierte al mostrarlo. Con enteros, tanto la comparación como la agregación son exactas y rápidas, y la trampa del tipo desaparece. Si por las circunstancias de un esquema existente hay que seguir manejándolo como decimal sí o sí, resígnese a no hacer la comparación ni la agregación en SQL, sino leer los datos y hacerlo del lado de .NET.

Por cierto, el nombre de tipo que se escribe en CREATE TABLE es solo una pista de «afinidad» (affinity), y un nombre de tipo inventado como STRING es fuente de accidentes por conversión implícita. La recomendación oficial es usar en el nombre de tipo de columna únicamente los cuatro nombres INTEGER, REAL, TEXT y BLOB. ⁴

7. Operación — no romperla, y poder recuperarla si se rompe

7.1 quick_check al iniciar

Aunque SQLite está protegido por transacciones, no se puede reducir a cero la corrupción provocada por fallos de disco u operaciones de archivo equivocadas. Para no seguir funcionando con una base de datos rota y agrandar el daño, conviene incluir una comprobación de integridad al iniciar. El integrity_check completo tarda en bases de datos grandes, así que para el día a día basta con la versión ligera, quick_check.

using var cmd = conn.CreateCommand();
cmd.CommandText = "PRAGMA quick_check";
var result = (string)cmd.ExecuteScalar()!;
if (result != "ok")
{
    // No seguir escribiendo en una base de datos corrupta. Degradar a solo lectura e invitar a restaurar
    logger.LogError("Se detectó corrupción en la base de datos: {Detail}", result);
    EnterReadOnlyMode(result);
}

La política de no reparar ni revertir automáticamente al detectar corrupción (dejando que intervenga el usuario) es la que se explicó en la sección 6.2 del artículo anterior.

7.2 Copias de seguridad — por qué no basta con copiar el archivo

Una copia simple del archivo de la base de datos mientras está en funcionamiento puede capturar un estado a medio camino de una transacción, y la propia documentación oficial de SQLite lo señala expresamente como causa de corrupción. ⁵ Con el modo WAL, además, si se copia solo el cuerpo y se deja atrás el -wal (que contiene commits aún no aplicados al cuerpo, ver capítulo 4), se pierden los datos más recientes.

Hay dos métodos correctos, y ambos permiten obtener una instantánea consistente mientras la base de datos está en funcionamiento.

• VACUUM INTO: con una sola sentencia SQL, crea una copia de tamaño mínimo y sin fragmentación. ⁸ El ejemplo de código está en la sección 6.3 del artículo anterior; consúltelo allí.
• SqliteConnection.BackupDatabase: es un envoltorio de la Backup API de SQLite que copia entre objetos de conexión.

using var source = new SqliteConnection($"Data Source={dbPath}");
using var target = new SqliteConnection($"Data Source={backupPath}");
source.Open();
target.Open();
source.BackupDatabase(target);  // Produce una instantánea consistente incluso con la base de datos en funcionamiento

Sin embargo, BackupDatabase tiene un punto a tener en cuenta. La implementación actual de Microsoft.Data.Sqlite copia lo más rápido posible, pero bloquea la escritura de otras conexiones hasta que termina. ⁹ Si se hace una copia de seguridad de una base de datos grande mientras siguen entrando mediciones u operaciones del usuario, esas escrituras aparecen durante ese lapso como SQLITE_BUSY o como la interfaz congelada. Lo más seguro es diferenciar los usos: VACUUM INTO para las copias de seguridad periódicas del día a día, y BackupDatabase para la copia recíproca con una base de datos en memoria o para duplicados en franjas horarias sin escrituras o en procesos de mantenimiento. Si se usa el Programador de tareas para la ejecución periódica, hágalo de forma que sea la propia aplicación (o una pequeña herramienta que abra SQLite correctamente) la que ejecute la copia de seguridad descrita arriba, en lugar de una copia de archivo desde fuera. El diseño de la ejecución periódica en sí está descrito en «Cómo operar de forma segura las tareas periódicas con el Programador de tareas».

7.3 Ubicación y migración

La ubicación básica del archivo de la base de datos es %LOCALAPPDATA%\NombreDeLaEmpresa\NombreDeLaAplicación, y la configuración mínima para el control de versiones del esquema es una migración al iniciar basada en PRAGMA user_version. Ambos puntos se explicaron con código en el artículo anterior (capítulo 3 y sección 6.1), así que no los repetimos aquí. Solo un apunte adicional: si se toma una generación de copia de seguridad (sección 7.2) antes de ejecutar la migración, la recuperación ante el peor caso —«la migración falló y la aplicación ya no arranca»— se reduce a una simple sustitución de archivo.

8. Cuándo conviene usar EF Core — el punto de equilibrio del ORM

Hasta aquí escribimos usando Microsoft.Data.Sqlite puro, pero también hay situaciones claras en las que conviene usar el proveedor de SQLite de EF Core. El eje de la decisión es el carácter de la aplicación.

Si opta por EF Core, es necesario conocer las restricciones propias del proveedor de SQLite. ¹⁰

• Reconstrucción de tablas por las restricciones de ALTER TABLE: como SQLite no admite directamente cambiar el tipo de una columna ni eliminarla, las migraciones que incluyen AlterColumn o DropColumn se ejecutan mediante una reconstrucción: «crear tabla nueva → copiar datos → eliminar la tabla antigua → renombrar». Esto repercute en el tiempo de aplicación y en el uso de disco cuando hay mucho volumen de datos, así que planifique con cuidado los cambios de esquema en tablas grandes.
• No se pueden generar scripts idempotentes: no es posible generar scripts de migración con condicionales if-then como en SQL Server. Lo realista es aplicarlos con dbContext.Database.Migrate() al iniciar la aplicación.
• Las operaciones con decimal / DateTimeOffset se evalúan del lado del cliente: la situación de los tipos descrita en el capítulo 6 no desaparece con EF Core. Las comparaciones que no son de igualdad y los ordenamientos se evalúan del lado del cliente, así que la pauta de guardar los importes como enteros en la unidad mínima sigue siendo la misma con EF Core (se puede convertir a long mediante un conversor de valores para almacenarlo).
• El WAL está activo de forma predeterminada: una base de datos creada por EF Core ya está en modo WAL desde el principio⁷, así que no hace falta la configuración del capítulo 4. Aun así, sigue siendo necesario comprender su comportamiento.

Cabe señalar que, incluso adoptando EF Core, resulta muy eficaz usar una base de datos SQLite en memoria para las pruebas unitarias de la capa de repositorio. Al ejecutarse con el mismo proveedor que en producción, se reduce el hueco de «pasa con el mock pero falla con la base de datos real». Para el criterio sobre en qué capa escribir las pruebas, consulte «El límite entre pruebas unitarias y pruebas de integración».

9. Resumen

SQLite es una biblioteca de la que se puede decir que «integrarla lleva 30 minutos, pero operarla correctamente requiere diseño». Aun así, el diseño necesario está bastante bien delimitado, y si convertimos el contenido de este artículo en una lista de verificación, quedan los siguientes seis puntos.

• La biblioteca es Microsoft.Data.Sqlite (o EF Core sobre ella). No mezclarla con información que asume System.Data.SQLite
• Active el modo WAL desde la primera versión publicada, y comprenda el papel de -wal / -shm
• Unifique la escritura en un solo camino (cola de escritura con Channels) y agrupe los INSERT pequeños en transacciones
• Unifique DateTime en UTC, y guarde los importes como enteros en la unidad monetaria mínima. No compare ni agregue un decimal dejándolo como TEXT
• Ejecute quick_check al iniciar, y haga las copias de seguridad con VACUUM INTO o BackupDatabase. No copie el archivo mientras está en funcionamiento
• No olvide SqliteConnection.ClearPool en los procesos que borran o sustituyen el archivo de la base de datos

Si le suena familiar una configuración en la que se sortea database is locked a base de reintentos, o en la que las copias de seguridad se hacen copiando el archivo, revise una vez estos puntos del artículo antes de que algo se rompa. Corregir cualquiera de ellos, en sí mismo, es un cambio pequeño.

Artículos relacionados

• Cómo elegir dónde guardar los datos de una aplicación de Windows — tabla de decisión entre SQLite, JSON, el Registro y Access
• Almacenamiento de información confidencial en aplicaciones de Windows: evite las configuraciones en texto plano con DPAPI
• Cómo operar de forma segura las tareas periódicas con el Programador de tareas
• Tabla práctica de decisión sobre async/await en C# - Task.Run y ConfigureAwait

Áreas de consultoría relacionadas

En KomuraSoft LLC nos ocupamos de la revisión de diseño de aplicaciones empresariales que incorporan SQLite (control de exclusión, copias de seguridad, diseño de migraciones), de la investigación de problemas en aplicaciones ya en producción como database is locked, corrupción de datos o degradación del rendimiento, y del apoyo a la migración desde almacenes de datos existentes como Access.

• Consultoría técnica y revisión de diseño
• Desarrollo de aplicaciones de Windows
• Aprovechamiento de activos existentes y apoyo a la migración
• 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.

¿Por qué aparece «database is locked» en SQLite?

SQLITE_BUSY se produce cuando otra conexión tiene el bloqueo de escritura. Microsoft.Data.Sqlite reintenta automáticamente hasta el tiempo de espera del comando (30 segundos por defecto), así que normalmente no hace falta implementar un reintento propio. Si aun así llega la excepción, la causa es una transacción de más de 30 segundos, una colisión al intentar pasar de lectura a escritura, o una pugna por bloqueos entre escrituras pequeñas provenientes de varios hilos, y aumentar el número de reintentos no lo resuelve en ninguno de esos casos. La solución de fondo consiste en acortar las transacciones y en unificar el camino de escritura en uno solo usando una cola, por ejemplo con System.Threading.Channels. Activar el modo WAL también elimina la mayor parte del bloqueo entre lectura y escritura.

¿Qué biblioteca debo elegir para usar SQLite en C#?

Para el desarrollo nuevo, la base es Microsoft.Data.Sqlite, el proveedor ADO.NET mantenido por Microsoft (o el proveedor de SQLite de EF Core que se apoya en él). Como el paquete NuGet incluye el propio SQLite nativo, no hace falta ninguna tarea de distribución. Es un producto distinto del veterano System.Data.SQLite, sin compatibilidad ni en la cadena de conexión ni en el tratamiento de los tipos: por ejemplo, un Guid se guarda como BLOB de forma predeterminada en System.Data.SQLite, pero como TEXT en Microsoft.Data.Sqlite. Tenga siempre presente cuál de las dos supone cada ejemplo que encuentre en internet.

¿Basta con copiar el archivo para hacer una copia de seguridad de SQLite?

Está prohibido hacer una copia simple del archivo mientras está en funcionamiento. Puede capturar un estado a medio camino de una transacción, y la propia documentación oficial de SQLite lo señala expresamente como causa de corrupción. En el modo WAL, además, el archivo -wal contiene commits que aún no se aplicaron al cuerpo, así que si se copia solo el cuerpo se pierden los datos más recientes. Los métodos correctos son VACUUM INTO (crea con una sola sentencia SQL una copia consistente y de tamaño mínimo) o SqliteConnection.BackupDatabase. Sin embargo, BackupDatabase bloquea la escritura de otras conexiones hasta que termina, así que para las copias de seguridad periódicas del día a día lo más seguro es VACUUM INTO.

¿Por qué son lentos los INSERT masivos en SQLite?

En la mayoría de los casos, la causa es la unidad de commit. SQLite realiza una escritura sincronizada al almacenamiento (fsync) en cada commit, así que si se hace INSERT uno a uno con commit implícito, el rendimiento se estanca en unos pocos cientos o miles por segundo incluso en SSD. Basta con agrupar esos mismos INSERT en transacciones explícitas de 1000 en 1000 (encerrándolos entre BeginTransaction y un Commit final) para alcanzar un orden de decenas o cientos de miles por segundo, es decir, dos o tres órdenes de magnitud más rápido. Por el contrario, si la transacción se alarga demasiado, hace esperar a otras escrituras, así que el punto de equilibrio práctico está en confirmar unos cientos o miles de registros, o el equivalente a unos cientos de milisegundos, por cada commit.

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.