SQLite también necesita ediciones al estilo Rust

 (mort.coffee)
3 puntos por GN⁺ 3 시간 전 | 2 comentarios | Compartir por WhatsApp
  • SQLite es un RDBMS autocontenido muy usado en almacenamiento local y proyectos embebidos, pero por compatibilidad hacia atrás mantiene valores predeterminados que perjudican la seguridad y el rendimiento
  • De forma predeterminada no aplica las restricciones de claves foráneas y puede reutilizar ROWID, lo que puede causar problemas de integridad de datos donde las publicaciones de un usuario eliminado quedan asociadas a otro usuario que recibió el mismo ID
  • Las tablas normales también almacenan tipos distintos a los declarados en sus columnas, y las tablas STRICT lo evitan, pero hay que especificar strict en cada tabla y esto entra en conflicto con la forma de usar nombres de tipos definidos por el usuario
  • En escrituras concurrentes, el SQLITE_BUSY inmediato, el WAL desactivado y una configuración conservadora de sincronización pueden mejorarse con las pragmas busy_timeout, journal_mode y synchronous, respectivamente
  • Si se pudiera elegir un paquete de valores seguros mediante una edición basada en año como PRAGMA edition = 2026, SQLite podría seguir evolucionando sus configuraciones predeterminadas sin romper el comportamiento existente

Fortalezas de SQLite y valores predeterminados anticuados

  • SQLite puede considerarse el estándar de la industria como base de datos para proyectos embebidos y almacenamiento local, y hasta cierto software de servidor como lobste.rs ya corre sobre SQLite
  • A diferencia de un RDBMS tradicional que corre en un proceso separado, es un RDBMS en forma de biblioteca, lo que permite mantener el software como una unidad autocontenida
  • Como no hace falta escribir serializadores y parsers personalizados como si fuera un formato de archivo común, ofrece a la vez las ventajas de un RDBMS y del almacenamiento basado en archivos
  • Sin embargo, sigue existiendo el problema de que varios valores predeterminados son inadecuados y afectan la integridad de datos, la concurrencia y el rendimiento

Problemas de integridad por no aplicar claves foráneas

  • Las claves foráneas son un mecanismo esencial para mantener la consistencia de la base de datos y evitar referencias rotas, pero SQLite no las aplica por defecto
  • Aunque se declare que posts.user_id referencia a users.id, con la configuración predeterminada no se impiden acciones como las siguientes
    • Crear una publicación con un ID de usuario no válido
    • Eliminar a un usuario dejando sus publicaciones atrás
  • INTEGER PRIMARY KEY se convierte en un alias del ROWID de la tabla, y el algoritmo de asignación de ROWID de SQLite reutiliza IDs en algunas situaciones
    • Si Bob crea publicaciones con el ID 1 y luego solo se elimina su cuenta, no se produce ningún error de clave foránea
    • Después, si Alice recibe el ID reutilizado 1, las publicaciones anteriores de Bob quedan asociadas a Alice
    • Como las consultas siguen funcionando normalmente, este problema puede ser más difícil de detectar que una simple referencia rota
  • Se puede aplicar el uso de claves foráneas activando la siguiente pragma en cada conexión
PRAGMA foreign_keys = ON;
  • Si esto se hubiera activado desde el principio, eliminar a un usuario que todavía tiene publicaciones se habría detenido con el error FOREIGN KEY constraint failed

Tablas normales que permiten tipos distintos a los declarados

  • Los valores en SQLite son uno de NULL, INTEGER, REAL, TEXT o BLOB, pero la declaración de columnas en una tabla normal no limita estrictamente los tipos que pueden almacenarse, sino que determina la afinidad de tipo (type affinity)
  • A una columna con afinidad INTEGER se le aplican las siguientes reglas de almacenamiento
    • Un TEXT que pueda interpretarse como entero se convierte a INTEGER
    • Un TEXT que pueda interpretarse como número real se convierte a REAL, es decir, punto flotante de doble precisión
    • Los demás valores se almacenan con su tipo original
  • Otras afinidades también tienen sus propias reglas de conversión
    • La afinidad BLOB almacena el valor tal como está
    • La afinidad TEXT deja BLOB, TEXT y NULL como están, y convierte números a TEXT
    • La afinidad REAL funciona como la afinidad INTEGER, pero convierte enteros a REAL
  • Por eso, incluso en una columna duration_sec INTEGER se puede guardar una cadena no numérica como 'Way too long, I mean come on'
  • En proyectos reales incluso hubo casos donde, en vez de guardarse números booleanos 1 y 0, se registraron las cadenas '1' y '0', obligando a limpiar y depurar los datos

Conflicto entre tablas STRICT y tipos definidos por el usuario

  • Las tablas STRICT generan un error de tipo como cannot store TEXT value in INTEGER column si se intenta insertar un tipo incorrecto en una columna
CREATE TABLE music (
  id INTEGER PRIMARY KEY,
  name TEXT,
  duration_sec INTEGER
) strict;

  • No existe una pragma para volver estrictas todas las tablas globalmente, así que hay que agregar strict sin falta en cada sentencia CREATE TABLE

  • SQLite sostiene que los tipos flexibles son útiles, pero incluso en tablas STRICT se puede crear una columna que acepte cualquier tipo de valor si se declara el tipo ANY

    • Eso permite distinguir explícitamente entre columnas que aceptan tipos arbitrarios y columnas que deben forzar un tipo específico

  • Un problema de compatibilidad más realista es que las tablas STRICT no solo fuerzan tipos, sino que también cambian las reglas de interpretación de los especificadores de tipo

  • Cómo interpretan los nombres de tipo las tablas normales

    • Una tabla normal de SQLite determina la afinidad según la cadena de tipo declarada
    • Si contiene "INT", se trata como INTEGER
    • Si contiene alguna de "CHAR", "CLOB" o "TEXT", pasa a ser TEXT
    • Si contiene "BLOB" o se omite el tipo, se trata como BLOB
    • Si contiene alguna de "REAL", "FLOA" o "DOUB", pasa a ser REAL
    • Si no entra en ninguno de esos casos, se trata como NUMERIC
    • Al combinar tipos flexibles con estas reglas, se pueden usar nombres de tipos definidos por el usuario como DATETIME, KEY_VALUE_SET o COLOR en las columnas
    • Los conectores o wrappers de base de datos pueden mirar el nombre del tipo para serializar y deserializar automáticamente
    • Incluso si no hay una función de procesamiento especial, el propio nombre del tipo documenta los datos esperados en la columna
    • Si STRICT se convirtiera tal cual en el valor predeterminado, habría que renunciar a esta forma de uso, así que se necesitarían alias explícitos de tipos definidos por el usuario
    CREATE TYPE KEY_VALUE_SET = TEXT;
    • Si en tablas estrictas se pudiera usar un alias como KEY_VALUE_SET, el esquema también podría registrar el patrón esperado de datos para una columna TEXT que la aplicación debe parsear
    • También sería útil una función para asociar restricciones CHECK a tipos definidos por el usuario
    • El estándar SQL:1999 ya incluye CREATE DOMAIN, una sintaxis de alias de tipos que incluso admite restricciones, así que SQLite podría implementarlo soportando esa sentencia estándar

Escrituras concurrentes y SQLITE_BUSY

  • SQLite permite múltiples lecturas concurrentes, pero solo procesa una escritura a la vez
  • En la configuración predeterminada, cuando dos procesos intentan obtener al mismo tiempo un bloqueo de escritura, uno de ellos recibe inmediatamente el error SQLITE_BUSY en lugar de esperar
    • Como una escritura que realiza E/S de disco puede tardar por naturaleza, es más natural esperar un tiempo a que se libere el bloqueo
    • En sistemas reales hubo casos donde el software se detenía de forma intermitente y obligaba a escribir bucles de reintento manualmente
  • La siguiente configuración hace que solo se devuelva SQLITE_BUSY después de reintentar adquirir el bloqueo durante un máximo de 5 segundos
PRAGMA busy_timeout = 5000;
  • En operación normal, lo adecuado es concentrar todas las escrituras en un solo proceso y, si es posible, en un solo hilo; las escrituras concurrentes no pueden ser rápidas
  • Aun así, hay casos perfectamente normales donde se necesitan escrituras concurrentes
    • Limpiar interactivamente la base de datos con la herramienta de línea de comandos sqlite3
    • Ejecutar scripts para tareas administrativas poco frecuentes sin un frontend aparte
  • Debido a la configuración predeterminada, estas tareas pueden provocar SQLITE_BUSY inesperados y detener el software en ejecución

WAL y valores predeterminados de rendimiento

  • SQLite, si se configura correctamente, puede ser lo bastante rápido como para asumir parte de las funciones que antes recaían en grandes servidores como PostgreSQL o MySQL, pero su rendimiento predeterminado no es bueno
  • Puede verse más sobre ajuste fino para entornos de servidor en Optimizing SQLite for servers
  • El problema más importante entre los valores predeterminados es que Write-Ahead Log, es decir, WAL está desactivado
PRAGMA journal_mode = WAL;
  • WAL mejora mucho la velocidad de escritura en la mayoría de los casos
  • Si además se usa la siguiente configuración, se puede reducir mucho la cantidad de sincronizaciones a disco sin riesgo de corrupción de datos
PRAGMA synchronous = NORMAL;

Ediciones de SQLite basadas en año

  • Si se cambiaran directamente ahora los valores predeterminados existentes, podría romperse software antiguo y hacer que los usuarios teman futuras actualizaciones de SQLite
  • Manteniendo la compatibilidad hacia atrás, podría agregarse una super pragma para elegir un conjunto mejorado de valores predeterminados
PRAGMA edition = 2026;
  • La edición 2026 funcionaría al menos como alias de la siguiente configuración
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
  • En esta edición, el modo strict para tablas nuevas también se aplicaría de forma predeterminada
  • Sería un enfoque similar al de las ediciones de Rust, donde se elige explícitamente un comportamiento nuevo sin alterar el del código existente
  • A diferencia de un modo único como "use strict"; de JavaScript, una edición basada en año puede ir cambiando con el tiempo un conjunto razonable de valores predeterminados
  • Por ejemplo, si WAL2 de Hctree entrara a la rama principal en 2034, un futuro PRAGMA edition = 2034 podría configurarse para elegir journal_mode = WAL2
  • Un sistema de ediciones podría ser una solución intermedia para que SQLite siga evolucionando sus valores predeterminados de seguridad y rendimiento sin romper el comportamiento predeterminado de los programas existentes

Lecturas relacionadas

2 comentarios

 
tomskang 2 시간 전

La teoría suena bien, pero considerando los conflictos de compatibilidad y la sobrecarga, creo que saldría perdiendo.
SQLite es un DBMS que nació para funcionar en entornos embebidos como Aegis,
y el mundo embebido es de andar reventando void**, declarar __weak__ y luego sobrescribir funciones. En ese mundo, me queda la duda de si realmente hace falta eso.
 
GN⁺ 3 시간 전
Opiniones en Lobste.rs

  • Escribí este texto ayer después de ver la noticia de que lobste.rs había migrado a SQLite (https://lobste.rs/s/ko1ji1/lobste_rs_is_now_running_on_sqlite)
    Mientras leía esa conversación, recordé pequeñas molestias de los valores predeterminados de SQLite y las reuní en una entrada de blog

    • Yo también leí esa conversación y llegué a la misma conclusión
      Si tienes tiempo, valdría la pena compartir este texto también en el foro de SQLite: https://sqlite.org/forum/forum

  • En SQL, CREATE TYPE se usa para crear tipos compuestos, y Postgres admite ahí rangos, enumeraciones, arreglos e incluso tipos base bastante complejos
    La funcionalidad deseada se parece más a CREATE DOMAIN. Permite crear un newtype a partir de un tipo concreto base y también especificar valores predeterminados y restricciones, lo que hace mucho más concisos los esquemas que repiten la misma restricción CHECK en varias tablas. Por ejemplo, se puede escribir create domain mything as text; o create domain mything as integer not null check (VALUE < 5);

    • Debí haber comprobado primero si ya existía una función de alias de tipos. Agregaré contenido relacionado al texto

  • También hace falta cubrir una trampa bastante peligrosa: solo con busy_timeout no se pueden evitar los errores SQLITE_BUSY que ocurren de inmediato
    Si después de iniciar una transacción con una consulta de solo lectura se ejecuta una consulta de escritura para intentar elevar el bloqueo que ya se tiene, y hay otra operación de escritura, falla de inmediato sin importar la configuración de busy_timeout. Para resolverlo hay que usar begin immediate

    • Sé que no es un bug real, pero sinceramente es un comportamiento que casi se siente como un bug

  • Hay que considerar que algunas de las configuraciones incluidas en la edición propuesta son propiedades de la conexión en el modelo de SQLite, mientras que las demás son propiedades del archivo de base de datos o de tablas individuales
    PRAGMA journal_mode = WAL se mantiene en el archivo de base de datos, pero PRAGMA foreign_keys = ON debe configurarse cada vez para cada conexión. STRICT es una propiedad de cada tabla, así que dentro de una misma base de datos también se pueden mezclar tablas estrictas y no estrictas. Por lo tanto, hay que decidir si este PRAGMA unificado se definirá como una propiedad del archivo de base de datos o de la conexión. También me da curiosidad si existe algún mecanismo incorporado que advierta para que una biblioteca antigua no modifique y arruine un archivo que contiene funciones que no conoce

  • El enfoque por ediciones parece, en esencia, un buen punto medio entre el objetivo del equipo de SQLite de mantener la compatibilidad hacia atrás y valores predeterminados menos confusos para usuarios que quieren la configuración moderna recomendada

  • Últimamente estoy muy metido en RocksDB. Es una biblioteca de base de datos embebida como SQLite, pero considero que el hecho de que no tenga SQL es más bien una gran ventaja
    Siento que la restricción de tener que expresar todas las operaciones en SQL solo estorba al trabajar con bases de datos

    • RocksDB es una base de datos clave-valor y SQLite es una base de datos relacional, así que son conceptos separados creados para propósitos distintos
      Me cuesta imaginar cómo migrar un sistema que usa SQL, como lobste.rs, a RocksDB u otra base de datos clave-valor

  • No estoy de acuerdo con que configurar busy_timeout sea la solución. SQLite tiene un rendimiento muy alto si se usa un único hilo de escritura y se procesan por lotes transacciones lógicas independientes entre sí
    El método concreto se explica en https://andersmurphy.com/2025/12/…

    • En cargas de trabajo comunes, lo correcto es limitar las escrituras a una sola operación y no crear una estructura en la que varias escrituras compitan constantemente
      Pero también es común modificar directamente la base de datos para tareas de mantenimiento, o ejecutar por script tareas administrativas ocasionales que no ameritan crear una pantalla de administración aparte. En ese caso, no es bueno que el servidor o la aplicación pueda detenerse de forma aleatoria solo porque, por mala suerte, coincidió el momento de ejecución