Tipos de datos SQLite: Una mirada detallada a las clases de almacenamiento

SQLite utiliza un sistema de tipos flexible basado en clases de almacenamiento y afinidad de tipos. A diferencia de los RDBMS tradicionales que imponen un tipado estricto, SQLite permite un manejo más dinámico de los tipos. Comprender este modelo es importante para garantizar la coherencia de los datos y evitar errores sutiles en la lógica de la aplicación.

En este artículo, exploraré el sistema de tipos de SQLite y te mostraré cómo trabajar con los tipos de datos más comunes en SQLite. Si eres nuevo en SQL, considera comenzar con nuestro curso Introducción a SQL para adquirir una base sólida. Además, la hoja de referencia rápida sobre conceptos básicos de SQL, que puedes descargar, me parece muy útil porque incluye todas las funciones SQL más comunes.

Conceptos básicos del sistema de tipos de SQLite

Para trabajar con SQLite, es importante comprender cómo almacena e interpreta los datos internamente. A continuación explicaré cómo un sistema de tipo SQLite diseña aplicaciones fiables y eficientes.

Explicación de las clases de almacenamiento

SQLite utiliza cinco clases de almacenamiento para representar valores internamente:

• NULL representa un valor que falta o no está definido.

• INTEGER representa un entero con signo almacenado en 1 a 8 bytes, dependiendo de la magnitud.

• REAL describe un número de punto flotante almacenado como un valor IEEE-754 de 8 bytes.

• TEXT almacena cadenas de texto codificadas en UTF-8, UTF-16BE o UTF-16LE.

• BLOB almacena datos binarios exactamente tal y como se introducen, sin ninguna transformación.

Tipos de datos SQLite. Fuente de la imagen: OpenAI

Estas clases de almacenamiento difieren de los tipos de datos declarados en un esquema de tabla. Una columna puede declararse como « VARCHAR(50) » o « BOOLEAN », pero el valor real se almacena utilizando una de las cinco clases de almacenamiento en función de su contenido. Esta separación confiere a SQLite su naturaleza dinámica y tolerante, pero también exige a los programadores que presten atención a la validación de datos y a la coherencia de los tipos en el código de sus aplicaciones.

Afinidad de tipos y comportamiento de la columna

La afinidad de tipos en SQLite es un tipo recomendado que se asigna a una columna en función de su tipo de datos declarado. SQLite utiliza las siguientes reglas para determinar la afinidad a partir del texto del tipo declarado.

• Las columnas declaradas como « VARCHAR », « CHAR » o « TEXT » obtienen una afinidad « TEXT ».

• Los tipos como INT y INTEGER obtienen afinidad INTEGER.

• Las declaraciones como DECIMAL, NUMERIC o BOOLEAN obtienen una afinidad de NUMERIC.

• Tipos como REAL, FLOAT o DOUBLE obtienen una afinidad de REAL.

• La afinidad predeterminada es BLOB si no se declara ningún tipo reconocido.

Por ejemplo, una columna declarada como « VARCHAR(100) » tendrá una afinidad « TEXT », lo que significa que SQLite intentará convertir los valores a texto cuando se almacenen. Esta afinidad guía la forma en que SQLite convierte y almacena los datos, pero no restringe el almacenamiento de otros tipos en esa columna.

Tipificación manifiesta y sus implicaciones

SQLite utiliza tipado manifiesto, lo que significa que el tipo de datos está asociado al valor individual, no al contenedor de la columna. Esto permite que coexistan varios tipos de valores en una sola columna. Por ejemplo, una columna declarada como « INTEGER » puede contener valores « TEXT » o « BLOB ».

El tipado manifiesto permite a los programadores insertar diversos tipos de datos sin alterar el esquema. Sin embargo, la desventaja es que puede dar lugar a datos incoherentes, una clasificación impredecible y una mayor dependencia de la lógica de la aplicación para aplicar las reglas de tipo.

Te recomiendo que realices nuestro curso de Diseño de bases de datos para aprendera gestionar bases de datos, incluyendo la organización de datos en diferentes tipos de datos y la gestión de bases de datos.

Trabajar con tipos de datos comunes en SQLite

Ahora veamos cómo manejar los tipos de datos comunes en SQLite, como texto, números, booleanos, fechas y datos binarios.

Texto, números y booleanos

Las declaraciones de datos más utilizadas en SQLite incluyen TEXT, INTEGER, REAL, NUMERIC y BOOLEAN, pero almacena los valores en función de la afinidad de tipos, no de tipos estrictos. A continuación se muestran casos de uso para cada tipo de datos en un esquema real.

• TEXT almacena cadenas, adecuadas para nombres, correos electrónicos, URL y datos textuales generales.

• INTEGER Es ideal para números enteros, como recuentos, identificadores y códigos, ya que ofrece un almacenamiento y una clasificación eficientes.

• REAL almacena números de coma flotante para mediciones, divisas o valores no enteros.

• La afinidad NUMERIC cubre los valores declarados como DECIMAL, BOOLEAN o similares. Dependiendo del valor, SQLite intenta almacenarlos como INTEGER o REAL.

• BOOLEAN no es una clase de almacenamiento nativa, sino que se simula almacenando FALSE ( 0 ) o TRUE ( 1 ) como valores de INTEGER detrás de escena.

Recomiendo utilizar INTEGER para valores booleanos con restricciones explícitas del lado de la aplicación, mientras que NUMERIC es adecuado para valores que requieren un análisis numérico flexible.

Fechas, horarios y estrategias temporales

SQLite no tiene un tipo nativo de fecha y hora ( DATE o DATETIME ), pero admite los siguientes formatos para almacenar datos temporales:

• TEXT: Almacena las fechas y horas como cadenas ISO8601, por ejemplo, « YYYY-MM-DD HH:MM:SS ». Este formato es legible para los humanos y compatible con muchas herramientas, pero puede ser más lento para las comparaciones.

• REAL: Almacena las fechas como números de días julianos (en coma flotante), útiles para operaciones aritméticas con fechas y consultas de rangos, pero menos legibles.

• INTEGER: Almacena marcas de tiempo Unix, segundos desde el 1 de enero de 1970. Es eficiente para el almacenamiento y las comparaciones numéricas rápidas, pero no es legible para los humanos.

Cuando trabajes con datos temporales, puedes utilizar estas funciones: date(), time(), datetime(), julianday() y strftime() para analizar, formatear y convertir entre formatos temporales.

Casos extremos binarios y numéricos

SQLite también admite datos binarios a través de la clase de almacenamiento « BLOB ». Los BLOB son útiles para almacenar imágenes, archivos o cualquier dato no textual. Cuando trabajes con tipos de datos de BLOB, utiliza siempre consultas parametrizadas o enlaces para insertar BLOB. También puedes almacenar archivos binarios de gran tamaño externamente y hacer referencia a ellos mediante rutas o hash.

Aunque SQLite puede almacenar números enteros de hasta 8 bytes (-2^63 a 2^63-1), los valores que superen este rango pueden convertirse silenciosamente al tipo de datos REAL. Esto puede hacer que los valores pierdan precisión. SQLite carece de una aplicación estricta de la precisión decimal, por lo que siempre debes utilizar bibliotecas externas o lógica de aplicación para los datos financieros cuando necesites precisión.

Aplicar estructura con tablas STRICT

A diferencia del sistema de tipado flexible tradicional, SQLite introdujo las tablas de tipo flexible ( STRICT ) en la versión 3.37.0. Una tabla de tipo « STRICT » proporciona un modo de tipificación más estricto que obliga a que los valores de las columnas coincidan exactamente con su tipo. Cuando declaras una tabla como un STRICT, todos los valores insertados deben coincidir con el tipo declarado de la columna, y cualquier discrepancia da lugar a un error. 

El modo « STRICT » mejora la coherencia y la validación de los datos. También ayuda a detectar errores relacionados con los tipos en una fase temprana del desarrollo y hace que el comportamiento de SQLite sea más predecible, más parecido al de los RDBMS tradicionales.

Antes de utilizar STRICT como programador, debes tener en cuenta que solo está disponible en SQLite 3.37.0 y versiones posteriores. Además, no puedes mezclar el tipado e STRICT o y el tipado manifiesto en la misma tabla.

Consideraciones sobre el rendimiento y el diseño

SQLite tiene un sistema de tipos flexible con implicaciones en el rendimiento. SQLite utiliza codificación de longitud variable para los valores de tipo INTEGER y REAL, lo que significa que los números más pequeños utilizan menos bytes. Esto ayuda a minimizar el tamaño de la base de datos, especialmente en el caso de conjuntos de datos de gran tamaño. 

La afinidad de tipos también influye en cómo se almacenan y comparan los valores. Los tipos inconsistentes en una columna pueden reducir la eficacia de los índices, ya que SQLite puede realizar conversiones de tipo adicionales durante las búsquedas, lo que ralentiza las consultas.

Ten en cuenta las siguientes estrategias para diseñar esquemas y consultas eficientes.

• Define columnas con afinidades claras y coherentes para minimizar la sobrecarga de la coerción de tipos.

• Considera la posibilidad de realizar conversiones explícitas en las consultas cuando sea necesario convertir tipos para garantizar un comportamiento coherente.

• Utiliza INTEGER para ID numéricos y contadores, REAL para datos de punto flotante y TEXT para cadenas.

• Evita mezclar tipos de datos en la misma columna para evitar problemas de ordenación y comparación.

• Normaliza los esquemas para evitar la coerción de tipos redundantes o la duplicación de datos.

• Utiliza EXPLAIN QUERY PLAN para analizar el rendimiento y ajustar las consultas.

Ejemplos de tipos de datos SQLite con patrones de esquema

Los siguientes esquemas de tablas ilustran el uso reflexivo de los tipos de datos, la afinidad de tipos y las restricciones de SQLite.

Tabla de usuarios con simulación booleana y restricciones

En el ejemplo siguiente:

• is_active simula un booleano utilizando un control de tipo « INTEGER » con una restricción de tipo « CHECK ».

• TEXT El tipo de datos garantiza la coherencia de los valores de cadena, mientras que el tipo booleano ( INTEGER ) se utiliza para la lógica booleana.

 -- Create users table with auto-incrementing rowid and enforce uniqueness
CREATE TABLE users (
    id INTEGER PRIMARY KEY, 
    username TEXT NOT NULL UNIQUE, 
    email TEXT NOT NULL, 
    is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1))  -- Simulates boolean
);

Ordena la tabla utilizando datos temporales y precisión numérica

En la consulta siguiente, total utiliza NUMERIC para valores similares a divisas con el fin de gestionar la precisión a nivel de aplicación. order_date se almacena como TEXT en formato ISO-8601, lo que garantiza la legibilidad y la compatibilidad con las funciones de fecha.

-- Create orders table with row identifier and foreign key in practice
-- ISO date format "YYYY-MM-DD"
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,  
    customer_id INTEGER NOT NULL, 
    total NUMERIC NOT NULL, 
    order_date TEXT NOT NULL CHECK (length(order_date) = 10)  );

Tabla de archivos con comprobaciones binarias y de tipo

En la consulta siguiente, content utiliza BLOB para almacenar datos binarios sin procesar. uploaded_at almacena la hora como un INTEGER para optimizar el espacio y el rendimiento.

-- Create files table with raw binary data and Unix timestamp (epoch seconds)
CREATE TABLE files (
    file_id INTEGER PRIMARY KEY, 
    name TEXT NOT NULL, 
    content BLOB NOT NULL,  
    uploaded_at INTEGER NOT NULL 
);

Puedes verificar el tipo en tiempo de ejecución con la siguiente consulta:

SELECT typeof(uploaded_at), typeof(content) FROM files;

Te recomiendo realizar nuestro curso Introducción a las bases de datos relacionales en SQL para aprendermás sobre estructuras de datos y cómo crear tablas en bases de datos.

Comparación de SQLite con otras bases de datos

El sistema de tipos de SQLite es más flexible que los RDBMS tradicionales como MySQL o PostgreSQL. La tabla siguiente compara SQLite con estas bases de datos.

Conclusión

SQLite tiene un sistema de tipado flexible que ofrece diferentes ventajas cuando se utiliza de forma inteligente. Los programadores pueden diseñar esquemas que equilibren la adaptabilidad y la fiabilidad mediante la comprensión de las clases de almacenamiento, la afinidad de tipos y la tipificación manifiesta. Características como las tablas de e STRICT, el uso coherente de los tipos y la normalización ayudan a reforzar la estructura donde es necesario.

Como siguiente paso, te recomiendo que realices nuestros cursos Análisis exploratorio de datos en SQL y Manipulación de datos en SQL para aprender a analizar datos de diferentes tipos utilizando consultas SQL avanzadas.