Skip to content

SQLite Folha de referência

Motor de banco de dados SQL autocontido, sem servidor e sem configuração.

01

Primeiros Passos

Básico da CLI

SQLite guarda um banco inteiro num único arquivo. A CLI sqlite3 abre ou cria um arquivo .sqlite/.db sob demanda. Diferente de MySQL/PostgreSQL, não há processo de servidor — as aplicações ligam a biblioteca SQLite diretamente. .dump exporta o banco completo como SQL em texto, para backup ou migração.

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 de Ponto (Dot Commands)

Comandos de ponto são interpretados pela CLI sqlite3, não pelo motor SQL — nunca precisam de ponto e vírgula e não estão disponíveis via APIs de programação. .mode controla a formatação da saída (experimente 'column' com .header on para uso interativo legível). .schema mostra instruções CREATE; .fullschema adiciona índices, views e triggers. Use .help para descobrir mais.

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

Gestão de Arquivos de Banco

ATTACH DATABASE permite que uma conexão consulte até 10 arquivos de banco simultaneamente como schemas (main, temp, mais os anexados). A URI :memory: cria um banco puramente em memória que desaparece ao fechar — ideal para testes ou computação de rascunho. Nomes de arquivo URI (file:...) habilitam modos somente leitura, shared-cache e outros. Cada banco anexado é um arquivo separado no 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;

Cabeçalhos e Formatação de Saída

A formatação de saída afeta só a CLI, nunca os dados em si. O modo 'column' com .headers on é o mais legível para humanos. 'list' (padrão) é melhor para pipelines de shell. 'json' e 'csv' fazem do SQLite um conversor prático. .output redireciona resultados para um arquivo; lembre de voltar com .output stdout. .mode box/table desenham bordas 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

Executando Scripts e Arquivos SQL

.read executa um arquivo de SQL dentro de uma sessão CLI aberta; redirecionar com < roda antes do prompt interativo. --bail para o lote no primeiro erro (caso contrário, o SQLite continua, o que pode mascarar falhas). .parameter (3.44+) vincula parâmetros nomeados do shell, evitando interpolação de string. Sempre faça uma checagem de sanidade dos scripts com .echo on no desenvolvimento.

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

Tabelas e Tipos de Dados

Criando Tabelas

INTEGER PRIMARY KEY é um apelido para o rowid — auto-incrementa e é a chave mais rápida possível. AUTOINCREMENT muda o algoritmo para que IDs deletados nunca sejam reutilizados (um pouco mais lento; geralmente desnecessário). DEFAULT (expr) permite expressões arbitrárias como datetime('now'). CREATE TABLE AS SELECT cria uma tabela pré-carregada com resultados da consulta, mas não copia restrições nem í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';

Classes de Armazenamento e Afinidade de Tipo

SQLite usa tipagem dinâmica: qualquer coluna pode guardar qualquer uma das 5 classes de armazenamento. A 'afinidade de tipo' tenta converter valores quando possível (ex.: '42' numa coluna INTEGER vira o inteiro 42), mas nunca dá erro em divergência. Tabelas STRICT (3.37+) restauram a checagem de tipo rígida tradicional por coluna. typeof() reporta a classe realmente guardada. Essa flexibilidade é poderosa, mas exige disciplina — use STRICT para schemas novos onde a integridade importa.

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 Dados Comuns

SQLite não tem tipos nativos DATE/TIME nem BOOLEAN — guarde timestamps como TEXT ISO-8601 ('YYYY-MM-DD HH:MM:SS') para ordenabilidade e use funções de data/hora para manipulá-los. Booleanos são inteiros 0/1. Limites de comprimento de VARCHAR(n) são ignorados (só compatibilidade). A afinidade NUMERIC tenta INTEGER primeiro, depois REAL, preservando decimais exatos quando possível. BLOB guarda bytes crus verbatim.

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
);

Alterando Tabelas

O ALTER TABLE do SQLite é intencionalmente limitado, por ser um motor sem servidor. ADD COLUMN é O(1) (sem reescrita da tabela). RENAME COLUMN (3.25+) e DROP COLUMN (3.35+) são conveniências modernas. Para mudanças de tipo ou adições de restrição, use o padrão de reconstrução de tabela de 12 passos (criar nova, copiar, descartar, renomear, reconstruir índices). Chaves estrangeiras precisam ser temporariamente desativadas durante reconstruções — veja 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

Restrições

Restrições impõem integridade de dados no nível do motor. PRIMARY KEY implica NOT NULL e UNIQUE. CHECK pode referenciar qualquer coluna da linha. Cláusulas ON CONFLICT (OR IGNORE / OR REPLACE / OR ABORT / OR FAIL / OR ROLLBACK) controlam a resolução de conflito por instrução — OR REPLACE deleta a linha conflitante e insere, o que reseta seu rowid. Para semântica de UPSERT, prefira ON CONFLICT ... DO UPDATE (veja a seção 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

Operações CRUD

INSERT

INSERT de múltiplas linhas é dramaticamente mais rápido que inserções por linha num loop — agrupe-as. INSERT ... SELECT copia dados entre tabelas. last_insert_rowid() retorna o rowid do INSERT mais recente na conexão atual (não na transação). Para apps, prefira inserções parametrizadas (veja a seção Integração) para evitar SQL injection e dores de escape. Use DEFAULT para assumir explicitamente o valor padrão de uma coluna.

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) é o idioma do SQLite para 'inserir ou atualizar se existir' — muito mais seguro que INSERT OR REPLACE, que deleta a linha existente (disparando triggers DELETE e resetando o rowid). 'excluded' refere-se à linha proposta para inserção. DO NOTHING pula silenciosamente. Alvos de conflito podem ser quaisquer restrições UNIQUE ou PRIMARY KEY. Este é o padrão correto para contadores, atualizações de última-visão e importações 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

Sempre inclua WHERE, a menos que realmente pretenda atualizar todas as linhas — SQLite não tem modo de ensaio. UPDATE ... FROM (3.33+) e UPDATE ... RETURNING (3.35+) aproximam o SQLite da ergonomia do PostgreSQL. RETURNING é inestimável para apps que precisam do estado pós-atualização sem um SELECT separado. CASE permite atualizações em massa de instrução única com lógica por linha, muito mais rápido que atualizações linha a linha.

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 não tem TRUNCATE — DELETE FROM tabela remove todas as linhas, mas dispara triggers no nível de linha e não reseta o contador AUTOINCREMENT (guardado em sqlite_sequence). DELETE ... RETURNING (3.35+) registra o que foi removido. Para dados de auditoria, prefira deletes suaves (uma coluna deleted_at) a deletes duros. O espaço livre de deletes é reutilizado por inserções futuras; VACUUM o devolve ao SO.

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;

Básico de SELECT

SELECT é o cavalo de batalha. LIMIT contagem OFFSET pular implementa paginação (paginação por keyset é mais rápida para offsets grandes — veja a seção de Consultas). Apelidos (AS, opcional) melhoram a legibilidade e são exigidos para colunas computadas em views. DISTINCT colapsa linhas idênticas; considere GROUP BY para controle mais fino. CASE adiciona lógica if/else aos conjuntos de resultados e serve em qualquer 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

Consultas e Filtragem

WHERE e Operadores

NULL é especial em SQL — é 'desconhecido', não um valor. = NULL sempre retorna NULL (tratado como falso), então use IS NULL / IS NOT NULL. NULL combinado com AND/OR segue lógica trivalente. BETWEEN é inclusivo. IN é abreviação de OR-equalidade; evite IN (NULL, ...), pois se comporta estranho. Use parênteses generosamente para tornar condições compostas inequívocas.

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 e LIMIT

ORDER BY sem um desempate único dá ordem não determinística entre execuções — sempre adicione uma coluna única (como id) como chave final de ordenação. A paginação por OFFSET é O(n), pois varre e descarta linhas; a paginação por keyset (WHERE id > ultimo_id_visto) é O(limite) e a escolha certa para tabelas grandes. RANDOM() exige uma ordenação completa — use só em conjuntos de resultados pequenos ou subconjuntos amostrados.

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;

Casamento de Padrões

LIKE não diferencia caixa para ASCII por padrão (pegadinha surpreendente — use COLLATE BINARY para diferenciar caixa). GLOB diferencia caixa e suporta classes de caracteres como [A-Z]. REGEXP não é embutido — você precisa registrar uma função ou carregar uma extensão. Para busca de texto completo em escala, use FTS5 (veja a seção Desempenho) em vez de LIKE '%termo%', que não consegue 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 e Lógica Condicional

CASE é o if/else do SQL e funciona em SELECT, WHERE, ORDER BY, GROUP BY e HAVING. A forma 'simples' casa um valor; a 'buscada' avalia condições booleanas em ordem. SUM(CASE WHEN ... THEN 1 ELSE 0 END) é o idioma clássico de pivot/contagem condicional. CASE curto-circuita no primeiro WHEN que casa, então a ordem importa. ELSE assume NULL se omitido.

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 e Operações de Conjunto

UNION ALL é mais rápido que UNION, porque UNION executa uma ordenação de deduplicação — prefira UNION ALL quando duplicatas são impossíveis ou aceitáveis. Todas as operações de conjunto exigem contagens e tipos de colunas compatíveis. SQLite aplica INTERSECT/EXCEPT com precedência maior que UNION/UNION ALL; use parênteses para controlar a ordem. ORDER BY numa consulta composta se aplica ao conjunto de resultados inteiro, não ao ú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 retorna só linhas com correspondências nas duas tabelas — linhas sem correspondência de qualquer lado são descartadas. USING (col) é abreviação quando ambas as tabelas têm uma coluna de mesmo nome e produz uma única coluna mesclada. NATURAL JOIN usa implicitamente todas as colunas de mesmo nome — conveniente, mas frágil (uma mudança de schema pode alterar silenciosamente o comportamento do join). Prefira ON explícito, por clareza.

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 preserva cada linha da tabela à esquerda; colunas ausentes do lado direito viram NULL. O padrão 'WHERE right.id IS NULL' (anti-join) acha linhas sem correspondência — frequentemente mais claro e rápido que NOT IN com subconsultas, especialmente quando há NULLs. Ao agregar através de um LEFT JOIN, COUNT(right.id) (não COUNT(*)) conta só as linhas com correspondência; um usuário sem pedidos dá 0, não 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 e RIGHT/FULL

CROSS JOIN produz o produto cartesiano — útil para gerar combinações (tamanho × cor), mas perigoso em tabelas grandes (linhas n×m). SQLite suporta RIGHT JOIN (3.39+), mas é raramente usado; reescreva como LEFT JOIN por clareza. Não há FULL OUTER JOIN nativo; emule com LEFT JOIN ... UNION ... RIGHT JOIN, em que a união deduplica as linhas com correspondência sobrepostas.

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

Um self join consulta a mesma tabela duas vezes, usando apelidos para dar papel a cada cópia (funcionário vs gerente, user_a vs user_b). O truque 'a.id < b.id' evita casar uma linha com ela mesma e produzir pares duplicados espelhados. Para hierarquias profundas (profundidade arbitrária), um CTE recursivo (veja a seção CTE) é a ferramenta certa — self joins só tratam profundidades fixas.

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-tabela

Joins se encadeiam da esquerda para a direita; cada JOIN vê o resultado acumulado. Ao misturar joins LEFT e INNER, um INNER JOIN depois de um LEFT JOIN pode refiltrar NULLs para fora, derrotando o propósito do LEFT — ordene com cuidado. COUNT(DISTINCT col) evita contar duas vezes em joins um-para-muitos. Atenção à multiplicidade dos joins: juntar duas tabelas um-para-muitos pode produzir uma explosão de linhas (m × n linhas por pai).

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

Agregações e GROUP BY

Básico de GROUP BY

GROUP BY colapsa linhas que compartilham as colunas agrupadas numa linha por grupo. Toda coluna não agregada no SELECT deve aparecer no GROUP BY (SQLite é leniente e escolhe um valor arbitrário, mas não conte com isso). Agrupar por uma expressão (como strftime numa data) é ótimo para relatórios por intervalo de tempo. ORDER BY é aplicado depois do GROUP BY — use-o para ordenar 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;

Funções de Agregação

COUNT(*) conta linhas; COUNT(col) conta valores não NULL; COUNT(DISTINCT col) conta não-NULLs únicos. AVG/SUM ignoram NULLs. GROUP_CONCAT une valores numa string (separador vírgula por padrão). TOTAL() sempre retorna um REAL (0.0 para entrada vazia), diferentemente de SUM(), que retorna NULL para entrada vazia. A cláusula FILTER (3.30+) é mais limpa que CASE para agregação condicional e pode ser mais 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 linhas de entrada antes do agrupamento; HAVING filtra grupos depois da agregação. Essa ordem importa: um WHERE sobre uma agregação é ilegal. SQLite permite referências a apelidos no HAVING (e GROUP BY), por legibilidade, mas isso é não padrão. O padrão 'group by X having count(*) > 1' é a consulta clássica de acha-duplicatas.

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 é a função de agregação de strings do SQLite (chamada STRING_AGG ou LISTAGG em outros lugares). O separador padrão é vírgula; especifique um personalizado como segundo argumento. ORDER BY dentro da agregação (3.44+) controla a ordem de concatenação. DISTINCT deduplica antes de unir. O resultado tem limite de 1GB; para grupos enormes, busque as linhas e agregue na sua aplicação.

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;

Pivotando Dados

SQLite não tem PIVOT nativo; emule com SUM(CASE WHEN ...) ou a cláusula FILTER mais limpa. Cada coluna pivotada testa um valor do atributo de agrupamento. Este padrão transforma dados longos (normalizados) em relatórios largos (cruzados). Para pivotar totalmente dinâmico (categorias desconhecidas), você precisa construir a string SQL no código da aplicação — SQL não gera colunas em 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

Uma subconsulta escalar retorna exatamente uma linha e uma coluna; pode aparecer em qualquer lugar onde uma expressão de valor seja válida. Subconsultas correlacionadas referenciam a consulta externa e reexecutam por linha — poderosas, mas potencialmente lentas em tabelas grandes (considere um JOIN ou função de janela em vez disso). SQLite é boa em otimizar subconsultas correlacionadas simples em joins, mas sempre confira EXPLAIN QUERY PLAN nos caminhos quentes.

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 e NOT IN

Subconsultas IN são intuitivas, mas cuidado com NULLs: se o resultado da subconsulta contém um NULL, NOT IN avalia como NULL (tratado como falso) para toda linha, retornando um resultado vazio. Prefira NOT EXISTS para anti-joins com colunas anuláveis — é seguro contra NULL e frequentemente mais rápido. IN de múltiplas colunas é conciso para casar chaves compostas.

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 e NOT EXISTS

EXISTS testa a existência de linhas sem buscar dados — para na primeira correspondência, então é eficiente. NOT EXISTS é a forma segura contra NULL de achar linhas sem correspondência (diferente de NOT IN). Para melhor desempenho, garanta que exista um índice na coluna em que a subconsulta correlacionada se une (orders.user_id no exemplo). SELECT 1 é convenção; a lista de colunas é 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
);

Tabelas Derivadas (Subconsulta no FROM)

Uma subconsulta no FROM (tabela derivada) é uma forma poderosa de estagiar resultados intermediários, pré-agregar ou filtrar antes de unir. Tabelas derivadas precisam de apelido. SQLite materializa a subconsulta (escreve numa tabela temporária) na maioria dos casos — para uso repetido, um CTE (cláusula WITH) costuma ser mais claro e pode ser referenciado múltiplas vezes. SQLite não permite referências correlacionadas a uma subconsulta no 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 Não Correlacionada

Subconsultas não correlacionadas rodam uma vez e podem ser cacheadas; correlacionadas reexecutam por linha externa (use índices para mantê-las rápidas). SQLite carece de LATERAL/APPLY; para 'juntar' uma agregação por linha, pré-agregue numa tabela derivada e faça LEFT JOIN dela. Quando uma subconsulta correlacionada performa mal, reescrevê-la como JOIN com GROUP BY ou função de janela é a correção usual.

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

Criando Índices

Índices aceleram leituras, mas retardam escritas (cada INSERT/UPDATE/DELETE atualiza cada um dos índices). A ordem das colunas importa: um índice composto em (a, b) ajuda WHERE a=? e WHERE a=? AND b=?, mas NÃO WHERE b=? sozinho (regra do prefixo mais à esquerda). Índices únicos impõem restrições. SQLite guarda índices como B-trees. Descarte índices não usados — custam espaço e desempenho de escrita.

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 Parciais

Índices parciais indexam só as linhas que casam, então são menores e mais rápidos de manter — ideais quando as consultas sempre filtram por um predicado estável ('active = 1', 'deleted_at IS NULL'). O WHERE da consulta precisa casar com o WHERE do índice (ou ser mais estrito) para que o otimizador o use. Índices parciais únicos impõem unicidade só no subconjunto — perfeito para restrições de 'uma linha ativa por pai'.

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 Expressão

Índices de expressão indexam o resultado de uma função ou expressão, habilitando buscas rápidas em valores transformados — mas a consulta deve usar exatamente a mesma expressão. Usos comuns: busca sem diferenciar caixa (LOWER), agrupamento por data (strftime) e extração de campos JSON (json_extract). Só funções determinísticas (sem RANDOM() ou NOW()). Índices de expressão são poderosos, mas tornam mudanças de schema mais delicadas.

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 Cobertores e WITHOUT ROWID

Um índice cobertor inclui toda coluna que a consulta lê, então o SQLite satisfaz a consulta só pelo índice (sem busca na tabela). Tabelas WITHOUT ROWID fazem do PRIMARY KEY um índice clusterizado — as linhas são guardadas ordenadas pela chave, ideal para cargas chave/valor e varreduras de intervalo na PK. Trocas: sem AUTOINCREMENT, sem apelidamento de rowid, atualizações um pouco mais complexas. Faça benchmark antes de adotar amplamente.

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

Inspeção de Índices

EXPLAIN QUERY PLAN é a ferramenta essencial — 'USING INDEX' significa que o índice é usado; 'SCAN' significa varredura completa da tabela (geralmente ruim). sqlite_stat1 (povoada por ANALYZE) guarda estatísticas de cardinalidade que o planejador usa para escolher índices. REINDEX reconstrói um índice inchado ou corrompido sem reconstruir a tabela. Rode ANALYZE após grandes cargas de dados, para que o planejador tenha estatísticas frescas para escolher bons planos.

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

Views

Criando Views

Views são tabelas virtuais definidas por uma consulta salva — não guardam dados (exceto views temporárias), reexecutam o SELECT subjacente a cada vez. Use-as para simplificar consultas complexas, impor padrões consistentes de acesso ou abstrair mudanças de schema das aplicações. Views são somente leitura por padrão no SQLite (com restrições a views atualizáveis). DROP VIEW nunca afeta as tabelas subjacentes.

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;

Views Atualizáveis

SQLite permite INSERT/UPDATE/DELETE em views simples (tabela base única, sem JOIN/agregações/DISTINCT/GROUP BY). A operação é encaminhada à tabela subjacente. SQLite NÃO suporta WITH CHECK OPTION, então uma linha inserida através de uma view pode não satisfazer o WHERE da view e ficar invisível através dela depois. Para views complexas que precisam de escritas, use 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 em Views

Triggers INSTEAD OF interceptam escritas numa view e rodam sua lógica personalizada, tornando qualquer view atualizável. É a forma padrão de tornar graváveis views complexas (multi-tabela, agregadas). NEW e OLD referem-se às linhas de entrada e existentes. Triggers INSTEAD OF só disparam em views, não em tabelas base. Use-os para implementar lógica de negócio encapsulada ou esconder um schema normalizado atrás de uma view 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);

Views Temporárias

Views temporárias vivem só pela conexão atual de banco e são descartadas automaticamente ao desconectar — perfeitas para estagiar consultas ad hoc complexas sem poluir o schema compartilhado. São guardadas num schema temp separado, então outras conexões (mesmo ao mesmo arquivo) não as veem. Views TEMP podem referenciar tabelas TEMP, o que é útil para pipelines de ETL com escopo de sessão.

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

Views Materializadas (Emuladas)

SQLite não tem view materializada nativa, mas uma tabela comum renovada num cronograma alcança o mesmo. Para renovação atômica, construa uma tabela nova numa transação e renomeie-a — leitores veem a versão antiga até o commit, depois a nova. Adicione índices depois da reconstrução. Views materializadas trocam custo de escrita/armazenamento por velocidade de leitura — ideais para agregações caras consultadas com frequência.

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

Triggers BEFORE rodam antes da linha ser escrita — use-os para validar (RAISE ABORT) ou transformar valores NEW. Triggers AFTER rodam depois que a linha é confirmada — use-os para auditoria, efeitos em cascata ou desnormalização. FOR EACH ROW é a única opção (não há triggers no nível de instrução). NEW guarda a linha de entrada (INSERT/UPDATE); OLD guarda a linha anterior (UPDATE/DELETE). A cláusula WHEN dispara condicionalmente o trigger.

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 Efeitos em Cascata

Triggers podem manter contadores desnormalizados, tabelas de resumo e timestamps updated_at automaticamente. Cuidado: cadeias recursivas de triggers são possíveis (um trigger atualizando a própria tabela), então use PRAGMA recursive_triggers para controlar (padrão OFF, por compatibilidade retroativa). A ordem dos triggers para múltiplos triggers no mesmo evento segue a ordem de criação; não há prioridade BEFORE/AFTER entre triggers do mesmo timing.

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 (Views)

Triggers INSTEAD OF rodam em vez do INSERT/UPDATE/DELETE original numa view, permitindo rotear escritas a uma ou mais tabelas subjacentes. É a forma canônica de tornar graváveis views complexas. Disparam por linha da view. Só triggers INSTEAD OF podem ser definidos em views; triggers BEFORE/AFTER exigem tabelas base. Úteis para camadas de abstração estilo API sobre um schema 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 e Tratamento de Erros

RAISE controla como uma falha de trigger se propaga: ABORT (o mais comum) cancela a instrução e desfaz suas mudanças, ROLLBACK desfaz a transação inteira, FAIL aborta a instrução, mas mantém instruções anteriores bem-sucedidas na transação, e IGNORE pula só a linha ofensora. RAISE(IGNORE) é uma forma elegante de descartar silenciosamente linhas inválidas durante cargas em massa.

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;

Gerenciando Triggers

Triggers são guardados em sqlite_master (ou temp.sqlite_master para triggers TEMP). DROP TRIGGER os remove; não há ALTER TRIGGER — descarte e recrie. Triggers TEMP são locais da conexão e desaparecem ao desconectar, úteis para depuração. Não há forma embutida de desativar um trigger temporariamente, exceto descartá-lo; para execução condicional, use a cláusula WHEN. Múltiplos triggers no mesmo evento disparam em ordem de criação, dentro do seu timing.

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

Configurações PRAGMA

Básico de PRAGMA

PRAGMA é a interface de configuração proprietária do SQLite — as configurações controlam journaling, cache, chaves estrangeiras, integridade e o planejador de consultas. As configurações têm escopo de sessão (por conexão), a menos que os docs digam o contrário; defini-las dentro de uma transação pode ser ignorado. Alguns PRAGMAs (journal_mode, page_size) só têm efeito fora de transações ou antes da tabela ser criada. Use PRAGMA compile_options para ver o que seu build suporta.

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.

Chaves Estrangeiras

A imposição de chaves estrangeiras está OFF por padrão no SQLite — uma pegadinha notória para quem vem de outros bancos. Você precisa habilitá-la em cada conexão com PRAGMA foreign_keys = ON; não pode ser definida dentro de uma transação. Essa configuração existe porque schemas antigos (pré-suporte a FK) quebrariam de outra forma. Sempre habilite em produção. defer_foreign_keys ajuda ao importar dados em ordem que viola dependências.

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 de Journal e WAL

WAL (Write-Ahead Logging) é o modo de journal recomendado para a maioria dos apps: leitores e escritores não se bloqueiam mutuamente, e a durabilidade é boa. Cria arquivos sidecar -wal e -shm junto ao banco principal. WAL é persistente por arquivo de banco (defina uma vez). Use wal_checkpoint(TRUNCATE) para dobrar o conteúdo do WAL de volta ao arquivo principal e recuperar espaço. Evite WAL em filesystems de rede — exige memória compartilhada.

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 e Durabilidade

synchronous controla quão agressivamente SQLite chama fsync. FULL é o mais seguro (cada commit faz fsync); NORMAL é o valor recomendado ao usar WAL (fsync no checkpoint, não por commit, com risco de corrupção insignificante na prática); OFF arrisca corrupção do banco em queda de energia — só para dados realmente descartáveis ou caches reconstruíveis. page_size deve ser definido antes do schema ser criado; mudar depois 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;

Cache, Memória e Temp Store

cache_size é o cache de páginas em memória — maior é geralmente melhor para cargas de muita leitura; valores negativos são em KB, positivos em páginas. temp_store = MEMORY move resultados intermediários (ordenações, tabelas temporárias) para a RAM, acelerando agregações grandes. mmap_size habilita I/O mapeado em memória para leituras, o que pode acelerar bancos grandes somente leitura. Ajuste conforme o orçamento de memória e o padrão de acesso do seu 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;

Integridade e Info de Schema

integrity_check valida toda a estrutura e o conteúdo do banco (lento, mas completo); quick_check pula a validação de conteúdo e é muito mais rápido. Rode integrity_check durante backups ou após quedas. table_info reporta metadados de colunas (use table_xinfo para colunas ocultas como o rowid do FTS5). index_list e index_info revelam quais índices existem e suas colunas. database_list mostra os bancos anexados.

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

Funções de Data e Hora

date / time / datetime

SQLite não tem tipo DATE nativo; datas são TEXT em ISO-8601 ('YYYY-MM-DD HH:MM:SS'). As funções de data/hora aceitam muitos formatos de entrada (ISO, barras, nomes de meses, timestamps Unix via o modificador 'unixepoch'). 'now' é avaliado uma vez por instrução, não por linha — toda linha num UPDATE de múltiplas linhas recebe o mesmo timestamp, o que geralmente é o que você quer. Guarde em UTC, formate para exibição com 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');

Formatação com strftime

strftime é o formatador de datas universal — os códigos de formato espelham o strftime do C. Use-o para extrair partes de uma data (ano, mês, dia da semana) para agrupamento ou renderizar um formato de exibição personalizado. Para agrupar por mês, strftime('%Y-%m', created) dá 'YYYY-MM', que ordena e agrupa naturalmente. SQLite não tem DAYNAME()/MONTHNAME(); combine strftime('%w', ...) com um CASE se precisar de nomes de dias da semana.

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)

Modificadores se encadeiam da esquerda para a direita para deslocar ou alinhar datas. Intervalos são '+N unidades' / '-N unidades' (unidades: days, hours, minutes, seconds, months, years). 'start of month/year/day' ajusta à fronteira do período — perfeito para relatórios mensais. 'weekday N' retorna a data do próximo dia da semana N (hoje, se hoje casar). Combinar 'start of month', '+1 month', '-1 day' dá o último dia do mês atual — um idioma clássico.

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

Unix Time e Dia Juliano

Timestamps Unix são inteiros/segundos desde a época; unixepoch() (3.38+) retorna um inteiro diretamente. O dia juliano é uma contagem fracionária contínua de dias — subtrair dois valores julianday dá o tempo decorrido em dias (multiplique por 86400 para segundos). Para cálculos de idade, dividir por 365.25 contabiliza aproximadamente anos bissextos; para precisão legal, compare componentes de ano/mês/dia explicitamente.

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;

Fusos Horários e localtime

SQLite não tem banco de fusos horários — 'now' é UTC, e os modificadores 'localtime'/'utc' dependem da configuração de fuso do SO hospedeiro. O padrão recomendado: sempre guarde timestamps em UTC (datetime('now') é UTC) e converta para a hora local só na exibição, com o modificador 'localtime'. Isso evita ambiguidade entre servidores em zonas diferentes e durante transições de horário de verão. Nunca guarde 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

Funções de String

Comprimento e Substring

length() conta caracteres para TEXT e bytes para BLOB — para comprimento de bytes UTF-8, faça cast para BLOB primeiro. substr() é indexado a partir de 1; início negativo conta do fim. SQLite não tem LPAD/RPAD nativos — emule com concatenação de string e substr. Cuidado com a confusão byte-vs-caractere ao truncar texto multibyte: substr opera em caracteres, então é seguro, mas fatiar no nível de bytes não é.

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'

Caixa e Aparo (Trim)

upper()/lower() afetam só letras ASCII por padrão — caracteres não ASCII ficam inalterados, a menos que ICU esteja carregada. trim() remove espaços iniciais/finais por padrão, ou caracteres específicos dados como segundo argumento. replace() faz substituição literal de substring (sem regex nem padrões). Não há TRANSLATE() nativo para substituição de conjunto de caracteres — encadeie chamadas replace() ou use uma função 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'

Concatenação e printf

O operador || é o concatenador padrão do SQL, mas propaga NULL (NULL || x = NULL) — envolva com COALESCE quando NULLs forem possíveis. printf()/format() traz formatação estilo C e é a forma mais limpa de construir strings com preenchimento, formatação de números e separadores de vírgula. quote() retorna uma string devidamente entre aspas e escapada para embutir em SQL — útil ao 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'''

Divisão e Busca

instr() acha a primeira ocorrência de uma substring (indexada a partir de 1; 0 se não achar). SQLite não tem SPLIT_PART nativo — emule com substr + instr ou um CTE recursivo. O idioma de 'contar ocorrências' subtrai comprimentos antes/depois de remover o delimitador. char() constrói uma string a partir de pontos de código; unicode() retorna o ponto de código do primeiro caractere. Para divisão completa de texto, carregue um array JSON via 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 / Collations

LIKE não diferencia caixa para ASCII por padrão (surpresa frequente); use COLLATE BINARY para diferenciar caixa. GLOB é sempre sensível a caixa e suporta classes de caracteres estilo shell. NOCASE é uma collation embutida para comparação sem diferenciar caixa; aplique-a ao índice de uma coluna, para que consultas sem diferenciar caixa usem o índice. Para caixa não ASCII, carregue a extensão ICU ou guarde uma coluna de cópia em minúsculas com í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

Funções JSON

Extraindo Valores

json_extract (e os operadores -> / ->>, 3.38+) são os cavos de batalha para ler JSON. $ é a raiz; $.chave acessa uma propriedade de objeto; $.arr[i] acessa um elemento de array (indexado a partir de 0). ->> retorna um escalar (texto/inteiro/real), -> retorna JSON (então '$.b' num array dá o array como JSON). Guarde campos JSON consultados com frequência como colunas comuns, ou adicione um índice de expressão em json_extract, pelo desempenho.

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'

Construindo JSON

json_object e json_array constroem JSON a partir de valores SQL (NULLs viram JSON null, não são omitidos). json_group_array e json_group_object (3.38+) são funções de agregação que constroem JSON a partir das linhas da consulta — inestimáveis para produzir APIs JSON direto do SQL. Aninhe construtores para construir documentos complexos. Use json_quote para embutir com segurança uma string como literal de string 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"'

Modificando JSON

json_set/insert/replace distinguem comportamento de inserir-vs-atualizar; json_remove deleta caminhos. Estes retornam NOVOS valores JSON — o JSON do SQLite é imutável, então para persistir mudanças você faz UPDATE na coluna com o resultado: UPDATE t SET data = json_set(data, '$.x', 1) WHERE id = 5. json_patch (RFC 7396) mescla objetos; valores null no patch deletam chaves. Para documentos grandes, considere guardar campos quentes como colunas 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}

Consultando Arrays JSON

json_each e json_tree são funções virtuais que retornam tabelas e expandem JSON em linhas. json_each trata um único array/objeto; json_tree percorre recursivamente toda a estrutura, expondo caminhos fullkey. Isso desbloqueia operações baseadas em conjunto sobre arrays JSON — filtrar, unir e agregar. Para consultar arrays de objetos numa coluna, use um 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

Indexação e Validação de JSON

Indexe expressões json_extract para buscas rápidas de campos JSON — a consulta deve usar a expressão idêntica para se beneficiar. json_valid() retorna 1/0; use-a numa restrição CHECK para impor colunas só-JSON. json_type() reporta o tipo JSON num caminho ('object', 'array', 'integer', 'real', 'string', 'boolean', 'null'). Para cargas pesadas de JSON, desnormalize campos quentes em colunas comuns indexadas e mantenha o JSON para a cauda flexível.

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

Funções de Janela

Básico da Cláusula OVER

Funções de janela (SQLite 3.25+) computam sobre um conjunto de linhas relacionadas à linha atual, sem colapsá-las como o GROUP BY faz. OVER () significa 'todas as linhas'; OVER (ORDER BY ...) define a ordenação para rankings. ROW_NUMBER é único; RANK deixa lacunas após empates; DENSE_RANK não. Funções de janela aparecem em SELECT e ORDER BY, não em WHERE — filtre com uma consulta externa ou 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 as linhas em grupos para a função de janela, como GROUP BY, mas sem colapsar — cada linha retém sua identidade enquanto recebe uma agregação por grupo. O padrão 'top N por grupo' (ROW_NUMBER + WHERE externo rn <= N) é a solução canônica para 'os 3 pedidos por cliente', 'as 5 últimas postagens por autor' etc. Tipicamente muito mais rápido que 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;

Especificações de Frame

Um frame define quais linhas a função de janela vê, relativamente à linha atual. UNBOUNDED PRECEDING..CURRENT ROW dá um total acumulado. N PRECEDING..N FOLLOWING dá uma janela deslizante (média móvel). O frame padrão com ORDER BY usa RANGE, que inclui linhas pares (mesmo valor de ORDER BY); ROWS é contagem estrita de linhas. Mal entender frames vs ORDER BY é fonte comum de análises incorretas.

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 e LEAD

LAG e LEAD acessam outras linhas relativas à atual — essenciais para análise de séries temporais (deltas dia a dia, detecção de sequências). O terceiro argumento opcional é um padrão para linhas fora do intervalo. FIRST_VALUE/LAST_VALUE retornam valores das bordas do frame; note que LAST_VALUE precisa de um frame UNBOUNDED FOLLOWING explícito, ou retorna o valor da linha atual (pegadinha clássica).

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 e Percentis

NTILE(n) divide linhas ordenadas em n buckets aproximadamente iguais — use 4 para quartis, 100 para percentis, 10 para decis. CUME_DIST é a fração de linhas com valor <= à linha atual; PERCENT_RANK é o rank relativo como fração 0..1. São os blocos de construção de consultas estatísticas/analíticas. Como todas as funções de janela, não colapsam linhas — envolva numa 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

Um CTE (Common Table Expression) definido com WITH é uma subconsulta nomeada e com escopo — melhora a legibilidade e pode ser referenciado múltiplas vezes na mesma instrução. SQLite tipicamente materializa CTEs (os escreve numa tabela temporária), então o desempenho é similar ao de uma tabela derivada. Use CTEs para quebrar consultas complexas em estágios, pré-agregar ou evitar repetir subconsultas. São SQL padrão e portáteis.

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).

Múltiplos CTEs

Múltiplos CTEs separados por vírgula podem referenciar CTEs anteriores no mesmo WITH — ideal para pipelines de dados em estágios numa única instrução. Cada CTE é um passo lógico; ler de cima para baixo espelha o fluxo de dados. Isso é muito mais sustentável que subconsultas aninhadas. SQLite materializa cada CTE; para resultados intermediários muito grandes, considere tabelas temporárias entre instruções em vez de uma cadeia gigante de CTE.

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

CTEs recursivos têm uma âncora (caso base) e um membro recursivo unidos por UNION (dedup) ou UNION ALL. São a forma padrão do SQL de percorrer árvores (organogramas, árvores de categorias, comentários encadeados) e grafos. SQLite limita a profundidade de recursão (padrão 1000 via limites do SQLITE_MAX_TRIGGER_DEPTH). O truque de 'gerar série' é inestimável, já que SQLite carece de GENERATE_SERIES (embora a extensão series exista).

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 Hierárquicas

CTEs recursivos brilham em tabelas autorreferentes. O primeiro exemplo constrói um caminho delimitado por barras da raiz a cada nó — útil para breadcrumbs. O segundo acha cada um dos descendentes de um nó (subárvore). Use UNION (não UNION ALL) se ciclos forem possíveis, para prevenir loops infinitos; caso contrário, UNION ALL é mais rápido. Para árvores muito profundas, monitore o desempenho — CTEs recursivos podem ser lentos sem índices adequados na coluna 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);

Otimização de CTE

SQLite 3.35+ suporta dicas MATERIALIZED / NOT MATERIALIZED para controlar a avaliação de CTEs. MATERIALIZED força escrita em tabela temporária (bom para CTEs caros referenciados múltiplas vezes); NOT MATERIALIZED embute o CTE para que o otimizador possa empurrar predicados para baixo (bom para CTEs baratos). O planejador geralmente escolhe bem sozinho; use dicas só quando EXPLAIN QUERY PLAN mostrar um plano ruim. CTEs recursivos são sempre materializados.

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

Backup e Restauração

.dump (Backup em Texto)

.dump exporta o banco como instruções SQL INSERT simples — legível por humanos, comparável por diff e portável entre versões e arquiteturas do SQLite. É o formato de backup mais compatível. Restaurar reproduz o SQL num banco novo. --nosys pula sqlite_sequence e outras tabelas internas. Para bancos grandes, .dump é lento e produz arquivos enormes; use .backup (cópia binária) ou a API Online Backup.

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 (Cópia Binária)

.backup executa uma cópia binária online segura usando a API Online Backup do SQLite — funciona mesmo com escritores ativos, produzindo um snapshot transacionalmente consistente. Esta é a forma recomendada de fazer backup de um banco em execução. A saída é um arquivo SQLite válido, então restaurar é só copiar o arquivo. Diferente de .dump, o formato binário é compatível entre versões e arquiteturas, mas não legível 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

API Online Backup

A API Online Backup (sqlite3_backup_*) é a técnica de backup padrão-ouro: copia um banco vivo página por página, tratando escritas concorrentes corretamente e produzindo um snapshot consistente. O sqlite3.Connection.backup() do Python e o .backup() do better-sqlite3 do Node o embrulham. Funciona em modo WAL. Cópia de arquivo crua pode produzir um backup corrompido se uma escrita estiver em andamento — só use cópia de arquivo num banco parado ou com snapshots no nível do arquivo.

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();

Anexando Bancos de Dados

ATTACH DATABASE traz até 10 arquivos de banco numa única conexão como schemas nomeados (main, temp, mais os anexados). Você pode consultar, unir e copiar dados entre eles com nomes qualificados por schema (main.users, archive.orders). É a base para arquivamento (mover linhas velhas a um arquivo de arquivo), sharding e fluxos de mesclagem. DETACH fecha o schema, mas deixa o arquivo intacto. Transações entre bancos são atômicas em 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).

Recuperação e Corrupção

integrity_check detecta corrupção estrutural; .recover (3.29+) é a melhor ferramenta para salvar dados de um arquivo corrompido — extrai linhas legíveis e pula páginas ruins, produzindo SQL que você pode conectá-lo por pipe a um banco novo. Se .recover falhar, recue para .dump e aceite perda parcial. A maioria das corrupções vem de operações de arquivo inseguras (copiar um banco vivo, filesystems de rede, queda de energia com synchronous=OFF). Sempre faça backup antes de tentativas de recuperação.

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

Importação e Exportação

Importação CSV

.import lê CSV numa tabela; se a tabela não existir, SQLite a cria com colunas todas-TEXT (frequentemente não o que você quer — crie a tabela primeiro com tipos adequados). --skip N pula linhas de cabeçalho. .mode csv configura o parser para tratar campos entre aspas com vírgulas e quebras de linha embutidas. Para importações grandes, envolva em BEGIN/COMMIT e PRAGMA synchronous=OFF (temporariamente) para aceleração de 10x+. Desative índices durante cargas em massa e recrie depois.

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

Exportação CSV

.mode csv com .headers on produz um CSV padrão com linha de cabeçalho — conecte por pipe a um arquivo com .output. As flags de shell -header -csv mais redirecionamento de saída fazem isso numa linha, sem entrar na CLI. Para TSV use .mode list com .separator "\t". Para exportações grandes, isto é baseado em stream e eficiente em memória. Para exportar múltiplas tabelas, mude o .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

Exportação JSON

.mode json emite um objeto JSON por linha (sem array envolvente). Para um array JSON apropriado, use json_group_array(json_object(...)) — isso dá controle total sobre nomes de campos e aninhamento, e funciona para arrays aninhados via subconsultas correlacionadas. É uma forma poderosa de construir APIs JSON direto do SQL sem ORM. Para conjuntos de resultados grandes, transmita o .mode json linha a linha em vez de construir um array gigante na memória.

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;

Importando JSON

SQLite não tem .import para JSON — carregue o arquivo como TEXT (via código do app ou .read com INSERTs) e depois analise com json_each/json_extract. json_each(array) expande um array em linhas para inserção baseada em conjunto. Para arquivos JSON grandes, analise em stream na sua aplicação (não carregue o arquivo inteiro na memória). Valide com json_valid() durante a carga e use uma restrição CHECK em colunas JSON para integridade contínua.

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 e Outros Formatos

Para Excel, exporte CSV (Excel abre direto); .mode excel (3.36+) adiciona um BOM para que UTF-8 renderize corretamente. .mode markdown produz marcação de tabela colável para docs. .mode insert gera instruções INSERT — ótimo para semear bancos de teste ou migrar dados entre schemas. .mode quote cita cada valor, útil quando dados contêm vírgulas/quebras/aspas que podem confundir outras ferramentas. .mode table/box desenham tabelas ASCII para terminais.

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

Integração com Aplicações

Python (sqlite3)

O sqlite3 do Python vem na biblioteca padrão. SEMPRE use placeholders ? (ou :nome) — nunca f-strings nem % — para prevenir SQL injection e tratar escape corretamente. executemany é muito mais rápido que executar num loop para inserções em massa. Defina row_factory = sqlite3.Row para acesso por nome de coluna. Use gerenciadores de contexto (with conn:) para commit/rollback automáticos. Para concorrência, use uma conexão por thread (sqlite3 proíbe compartilhamento entre threads por padrão).

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 é síncrono (sem boilerplate de callback/Promise) e o driver SQLite mais rápido para Node.js. Instruções preparadas são reutilizadas — prepare uma vez, rode muitas. .transaction(fn) envolve a função em BEGIN/COMMIT (ou ROLLBACK em throw) e é dramaticamente mais rápido que auto-commit por instrução. Use placeholders ? para evitar SQL injection. Habilite WAL para concorrência. Para APIs baseadas em Promise, embrulhe as chamadas ou use sqlite/sqlite3.

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 Conexão e Modos

Nomes de arquivo URI (file:...) desbloqueiam modos somente leitura, shared-cache e em memória — passe uri=True no Python ou a string file: aos drivers Node. :memory: é por conexão por padrão; file:nome?mode=memory&cache=shared cria um banco em memória nomeado compartilhado entre conexões no mesmo processo (ótimo para testes). Defina um busy_timeout para que escritores esperando brevemente por travas não deem erro imediato. Sempre defina PRAGMA foreign_keys = ON por conexão.

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")

Transações e Concorrência

Transações agrupam instruções atomicamente — tudo-ou-nada. SQLite permite um único escritor por vez (trava de escrita no nível do banco), mas o modo WAL permite leitores concorrentes. No Python, o isolation_level padrão envolve DML em transações implícitas (commit necessário). Para controle explícito, defina isolation_level = None e emita BEGIN/COMMIT você mesmo. Use um busy_timeout (PRAGMA busy_timeout ou timeout da conexão) para que escritores esperando brevemente por uma trava aguardem em vez de lançar SQLITE_BUSY imediatamente.

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.

Instruções Preparadas e Segurança

Consultas parametrizadas (placeholders ?) são a prática de segurança mais importante — previnem SQL injection e tratam conversão de tipo/escape corretamente. NUNCA interpolate entrada de usuário em strings SQL. Placeholders funcionam só para valores; identificadores (nomes de tabela/coluna) não podem ser parametrizados — valide-os contra uma whitelist. Para LIKE, escape % e _ na entrada do usuário, para que usuários não injetem curingas. Isso vale em toda linguagem e driver.

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

Otimização de Desempenho

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN é a ferramenta de ajuste nº 1. 'USING INDEX' = bom; 'SCAN' (varredura completa da tabela) = geralmente ruim em tabelas grandes. Para joins, confira que as colunas de join estão indexadas e que o planejador não está varrendo a tabela maior. EXPLAIN (sem QUERY PLAN) mostra o bytecode do VDBE — raramente necessário, mas útil para depuração profunda. O comando experimental EXPERT pode sugerir índices (3.36+), ou use o comando de ponto .expert da 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 e ANALYZE

VACUUM reconstrói o arquivo, recuperando espaço de deletes e desfragmentando páginas — rode após deletes grandes ou periodicamente para bancos de muita escrita. VACUUM INTO (3.27+) escreve uma cópia limpa sem travar a fonte por muito tempo. ANALYZE atualiza as estatísticas de sqlite_stat1 que o planejador de consultas usa para escolher índices — rode após cargas em massa ou mudanças de schema/dados. auto_vacuum=INCREMENTAL recupera espaço incrementalmente sem um 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 em Massa

Para cargas em massa: envolva numa única transação (a maior aceleração — autocommit por linha é catastroficamente lento), desative temporariamente synchronous e journaling, descarte e recrie índices, e considere aumentar PRAGMA cache_size. O comando de ponto .import é mais rápido que instruções INSERT. Para cargas realmente enormes, considere o .backup de um banco pré-construído. Sempre restaure os PRAGMAs de segurança após carregar dados de produção.

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 (Busca de Texto Completo)

FTS5 é o motor de busca de texto completo do SQLite — indexa texto para consultas MATCH rápidas, muito mais rápidas que LIKE '%termo%', que varre toda linha. Suporta booleano (AND/OR/NOT), prefixo (termo*), frase ("...") e ranking por relevância. O padrão de conteúdo externo (content='posts') evita duplicar dados. Mantenha o índice FTS sincronizado com triggers (INSERT/UPDATE/DELETE na tabela base). FTS5 é ideal para busca-ao-digitar, autocomplete e busca 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

Armadilhas Comuns e Ajuste

A maioria dos problemas de desempenho do SQLite vem de um punhado de causas: índices ausentes em colunas WHERE/JOIN/ORDER BY, transações ausentes (autocommit por linha é a causa nº 1 de inserções em massa lentas), chaves estrangeiras silenciosamente desligadas, e SELECT * em tabelas largas. Habilite WAL e chaves estrangeiras em cada setup de conexão. Agrupe DELETEs grandes para evitar travas de escrita longas e espaços temporários enormes. Rode ANALYZE após mudanças de dados importantes, para que o planejador escolha bons planos.

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?