- Agregar
STRICT al final de la definición de una tabla permite bloquear desde temprano errores de tipo, como texto arbitrario en columnas enteras, y así mejorar la integridad de los datos
- Aunque valida los tipos al insertar y actualizar, sigue permitiendo valores convertibles sin pérdida, como
'123'; además, los tipos de columna quedan limitados a INT, INTEGER, REAL, TEXT, BLOB, ANY
- Para columnas que deben admitir varios tipos, se puede usar
ANY, lo que permite combinar validación estricta y almacenamiento flexible dentro de una misma tabla
- No es posible convertir una tabla existente a STRICT de forma directa, así que hace falta crear una tabla nueva y copiar los datos; si hay datos inválidos, habrá que limpiarlos o convertirlos
- Las tablas STRICT solo están disponibles en SQLite 3.37.0 o superior y, aunque en teoría añaden costo de validación, en pruebas no oficiales no se observaron diferencias claras en rendimiento ni tamaño de archivo
Crear una tabla STRICT
- Las tablas STRICT de SQLite aplican verificaciones de tipos más estrictas, similares a las de otros motores SQL
- Basta con agregar
STRICT al final de la definición de CREATE TABLE
CREATE TABLE people (name TEXT) STRICT;
Validación de tipos en inserciones y actualizaciones
- En una tabla normal de SQLite se puede guardar texto como
'garbage' incluso en una columna INTEGER, pero una tabla STRICT trata la incompatibilidad de tipos como un error
CREATE TABLE people_nonstrict (age INTEGER);
INSERT INTO people_nonstrict (age) VALUES ('garbage');
-- Se procesa sin problemas
CREATE TABLE people_strict (age INTEGER) STRICT;
INSERT INTO people_strict (age) VALUES ('garbage');
-- Error: cannot store TEXT value in INTEGER column
- La misma validación también se aplica a
UPDATE, así que evita tipos incorrectos incluso al cambiar valores después de guardarlos
- Aun así, si el valor puede convertirse sin pérdida, también se permite en una tabla STRICT
- La cadena
'123' puede convertirse por completo al entero 123, así que las dos inserciones siguientes se procesan igual
INSERT INTO people_strict (age) VALUES ('123');
INSERT INTO people_strict (age) VALUES (123);
Restricciones de tipos al definir la tabla
- En una tabla normal, SQLite permite usar en una columna incluso nombres de tipo que no soporta
GARBAGE, DATETIME, JSON, UUID y BLOBB se aceptan sin problema- Estas declaraciones pueden surgir por errores tipográficos o por malentendidos sobre los tipos de datos que SQLite sí soporta
- En una tabla STRICT, usar un nombre de tipo no soportado provoca un error en el momento de crearla
CREATE TABLE tbl (name GARBAGE) STRICT;
CREATE TABLE tbl (name DATETIME) STRICT;
CREATE TABLE tbl (name JSON) STRICT;
CREATE TABLE tbl (name UUID) STRICT;
CREATE TABLE tbl (name BLOBB) STRICT;
- Los únicos tipos de columna permitidos son
INT, INTEGER, REAL, TEXT, BLOB, ANY
- Todas las columnas deben declarar un tipo, por lo que no se puede omitir como en
CREATE TABLE tbl (name)
Mantener flexibilidad con ANY
- Si necesitas almacenar datos con tipos variables, puedes usar una columna
ANY
- Incluso dentro de una tabla STRICT, una columna
ANY puede guardar enteros, texto, números reales, BLOB y más
CREATE TABLE tbl (value ANY) STRICT;
INSERT INTO tbl (value) VALUES (123);
INSERT INTO tbl (value) VALUES ('text');
INSERT INTO tbl (value) VALUES (12.34);
INSERT INTO tbl (value) VALUES (X'8647');
Migración de tablas existentes
- No existe una forma de convertir directamente con
ALTER una tabla no estricta en una tabla STRICT, por lo que suele ser más simple crearlas así desde el inicio
- Para migrar una tabla existente hay que seguir el proceso crear una nueva tabla STRICT → copiar los datos → reemplazar la tabla anterior
CREATE TABLE new_people (name TEXT) STRICT;
INSERT INTO new_people SELECT * FROM people;
DROP TABLE people;
ALTER TABLE new_people RENAME TO people;
- Si los datos existentes tienen tipos incorrectos, por ejemplo texto en una columna entera, la copia puede fallar
- Antes de migrar, quizá haga falta limpiar los datos o convertirlos con
CAST
- También es posible aplicar STRICT solo a tablas nuevas, pero eso deja distintos niveles de validación entre tablas, lo que puede volver el comportamiento menos predecible que si todas fueran laxas
Cuándo conviene el tipado flexible
- El equipo de SQLite reúne por separado las ventajas del tipado flexible y presenta casos en los que el comportamiento por defecto resulta útil
- Algunos usos donde una tabla no estricta puede tener sentido son:
- Un almacenamiento puro de clave-valor que guarda varios tipos tal cual
- Un espacio para conservar atributos adicionales de tipos distintos
- Casos en los que se importa un CSV desordenado directamente y hay que preservar incluso valores inválidos sin perderlos
- Aun así, los tipos inesperados pueden provocar bugs sutiles, así que en tablas comunes suele ser mejor fallar de inmediato que permitirlos en silencio
- En el código fuente de SQLite incluso hay comentarios que llaman
legacy a las tablas no estrictas, aunque eso difícilmente sirve como evidencia más confiable que la documentación oficial
Compatibilidad de versiones
- Las tablas STRICT se introdujeron en SQLite 3.37.0, lanzado en noviembre de 2021
- No se pueden usar tablas STRICT en versiones anteriores de SQLite
- Las versiones viejas tampoco pueden leer bases de datos que incluyan tablas STRICT
- Si creas una tabla STRICT con una versión reciente y luego abres esa misma base con SQLite 3.36.0, se producirá un error
Rendimiento y espacio de almacenamiento
- Las tablas STRICT hacen una validación adicional del tipo de datos al insertar y actualizar, así que en teoría podrían ser más lentas
- En pruebas no oficiales que insertaron millones de filas en tablas con 100 columnas, no aparecieron diferencias claras de rendimiento en ninguna de varias computadoras
- El tamaño del archivo de base de datos en disco también fue el mismo, aunque como no fue un benchmark riguroso podría haber diferencias que no se detectaron
- Incluso podría mejorar el rendimiento al evitar que se guarden por error valores que no coinciden con la afinidad de la columna, aunque eso no se ha verificado aparte
Qué evaluar al adoptarlas
- Las tablas STRICT no resuelven todos los problemas de datos, pero sí reducen errores de tipo y refuerzan la integridad de los datos
- En la mayoría de los casos, aplicarlas es tan simple como agregar
STRICT a la definición de la tabla
- En tablas comunes, los beneficios de una validación estricta de tipos suelen superar la carga de la migración, la incompatibilidad con versiones antiguas y la flexibilidad limitada
1 comentarios
Opiniones de Hacker News
SQLite no tiene una función para hacer
ALTERa una tabla y convertirla al modo estricto, así que, partiendo de la idea de que hay que copiar los datos de una tabla no estricta a una estricta, se agregó una función de conversión en ambos sentidos en sqlite-utils 4.1En la CLI se puede usar con
uvx sqlite-utils transform data.db mytable --strict, y en Python condb.table("mytable").transform(strict=True)Las notas de la versión se pueden ver en https://sqlite-utils.datasette.io/en/stable/changelog.html#v..., la documentación de la API de Python en https://sqlite-utils.datasette.io/en/stable/python-api.html#..., y la documentación de la CLI en https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...
https://sqlite.org/flextypegood.html explica por qué el tipado flexible es el valor predeterminado y por qué probablemente no cambiará en el futuro, pero no coincide con la experiencia real
A diferencia de la explicación de que, si se pone el nombre de un cliente en el entero
Customer.creditScore, se puede detectar y corregir fácilmente, las filas dañadas son difíciles de recuperar y los datos podrían haber desaparecido por completoTambién es muy difícil estar de acuerdo con el argumento de que la verificación estricta de tipos solo evita errores fáciles de encontrar y, por eso, en realidad dificulta detectar y corregir bugs
En Postgres se suelen agregar tantas validaciones y protecciones como sea posible, y así no hay que andar buscando después errores que no deberían haber ocurrido desde el principio
Esto parece más bien el resultado de la época en que se creó SQLite y de las firmes convicciones de su autor, más que de principios rigurosos de ingeniería; y da la impresión de que, tras recibir muchas críticas, se mantiene la postura recurriendo a cualquier justificación
Tipos como JSON o HSTORE en Postgres pueden ofrecer la flexibilidad deseada, pero casi siempre es mejor permitir elegirlos cuando se necesitan, en lugar de imponer por defecto un tipado sin límites
Sería bueno que STRICT fuera el valor predeterminado
Dejando eso de lado, el desarrollador de SQLite es una persona extraordinaria que creó una herramienta excelente
PRAGMA foreign_keys = ON;El problema mayor es que para las tablas estrictas no existe un pragma equivalente, así que hay que agregar el
STRICTno estándar a cadaCREATE TABLETambién se consideró un pragma STRICT global, pero no se implementó; se puede ver material relacionado en https://sqlite.org/foreignkeys.html y https://sqlite.org/forum/forumpost/1b9d073a37ca5998
La idea es evitar que software hecho para SQLite 3.53, al actualizarse a 3.54, de pronto haga que
CREATE TABLEcree tablas estrictas, arroje errores y rompa todoDa curiosidad si en algún momento SQLite solo soportaba la sintaxis de claves foráneas pero no tenía implementada la funcionalidad real
Tuve que limpiar código desplegado en miles de dispositivos que guardaba los strings
'1'y'0'en una columna booleana, y no fue nada divertidoTampoco existe un tipo timestamp, así que hay que guardarlo en una columna de texto, e incluso las funciones estándar de fecha y hora generan
yyyy-mm-dd HH:MM:SS, que hay que asumir implícitamente como UTC, en vez del formato ISOyyyy-mm-ddTHH:MM:SSZSQLite es un proyecto realmente excelente, pero algunas decisiones de diseño son desconcertantes
Desde la perspectiva de alguien que viene de entornos SQL empresariales, no me tomaba SQLite en serio porque los tipos de campo no se aplican de forma predeterminada, y también me sorprendió cuando se convirtió en la base de los metadatos de apps para smartphones.
Esto me recuerda a esa vieja historia de redes en la que se elige UDP por su baja latencia y simplicidad, para luego volver a implementar directamente en la aplicación la mayoría de las funciones de confiabilidad de TCP.
Pero implementar por cuenta propia la verificación de tipos sobre SQLite no estricto no parece generar ventajas similares.
PostgreSQL también trae valores por defecto orientados a sistemas de baja especificación, así que requiere configuración para obtener un rendimiento óptimo.
Además, TCP está basado en streams, por lo que muchas aplicaciones sufren el problema de bloqueo HOL; si uno crea su propia capa de confiabilidad sobre UDP, después de bastante trabajo puede terminar obteniendo mejores resultados que con TCP.
Ellos dirán que son ruidosos y que se manejan mal, pero porque se acercan a productos pensados desde el inicio para otros usos como si solo importara su propio caso de uso.
Las tablas estrictas tienen la desventaja de que no se pueden usar algunos tipos como Date, pero aun así deberían ser el valor predeterminado.
Si varias aplicaciones comparten una base de datos, deberías poder confiar en los tipos declarados; si una guarda una cadena en una columna numérica, rompe a todas las demás.
En cambio, una base de datos embebida, que es el uso principal de SQLite, normalmente la usa una sola aplicación, así que también existe la ventaja de poder evolucionar el esquema en vez de crear una nueva base de datos y copiar los datos.
En ese caso, el código de la aplicación sabe qué esperar de cada columna, incluidos los tipos mixtos.
INTEGER,TEXT,BLOB,REALyNUMERIC: https://sqlite.org/datatype3.htmlEXPLAINsobre una consultaSELECTarbitraria para obtener el nombre del tipo especificado formalmente.Por lo tanto, aunque uses nombres como
DATEoDATETIME, ni siquiera se pueden inferir.Dateno es un tipo real; simplemente se crea una columna con afinidad numérica.El modo estricto restringe la notación de tipos de columna, impide usar nombres más significativos y evita que el código aproveche esos nombres al mapear tipos de base de datos a tipos de aplicación, por lo que en realidad estorba a un sistema de tipos más estricto en la capa de aplicación.
Hay más detalles en https://hn.algolia.com/?query=chrismorgan+strict+sqlite&type..., y si manejas bases de datos con crates como sqlx de Rust, creo que conviene evitar el modo estricto.
Quiero tipos estrictos en bases de datos y RPC, pero SQLite tiene un propósito algo distinto; quizá, al usarlo directamente, se entienda mejor https://sqlite.org/flextypegood.html.
Por ejemplo, el objetivo de hacer que scripts arbitrarios no pensados para SQLite funcionen por casualidad es una característica que normalmente no se considera en otros DBMS.
La ventaja principal del tipado flexible es que permite evolucionar el esquema fácilmente.
Cada vez que cambian los requisitos de una base de datos embebida, en vez de crear una nueva base de datos y migrar los datos, puedes modificar el esquema en el lugar; y si solo una aplicación lee y escribe, no sorprende que se agregue una cadena a una columna de enteros.
En cambio, si varias aplicaciones, actualizadas cada una según su propio calendario, comparten la misma base de datos, el esquema es un contrato, por lo que un solo tipo incorrecto puede romper otra aplicación.
Si una aplicación usa una tabla creada por otra, debe respetar estrictamente los tipos de datos acordados.
Volver a comprobarlo en tiempo de ejecución no aporta un beneficio real, y un código que el análisis estático confirmó que inserta una cadena no va a mutar de repente para insertar un entero.
SQLite no es como Postgres, donde varias aplicaciones comparten datos y hay que confiar en terceros.
En entornos como Postgres, la validación en tiempo de ejecución es esencial, pero SQLite está diseñado básicamente para una aplicación y una base de datos, así que solo necesitas confiar en tu propio código que puede evaluarse en tiempo de compilación.
En el caso atípico de que varias aplicaciones compartan un solo archivo, puedes activar tablas estrictas.
Los tipos de datos dinámicos pueden tener sentido para usos como un almacén simple de clave-valor, pero hay que preguntarse qué encaja mejor con el principio de mínima sorpresa: que
INTEGERacepte'hello world'sin error, o que solo puedas insertar ese valor si especificas una palabra clave comoNONSTRICTo el tipoANY.Me parece que la gran mayoría de usuarios de SQLite no esperaría que lo primero fuera posible.
Eso también coincide con la explicación de que se desea que código escrito para otros DBMS funcione por casualidad en SQLite.
Aun así, es muy sorprendente que, sin que el usuario haya omitido el tipo, pueda especificar
INTEGERe insertar texto.Alguna vez, UUID que comenzaban con valores como
08123…se parsearon como octales, y algunos se convirtieron erróneamente en números.Fue confuso y molesto, pero lo resolví aplicando
STRICTy recreando toda la tabla.SQLite no tiene tipo octal.
Uso
CREATE TABLE ... STRICT WITHOUT ROWIDde forma predeterminada y no veo ninguna razón para hacerlo de otra manera.