Skip to content

SQLite Hoja de referencia

Motor de base de datos SQL autosuficiente, sin servidor y sin configuración.

01

Primeros pasos

Fundamentos de la CLI

SQLite guarda una base de datos entera en un solo archivo. La CLI sqlite3 abre o crea un archivo .sqlite/.db bajo demanda. A diferencia de MySQL/PostgreSQL, no hay proceso servidor — las aplicaciones enlazan la librería SQLite directamente. .dump exporta la base de datos completa como SQL de texto para respaldar o migrar.

sqlite
# open/create a database file
sqlite3 mydb.sqlite

# at the sqlite> prompt
.help              # show help
.tables            # list tables
.schema users      # show CREATE statement for users
.databases         # list attached databases
.dump              # dump entire DB as SQL text
.quit              # exit

# execute SQL from a file
sqlite3 mydb.sqlite < script.sql

# execute a query from command line
sqlite3 mydb.sqlite "SELECT COUNT(*) FROM users;"

Comandos punto

Los comandos punto los interpreta la CLI sqlite3, no el motor SQL — nunca necesitan punto y coma y no están disponibles vía APIs de programación. .mode controla el formato de salida (prueba 'column' con .header on para un uso interactivo legible). .schema muestra las sentencias CREATE; .fullschema añade índices, vistas y triggers. Usa .help para descubrir más.

sqlite
-- dot commands are CLI-only, NOT SQL (no semicolon needed)
.help                 -- list all dot commands
.tables               -- list all tables
.schema               -- schema for all tables
.schema users         -- schema for the users table
.fullschema           -- schema incl. indexes, triggers, views
.indices users        -- list indexes on users
.header on            -- show column headers
.mode column          -- column-aligned output
.mode list            -- default, pipe-separated
.mode csv             -- CSV output
.mode json            -- JSON output
.show                 -- show current settings
.width 10 20 30       -- set column widths
.timer on             -- show query execution time

Gestión de archivos de base de datos

ATTACH DATABASE permite a una conexión consultar hasta 10 archivos de base de datos simultáneamente como esquemas (main, temp, más los adjuntos). El URI :memory: crea una base de datos puramente en memoria que desaparece al cerrar — ideal para pruebas o computación temporal. Los nombres de archivo URI (file:...) habilitan los modos de solo lectura, caché compartida y otros. Cada base adjunta es un archivo aparte en disco.

sqlite
-- attach another database file to the current connection
ATTACH DATABASE 'archive.sqlite' AS archive;
DETACH DATABASE archive;

-- list attached databases (main + attached)
.databases

-- the in-memory database (never written to disk)
sqlite3 :memory:

-- temporary database (deleted when connection closes)
sqlite3 ""          -- empty arg => temp DB

-- open read-only
sqlite3 "file:mydb.sqlite?mode=ro" --readonly

-- query the underlying page size / file format
PRAGMA page_size;
PRAGMA journal_mode;

Encabezados y formato de salida

El formato de salida solo afecta a la CLI, nunca a los datos en sí. El modo 'column' con .headers on es el más legible para humanos. 'list' (por defecto) es el mejor para pipelines de shell. 'json' y 'csv' convierten a SQLite en un conversor práctico. .output redirige los resultados a un archivo; recuerda volver con .output stdout. .mode box/table dibuja bordes ASCII.

sqlite
-- readable interactive mode
.headers on
.mode column
.width 15 20 10
SELECT id, name, email FROM users LIMIT 5;

-- pipe-separated (good for scripts)
.mode list
.separator "|"
SELECT id, name FROM users;

-- boxed table output (sqlite 3.36+)
.mode box
.mode table

-- JSON output for piping to other tools
.mode json
SELECT id, name FROM users;

-- write output to a file
.output users.txt
SELECT id, name FROM users;
.output stdout

Ejecutar scripts y archivos SQL

.read ejecuta un archivo de SQL dentro de una sesión CLI abierta; redirigir con < lo corre antes del prompt interactivo. --bail detiene el lote ante el primer error (de lo contrario SQLite sigue adelante, lo que puede enmascarar fallos). .parameter (3.44+) enlaza parámetros con nombre desde el shell, evitando la interpolación de cadenas. Comprueba siempre los scripts con .echo on en desarrollo.

sqlite
-- run a .sql file from the shell
sqlite3 mydb.sqlite ".read setup.sql"
-- or piped in
sqlite3 mydb.sqlite < setup.sql

-- inside the CLI
.read setup.sql

-- stop on first error (shell flag)
sqlite3 mydb.sqlite --bail < script.sql

-- echo commands as they run
.echo on
.read migrations.sql

-- show EXPLAIN before each statement (debug)
.explain on

-- parameterize from the shell (sqlite 3.44+)
sqlite3 mydb.sqlite \
  -cmd ".parameter set @min 18" \
  "SELECT name FROM users WHERE age >= @min"
02

Tablas y tipos de datos

Crear tablas

INTEGER PRIMARY KEY es un alias del rowid — se autoincrementa y es la clave más rápida posible. AUTOINCREMENT cambia el algoritmo para que los IDs eliminados nunca se reutilicen (ligeramente más lento; normalmente innecesario). DEFAULT (expr) permite expresiones arbitrarias como datetime('now'). CREATE TABLE AS SELECT crea una tabla precargada con los resultados de la consulta pero no copia restricciones ni índices.

sqlite
CREATE TABLE users (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT    NOT NULL UNIQUE,
  email    TEXT    NOT NULL UNIQUE,
  age      INTEGER CHECK (age >= 0),
  role     TEXT    NOT NULL DEFAULT 'user',
  created  TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS orders (
  id        INTEGER PRIMARY KEY,
  user_id   INTEGER NOT NULL,
  total     REAL    NOT NULL DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

-- quick table from a query
CREATE TABLE archive AS
  SELECT * FROM orders WHERE created < '2024-01-01';

Clases de almacenamiento y afinidad de tipos

SQLite usa tipado dinámico: cualquier columna puede guardar cualquiera de las 5 clases de almacenamiento. La 'afinidad de tipos' intenta convertir los valores cuando es posible (por ejemplo, '42' en una columna INTEGER se convierte en el entero 42) pero nunca da error ante discrepancia. Las tablas STRICT (3.37+) restauran la comprobación de tipos rígida tradicional por columna. typeof() reporta la clase realmente almacenada. Esta flexibilidad es potente pero exige disciplina — usa STRICT para esquemas nuevos donde la integridad de datos importe.

sqlite
-- SQLite has 5 storage classes (not strict types):
--   NULL, INTEGER, REAL, TEXT, BLOB
CREATE TYPE demo...  -- not supported

-- columns have 'type affinity', not enforced types
CREATE TABLE t (
  a INTEGER,   -- affinity INTEGER
  b TEXT,      -- affinity TEXT
  c REAL,      -- affinity REAL
  d BLOB,      -- affinity BLOB (no conversion)
  e            -- no affinity (accepts anything)
);

-- this WORKS despite 'INTEGER' column (dynamic typing)
INSERT INTO t (a) VALUES ('hello');
SELECT typeof(a) FROM t;   -- 'text'

-- STRICT tables (SQLite 3.37+) actually enforce types
CREATE TABLE strict_t (a INTEGER, b TEXT) STRICT;
INSERT INTO strict_t VALUES ('hi', 1);   -- error

Tipos de datos comunes

SQLite no tiene tipos DATE/TIME ni BOOLEAN nativos — guarda las marcas de tiempo como TEXT ISO-8601 ('YYYY-MM-DD HH:MM:SS') por ordenabilidad y usa las funciones de fecha/hora para manipularlas. Los booleanos son enteros 0/1. Los límites de longitud VARCHAR(n) se ignoran (solo compatibilidad). La afinidad NUMERIC prueba INTEGER primero, luego REAL, preservando los decimales exactos cuando puede. BLOB guarda bytes crudos textualmente.

sqlite
-- integers
CREATE TABLE numerics (
  small  INTEGER,        -- 1/2/4/8 bytes depending on value
  big    INTEGER,        -- up to 64-bit
  -- no separate BIGINT/SMALLINT, all map to INTEGER affinity
  flag   BOOLEAN         -- stored as 0 (false) or 1 (true)
);

-- text & blobs
CREATE TABLE blobs (
  name   TEXT,           -- variable-length UTF-8, no length limit
  data   BLOB,           -- raw bytes, no conversion
  note   VARCHAR(255)    -- length ignored, affinity TEXT
);

-- real numbers
CREATE TABLE floats (
  price  REAL,           -- 8-byte IEEE float
  precise NUMERIC(10,2)  -- affinity NUMERIC, decimal preserved if exact
);

-- dates: SQLite has no native DATE type — store as TEXT (ISO-8601)
CREATE TABLE events (
  ts TEXT  -- 'YYYY-MM-DD HH:MM:SS' recommended
);

Alterar tablas

El ALTER TABLE de SQLite es limitado intencionalmente para un motor sin servidor. ADD COLUMN es O(1) (sin reescritura de tabla). RENAME COLUMN (3.25+) y DROP COLUMN (3.35+) son conveniencias modernas. Para cambios de tipo o adiciones de restricciones, usa el patrón de reconstrucción de tabla de 12 pasos (crear nueva, copiar, eliminar, renombrar, reconstruir índices). Las claves foráneas deben deshabilitarse temporalmente durante las reconstrucciones — mira PRAGMA legacy_alter_table.

sqlite
-- add a column (fast, appends to end)
ALTER TABLE users ADD COLUMN bio TEXT;

-- rename a column (SQLite 3.25+)
ALTER TABLE users RENAME COLUMN name TO username;

-- rename a table
ALTER TABLE users RENAME TO accounts;

-- drop a column (SQLite 3.35+)
ALTER TABLE users DROP COLUMN deprecated_field;

-- SQLite CANNOT: change a column type, add constraints to
-- an existing column, or reorder columns. Workaround:
-- 1) create new table with desired schema
-- 2) INSERT INTO new SELECT * FROM old
-- 3) DROP old; ALTER TABLE new RENAME TO old
-- 4) recreate indexes/triggers

Restricciones

Las restricciones imponen la integridad de datos a nivel de motor. PRIMARY KEY implica NOT NULL y UNIQUE. CHECK puede referenciar cualquier columna de la fila. Las cláusulas ON CONFLICT (OR IGNORE / OR REPLACE / OR ABORT / OR FAIL / OR ROLLBACK) controlan la resolución de conflictos por sentencia — OR REPLACE elimina la fila en conflicto y luego inserta, lo que reinicia su rowid. Para semántica UPSERT, prefiere ON CONFLICT ... DO UPDATE (ver la sección CRUD).

sqlite
CREATE TABLE products (
  id         INTEGER PRIMARY KEY,
  sku        TEXT    NOT NULL UNIQUE,
  name       TEXT    NOT NULL,
  price      REAL    NOT NULL CHECK (price >= 0),
  stock      INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
  category   TEXT    CHECK (category IN ('a','b','c')),

  -- composite unique constraint
  UNIQUE (name, category),

  -- table-level check
  CHECK (price * stock < 1000000)
);

-- name a constraint for clearer error messages
CREATE TABLE t (
  x INTEGER CONSTRAINT x_positive CHECK (x > 0)
);

-- conflict handling
INSERT OR IGNORE INTO products (sku, name, price) VALUES (...);
INSERT OR REPLACE INTO products (...) VALUES (...);
UPDATE OR ABORT products SET stock = stock - 1 WHERE id = 1;
03

Operaciones CRUD

INSERT

El INSERT de varias filas es dramáticamente más rápido que los inserts por fila en un bucle — lotealos. INSERT ... SELECT copia datos entre tablas. last_insert_rowid() devuelve el rowid del INSERT más reciente en la conexión actual (no en la transacción). Para apps, prefiere los inserts parametrizados (ver la sección de Integración) para evitar la inyección SQL y los dolores de cabeza con las comillas. Usa DEFAULT para tomar explícitamente el valor por defecto de una columna.

sqlite
-- single row
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);

-- multiple rows in one statement (fast)
INSERT INTO users (username, email) VALUES
  ('bob', '[email protected]'),
  ('carol', '[email protected]'),
  ('dave', '[email protected]');

-- insert from a query
INSERT INTO archive_users
SELECT * FROM users WHERE active = 0;

-- explicit NULL / default
INSERT INTO users (username, email, age)
VALUES ('eve', '[email protected]', DEFAULT);

-- return the new rowid (handy in apps)
INSERT INTO users (username) VALUES ('frank');
SELECT last_insert_rowid();   -- returns the new id

UPSERT (ON CONFLICT)

ON CONFLICT (UPSERT) es el modismo de SQLite para 'insertar o actualizar si existe' — mucho más seguro que INSERT OR REPLACE, que elimina la fila existente (disparando triggers DELETE y reiniciando el rowid). 'excluded' se refiere a la fila propuesta para inserción. DO NOTHING se salta en silencio. Los objetivos de conflicto pueden ser cualquier restricción UNIQUE o PRIMARY KEY. Este es el patrón correcto para contadores, actualizaciones de última vista e importaciones idempotentes.

sqlite
-- insert, but update on conflict
INSERT INTO users (id, username, email)
VALUES (1, 'alice', '[email protected]')
ON CONFLICT(id) DO UPDATE SET
  email  = excluded.email,
  username = excluded.username;

-- ignore on conflict
INSERT INTO users (id, username)
VALUES (1, 'alice')
ON CONFLICT(id) DO NOTHING;

-- conflict on a specific unique constraint
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]')
ON CONFLICT(email) DO UPDATE SET
  username = excluded.username;

UPDATE

Incluye siempre WHERE salvo que realmente pretendas actualizar todas las filas — SQLite no tiene modo de simulación. UPDATE ... FROM (3.33+) y UPDATE ... RETURNING (3.35+) acercan a SQLite a la ergonomía de PostgreSQL. RETURNING es inestimable para apps que necesitan el estado tras la actualización sin un SELECT aparte. CASE habilita actualizaciones masivas de una sola sentencia con lógica por fila, que es mucho más rápido que las actualizaciones fila por fila.

sqlite
-- basic update (ALWAYS use WHERE in production!)
UPDATE users SET age = 31 WHERE id = 1;

-- multiple columns
UPDATE users
SET age = age + 1, role = 'admin'
WHERE username = 'alice';

-- conditional update with CASE
UPDATE products
SET price = CASE
  WHEN category = 'a' THEN price * 0.9
  WHEN category = 'b' THEN price * 0.95
  ELSE price
END;

-- update from another table (subquery)
UPDATE orders
SET total = (SELECT SUM(qty * price)
             FROM items WHERE order_id = orders.id);

-- UPDATE ... RETURNING (SQLite 3.35+)
UPDATE users SET role = 'admin'
WHERE id IN (1,2,3)
RETURNING id, username, role;

DELETE

SQLite no tiene TRUNCATE — DELETE FROM tabla elimina todas las filas pero dispara triggers a nivel de fila y no reinicia el contador de AUTOINCREMENT (guardado en sqlite_sequence). DELETE ... RETURNING (3.35+) registra lo eliminado. Para datos de auditoría, prefiere los borrados suaves (una columna deleted_at) a los duros. El espacio liberado por los deletes se reutiliza en inserts futuros; VACUUM lo recupera para el sistema operativo.

sqlite
-- basic delete
DELETE FROM users WHERE id = 1;

-- delete with a subquery condition
DELETE FROM orders
WHERE user_id IN (SELECT id FROM users WHERE active = 0);

-- DELETE ... RETURNING
DELETE FROM sessions
WHERE expires < datetime('now')
RETURNING id, user_id;

-- delete ALL rows (fast, but fires triggers)
DELETE FROM logs;

-- TRUNCATE equivalent: delete all + reset rowid
DELETE FROM logs;
DELETE FROM sqlite_sequence WHERE name = 'logs';

-- soft delete pattern
UPDATE users SET deleted_at = datetime('now') WHERE id = 1;

Fundamentos de SELECT

SELECT es el caballo de batalla. LIMIT conteo OFFSET salto implementa la paginación (la paginación por keyset es más rápida para offsets grandes — ver la sección de Consultas). Los alias (AS, opcional) mejoran la legibilidad y son obligatorios para columnas calculadas en vistas. DISTINCT colapsa filas idénticas; considera GROUP BY para un control más fino. CASE añade lógica if/else a los conjuntos de resultados y es utilizable en cualquier cláusula.

sqlite
-- basic select
SELECT id, username, email FROM users;

-- limit and offset
SELECT id, username FROM users LIMIT 10 OFFSET 20;
SELECT id, username FROM users LIMIT 20, 10;  -- offset, count

-- aliasing columns and tables
SELECT u.id AS user_id, u.username AS name
FROM users AS u;

-- expressions
SELECT
  username,
  age,
  age * 365 AS days_alive,
  UPPER(username) AS upper_name
FROM users;

-- distinct rows
SELECT DISTINCT category FROM products;

-- conditional output
SELECT name,
  CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END AS status
FROM users;
04

Consultar y filtrar

WHERE y operadores

NULL es especial en SQL — es 'desconocido', no un valor. = NULL siempre devuelve NULL (tratado como falso), así que usa IS NULL / IS NOT NULL. NULL combinado con AND/OR sigue la lógica de tres valores. BETWEEN es inclusivo. IN es abreviatura de igualdad-OR; evita IN (NULL, ...) pues se comporta de forma extraña. Usa paréntesis con generosidad para que las condiciones compuestas no sean ambiguas.

sqlite
-- comparison operators
SELECT * FROM products WHERE price < 50;
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
SELECT * FROM users WHERE age IN (18, 21, 25);
SELECT * FROM users WHERE age NOT IN (18, 21);

-- NULL handling (NEVER use = NULL)
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- logical operators
SELECT * FROM users
WHERE age >= 18 AND age <= 65 AND role = 'admin';

SELECT * FROM users
WHERE role = 'admin' OR role = 'editor';

-- range with multiple conditions
SELECT * FROM orders
WHERE (status = 'shipped' AND total > 100)
   OR (status = 'pending' AND total > 500);

ORDER BY y LIMIT

ORDER BY sin un desempatador único da un orden no determinista entre ejecuciones — añade siempre una columna única (como id) como clave de ordenación final. La paginación OFFSET es O(n) porque escanea y descarta filas; la paginación por keyset (WHERE id > ultimo_id_visto) es O(limite) y la elección correcta para tablas grandes. RANDOM() exige una ordenación completa — úsalo solo en conjuntos de resultados pequeños o subconjuntos muestreados.

sqlite
-- ascending (default) / descending
SELECT * FROM users ORDER BY username;
SELECT * FROM users ORDER BY created DESC;
SELECT * FROM users ORDER BY age DESC, username ASC;

-- sort with NULLs first or last (SQLite 3.30+)
SELECT * FROM tasks ORDER BY due_date ASC NULLS FIRST;
SELECT * FROM tasks ORDER BY due_date DESC NULLS LAST;

-- pagination: keyset is faster than OFFSET
-- slow (offset scans & discards rows):
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10000;
-- fast (keyset):
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;

-- random sample
SELECT * FROM users ORDER BY RANDOM() LIMIT 5;

Coincidencia de patrones

LIKE no distingue mayúsculas para ASCII por defecto (una trampa sorprendente — usa COLLATE BINARY para distinguirlas). GLOB distingue mayúsculas y soporta clases de caracteres como [A-Z]. REGEXP no viene integrado — debes registrar una función o cargar una extensión. Para búsqueda de texto completo a escala, usa FTS5 (ver la sección de Rendimiento) en lugar de LIKE '%termino%', que no puede usar índices.

sqlite
-- LIKE: case-insensitive by default for ASCII,
--   wildcards % (any chars) and _ (single char)
SELECT * FROM users WHERE username LIKE 'al%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username LIKE '_lice';

-- case-sensitive LIKE
SELECT * FROM users WHERE username LIKE 'AL%' COLLATE BINARY;

-- GLOB: Unix shell-style, case-SENSITIVE
--   wildcards * and ?, plus [abc] character classes
SELECT * FROM users WHERE username GLOB 'Al*';
SELECT * FROM users WHERE username GLOB '[A-D]*';

-- REGEXP (requires extension or custom function)
SELECT * FROM users WHERE username REGEXP '^a.*e$';

-- substring match
SELECT * FROM users WHERE INSTR(username, 'lic') > 0;

CASE y lógica condicional

CASE es el if/else de SQL y funciona en SELECT, WHERE, ORDER BY, GROUP BY y HAVING. La forma 'simple' empareja un valor; la 'buscada' evalúa condiciones booleanas en orden. SUM(CASE WHEN ... THEN 1 ELSE 0 END) es el modismo clásico de pivote/conteo condicional. CASE cortocircuita en el primer WHEN que coincida, así que el orden importa. ELSE pasa por defecto a NULL si se omite.

sqlite
-- simple CASE (value match)
SELECT name,
  CASE role
    WHEN 'admin' THEN 'Administrator'
    WHEN 'editor' THEN 'Editor'
    ELSE 'Regular User'
  END AS role_label
FROM users;

-- searched CASE (conditions)
SELECT name,
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age < 65 THEN 'adult'
    ELSE 'senior'
  END AS age_group
FROM users;

-- CASE in aggregate (conditional count)
SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) AS active_count,
  SUM(CASE WHEN active = 0 THEN 1 ELSE 0 END) AS inactive_count
FROM users;

-- CASE in ORDER BY (custom sort order)
SELECT * FROM products
ORDER BY CASE category
  WHEN 'featured' THEN 0
  WHEN 'new' THEN 1
  ELSE 2
END;

DISTINCT y operaciones de conjuntos

UNION ALL es más rápido que UNION porque UNION realiza una ordenación de deduplicación — prefiere UNION ALL cuando los duplicados sean imposibles o aceptables. Todas las operaciones de conjuntos exigen conteos y tipos de columnas compatibles. SQLite aplica INTERSECT/EXCEPT con mayor precedencia que UNION/UNION ALL; usa paréntesis para controlar el orden. El ORDER BY en una consulta compuesta aplica al conjunto de resultados completo, no al último SELECT.

sqlite
-- distinct rows
SELECT DISTINCT category FROM products;
SELECT DISTINCT city, country FROM users;

-- UNION (dedup) and UNION ALL (faster, keeps dups)
SELECT 'user' AS type, id FROM users WHERE active = 1
UNION ALL
SELECT 'admin', id FROM admins;

-- INTERSECT: rows in both
SELECT id FROM users
INTERSECT
SELECT user_id FROM orders;

-- EXCEPT: rows in first but not second
SELECT id FROM users
EXCEPT
SELECT user_id FROM orders;

-- combine with ORDER BY (applies to whole result)
SELECT name FROM users WHERE active = 1
UNION
SELECT name FROM archived_users
ORDER BY name;
05

JOINs

INNER JOIN

INNER JOIN devuelve solo las filas con coincidencia en ambas tablas — las filas sin coincidencia de cualquier lado se descartan. USING (col) es abreviatura cuando ambas tablas tienen una columna del mismo nombre y produce una única columna fusionada. NATURAL JOIN usa implícitamente todas las columnas del mismo nombre — cómodo pero frágil (un cambio de esquema puede alterar silenciosamente el comportamiento del join). Prefiere ON explícito por claridad.

sqlite
-- only matching rows from both tables
SELECT u.username, o.id AS order_id, o.total
FROM users AS u
INNER JOIN orders AS o ON o.user_id = u.id;

-- join with additional filter
SELECT u.username, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.total > 100 AND u.active = 1;

-- USING shorthand (when join columns share a name)
SELECT u.username, o.total
FROM users u
JOIN orders o USING (user_id);

-- natural join (joins on all same-named columns — risky)
SELECT * FROM users NATURAL JOIN profiles;

LEFT JOIN

LEFT JOIN conserva cada fila de la tabla izquierda; las columnas del lado derecho ausentes pasan a NULL. El patrón 'WHERE right.id IS NULL' (anti-join) halla filas sin coincidencias — a menudo más claro y rápido que NOT IN con subconsultas, especialmente cuando hay NULLs de por medio. Al agregar a través de un LEFT JOIN, COUNT(right.id) (no COUNT(*)) cuenta solo las filas coincidentes; un usuario sin pedidos da 0, no 1.

sqlite
-- all users, with their orders (NULLs if no orders)
SELECT u.username, o.id AS order_id, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

-- find users with NO orders (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- multiple left joins
SELECT u.username,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

CROSS JOIN y RIGHT/FULL

CROSS JOIN produce el producto cartesiano — útil para generar combinaciones (talla × color) pero peligroso en tablas grandes (n×m filas). SQLite soporta RIGHT JOIN (3.39+) pero se usa rara vez; reescríbelo como LEFT JOIN por claridad. No hay FULL OUTER JOIN nativo; emúlalo con LEFT JOIN ... UNION ... RIGHT JOIN donde la unión deduplica las filas coincidentes solapadas.

sqlite
-- CROSS JOIN: cartesian product (every combination)
SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c;

-- explicit CROSS JOIN (same as comma join)
SELECT a.name, b.name FROM teams a, teams b;

-- RIGHT JOIN (preserves right table) — supported but unusual
SELECT u.username, o.total
FROM orders o
RIGHT JOIN users u ON u.id = o.user_id;

-- SQLite has no FULL OUTER JOIN; emulate with LEFT + UNION:
SELECT u.username, o.id AS order_id
FROM users u LEFT JOIN orders o ON o.user_id = u.id
UNION
SELECT u.username, o.id
FROM users u RIGHT JOIN orders o ON o.user_id = u.id;

Self join

Un self join consulta la misma tabla dos veces usando alias para dar a cada copia un papel (empleado vs. jefe, usuario_a vs. usuario_b). El truco 'a.id < b.id' evita emparejar una fila consigo misma y producir pares duplicados en espejo. Para jerarquías profundas (profundidad arbitraria), un CTE recursivo (ver la sección CTE) es la herramienta correcta — los self joins solo manejan profundidades fijas.

sqlite
-- employees and their managers (same table)
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  manager_id INTEGER REFERENCES employees(id)
);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- find pairs of users in the same city
SELECT a.username AS user_a, b.username AS user_b, a.city
FROM users a
JOIN users b ON a.city = b.city AND a.id < b.id;

-- recursive hierarchy (tree traversal) — see CTE section
-- multi-level category paths
SELECT c.name, p.name AS parent
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;

Joins multi-tabla

Los joins se encadenan de izquierda a derecha; cada JOIN ve el resultado acumulado. Al mezclar joins LEFT e INNER, un INNER JOIN después de un LEFT JOIN puede volver a filtrar los NULL, anulando el propósito del LEFT — ordena con cuidado. COUNT(DISTINCT col) evita el doble conteo en los joins uno-a-muchos. Cuida la multiplicidad de joins: unir dos tablas uno-a-muchos puede producir una explosión de filas (m × n filas por padre).

sqlite
-- chain joins across 3+ tables
SELECT u.username, o.id AS order_id, i.name AS item, i.qty
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items i ON i.order_id = o.id
WHERE u.active = 1
ORDER BY o.id, i.name;

-- mixing JOIN types
SELECT u.username, o.id AS order_id, p.name AS product
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN products p ON p.id = i.product_id;

-- join with aggregation
SELECT u.username,
  COUNT(DISTINCT o.id) AS orders,
  SUM(i.qty * i.price) AS lifetime_value
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items i ON i.order_id = o.id
GROUP BY u.id, u.username;
06

Agregaciones y GROUP BY

Fundamentos de GROUP BY

GROUP BY colapsa las filas que comparten las columnas agrupadas en una fila por grupo. Toda columna no agregada en SELECT debe aparecer en GROUP BY (SQLite es tolerante y elige un valor arbitrario, pero no dependas de ello). Agrupar por una expresión (como strftime sobre una fecha) es genial para reportes por franja temporal. ORDER BY se aplica tras GROUP BY — úsalo para ordenar los grupos.

sqlite
-- count users per role
SELECT role, COUNT(*) AS user_count
FROM users
GROUP BY role;

-- multiple aggregates per group
SELECT role,
  COUNT(*)            AS user_count,
  AVG(age)            AS avg_age,
  MIN(age)            AS min_age,
  MAX(age)            AS max_age,
  SUM(age)            AS total_age
FROM users
GROUP BY role;

-- group by multiple columns
SELECT role, active, COUNT(*) AS cnt
FROM users
GROUP BY role, active
ORDER BY role, active;

-- group by expression
SELECT strftime('%Y-%m', created) AS month,
  COUNT(*) AS signups
FROM users
GROUP BY month
ORDER BY month;

Funciones de agregación

COUNT(*) cuenta filas; COUNT(col) cuenta los valores no NULL; COUNT(DISTINCT col) cuenta los no NULL únicos. AVG/SUM ignoran los NULLs. GROUP_CONCAT une valores en una cadena (coma por defecto como separador). TOTAL() siempre devuelve un REAL (0.0 ante entrada vacía), a diferencia de SUM() que devuelve NULL ante entrada vacía. La cláusula FILTER (3.30+) es más limpia que CASE para la agregación condicional y puede ser más rápida.

sqlite
SELECT
  COUNT(*)             AS row_count,
  COUNT(email)         AS emails_non_null,
  COUNT(DISTINCT role) AS distinct_roles,
  AVG(age)             AS avg_age,
  SUM(total)           AS revenue,
  MIN(created)         AS first_signup,
  MAX(created)         AS last_signup,
  GROUP_CONCAT(name)   AS all_names,
  GROUP_CONCAT(name, ' | ') AS names_piped,
  TOTAL(price)         AS total_real  -- always returns REAL, 0.0 if empty
FROM orders;

-- aggregate with FILTER (SQLite 3.30+)
SELECT
  COUNT(*)                              AS all_orders,
  COUNT(*) FILTER (WHERE status='paid') AS paid,
  SUM(total) FILTER (WHERE status='paid') AS paid_revenue
FROM orders;

HAVING (filtrar grupos)

WHERE filtra las filas de entrada antes de agrupar; HAVING filtra los grupos tras la agregación. Este ordenamiento importa: un WHERE sobre un agregado es ilegal. SQLite permite referencias de alias en HAVING (y GROUP BY) por legibilidad, pero no es estándar. El patrón 'group by X having count(*) > 1' es la consulta clásica de búsqueda de duplicados.

sqlite
-- HAVING filters groups (after GROUP BY);
-- WHERE filters rows (before GROUP BY)
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS spent
FROM orders
WHERE status = 'paid'         -- filter rows first
GROUP BY user_id
HAVING COUNT(*) >= 3 AND SUM(total) > 100   -- filter groups
ORDER BY spent DESC;

-- HAVING with aliases (SQLite allows this)
SELECT role, COUNT(*) AS n
FROM users
GROUP BY role
HAVING n > 1;

-- find duplicate usernames
SELECT username, COUNT(*) AS dupes
FROM users
GROUP BY username
HAVING COUNT(*) > 1;

GROUP_CONCAT

GROUP_CONCAT es la función de agregación de cadenas de SQLite (llamada STRING_AGG o LISTAGG en otras). El separador por defecto es la coma; especifica uno personalizado como segundo argumento. ORDER BY dentro del agregado (3.44+) controla el orden de concatenación. DISTINCT deduplica antes de unir. El resultado tiene un límite de 1GB; para grupos enormes, obtén las filas y agrega en tu aplicación.

sqlite
-- concatenate values within a group
SELECT role, GROUP_CONCAT(username) AS members
FROM users
GROUP BY role;
-- members: 'alice,bob,carol'

-- custom separator
SELECT role, GROUP_CONCAT(username, ' | ') AS members
FROM users
GROUP BY role;

-- ordered concatenation (SQLite 3.44+)
SELECT role,
  GROUP_CONCAT(username ORDER BY username) AS sorted_members
FROM users
GROUP BY role;

-- distinct values only
SELECT role,
  GROUP_CONCAT(DISTINCT city) AS cities
FROM users
GROUP BY role;

Pivotar datos

SQLite no tiene PIVOT nativo; emúlalo con SUM(CASE WHEN ...) o la cláusula FILTER más limpia. Cada columna pivotada comprueba un valor del atributo de agrupación. Este patrón transforma datos largos (normalizados) en reportes anchos (tablas cruzadas). Para pivoteo totalmente dinámico (categorías desconocidas), debes construir la cadena SQL en el código de la aplicación — SQL no puede generar columnas en runtime.

sqlite
-- manual pivot with SUM(CASE ...)
SELECT user_id,
  SUM(CASE WHEN strftime('%w', created) = '0' THEN 1 ELSE 0 END) AS sun,
  SUM(CASE WHEN strftime('%w', created) = '1' THEN 1 ELSE 0 END) AS mon,
  SUM(CASE WHEN strftime('%w', created) = '2' THEN 1 ELSE 0 END) AS tue,
  SUM(CASE WHEN strftime('%w', created) = '3' THEN 1 ELSE 0 END) AS wed,
  SUM(CASE WHEN strftime('%w', created) = '4' THEN 1 ELSE 0 END) AS thu,
  SUM(CASE WHEN strftime('%w', created) = '5' THEN 1 ELSE 0 END) AS fri,
  SUM(CASE WHEN strftime('%w', created) = '6' THEN 1 ELSE 0 END) AS sat
FROM orders
GROUP BY user_id;

-- pivot with FILTER (cleaner)
SELECT user_id,
  COUNT(*) FILTER (WHERE status='paid')    AS paid,
  COUNT(*) FILTER (WHERE status='pending') AS pending,
  COUNT(*) FILTER (WHERE status='refunded') AS refunded
FROM orders
GROUP BY user_id;
07

Subconsultas

Subconsultas escalares

Una subconsulta escalar devuelve exactamente una fila y una columna; puede aparecer donde una expresión de valor sea válida. Las subconsultas correlacionadas referencian la consulta externa y se re-ejecutan por fila — potentes pero potencialmente lentas en tablas grandes (considera un JOIN o una función de ventana en su lugar). SQLite es bueno optimizando subconsultas correlacionadas simples en joins, pero comprueba siempre EXPLAIN QUERY PLAN en los caminos calientes.

sqlite
-- a subquery returning a single value
SELECT username, age
FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- in SELECT list
SELECT username,
  age,
  age - (SELECT AVG(age) FROM users) AS age_diff
FROM users;

-- in HAVING
SELECT role, AVG(age) AS avg_age
FROM users
GROUP BY role
HAVING AVG(age) > (SELECT AVG(age) FROM users);

-- correlated scalar subquery (re-evaluated per row)
SELECT username,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

Subconsultas IN y NOT IN

Las subconsultas IN son intuitivas pero cuidado con los NULLs: si el resultado de la subconsulta contiene un NULL, NOT IN evalúa a NULL (tratado como falso) para cada fila, devolviendo un resultado vacío. Prefiere NOT EXISTS para los anti-joins con columnas nullable — es seguro con NULLs y a menudo más rápido. El IN multicolombna es conciso para emparejar claves compuestas.

sqlite
-- users who have placed an order
SELECT username FROM users
WHERE id IN (SELECT user_id FROM orders);

-- users who have NOT placed an order
SELECT username FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

-- DANGER: NOT IN with NULLs returns no rows!
-- if the subquery returns any NULL, NOT IN matches nothing.
-- Safe version using NOT EXISTS:
SELECT username FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- multi-column IN
SELECT * FROM products
WHERE (category, price) IN (
  SELECT category, MIN(price) FROM products GROUP BY category
);

EXISTS y NOT EXISTS

EXISTS prueba la existencia de filas sin obtener datos — se detiene en la primera coincidencia, así que es eficiente. NOT EXISTS es la forma segura con NULLs de hallar filas sin coincidencia (a diferencia de NOT IN). Para el mejor rendimiento, asegúrate de que exista un índice en la columna sobre la que hace join la subconsulta correlacionada (orders.user_id en el ejemplo). SELECT 1 es convención; la lista de columnas es irrelevante para EXISTS.

sqlite
-- EXISTS: true if subquery returns any row
SELECT username FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 100
);

-- NOT EXISTS: classic anti-join (NULL-safe)
SELECT username FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- correlated EXISTS — usually efficient with an index on
-- the subquery's join column (orders.user_id here)
SELECT p.name FROM products p
WHERE EXISTS (
  SELECT 1 FROM order_items i
  WHERE i.product_id = p.id AND i.qty > 10
);

Tablas derivadas (subconsulta en FROM)

Una subconsulta en FROM (tabla derivada) es una forma potente de preparar resultados intermedios, pre-agregar o filtrar antes de unir. Las tablas derivadas deben llevar alias. SQLite materializa la subconsulta (la escribe en una tabla temporal) en la mayoría de los casos — para uso repetido, un CTE (cláusula WITH) suele ser más claro y puede referenciarse varias veces. SQLite no permite referencias correlacionadas hacia una subconsulta de FROM.

sqlite
-- treat a query result as a table
SELECT t.role, t.cnt
FROM (
  SELECT role, COUNT(*) AS cnt
  FROM users
  GROUP BY role
) t
WHERE t.cnt > 1;

-- join a derived table
SELECT u.username, t.total_spent
FROM users u
JOIN (
  SELECT user_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY user_id
) t ON t.user_id = u.id;

-- derived tables must be aliased
SELECT * FROM (SELECT 1 AS x) AS sub;

Correlacionada vs no correlacionada

Las subconsultas no correlacionadas corren una vez y pueden cachearse; las correlacionadas se re-ejecutan por fila externa (usa índices para mantenerlas rápidas). SQLite carece de LATERAL/APPLY; para 'unir' un agregado por fila, pre-agrega en una tabla derivada y hazle LEFT JOIN. Cuando una subconsulta correlacionada rinde mal, reescribirla como JOIN con GROUP BY o como función de ventana es el arreglo habitual.

sqlite
-- UNCORRELATED: runs once, result cached
SELECT username FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- CORRELATED: references outer row, re-runs per outer row
SELECT u.username,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS orders
FROM users u;

-- lateral-style correlation in a JOIN subquery is NOT
-- supported directly; use a scalar subquery in SELECT or
-- rewrite as a join with GROUP BY:
SELECT u.username, COALESCE(t.n, 0) AS orders
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) AS n FROM orders GROUP BY user_id
) t ON t.user_id = u.id;
08

Índices

Crear índices

Los índices aceleran las lecturas pero ralentizan las escrituras (cada INSERT/UPDATE/DELETE actualiza la totalidad de los índices). El orden de columnas importa: un índice compuesto sobre (a, b) ayuda a WHERE a=? y WHERE a=? AND b=?, pero NO a WHERE b=? solo (regla del prefijo más a la izquierda). Los índices únicos imponen restricciones. SQLite guarda los índices como B-trees. Elimina los índices sin usar — cuestan espacio y rendimiento de escritura.

sqlite
-- single-column index
CREATE INDEX idx_users_email ON users(email);

-- unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- composite index (column order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- IF NOT EXISTS for idempotent scripts
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- drop an index
DROP INDEX IF EXISTS idx_users_email;

-- list indexes
.indices users
-- or
SELECT name, sql FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'users';

Índices parciales

Los índices parciales indexan solo las filas coincidentes, así que son menores y más rápidos de mantener — ideales cuando las consultas siempre filtran por un predicado estable ('active = 1', 'deleted_at IS NULL'). El WHERE de la consulta debe coincidir con el WHERE del índice (o ser más estricto) para que el optimizador lo use. Los índices parciales únicos imponen unicidad solo en el subconjunto — perfectos para restricciones de 'una fila activa por padre'.

sqlite
-- index only active users (smaller, faster)
CREATE INDEX idx_active_users ON users(username)
WHERE active = 1;

-- index only unpaid orders
CREATE INDEX idx_unpaid_orders ON orders(user_id)
WHERE status = 'unpaid';

-- unique partial index: one active session per user
CREATE UNIQUE INDEX idx_one_active_session
ON sessions(user_id) WHERE active = 1;

-- query must match the WHERE for the index to be used
SELECT * FROM users WHERE active = 1 AND username = 'alice';

Índices de expresión

Los índices de expresión indexan el resultado de una función o expresión, habilitando búsquedas rápidas sobre valores transformados — pero la consulta debe usar exactamente la misma expresión. Usos comunes: búsqueda sin distinguir mayúsculas (LOWER), agrupación por fechas (strftime) y extracción de campos JSON (json_extract). Solo funciones deterministas (sin RANDOM() ni NOW()). Los índices de expresión son potentes pero complican los cambios de esquema.

sqlite
-- index a function result (must match the query expression exactly)
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- query that can use it (must use the same expression)
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- index a date extraction for fast monthly queries
CREATE INDEX idx_orders_month ON orders(strftime('%Y-%m', created));
SELECT * FROM orders WHERE strftime('%Y-%m', created) = '2024-06';

-- index a JSON path
CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'));
SELECT * FROM events WHERE json_extract(data, '$.type') = 'login';

-- indexed computed value (collation)
CREATE INDEX idx_users_name_ci ON users(username COLLATE NOCASE);

Índices cubrientes y WITHOUT ROWID

Un índice cubriente incluye cada columna que lee una consulta, así que SQLite satisface la consulta solo desde el índice (sin búsqueda en la tabla). Las tablas WITHOUT ROWID hacen de la PRIMARY KEY un índice agrupado — las filas se guardan ordenadas por clave, ideal para cargas clave/valor y escaneos de rango sobre la PK. Contrapartidas: sin AUTOINCREMENT, sin alias de rowid, actualizaciones algo más complejas. Haz benchmark antes de adoptarlo ampliamente.

sqlite
-- covering index: includes all columns the query needs,
-- so SQLite never reads the table
CREATE INDEX idx_orders_cover ON orders(user_id, status, total);
SELECT user_id, status, total FROM orders WHERE user_id = 1;
-- ^ fully served by the index

-- WITHOUT ROWID tables: store rows IN the index (clustered)
CREATE TABLE kv (
  key   TEXT PRIMARY KEY,
  value TEXT
) WITHOUT ROWID;

-- the PK becomes the table itself; lookups by key are fast
SELECT value FROM kv WHERE key = 'config:theme';

-- good for key/value lookups and read-heavy tables
-- trade-off: no rowid, some features unsupported

Inspección de índices

EXPLAIN QUERY PLAN es la herramienta de ajuste esencial — 'USING INDEX' significa que el índice se usa; 'SCAN' (escaneo completo de tabla) suele ser malo en tablas grandes. sqlite_stat1 (poblada por ANALYZE) guarda las estadísticas de cardinalidad que usa el planificador para elegir índices. REINDEX reconstruye un índice hinchado o corrupto sin reconstruir la tabla. Corre ANALYZE tras cargas de datos grandes para que el planificador tenga estadísticas frescas para elegir buenos planes.

sqlite
-- see if an index is used
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = '[email protected]';
-- look for 'SEARCH users USING INDEX idx_users_email'

-- list all indexes on a table with their definitions
SELECT name, sql FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'users';

-- index stats (how often each index is used)
SELECT name, idx_scan, idx_read
FROM sqlite_stat1, sqlite_master
WHERE sqlite_stat1.tbl = 'users';

-- rebuild an index (after corruption or bloat)
REINDEX idx_users_email;
REINDEX;  -- rebuild all indexes

-- analyze tables to update query planner stats
ANALYZE;
09

Vistas

Crear vistas

Las vistas son tablas virtuales definidas por una consulta guardada — no guardan datos (salvo vistas temp), re-ejecutan el SELECT subyacente cada vez. Úsalas para simplificar consultas complejas, imponer patrones de acceso consistentes o abstraer cambios de esquema de las aplicaciones. Las vistas son de solo lectura por defecto en SQLite (con restricciones en las vistas actualizables). DROP VIEW nunca afecta a las tablas subyacentes.

sqlite
-- a view is a saved SELECT, queried like a table
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE active = 1;

SELECT * FROM active_users WHERE age > 18;

-- view with calculated columns
CREATE VIEW user_summary AS
SELECT u.id, u.username,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

-- IF NOT EXISTS for idempotent scripts
CREATE VIEW IF NOT EXISTS active_users AS
SELECT id, username FROM users WHERE active = 1;

-- drop a view
DROP VIEW IF EXISTS active_users;

Vistas actualizables

SQLite permite INSERT/UPDATE/DELETE sobre vistas simples (una sola tabla base, sin JOIN/agregados/DISTINCT/GROUP BY). La operación se reenvía a la tabla subyacente. SQLite NO soporta WITH CHECK OPTION, así que una fila insertada a través de una vista puede no satisfacer el WHERE de la vista y por tanto quedar invisible a través de ella después. Para vistas complejas que necesiten escrituras, usa triggers INSTEAD OF.

sqlite
-- simple views (one base table, no aggregates/distinct/etc.)
-- are auto-updatable in SQLite
CREATE VIEW active_users AS
SELECT id, username, email, age FROM users WHERE active = 1;

-- INSERT through the view (active defaults/applies)
INSERT INTO active_users (id, username, email) VALUES (1, 'x', '[email protected]');

-- UPDATE through the view
UPDATE active_users SET age = 30 WHERE id = 1;

-- DELETE through the view
DELETE FROM active_users WHERE id = 1;

-- the WITH CHECK OPTION is NOT supported in SQLite, so
-- inserts/updates that don't match the view's WHERE still
-- land in the base table but vanish from the view.

Triggers INSTEAD OF sobre vistas

Los triggers INSTEAD OF interceptan las escrituras a una vista y corren tu lógica personalizada, haciendo actualizable cualquier vista. Es la forma estándar de hacer escribibles vistas complejas (multitabla, agregadas). NEW y OLD se refieren a las filas entrante y existente. Los triggers INSTEAD OF solo se disparan sobre vistas, no sobre tablas base. Úsalos para implementar lógica de negocio encapsulada o para ocultar un esquema normalizado tras una vista desnormalizada.

sqlite
-- a view joining multiple tables is not auto-updatable,
-- but INSTEAD OF triggers let you define the write behavior
CREATE VIEW user_orders AS
SELECT u.id AS user_id, u.username, o.id AS order_id, o.total
FROM users u LEFT JOIN orders o ON o.user_id = u.id;

-- route INSERTs on the view to the orders table
CREATE TRIGGER trg_user_orders_insert
INSTEAD OF INSERT ON user_orders
FOR EACH ROW
BEGIN
  INSERT INTO orders (id, user_id, total)
  VALUES (NEW.order_id, NEW.user_id, NEW.total);
END;

-- now this works:
INSERT INTO user_orders (user_id, order_id, total)
VALUES (1, 100, 49.99);

Vistas temporales

Las vistas temporales viven solo para la conexión actual a la base de datos y se eliminan automáticamente al desconectarse — perfectas para preparar consultas ad-hoc complejas sin saturar el esquema compartido. Se guardan en un esquema temp aparte, así que otras conexiones (incluso al mismo archivo) no las ven. Las vistas TEMP pueden referenciar tablas TEMP, lo que es útil para pipelines de ETL con ámbito de sesión.

sqlite
-- TEMP view exists only for the current connection
CREATE TEMP VIEW recent_orders AS
SELECT * FROM orders WHERE created > datetime('now', '-7 days');

-- the view vanishes when the connection closes
-- (no other connection can see it)

-- also valid: CREATE TEMPORARY VIEW
CREATE TEMPORARY VIEW my_scratch AS
SELECT id, username FROM users WHERE role = 'admin';

-- use case: ad-hoc analysis without polluting the schema
SELECT * FROM recent_orders WHERE total > 100;

-- list views
SELECT name, sql FROM sqlite_master
WHERE type = 'view';
SELECT name, sql FROM temp.sqlite_master
WHERE type = 'view';  -- temp views

Vistas materializadas (emuladas)

SQLite no tiene vista materializada nativa, pero una tabla regular refrescada con programación logra lo mismo. Para un refresco atómico, construye una tabla nueva en una transacción y renómbrala — los lectores ven la versión antigua hasta el commit y luego la nueva. Añade los índices tras la reconstrucción. Las vistas materializadas intercambian coste de escritura/almacenamiento por velocidad de lectura — ideales para agregados caros consultados con frecuencia.

sqlite
-- SQLite has no native materialized views; emulate with a
-- real table that you refresh periodically

-- 1) create the 'materialized' table
CREATE TABLE mv_user_summary AS
SELECT u.id, u.username,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

-- 2) add an index for fast lookups
CREATE INDEX idx_mv_user_summary_id ON mv_user_summary(id);

-- 3) refresh (full rebuild)
DELETE FROM mv_user_summary;
INSERT INTO mv_user_summary
SELECT u.id, u.username, COUNT(o.id), COALESCE(SUM(o.total), 0)
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

-- 4) refresh atomically with a name swap
BEGIN;
CREATE TABLE mv_user_summary_new AS SELECT ...;
DROP TABLE mv_user_summary;
ALTER TABLE mv_user_summary_new RENAME TO mv_user_summary;
COMMIT;
10

Triggers

Triggers BEFORE / AFTER

Los triggers BEFORE corren antes de escribirse la fila — úsalos para validar (RAISE ABORT) o transformar los valores NEW. Los AFTER corren una vez confirmada la fila — úsalos para auditoría, efectos en cascada o desnormalización. FOR EACH ROW es la única opción (no hay triggers a nivel de sentencia). NEW guarda la fila entrante (INSERT/UPDATE); OLD la fila previa (UPDATE/DELETE). La cláusula WHEN dispara el trigger condicionalmente.

sqlite
-- AFTER INSERT: audit log
CREATE TRIGGER trg_users_audit
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO users_audit (user_id, action, at)
  VALUES (NEW.id, 'insert', datetime('now'));
END;

-- BEFORE INSERT: validate / normalize data
CREATE TRIGGER trg_users_lower_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SELECT RAISE(ABORT, 'email cannot be empty')
  WHERE NEW.email = '';
END;

-- AFTER UPDATE: log changes
CREATE TRIGGER trg_users_update_log
AFTER UPDATE ON users
FOR EACH ROW
WHEN OLD.email IS NOT NEW.email
BEGIN
  INSERT INTO email_changes (user_id, old_email, new_email, at)
  VALUES (NEW.id, OLD.email, NEW.email, datetime('now'));
END;

Triggers para efectos en cascada

Los triggers pueden mantener automáticamente contadores desnormalizados, tablas de resumen y marcas de tiempo updated_at. Cuidado: las cadenas de triggers recursivos son posibles (un trigger actualizando su propia tabla), así que usa PRAGMA recursive_triggers para controlarlo (OFF por defecto por compatibilidad hacia atrás). El orden de los triggers para varios triggers sobre el mismo evento sigue el orden de creación; no hay prioridad BEFORE/AFTER entre triggers del mismo momento.

sqlite
-- maintain a denormalized counter
CREATE TRIGGER trg_orders_count_inc
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  UPDATE users
  SET order_count = order_count + 1
  WHERE id = NEW.user_id;
END;

CREATE TRIGGER trg_orders_count_dec
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
  UPDATE users
  SET order_count = order_count - 1
  WHERE id = OLD.user_id;
END;

-- keep an 'updated_at' column fresh
CREATE TRIGGER trg_users_touch
AFTER UPDATE ON users
FOR EACH ROW
WHEN NEW.updated_at IS OLD.updated_at
BEGIN
  UPDATE users SET updated_at = datetime('now') WHERE id = NEW.id;
END;

Triggers INSTEAD OF (vistas)

Los triggers INSTEAD OF corren en lugar del INSERT/UPDATE/DELETE original sobre una vista, dejándote enrutar las escrituras a una o más tablas subyacentes. Es la forma canónica de hacer escribibles vistas complejas. Se disparan por fila de la vista. Solo los triggers INSTEAD OF pueden definirse sobre vistas; los BEFORE/AFTER exigen tablas base. Útil para capas de abstracción estilo API sobre un esquema normalizado.

sqlite
-- make a multi-table view writable
CREATE VIEW order_detail AS
SELECT o.id AS order_id, o.total, u.username, i.product_name
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items i ON i.order_id = o.id;

CREATE TRIGGER trg_order_detail_update
INSTEAD OF UPDATE ON order_detail
FOR EACH ROW
BEGIN
  UPDATE orders SET total = NEW.total WHERE id = NEW.order_id;
  UPDATE order_items SET product_name = NEW.product_name
  WHERE order_id = NEW.order_id;
END;

-- now UPDATE on the view routes to both tables
UPDATE order_detail SET total = 99.99, product_name = 'Widget'
WHERE order_id = 5;

RAISE y manejo de errores

RAISE controla cómo se propaga el fallo de un trigger: ABORT (el más común) cancela la sentencia y revierte sus cambios, ROLLBACK revierte toda la transacción, FAIL aborta la sentencia pero conserva las sentencias exitosas previas en la transacción, e IGNORE se salta solo la fila ofensora. RAISE(IGNORE) es una forma elegante de descartar silenciosamente filas inválidas durante cargas masivas.

sqlite
-- RAISE aborts the statement with a custom error
CREATE TRIGGER trg_check_age
BEFORE INSERT ON users
FOR EACH ROW
WHEN NEW.age < 0
BEGIN
  SELECT RAISE(ABORT, 'age must be non-negative');
END;

-- ROLLBACK: undo the whole transaction, statement fails
CREATE TRIGGER trg_rollback_example
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN NEW.balance < 0
BEGIN
  SELECT RAISE(ROLLBACK, 'balance cannot go negative');
END;

-- FAIL: abort the current statement only (like ABORT)
-- IGNORE: skip this row, continue with the rest
CREATE TRIGGER trg_skip_invalid
BEFORE INSERT ON logs
FOR EACH ROW
WHEN NEW.level NOT IN ('info','warn','error')
BEGIN
  SELECT RAISE(IGNORE);
END;

Gestionar triggers

Los triggers se guardan en sqlite_master (o temp.sqlite_master para triggers TEMP). DROP TRIGGER los elimina; no hay ALTER TRIGGER — elimina y recrea. Los triggers TEMP son locales a la conexión y desaparecen al desconectarse, útiles para depurar. No hay forma integrada de deshabilitar temporalmente un trigger salvo eliminarlo; para la ejecución condicional, usa la cláusula WHEN. Varios triggers sobre el mismo evento se disparan en orden de creación dentro de su momento.

sqlite
-- list all triggers
SELECT name, tbl_name, sql FROM sqlite_master
WHERE type = 'trigger';

-- list triggers on a specific table
SELECT name, sql FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'users';

-- drop a trigger
DROP TRIGGER IF EXISTS trg_users_audit;

-- triggers in a TEMP schema (connection-local)
CREATE TEMP TRIGGER trg_temp_log
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  SELECT 'inserted ' || NEW.id;
END;

-- trigger timing order: BEFORE triggers fire in
-- creation order, then AFTER triggers in creation order.
11

Ajustes PRAGMA

Fundamentos de PRAGMA

PRAGMA es la interfaz de configuración propietaria de SQLite — los ajustes controlan el journaling, la caché, las claves foráneas, la integridad y el planificador de consultas. Los ajustes tienen ámbito de sesión (por conexión) salvo que la documentación diga lo contrario; fijarlos dentro de una transacción puede ignorarse. Algunos PRAGMA (journal_mode, page_size) solo surten efecto fuera de transacciones o antes de crear la tabla. Usa PRAGMA compile_options para ver qué soporta tu compilación.

sqlite
-- PRAGMA is SQLite's configuration knob (CLI and APIs)
-- read a setting
PRAGMA journal_mode;
PRAGMA cache_size;
PRAGMA foreign_keys;

-- set a setting (session scope unless noted)
PRAGMA foreign_keys = ON;
PRAGMA cache_size = -20000;   -- negative = KB, positive = pages
PRAGMA temp_store = MEMORY;

-- many PRAGMAs are no-ops below a certain SQLite version
-- check the compile-time options
PRAGMA compile_options;
-- e.g. ENABLE_FTS5, ENABLE_JSON1, MAX_ATTACHED, etc.

-- PRAGMAs are NOT part of SQL standard and are SQLite-only.

Claves foráneas

La imposición de claves foráneas está OFF por defecto en SQLite — una trampa notoria para quienes vienen de otras bases de datos. Debes habilitarla en cada conexión con PRAGMA foreign_keys = ON; no puede fijarse dentro de una transacción. Este ajuste existe porque los esquemas antiguos (previos al soporte de FK) se romperían de otro modo. Habilítalo siempre en producción. defer_foreign_keys ayuda al importar datos en orden que viola dependencias.

sqlite
-- foreign keys are OFF by default in SQLite!
PRAGMA foreign_keys = ON;   -- enable per connection

-- check current state
PRAGMA foreign_keys;

-- inspect FK definitions on a table
PRAGMA foreign_key_list(orders);
-- returns: id, seq, table, from, to, on_update, on_delete, match

-- enable FKs in your app's connection setup, every time
-- (this is a frequent source of bugs when moving from MySQL/PG)

-- deferred FK checking (checked at COMMIT, not per statement)
PRAGMA defer_foreign_keys = ON;
-- or declare DEFERRABLE INITIALLY DEFERRED on the FK

Modo journal y WAL

WAL (Write-Ahead Logging) es el modo de journal recomendado para la mayoría de apps: lectores y escritores no se bloquean mutuamente, y la durabilidad es buena. Crea archivos secundarios -wal y -shm junto a la base de datos principal. WAL es persistente por archivo de base de datos (fíjalo una vez). Usa wal_checkpoint(TRUNCATE) para plegar el contenido del WAL de vuelta al archivo principal y recuperar espacio. Evita WAL en sistemas de archivos de red — exige memoria compartida.

sqlite
-- WAL: write-ahead logging (best for most apps)
PRAGMA journal_mode = WAL;
-- benefits: readers never block writers, writers never block
--           readers, crash recovery is fast
-- trade-off: creates -wal and -shm sidecar files

-- other modes: DELETE (default), TRUNCATE, PERSIST, MEMORY, OFF
PRAGMA journal_mode = DELETE;

-- WAL checkpoint: merge -wal back into the main file
PRAGMA wal_checkpoint;
PRAGMA wal_checkpoint(TRUNCATE);  -- also shrinks the -wal file

-- auto-checkpoint threshold (pages of WAL before auto-checkpoint)
PRAGMA wal_autocheckpoint = 1000;

-- WAL is persistent: setting it once keeps it for that DB file.

Synchronous y durabilidad

synchronous controla con qué agresividad llama SQLite a fsync. FULL es lo más seguro (cada commit hace fsync); NORMAL es el valor recomendado al usar WAL (fsync en el checkpoint, no por commit, con riesgo de corrupción insignificante en la práctica); OFF arriesga corrupción de la base ante pérdida de energía — solo para datos realmente desechables o cachés reconstruibles. page_size debería fijarse antes de crear el esquema; cambiarlo después exige VACUUM.

sqlite
-- how thoroughly SQLite flushes to disk on commit
PRAGMA synchronous;        -- read current value
PRAGMA synchronous = FULL;       -- safest, slowest (default for rollback journal)
PRAGMA synchronous = NORMAL;     -- safe with WAL, fast (recommended for WAL)
PRAGMA synchronous = OFF;        -- fastest; corruption risk on power loss
PRAGMA synchronous = EXTRA;      -- even more careful than FULL

-- trade-off: durability vs speed
-- FULL: fsync at every commit (survives OS crash)
-- NORMAL (WAL): fsync at checkpoint, not every commit
-- OFF: no fsync — fast but may corrupt on power loss

-- page size (set BEFORE creating tables, ideally)
PRAGMA page_size = 4096;
PRAGMA page_size;

Caché, memoria y temp store

cache_size es la caché de páginas en memoria — mayor suele ser mejor para cargas de muchas lecturas; los valores negativos van en KB y los positivos en páginas. temp_store = MEMORY mueve los resultados intermedios (ordenaciones, tablas temp) a RAM, acelerando las agregaciones grandes. mmap_size habilita I/O mapeada en memoria para lecturas, lo que puede acelerar las bases de datos grandes de solo lectura. Ajusta estos según el presupuesto de memoria y el patrón de acceso de tu app.

sqlite
-- page cache (negative = KB, positive = pages)
PRAGMA cache_size = -65536;   -- 64 MB cache
PRAGMA cache_size = 20000;    -- 20000 pages

-- where temp tables and intermediate results live
PRAGMA temp_store = DEFAULT;  -- compile-time default
PRAGMA temp_store = FILE;     -- temp files
PRAGMA temp_store = MEMORY;   -- all temp data in RAM

-- mmap memory-mapped I/O for reads
PRAGMA mmap_size = 268435456;  -- 256 MB

-- soft heap limit for the SQLite library
PRAGMA soft_heap_limit = 100000000;  -- 100 MB

-- store prepared statements in memory between calls
PRAGMA cache_spill;

Integridad e información de esquema

integrity_check valida toda la estructura y contenido de la base de datos (lento pero minucioso); quick_check se salta la validación de contenido y es mucho más rápido. Corre integrity_check durante los respaldos o tras caídas. table_info reporta los metadatos de columnas (usa table_xinfo para columnas ocultas como el rowid de FTS5). index_list e index_info revelan qué índices existen y sus columnas. database_list muestra las bases de datos adjuntas.

sqlite
-- full integrity check (slow on large DBs)
PRAGMA integrity_check;
-- returns 'ok' or a list of problems

-- quick check (faster, less thorough)
PRAGMA quick_check;

-- list tables / schema
SELECT name, sql FROM sqlite_master WHERE type = 'table';

-- table info: columns, types, notnull, default, pk
PRAGMA table_info(users);
-- returns: cid, name, type, notnull, dflt_value, pk

-- extended table info (includes hidden columns from FTS/etc.)
PRAGMA table_xinfo(users);

-- index list with origin and unique flag
PRAGMA index_list(users);
PRAGMA index_info(idx_users_email);

-- database file header info
PRAGMA database_list;
12

Funciones de fecha y hora

date / time / datetime

SQLite no tiene tipo DATE nativo; las fechas son TEXT en ISO-8601 ('YYYY-MM-DD HH:MM:SS'). Las funciones de fecha/hora aceptan muchos formatos de entrada (ISO, barras, nombres de mes, marcas de tiempo Unix vía el modificador 'unixepoch'). 'now' se evalúa una vez por sentencia, no por fila — cada fila de un UPDATE multiflea recibe la misma marca de tiempo, que suele ser lo que quieres. Guarda en UTC y formatea para mostrar con localtime.

sqlite
-- current date/time (UTC by default)
SELECT date('now');              -- '2024-06-15'
SELECT time('now');              -- '14:30:00'
SELECT datetime('now');          -- '2024-06-15 14:30:00'
SELECT julianday('now');         -- 2460476.1042 (days since 4713 BC)

-- parse various formats
SELECT date('2024-06-15');
SELECT date('2024/06/15');
SELECT date('June 15, 2024');
SELECT datetime('2024-06-15 14:30:00.123');
SELECT date('2024-06-15','unixepoch');  -- from Unix timestamp (seconds)

-- Unix timestamp to datetime
SELECT datetime(1718450400, 'unixepoch');
SELECT datetime(1718450400, 'unixepoch', 'localtime');

Formateo con strftime

strftime es el formateador de fechas universal — los códigos de formato reflejan el strftime de C. Úsalo para extraer partes de una fecha (año, mes, día de la semana) para agrupar o para renderizar un formato de visualización personalizado. Para agrupar por mes, strftime('%Y-%m', created) produce 'YYYY-MM' que ordena y agrupa naturalmente. SQLite no tiene DAYNAME()/MONTHNAME(); combina strftime('%w', ...) con un CASE si necesitas nombres de días.

sqlite
-- strftime is the most flexible formatter
--   strftime(format, timestring, modifier, modifier, ...)
SELECT strftime('%Y-%m-%d', 'now');          -- '2024-06-15'
SELECT strftime('%Y-%m-%d %H:%M', 'now');    -- '2024-06-15 14:30'
SELECT strftime('%H:%M:%S', 'now');          -- '14:30:00'

-- common format codes
--   %Y year (4-digit)    %m month (01-12)   %d day (01-31)
--   %H hour (00-23)      %M minute (00-59)  %S second (00-59)
--   %j day of year       %w day of week (0=Sun)
--   %W week of year      %p AM/PM           %I hour (01-12)

-- get the day name
SELECT strftime('%w', '2024-06-15');   -- '6' (Saturday)
SELECT strftime('%Y-%m', 'now');       -- month bucket for grouping

Modificadores (aritmética)

Los modificadores se encadenan de izquierda a derecha para desplazar o alinear fechas. Los intervalos son '+N unidades' / '-N unidades' (unidades: days, hours, minutes, seconds, months, years). 'start of month/year/day' ajusta al límite del periodo — perfecto para reportes mensuales. 'weekday N' devuelve la fecha del próximo día N de la semana (hoy si hoy coincide). Combinar 'start of month', '+1 month', '-1 day' da el último día del mes actual — un modismo clásico.

sqlite
-- shift a date by an interval
SELECT date('now', '+1 day');           -- tomorrow
SELECT date('now', '-1 month');         -- one month ago
SELECT date('now', '+1 year', '+1 day');-- next year + 1 day
SELECT datetime('now', '+2 hours', '+30 minutes');

-- start of period
SELECT date('now', 'start of month');       -- first day of month
SELECT date('now', 'start of year');        -- first day of year
SELECT date('now', 'start of day');         -- midnight today

-- weekday: next occurrence of a given weekday (0=Sun, 1=Mon, ... 6=Sat)
SELECT date('now', 'weekday 1');            -- next Monday
SELECT date('now', 'weekday 0', '+7 days'); -- Sunday after this one

-- combine modifiers
SELECT date('now', 'start of month', '+1 month', '-1 day'); -- last day of month

Tiempo Unix y día juliano

Las marcas de tiempo Unix son enteros/segundos desde la época; unixepoch() (3.38+) devuelve un entero directamente. El día juliano es un conteo continuo y fraccional de días — restar dos valores julianday da el tiempo transcurrido en días (multiplica por 86400 para segundos). Para cálculos de edad, dividir entre 365.25 compensa los años bisiestos aproximadamente; para precisión legal, compara explícitamente los componentes de año/mes/día.

sqlite
-- Unix timestamp (seconds since 1970-01-01 UTC)
SELECT strftime('%s', 'now');                  -- current Unix time (text)
SELECT unixepoch('now');                       -- integer Unix time (SQLite 3.38+)
SELECT datetime(1718450400, 'unixepoch');      -- back to datetime

-- Julian day (fractional days since 4713-01-01 BC)
SELECT julianday('now');                       -- 2460476.6042
SELECT julianday('2024-06-15') - julianday('2024-06-10');  -- 5.0 (days between)

-- compute elapsed time in seconds
SELECT (julianday('now') - julianday('2024-01-01')) * 86400.0 AS seconds_elapsed;

-- human-readable age from a birthdate
SELECT CAST((julianday('now') - julianday('1990-05-20')) / 365.25 AS INT) AS age;

Zonas horarias y localtime

SQLite no tiene base de datos de zonas horarias — 'now' es UTC, y los modificadores 'localtime'/'utc' confían en el ajuste de zona horaria del sistema operativo anfitrión. El patrón recomendado: guarda siempre las marcas de tiempo en UTC (datetime('now') es UTC) y convierte a hora local solo al mostrar con el modificador 'localtime'. Esto evita ambigüedad entre servidores en zonas distintas y durante las transiciones de horario de verano. Nunca guardes hora local.

sqlite
-- 'now' is always UTC; convert to local time for display
SELECT datetime('now', 'localtime');          -- local wall-clock time
SELECT datetime('now');                       -- UTC

-- convert a stored UTC timestamp to local time
SELECT datetime(created, 'localtime') FROM users;

-- convert local time back to UTC for storage
SELECT datetime('2024-06-15 14:30:00', 'utc');

-- best practice: store UTC, format for display
CREATE TABLE events (ts TEXT DEFAULT (datetime('now')));  -- UTC
-- display:
SELECT datetime(ts, 'localtime') AS local_ts FROM events;

-- timezone is determined by the OS; SQLite has no TZ database.
13

Funciones de cadena

Longitud y subcadena

length() cuenta caracteres para TEXT y bytes para BLOB — para longitud en bytes UTF-8, castea primero a BLOB. substr() se indexa desde 1; un inicio negativo cuenta desde el final. SQLite no tiene LPAD/RPAD nativos — emúlalos con concatenación de cadenas y substr. Cuidado con la confusión byte-carácter al truncar texto multibyte: substr opera sobre caracteres, así que es seguro, pero el slicing a nivel de bytes no lo es.

sqlite
SELECT length('hello');              -- 5
SELECT length('日本語');             -- 3 (characters, not bytes)
SELECT length(x'00ff');             -- 2 (blob length in bytes)

SELECT substr('hello world', 7);    -- 'world' (1-indexed)
SELECT substr('hello world', 1, 5); -- 'hello' (length 5)
SELECT substr('hello', -3);         -- 'llo' (negative = from end)
SELECT substr('hello', -3, 2);      -- 'll'

-- byte length (vs character length)
SELECT length('日本語');            -- 3 (characters)
SELECT length(CAST('日本語' AS BLOB)); -- 9 (UTF-8 bytes)

-- left/right pad (workaround; no native LPAD/RPAD)
SELECT substr('0000' || '42', -4, 4);  -- '0042'

Mayúsculas y recorte

upper()/lower() solo afectan a las letras ASCII por defecto — los caracteres no ASCII quedan sin cambios salvo que se cargue ICU. trim() quita el espacio en blanco inicial/final por defecto, o caracteres concretos dados como segundo argumento. replace() hace reemplazo literal de subcadenas (sin regex ni patrones). No hay TRANSLATE() nativa para sustitución de conjuntos de caracteres — encadena llamadas replace() o usa una función personalizada.

sqlite
SELECT upper('hello');       -- 'HELLO'
SELECT lower('HELLO');       -- 'hello'
SELECT upper('日本語');      -- '日本語' (no case to change)

SELECT trim('  hi  ');            -- 'hi'  (both sides)
SELECT ltrim('  hi  ');           -- 'hi   ' (left only)
SELECT rtrim('  hi  ');           -- '  hi' (right only)

-- trim specific characters
SELECT trim('xxhelloxx', 'x');    -- 'hello'
SELECT ltrim('aaabbb', 'a');      -- 'bbb'

-- replace characters
SELECT replace('a-b-c', '-', '/'); -- 'a/b/c'
SELECT replace('Hello World', 'o', '0'); -- 'Hell0 W0rld'

Concatenación y printf

El operador || es el concatenador estándar de SQL, pero propaga NULL (NULL || x = NULL) — envuélvelo con COALESCE cuando haya NULLs posibles. printf()/format() trae el formateo estilo C y es la forma más limpia de construir cadenas con relleno, formateo de números y separadores de miles. quote() devuelve una cadena con las comillas y escapes seguros para incrustarla en SQL — útil al construir SQL dinámico.

sqlite
-- || operator concatenates
SELECT 'Hello' || ' ' || 'World';      -- 'Hello World'
SELECT username || ' <' || email || '>' FROM users;

-- NULL propagates through || (NULL || 'x' IS NULL)
SELECT 'a' || NULL || 'b';             -- NULL
-- use COALESCE to handle NULLs:
SELECT COALESCE(username, '') || ' <' || COALESCE(email, '') || '>';

-- printf-style formatting (also called format())
SELECT printf('%s has %d orders', 'alice', 5);  -- 'alice has 5 orders'
SELECT format('%,d', 1234567);                   -- '1,234,567' (3.38+)
SELECT printf('%5.2f', 3.14159);                 -- ' 3.14'
SELECT printf('%04d', 42);                       -- '0042'

-- quote() escapes a string for SQL
SELECT quote("It's alive");   -- '''It''s alive'''

División y búsqueda

instr() halla la primera ocurrencia de una subcadena (indexado desde 1; 0 si no se halla). SQLite no tiene SPLIT_PART nativo — emúlalo con substr + instr o un CTE recursivo. El modismo de 'contar ocurrencias' resta las longitudes antes/después de quitar el delimitador. char() construye una cadena desde puntos de código; unicode() devuelve el punto de código del primer carácter. Para dividir texto completo, carga un array JSON vía json_each.

sqlite
-- find position of substring (1-indexed; 0 if not found)
SELECT instr('hello world', 'world');   -- 7
SELECT instr('hello', 'xyz');           -- 0

-- emulate split by getting the Nth field
-- (no native SPLIT_PART; use a CTE or substring + instr)
SELECT
  substr('a,b,c,d', 1, instr('a,b,c,d', ',') - 1) AS first;  -- 'a'

-- count occurrences of a substring
SELECT (length('a,b,c,d') - length(replace('a,b,c,d', ',', ''))) / length(',') AS commas;
-- 3

-- reverse a string
SELECT reverse('hello');   -- 'olleh'

-- character from integer code
SELECT char(65, 66, 67);   -- 'ABC'
SELECT unicode('A');       -- 65

LIKE / GLOB / colaciones

LIKE no distingue mayúsculas para ASCII por defecto (una sorpresa frecuente); usa COLLATE BINARY para distinguirlas. GLOB siempre distingue mayúsculas y soporta clases de caracteres estilo shell. NOCASE es una colación integrada para comparación sin distinguir mayúsculas; aplícala al índice de una columna para que las consultas sin distinguir mayúsculas usen el índice. Para el plegado de mayúsculas no ASCII, carga la extensión ICU o guarda una columna copia en minúsculas con índice.

sqlite
-- LIKE: case-insensitive (ASCII), wildcards % and _
SELECT * FROM users WHERE username LIKE 'al%';
SELECT * FROM users WHERE username LIKE '_lice';

-- case-sensitive LIKE
SELECT * FROM users WHERE username LIKE 'Al%' COLLATE BINARY;

-- make LIKE case-insensitive for non-ASCII too (needs ICU)
-- or store a lowercased copy and query that
SELECT * FROM users WHERE LOWER(username) = LOWER('Alice');

-- GLOB: case-sensitive, Unix-style (* ? [abc] [a-z])
SELECT * FROM users WHERE username GLOB 'A*';
SELECT * FROM users WHERE username GLOB '[A-D]*';

-- built-in collations: BINARY (default), NOCASE, RTRIM
CREATE INDEX idx_users_name_ci ON users(username COLLATE NOCASE);
SELECT * FROM users WHERE username = 'ALICE' COLLATE NOCASE;

-- COLLATE in ORDER BY
SELECT * FROM users ORDER BY username COLLATE NOCASE;
14

Funciones JSON

Extraer valores

json_extract (y los operadores -> / ->>, 3.38+) son los caballos de batalla para leer JSON. $ es la raíz; $.clave accede a una propiedad de objeto; $.arr[i] accede a un elemento de array (indexado desde 0). ->> devuelve un escalar (texto/entero/real), -> devuelve JSON (así '$.b' sobre un array da el array como JSON). Guarda los campos JSON consultados con frecuencia como columnas regulares, o añade un índice de expresión sobre json_extract por rendimiento.

sqlite
-- json_extract: get a value by JSON path
SELECT json_extract('{"a": 1, "b": [10, 20, 30]}', '$.a');       -- 1
SELECT json_extract('{"a": 1, "b": [10, 20, 30]}', '$.b[1]');    -- 20
SELECT json_extract('{"a": 1, "b": [10, 20, 30]}', '$.b');       -- '[10,20,30]'

-- -> returns JSON; ->> returns scalar (text/integer/etc.)
SELECT '{"a": 1, "b": "x"}' -> '$.a';   -- 1 (as JSON)
SELECT '{"a": 1, "b": "x"}' ->> '$.a';  -- 1 (as scalar)
SELECT '{"a": 1, "b": "x"}' ->> '$.b';  -- 'x' (as text)

-- nested paths
SELECT json_extract('{"u": {"name": "alice"}}', '$.u.name');   -- 'alice'

-- array length
SELECT json_array_length('[1, 2, 3]');  -- 3
SELECT json_array_length('{"a": [1,2]}', '$.a');  -- 2

-- get object keys
SELECT json_each.name FROM json_each('{"a":1,"b":2}');  -- 'a', 'b'

Construir JSON

json_object y json_array construyen JSON desde valores SQL (los NULLs se vuelven null de JSON, no se omiten). json_group_array y json_group_object (3.38+) son funciones de agregación que construyen JSON desde filas de consulta — inestimables para producir APIs JSON directamente desde SQL. Anida los constructores para construir documentos complejos. Usa json_quote para incrustar con seguridad una cadena como literal de cadena JSON.

sqlite
-- build a JSON object
SELECT json_object('id', 1, 'name', 'alice', 'active', 1);
-- {"id":1,"name":"alice","active":1}

-- build a JSON array
SELECT json_array(1, 2, 3, 'four', NULL);
-- [1,2,3,"four",null]

-- nest them
SELECT json_object(
  'user', json_object('id', 1, 'name', 'alice'),
  'roles', json_array('admin', 'editor')
);

-- aggregate rows into a JSON array
SELECT json_group_array(username) FROM users WHERE active = 1;
-- ["alice","bob","carol"]

-- aggregate into a JSON object (key:value)
SELECT json_group_object(username, email) FROM users WHERE active = 1;
-- {"alice":"[email protected]","bob":"[email protected]"}

-- quote a string as JSON
SELECT json_quote('hello');   -- '"hello"'

Modificar JSON

json_set/insert/replace distinguen el comportamiento de insertar-vs-actualizar; json_remove elimina rutas. Estas devuelven valores JSON NUEVOS — el JSON de SQLite es inmutable, así que para persistir los cambios haces UPDATE de la columna con el resultado: UPDATE t SET data = json_set(data, '$.x', 1) WHERE id = 5. json_patch (RFC 7396) fusiona objetos; los valores null en el parche eliminan claves. Para documentos grandes, considera guardar los campos calientes como columnas separadas.

sqlite
-- json_set: insert or update a path
SELECT json_set('{"a": 1}', '$.b', 2);
-- {"a":1,"b":2}
SELECT json_set('{"a": 1}', '$.a', 99);
-- {"a":99}

-- json_insert: insert only if path does NOT exist
SELECT json_insert('{"a": 1}', '$.a', 99, '$.b', 2);
-- {"a":1,"b":2}  (a unchanged because it exists)

-- json_replace: update only if path EXISTS
SELECT json_replace('{"a": 1}', '$.a', 99, '$.b', 2);
-- {"a":99}  (b not added because it doesn't exist)

-- json_remove: delete a path
SELECT json_remove('{"a": 1, "b": 2}', '$.b');
-- {"a":1}

-- json_patch: merge per RFC 7396
SELECT json_patch('{"a": 1, "b": 2}', '{"b": 3, "c": 4}');
-- {"a":1,"b":3,"c":4}

Consultar arrays JSON

json_each y json_tree son funciones con valor de tabla virtuales que expanden JSON a filas. json_each maneja un único array/objeto; json_tree recorre recursivamente toda la estructura, exponiendo rutas fullkey. Esto desbloquea operaciones por conjuntos sobre arrays JSON — filtrar, unir y agregar. Para consultar arrays-de-objetos en una columna, usa un join estilo LATERAL: FROM users u, json_each(u.tags) e.

sqlite
-- json_each: virtual table, one row per array element
SELECT value FROM json_each('[10, 20, 30]');
-- 10, 20, 30

-- with index
SELECT key, value FROM json_each('["a","b","c"]');
-- 0,'a'  1,'b'  2,'c'

-- query an array stored in a column
SELECT u.username, e.value AS tag
FROM users u, json_each(u.tags) e
WHERE e.value LIKE 'admin%';

-- aggregate back to JSON
SELECT json_group_array(value)
FROM json_each('[10, 20, 30]')
WHERE value > 15;
-- [20,30]

-- json_tree: recursively walk nested structures
SELECT fullkey, value
FROM json_tree('{"a": {"b": [1, 2]}}');
-- '$','{"a":{"b":[1,2]}}'
-- '$.a','{"b":[1,2]}'
-- '$.a.b[0]',1
-- '$.a.b[1]',2

Indexación y validación JSON

Indexa las expresiones json_extract para búsquedas rápidas de campos JSON — la consulta debe usar la expresión idéntica para beneficiarse. json_valid() devuelve 1/0; úsala en una restricción CHECK para imponer columnas de solo JSON. json_type() reporta el tipo JSON en una ruta ('object', 'array', 'integer', 'real', 'string', 'boolean', 'null'). Para cargas JSON pesadas, desnormaliza los campos calientes a columnas regulares indexadas y conserva el JSON para la cola flexible.

sqlite
-- expression index on a JSON field for fast lookups
CREATE INDEX idx_events_type
ON events(json_extract(data, '$.type'));
SELECT * FROM events WHERE json_extract(data, '$.type') = 'login';

-- unique index on a JSON field
CREATE UNIQUE INDEX idx_users_email_json
ON users(json_extract(profile, '$.email'));

-- validate JSON
SELECT json_valid('{"a": 1}');     -- 1
SELECT json_valid('{a: 1}');       -- 0 (unquoted keys)

-- JSON type of a path
SELECT json_type('{"a": 1, "b": [1,2]}', '$.a');  -- 'integer'
SELECT json_type('{"a": 1, "b": [1,2]}', '$.b');  -- 'array'

-- check constraint for valid JSON columns
CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  data TEXT CHECK (json_valid(data))
);
15

Funciones de ventana

Fundamentos de la cláusula OVER

Las funciones de ventana (SQLite 3.25+) computan sobre un conjunto de filas relacionadas con la fila actual, sin colapsarlas como hace GROUP BY. OVER () significa 'todas las filas'; OVER (ORDER BY ...) define el ordenamiento para clasificar. ROW_NUMBER es única; RANK deja huecos tras los empates; DENSE_RANK no. Las funciones de ventana aparecen en SELECT y ORDER BY, no en WHERE — filtra con una consulta externa o un CTE.

sqlite
-- window functions: aggregate OVER a 'window' of rows
-- without collapsing rows (unlike GROUP BY)
SELECT username, age,
  AVG(age) OVER () AS overall_avg
FROM users;

-- ROW_NUMBER: unique sequential number per row
SELECT username,
  ROW_NUMBER() OVER (ORDER BY created) AS row_num
FROM users;

-- RANK / DENSE_RANK
SELECT username, age,
  RANK()       OVER (ORDER BY age DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank
FROM users;

PARTITION BY

PARTITION BY divide las filas en grupos para la función de ventana, como GROUP BY pero sin colapsar — cada fila conserva su identidad mientras recibe un agregado por grupo. El patrón 'top N por grupo' (ROW_NUMBER + WHERE rn <= N externo) es la solución canónica para 'los 3 pedidos por cliente', 'las 5 últimas publicaciones por autor', etc. Suele ser mucho más rápido que las subconsultas correlacionadas.

sqlite
-- per-group ranking / aggregation
SELECT username, role, age,
  ROW_NUMBER() OVER (PARTITION BY role ORDER BY age DESC) AS rank_in_role,
  AVG(age)     OVER (PARTITION BY role)                   AS avg_role_age
FROM users;

-- top N per group (common pattern)
SELECT * FROM (
  SELECT user_id, total,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
  FROM orders
) WHERE rn <= 3;

-- percent of group total
SELECT username, role,
  age * 1.0 / SUM(age) OVER (PARTITION BY role) AS pct_of_role_total
FROM users;

Especificaciones de marco

Un marco define qué filas ve la función de ventana, relativas a la fila actual. UNBOUNDED PRECEDING..CURRENT ROW da un total acumulado. N PRECEDING..N FOLLOWING da una ventana deslizante (media móvil). El marco por defecto con ORDER BY usa RANGE, que incluye filas pares (mismo valor de ORDER BY); ROWS es un conteo estricto de filas. Malentender los marcos frente a ORDER BY es una fuente común de analítica incorrecta.

sqlite
-- RUNNING TOTAL: cumulative sum over rows so far
SELECT username, total,
  SUM(total) OVER (ORDER BY created
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM orders;

-- MOVING AVERAGE: 3-row window centered on current row
SELECT username, total,
  AVG(total) OVER (ORDER BY created
                   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM orders;

-- default frame for ORDER BY windows:
--   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- default frame (no ORDER BY):
--   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- ROWS vs RANGE: ROWS counts rows; RANGE includes peers.

LAG y LEAD

LAG y LEAD acceden a otras filas relativas a la actual — esenciales para el análisis de series temporales (deltas día contra día, detección de rachas). El tercer argumento opcional es un valor por defecto para las filas fuera de rango. FIRST_VALUE/LAST_VALUE devuelven valores de los bordes del marco; ojo, LAST_VALUE necesita un marco UNBOUNDED FOLLOWING explícito o devuelve el valor de la fila actual (una trampa clásica).

sqlite
-- compare current row to previous / next row
SELECT username, created,
  LAG(created, 1)  OVER (ORDER BY created) AS prev_created,
  LEAD(created, 1) OVER (ORDER BY created) AS next_created
FROM users;

-- difference from previous value (time-series analysis)
SELECT date, sales,
  sales - LAG(sales, 1) OVER (ORDER BY date) AS day_over_day
FROM daily_sales;

-- LAG with default value and offset
SELECT username, created,
  LAG(username, 3, 'N/A') OVER (ORDER BY created) AS three_back
FROM users;

-- FIRST_VALUE / LAST_VALUE / NTH_VALUE
SELECT username, age,
  FIRST_VALUE(age) OVER (ORDER BY age) AS youngest,
  LAST_VALUE(age)  OVER (ORDER BY age
                         ROWS BETWEEN UNBOUNDED PRECEDING
                         AND UNBOUNDED FOLLOWING) AS oldest
FROM users;

NTILE y percentiles

NTILE(n) divide las filas ordenadas en n cubetas aproximadamente iguales — usa 4 para cuartiles, 100 para percentiles, 10 para deciles. CUME_DIST es la fracción de filas con valor <= a la fila actual; PERCENT_RANK es el rango relativo como fracción 0..1. Son los bloques de construcción de las consultas estadísticas/analíticas. Como todas las funciones de ventana, no colapsan filas — envuélvelas en una consulta externa para filtrar.

sqlite
-- divide rows into N equal buckets (quartiles, percentiles)
SELECT username, age,
  NTILE(4) OVER (ORDER BY age) AS quartile
FROM users;

-- CUME_DIST: cumulative distribution (0..1)
SELECT username, age,
  CUME_DIST() OVER (ORDER BY age) AS cume_dist
FROM users;

-- PERCENT_RANK: rank as a fraction (0..1)
SELECT username, age,
  PERCENT_RANK() OVER (ORDER BY age) AS pct_rank
FROM users;

-- NTH_VALUE: value at a specific row in the frame
SELECT username, age,
  NTH_VALUE(age, 2) OVER (ORDER BY age) AS second_age
FROM users;
16

CTE (cláusula WITH)

CTE básico

Un CTE (Common Table Expression) definido con WITH es una subconsulta con nombre y acotada — mejora la legibilidad y puede referenciarse varias veces en la misma sentencia. SQLite típicamente materializa los CTEs (los escribe en una tabla temporal), así que el rendimiento es similar al de una tabla derivada. Usa CTEs para romper consultas complejas en etapas, pre-agregar o evitar repetir subconsultas. Son SQL estándar y portables.

sqlite
-- a CTE is a named temporary result set, scoped to one statement
WITH active_user_count AS (
  SELECT COUNT(*) AS n FROM users WHERE active = 1
)
SELECT username,
  (SELECT n FROM active_user_count) AS total_active
FROM users WHERE active = 1;

-- CTE in a join
WITH user_totals AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
)
SELECT u.username, COALESCE(t.spent, 0) AS spent
FROM users u
LEFT JOIN user_totals t ON t.user_id = u.id
ORDER BY spent DESC;

-- CTEs make complex queries readable; they are syntactic sugar
-- (SQLite often materializes them, like a temp table).

Varios CTEs

Varios CTEs separados por comas pueden referenciar cada uno CTEs anteriores en el mismo WITH — ideal para pipelines de datos por etapas en una sola sentencia. Cada CTE es un paso lógico; leer de arriba abajo refleja el flujo de datos. Es mucho más mantenible que las subconsultas anidadas. SQLite materializa cada CTE; para resultados intermedios muy grandes, considera tablas temporales entre sentencias en lugar de una cadena gigante de CTEs.

sqlite
-- chain multiple CTEs, each able to reference earlier ones
WITH
active_users AS (
  SELECT id, username FROM users WHERE active = 1
),
user_orders AS (
  SELECT user_id, COUNT(*) AS n_orders, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
),
labeled AS (
  SELECT au.username,
    COALESCE(uo.n_orders, 0) AS orders,
    COALESCE(uo.spent, 0)    AS spent
  FROM active_users au
  LEFT JOIN user_orders uo ON uo.user_id = au.id
)
SELECT * FROM labeled
WHERE orders > 0
ORDER BY spent DESC;

CTE recursivo

Los CTEs recursivos tienen un ancla (caso base) y un miembro recursivo unidos por UNION (deduplica) o UNION ALL. Son la forma estándar SQL de recorrer árboles (organigramas, árboles de categorías, comentarios en hilo) y grafos. SQLite acota la profundidad de recursión (por defecto 1000 vía límite). El truco de 'generar series' es inestimable ya que SQLite carece de GENERATE_SERIES (aunque existe la extensión series).

sqlite
-- a recursive CTE references itself; perfect for hierarchies
WITH RECURSIVE subordinates AS (
  -- anchor: top-level manager
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- recursive: their direct reports, one level deeper
  SELECT e.id, e.name, e.manager_id, s.depth + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT depth, id, name FROM subordinates ORDER BY depth, name;

-- generate a series of numbers (no native GENERATE_SERIES)
WITH RECURSIVE cnt(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cnt WHERE n < 10
)
SELECT n FROM cnt;

Consultas jerárquicas

Los CTEs recursivos brillan sobre las tablas autorreferentes. El primer ejemplo construye una ruta delimitada por barras desde la raíz a cada nodo — útil para migas de pan. El segundo halla la totalidad de los descendientes de un nodo (subárbol). Usa UNION (no UNION ALL) si son posibles ciclos para prevenir bucles infinitos; de lo contrario UNION ALL es más rápido. Para árboles muy profundos, monitorea el rendimiento — los CTEs recursivos pueden ser lentos sin índices adecuados en la columna de join.

sqlite
-- categories with parent/child self-reference
CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT,
  parent_id INTEGER REFERENCES categories(id)
);

-- full path from root to each node
WITH RECURSIVE cat_path(id, name, path) AS (
  SELECT id, name, name
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, cp.path || ' > ' || c.name
  FROM categories c
  JOIN cat_path cp ON c.parent_id = cp.id
)
SELECT id, path FROM cat_path ORDER BY path;

-- find all descendants of a node
WITH RECURSIVE descendants(id) AS (
  SELECT id FROM categories WHERE id = 5   -- root of subtree
  UNION ALL
  SELECT c.id FROM categories c
  JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM categories WHERE id IN (SELECT id FROM descendants);

Optimización de CTEs

SQLite 3.35+ soporta las pistas MATERIALIZED / NOT MATERIALIZED para controlar la evaluación de los CTEs. MATERIALIZED fuerza una escritura a tabla temporal (bueno para CTEs caros referenciados varias veces); NOT MATERIALIZED incrusta el CTE para que el optimizador pueda empujar predicados (bueno para CTEs baratos). El planificador suele elegir bien por sí solo; usa las pistas solo cuando EXPLAIN QUERY PLAN muestre un plan malo. Los CTEs recursivos siempre se materializan.

sqlite
-- MATERIALIZED: force SQLite to materialize (temp table)
WITH cte AS MATERIALIZED (
  SELECT user_id, SUM(total) AS spent FROM orders GROUP BY user_id
)
SELECT u.username, cte.spent
FROM users u JOIN cte ON cte.user_id = u.id;

-- AS NOT MATERIALIZED: inline the CTE (may run multiple times)
WITH cte AS NOT MATERIALIZED (
  SELECT COUNT(*) FROM users WHERE active = 1
)
SELECT * FROM products WHERE id < (SELECT * FROM cte);

-- choose MATERIALIZED when the CTE is expensive and used
-- multiple times; NOT MATERIALIZED when it's cheap or used
-- once and could benefit from predicate pushdown.

-- recursive CTEs are always materialized.
17

Respaldo y restauración

.dump (respaldo de texto)

.dump exporta la base de datos como sentencias INSERT de SQL plano — legible por humanos, comparable con diffs y portable entre versiones y arquitecturas de SQLite. Es el formato de respaldo más compatible. Restaurar reproduce el SQL en una base de datos fresca. --nosys se salta sqlite_sequence y otras tablas internas. Para bases de datos grandes, .dump es lento y produce archivos enormes; usa .backup (copia binaria) o la Online Backup API en su lugar.

sqlite
-- dump the entire database as SQL text (portable)
sqlite3 mydb.sqlite .dump > backup.sql

-- dump a single table
sqlite3 mydb.sqlite ".dump users" > users.sql

-- dump only data (no schema)
sqlite3 mydb.sqlite ".dump" | grep '^INSERT' > data.sql

-- restore from a dump
sqlite3 newdb.sqlite < backup.sql
-- or inside the CLI:
.read backup.sql

-- dump in a specific order to satisfy FK constraints
sqlite3 mydb.sqlite ".dump --nosys" > backup.sql

-- .dump works on attached databases too
ATTACH 'archive.sqlite' AS archive;
.dump archive

.backup (copia binaria)

.backup hace una copia binaria segura en línea usando la Online Backup API de SQLite — funciona incluso mientras hay escritores activos, produciendo una instantánea transaccionalmente consistente. Es la forma recomendada de respaldar una base de datos en marcha. La salida es un archivo SQLite válido, así que restaurar es solo copiar el archivo. A diferencia de .dump, el formato binario es compatible entre versiones y arquitecturas pero no es legible por humanos.

sqlite
-- binary copy to another file (online, safe with active writers)
sqlite3 mydb.sqlite ".backup 'backup.sqlite'"

-- inside the CLI
.backup backup.sqlite
.backup main backup.sqlite     -- source 'main', target file

-- backup to an attached database
ATTACH 'backup.sqlite' AS bk;
.backup main bk;
DETACH bk;

-- .backup uses the Online Backup API internally: it copies
-- page-by-page and handles concurrent writers safely, so it's
-- the right choice for live production backups.

-- restore = just copy the file back (or use it directly)
cp backup.sqlite mydb.sqlite

Online Backup API

La Online Backup API (sqlite3_backup_*) es la técnica de respaldo de oro: copia una base de datos viva página a página, maneja correctamente las escrituras concurrentes y produce una instantánea consistente. El sqlite3.Connection.backup() de Python y el .backup() de better-sqlite3 de Node la envuelven. Funciona en modo WAL. La copia cruda de archivos puede producir un respaldo corrupto si hay una escritura en curso — usa la copia de archivo solo con la base de datos detenida o con instantáneas a nivel de archivo.

sqlite
-- the sqlite3 CLI's .backup uses this C API; in Python:
import sqlite3
src = sqlite3.connect("mydb.sqlite")
dst = sqlite3.connect("backup.sqlite")
src.backup(dst)
dst.close()
src.close()

-- with progress callback
def progress(remaining, total):
    print(f"{total - remaining}/{total} pages copied")
src.backup(dst, pages=progress)

-- this is the safest backup method: it's online (doesn't block
-- writers), transactionally consistent, and works on WAL-mode
-- databases. Prefer it over raw file copy for production.

-- Node.js (better-sqlite3):
const backup = db.backup('backup.sqlite');
while (!backup.completed) backup.step(-1);
backup.delete();

Adjuntar bases de datos

ATTACH DATABASE trae hasta 10 archivos de base de datos a una conexión como esquemas con nombre (main, temp, más los adjuntos). Puedes consultar, unir y copiar datos entre ellos con nombres cualificados por esquema (main.users, archive.orders). Es la base para el archivado (mover filas viejas a un archivo de archivo), la fragmentación y los flujos de fusión. DETACH cierra el esquema pero deja el archivo intacto. Las transacciones entre bases de datos son atómicas en modo WAL.

sqlite
-- work with multiple DB files in one connection
ATTACH DATABASE 'archive.sqlite' AS archive;
ATTACH DATABASE 'lookup.sqlite' AS lookup;

-- query across databases
SELECT u.username, a.note
FROM main.users u
JOIN archive.old_users a ON u.id = a.id;

-- copy a table between databases
CREATE TABLE archive.users_copy AS
SELECT * FROM main.users;

-- move data: insert into one DB, delete from another
INSERT INTO archive.orders SELECT * FROM main.orders WHERE created < '2023-01-01';
DELETE FROM main.orders WHERE created < '2023-01-01';

-- list attached databases
.databases

-- detach (does NOT delete the file)
DETACH DATABASE archive;

-- limit: 10 attached databases (compile-time SQLITE_MAX_ATTACHED).

Recuperación y corrupción

integrity_check detecta corrupción estructural; .recover (3.29+) es la mejor herramienta para rescatar datos de un archivo corrupto — extrae las filas legibles y se salta las páginas malas, produciendo SQL que puedes pasar por pipe a una base de datos fresca. Si .recover falla, recurre a .dump y acepta la pérdida parcial. La mayoría de corrupción viene de operaciones de archivo inseguras (copiar una base viva, sistemas de archivos de red, pérdida de energía con synchronous=OFF). Respalda siempre antes de los intentos de recuperación.

sqlite
-- check integrity
PRAGMA integrity_check;
PRAGMA quick_check;

-- recover from a corrupt database (best effort)
sqlite3 corrupt.sqlite ".recover" > recovered.sql
sqlite3 clean.sqlite < recovered.sql

-- .recover extracts everything readable, skipping corrupt pages

-- if .recover fails, try dumping what you can
sqlite3 corrupt.sqlite ".dump" > partial.sql 2>errors.txt
sqlite3 clean.sqlite < partial.sql

-- point-in-time from a backup file
cp nightly_backup.sqlite mydb.sqlite

-- after a crash, WAL is replayed automatically on next open;
-- if stuck, checkpoint manually:
sqlite3 mydb.sqlite "PRAGMA wal_checkpoint(TRUNCATE);"

-- prevent corruption: never overwrite a live DB file,
-- never share a DB on a network filesystem without WAL off.
18

Importación y exportación

Importación CSV

.import lee CSV a una tabla; si la tabla no existe, SQLite la crea con columnas todas TEXT (a menudo no lo que quieres — crea la tabla primero con los tipos adecuados). --skip N se salta las filas de encabezado. .mode csv configura el parser para manejar campos entre comillas con comas y saltos de línea incrustados. Para importaciones grandes, envuélvelas en BEGIN/COMMIT y PRAGMA synchronous=OFF (temporalmente) para una aceleración de más de 10 veces. Deshabilita los índices durante las cargas masivas y recréalos después.

sqlite
-- import a CSV file into a table
.mode csv
.import users.csv users
-- ^ creates 'users' table with text columns named from the header row

-- import into an EXISTING table (column order must match)
.mode csv
.import --skip 1 users.csv users   -- --skip 1 to skip header

-- recommended: create the table first with proper types
CREATE TABLE users (id INTEGER, name TEXT, email TEXT);
.mode csv
.import --skip 1 users.csv users

-- from the shell, one-shot
sqlite3 mydb.sqlite ".mode csv" ".import users.csv users"

-- handle quotes/escapes
.mode csv
.separator ,
.import data.csv mytable

Exportación CSV

.mode csv con .headers on produce un CSV estándar con fila de encabezado — pásalo a un archivo con .output. Los flags del shell -header -csv más la redirección de salida lo hacen en una línea sin entrar en la CLI. Para TSV usa .mode list con .separator "\t". Para exportaciones grandes, esto es basado en stream y eficiente en memoria. Para exportar varias tablas, cambia .output entre consultas.

sqlite
-- export a query to CSV
.mode csv
.headers on
.output users.csv
SELECT id, username, email FROM users;
.output stdout

-- from the shell, one-shot
sqlite3 mydb.sqlite -header -csv \
  "SELECT id, username, email FROM users" > users.csv

-- export all rows of a table
.mode csv
.headers on
.output products.csv
SELECT * FROM products;
.output stdout

-- custom separator
.mode list
.separator "|"
.output users.txt
SELECT id, username FROM users;
.output stdout

Exportación JSON

.mode json emite un objeto JSON por fila (sin array envolvente). Para un array JSON propiamente, usa json_group_array(json_object(...)) — esto te da control total sobre los nombres de campo y el anidamiento, y funciona para arrays anidados vía subconsultas correlacionadas. Es una forma potente de construir APIs JSON directamente desde SQL sin un ORM. Para conjuntos de resultados grandes, transmite .mode json línea por línea en lugar de construir un array gigante en memoria.

sqlite
-- export a query as JSON (one JSON object per row)
.mode json
.output users.json
SELECT id, username, email FROM users;
.output stdout

-- build a single JSON array with json_group_array
SELECT json_group_array(json_object(
  'id', id, 'username', username, 'email', email
)) AS users_json
FROM users;
-- [{"id":1,"username":"alice","email":"[email protected]"},...]

-- nested JSON (orders with their items)
SELECT json_object(
  'order_id', o.id,
  'total', o.total,
  'items', (SELECT json_group_array(json_object('name', i.name, 'qty', i.qty))
            FROM order_items i WHERE i.order_id = o.id)
) AS order_json
FROM orders o;

Importar JSON

SQLite no tiene .import para JSON — carga el archivo como TEXT (vía código de la app o .read con INSERTs) y luego parsea con json_each/json_extract. json_each(array) expande un array a filas para inserción por conjuntos. Para archivos JSON grandes, parsea en streaming en tu aplicación (no cargues el archivo completo en memoria). Valida con json_valid() durante la carga y usa una restricción CHECK sobre las columnas JSON por integridad continua.

sqlite
-- load a JSON file into a text column, then parse
CREATE TABLE raw (data TEXT);
-- (no built-in .import for JSON; load via CLI or app)

-- once loaded, parse with json_each (array) or json_extract (object)
SELECT json_extract(data, '$.name') AS name,
       json_extract(data, '$.age')  AS age
FROM raw;

-- expand a JSON array into rows
INSERT INTO users (username, email)
SELECT json_extract(value, '$.username'),
       json_extract(value, '$.email')
FROM raw, json_each(raw.data);

-- import via Python
import sqlite3, json
db = sqlite3.connect("mydb.sqlite")
with open("users.json") as f:
    for row in json.load(f):
        db.execute("INSERT INTO users(username,email) VALUES(?,?)",
                   (row["username"], row["email"]))
db.commit()

Excel y otros formatos

Para Excel, exporta CSV (Excel lo abre directamente); .mode excel (3.36+) añade un BOM para que el UTF-8 se renderice correctamente. .mode markdown produce marcado de tabla pegable para documentación. .mode insert genera sentencias INSERT — genial para sembrar bases de datos de prueba o migrar datos entre esquemas. .mode quote entrecomilla cada valor, útil cuando los datos contienen comas/saltos/comillas que puedan confundir a otras herramientas. .mode table/box dibuja tablas ASCII para terminales.

sqlite
-- Excel: export CSV (Excel opens it) or use .mode excel
.mode csv
.headers on
.output report.csv
SELECT * FROM monthly_report;
.output stdout

-- .mode excel (3.36+): CSV with BOM, friendly to Excel
.mode excel
.output report.csv
SELECT * FROM monthly_report;
.output stdout

-- .mode markdown: GitHub-flavored markdown tables
.mode markdown
SELECT id, username, email FROM users LIMIT 5;

-- .mode insert: generate INSERT statements
.mode insert users
.output seed.sql
SELECT id, username, email FROM users;
.output stdout
-- produces: INSERT INTO "users" VALUES(1,'alice','[email protected]');

-- .mode quote: CSV with all values quoted
.mode quote
.output safe.csv
SELECT * FROM users;
.output stdout
19

Integración de aplicaciones

Python (sqlite3)

El sqlite3 de Python viene integrado en la librería estándar. Usa SIEMPRE placeholders ? (o :nombre) — nunca f-strings ni % — para prevenir la inyección SQL y manejar las comillas correctamente. executemany es mucho más rápido que iterar execute para inserts masivos. Fja row_factory = sqlite3.Row para el acceso por nombre de columna. Usa context managers (with conn:) para commit/rollback automáticos. Para concurrencia, usa una conexión por hilo (sqlite3 prohíbe compartir entre hilos por defecto).

sqlite
import sqlite3

# connect (creates the file if missing)
conn = sqlite3.connect("mydb.sqlite")
conn.row_factory = sqlite3.Row  # dict-like rows
cur = conn.cursor()

# parameterized query (NEVER use string formatting!)
cur.execute(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    ("alice", "[email protected]"),
)
conn.commit()

# query with parameters
cur.execute("SELECT id, username FROM users WHERE active = ?", (1,))
for row in cur:
    print(row["id"], row["username"])

# named parameters
cur.execute(
    "SELECT * FROM users WHERE age >= :min_age",
    {"min_age": 18},
)

# bulk insert (fast)
cur.executemany(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    [("bob", "[email protected]"), ("carol", "[email protected]")],
)
conn.commit()
conn.close()

Node.js (better-sqlite3)

better-sqlite3 es sincrónico (sin boilerplate de callback/Promise) y el driver SQLite para Node.js más rápido. Las sentencias preparadas se reutilizan — prepara una vez, corre muchas. .transaction(fn) envuelve la función en BEGIN/COMMIT (o ROLLBACK ante throw), y es dramáticamente más rápido que el autocommit por sentencia. Usa placeholders ? para evitar la inyección SQL. Habilita WAL para concurrencia. Para APIs basadas en Promise, envuelve las llamadas o usa sqlite/sqlite3 en su lugar.

sqlite
const Database = require("better-sqlite3");
const db = new Database("mydb.sqlite");
db.pragma("journal_mode = WAL");

// prepared statement (synchronous, fast)
const insert = db.prepare(
  "INSERT INTO users (username, email) VALUES (?, ?)"
);
const info = insert.run("alice", "[email protected]");
console.log(info.lastInsertRowid);

// query multiple rows
const select = db.prepare("SELECT id, username FROM users WHERE active = ?");
const rows = select.all(1);  // array of objects

// query one row
const one = select.get(1);

// transaction (atomic, fast)
const insertMany = db.transaction((users) => {
  for (const u of users) insert.run(u.username, u.email);
});
insertMany([{username: "bob"}, {username: "carol"}]);

db.close();

URI de conexión y modos

Los nombres de archivo URI (file:...) desbloquean los modos de solo lectura, caché compartida y en memoria — pasa uri=True en Python o la cadena file: a los drivers de Node. :memory: es por conexión por defecto; file:nombre?mode=memory&cache=shared crea una base de datos en memoria con nombre compartida entre conexiones del mismo proceso (genial para pruebas). Fja un busy_timeout para que los escritores que esperan brevemente locks no den error de inmediato. Fja siempre PRAGMA foreign_keys = ON por conexión.

sqlite
# open read-only
sqlite3.connect("file:mydb.sqlite?mode=ro", uri=True)

# open in-memory (vanishes on close)
sqlite3.connect(":memory:")
# or via URI:
sqlite3.connect("file::memory:", uri=True)

# shared in-memory database (visible across connections in same process)
sqlite3.connect("file:memdb1?mode=memory&cache=shared", uri=True)

# with a busy timeout (wait instead of erroring on locks)
conn = sqlite3.connect("mydb.sqlite", timeout=30)

# Node.js: same URI form
const db = new Database("file:mydb.sqlite?mode=ro", { readonly: true });

# Node.js in-memory
const mem = new Database(":memory:");

# Python: per-connection pragmas
conn.execute("PRAGMA journal_mode = WAL")
conn.execute("PRAGMA foreign_keys = ON")

Transacciones y concurrencia

Las transacciones agrupan sentencias atómicamente — se aplican por completo o no se aplican. SQLite permite un único escritor a la vez (lock de escritura a nivel de base de datos), pero el modo WAL permite lectores concurrentes. En Python, el isolation_level por defecto envuelve el DML en transacciones implícitas (hace falta commit). Para control explícito fija isolation_level = None y emite tú BEGIN/COMMIT. Usa un busy_timeout (PRAGMA busy_timeout o timeout de conexión) para que los escritores que esperan brevemente un lock esperen en lugar de lanzar SQLITE_BUSY de inmediato.

sqlite
# Python: explicit transaction
conn = sqlite3.connect("mydb.sqlite")
conn.isolation_level = None  # autocommit off -> manual BEGIN
cur = conn.cursor()
cur.execute("BEGIN")
try:
    cur.execute("UPDATE accounts SET bal = bal - 100 WHERE id = 1")
    cur.execute("UPDATE accounts SET bal = bal + 100 WHERE id = 2")
    cur.execute("COMMIT")
except Exception:
    cur.execute("ROLLBACK")
    raise

// Node.js (better-sqlite3) transaction
const transfer = db.transaction((from, to, amt) => {
  db.prepare("UPDATE accounts SET bal = bal - ? WHERE id = ?").run(amt, from);
  db.prepare("UPDATE accounts SET bal = bal + ? WHERE id = ?").run(amt, to);
});
transfer(1, 2, 100);

-- SQLite allows ONE writer at a time; many readers in WAL mode.
-- Use a busy_timeout to wait for locks instead of failing fast.

Sentencias preparadas y seguridad

Las consultas parametrizadas (placeholders ?) son la práctica de seguridad más importante — previenen la inyección SQL y manejan la conversión de tipos y el escape correctamente. NUNCA interpolés entrada de usuario en cadenas SQL. Los placeholders funcionan solo para valores; los identificadores (nombres de tabla/columna) no pueden parametrizarse — validalos contra una lista blanca. Para LIKE, escapa % y _ en la entrada del usuario para que no pueda inyectar comodines. Esto aplica en todos los lenguajes y drivers.

sqlite
-- SQL injection vulnerability (NEVER do this):
-- app: "SELECT * FROM users WHERE name = '" + user_input + "'"
-- user_input = "'; DROP TABLE users; --"
-- result:    SELECT * FROM users WHERE name = ''; DROP TABLE users; --'

-- SAFE: parameterized query (placeholders are NOT string substitution)
-- Python:
cur.execute("SELECT * FROM users WHERE name = ?", (user_input,))
-- Node.js:
stmt.get(user_input);

-- identifiers (table/column names) CANNOT be parameterized;
-- validate against a whitelist:
allowed_tables = {"users", "orders", "products"}
if table not in allowed_tables:
    raise ValueError("bad table")
cur.execute(f"SELECT * FROM {table} WHERE id = ?", (id_,))

-- LIKE with user input: escape wildcards
cur.execute(
    "SELECT * FROM users WHERE name LIKE ? ESCAPE '\'",
    (user_input.replace("\", "\\").replace("%", "\%").replace("_", "\_") + "%",)
)
20

Optimización de rendimiento

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN es la herramienta de ajuste número 1. 'USING INDEX' = bien; 'SCAN' (escaneo completo de tabla) = normalmente malo en tablas grandes. Para joins, comprueba que las columnas de join estén indexadas y que el planificador no escanee la tabla mayor. EXPLAIN (sin QUERY PLAN) muestra el bytecode VDBE — rara vez necesario pero útil para depuración profunda. El comando experimental EXPERT puede sugerir índices (3.36+), o usa el comando punto .expert de la CLI.

sqlite
-- see how SQLite executes a query (essential for tuning)
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = '[email protected]';

-- good: uses an index
--   SEARCH users USING INDEX idx_users_email (email=?)

-- bad: full table scan
--   SCAN users

-- join plan
EXPLAIN QUERY PLAN
SELECT u.username, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.active = 1;

-- automated index recommendation (3.36+, experimental)
SELECT * FROM users WHERE email = 'x';
-- then:
EXPLAIN QUERY PLAN SELECT * FROM sqlite_stat1;

-- lower-level: EXPLAIN (bytecode)
EXPLAIN SELECT * FROM users WHERE id = 1;

VACUUM y ANALYZE

VACUUM reconstruye el archivo, recuperando espacio de los deletes y defragmentando páginas — córrelo tras deletes grandes o periódicamente para bases de datos con muchas escrituras. VACUUM INTO (3.27+) escribe una copia limpia sin bloquear la fuente por mucho tiempo. ANALYZE actualiza las estadísticas de sqlite_stat1 que usa el planificador de consultas para elegir índices — córrelo tras cargas masivas o cambios de esquema/datos. auto_vacuum=INCREMENTAL recupera espacio incrementalmente sin un VACUUM completo.

sqlite
-- VACUUM: rebuild the database file, reclaiming free space
VACUUM;
-- after large deletes, this shrinks the file and defrags pages

-- VACUUM INTO: write a defragmented copy to a new file (3.27+)
VACUUM INTO 'clean.sqlite';

-- incremental VACUUM (only with auto_vacuum enabled)
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA incremental_vacuum(100);  -- free up to 100 pages

-- ANALYZE: update planner statistics for better query plans
ANALYZE;                          -- all tables
ANALYZE users;                    -- one table
ANALYZE users(idx_users_email);   -- one index

-- run ANALYZE after bulk loads or major data changes

Carga masiva

Para cargas masivas: envuélvelas en una única transacción (la mayor aceleración individual — el autocommit por fila es catastróficamente lento), deshabilita temporalmente synchronous y el journaling, elimina y recrea los índices, y considera aumentar PRAGMA cache_size. El comando punto .import es más rápido que las sentencias INSERT. Para cargas realmente enormes, considera el .backup de una base de datos preconstruida. Restaura siempre los PRAGMA de seguridad tras cargar datos de producción.

sqlite
-- fastest bulk load into a fresh table
PRAGMA synchronous = OFF;     -- risk: power-loss corruption (use only for one-off loads)
PRAGMA journal_mode = MEMORY;
PRAGMA temp_store = MEMORY;

BEGIN;
CREATE TABLE big (id INTEGER, data TEXT);
.import data.csv big
COMMIT;

-- then turn safety back on
PRAGMA synchronous = FULL;
PRAGMA journal_mode = WAL;

-- faster still: drop indexes, load, recreate
DROP INDEX idx_big_data;
-- ... load ...
CREATE INDEX idx_big_data ON big(data);

-- use a transaction (10-100x faster than autocommit per row)
BEGIN;
INSERT INTO big VALUES (1, 'a');
-- ... thousands of inserts ...
COMMIT;

FTS5 (búsqueda de texto completo)

FTS5 es el motor de búsqueda de texto completo de SQLite — indexa el texto para consultas MATCH rápidas, mucho más rápido que LIKE '%termino%' que escanea cada fila. Soporta booleano (AND/OR/NOT), prefijo (termino*), frase ("...") y ordenación por relevancia. El patrón de contenido externo (content='posts') evita duplicar datos. Mantén el índice FTS sincronizado con triggers (INSERT/UPDATE/DELETE sobre la tabla base). FTS5 es ideal para búsqueda-mientras-escribes, autocompletado y búsqueda de documentos.

sqlite
-- create a full-text search virtual table
CREATE VIRTUAL TABLE posts_fts USING fts5(
  title, body,
  content='posts', content_rowid='id'
);

-- populate it (keep in sync with triggers or rebuild)
INSERT INTO posts_fts(rowid, title, body)
  SELECT id, title, body FROM posts;

-- search with MATCH (much faster than LIKE '%term%')
SELECT p.* FROM posts p
JOIN posts_fts f ON f.rowid = p.id
WHERE posts_fts MATCH 'sqlite python';

-- ranking by relevance
SELECT p.title, rank
FROM posts p
JOIN posts_fts f ON f.rowid = p.id
WHERE posts_fts MATCH 'sqlite OR python'
ORDER BY rank;

-- prefix and phrase queries
SELECT * FROM posts_fts WHERE posts_fts MATCH 'sql*';        -- prefix
SELECT * FROM posts_fts WHERE posts_fts MATCH '"full text"'; -- exact phrase

Trampas comunes y ajuste

La mayoría de problemas de rendimiento de SQLite vienen de un puñado de causas: índices faltantes en columnas WHERE/JOIN/ORDER BY, transacciones faltantes (el autocommit por fila es la causa número 1 de los inserts masivos lentos), claves foráneas silenciosamente desactivadas y SELECT * sobre tablas anchas. Habilita WAL y claves foráneas en cada configuración de conexión. Lotea los DELETE grandes para evitar locks de escritura largos y espacios temporales enormes. Corre ANALYZE tras cambios importantes de datos para que el planificador elija buenos planes.

sqlite
-- 1. Enable foreign keys (OFF by default!)
PRAGMA foreign_keys = ON;

-- 2. Use WAL for concurrency
PRAGMA journal_mode = WAL;

-- 3. Index foreign keys AND join columns
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 4. Avoid SELECT * (more I/O, breaks on schema change)
SELECT id, username FROM users;

-- 5. Use transactions for multi-statement writes
BEGIN;
-- ... several inserts/updates ...
COMMIT;

-- 6. Don't index every column (slows writes)
-- index selectively on WHERE/JOIN/ORDER BY columns.

-- 7. Use LIMIT on exploratory queries
SELECT * FROM big_table LIMIT 10;

-- 8. Batch UPDATEs/DELETEs to avoid long locks
DELETE FROM logs WHERE id IN (
  SELECT id FROM logs WHERE ts < '2023-01-01' LIMIT 10000
);

-- 9. Prefer EXISTS over IN for large subquery results
-- 10. Run ANALYZE after big data changes.

Was this helpful?