SQLite

O que é SQLite?

SQLite é um banco de dados relacional serverless e embarcado — toda a database fica em um único arquivo .db ou .sqlite. Não há servidor separado; a biblioteca se linka direto à aplicação.

Características

• Zero-configuration: sem instalação, sem processo de servidor
• Self-contained: banco inteiro em um arquivo (ou :memory:)
• ACID compliant: transações, rollback, durabilidade real
• Cross-platform: arquivo portável entre sistemas operacionais
• Amplamente testado: um dos softwares mais testados do mundo
• Sem tipos rígidos: usa type affinity (flexível, mas requer atenção)
• Tamanho: biblioteca de ~800KB

Quando usar SQLite vs PostgreSQL/MySQL

Regra de ouro: SQLite é excelente para reads. Para writes intensivos ou multi-processo, use PostgreSQL.

Instalação e CLI

# Linux (Ubuntu/Debian)
sudo apt install sqlite3

# macOS
brew install sqlite

# Windows
# Baixar sqlite3.exe de https://sqlite.org/download.html

# Verificar versão
sqlite3 --version

# Abrir / criar banco
sqlite3 meu-banco.db

# Executar SQL direto no shell
sqlite3 meu-banco.db "SELECT * FROM users LIMIT 5;"

# Executar arquivo SQL
sqlite3 meu-banco.db < schema.sql
sqlite3 meu-banco.db ".read schema.sql"

# Banco em memória (destruído ao fechar)
sqlite3 :memory:

Comandos Dot (CLI)

# Dentro do sqlite3 CLI:

# Configuração de saída
.mode column       # colunas alinhadas
.mode table        # tabela com bordas
.mode json         # saída JSON
.mode csv          # saída CSV
.mode markdown     # tabela markdown
.headers on        # exibir nomes das colunas

# Navegação
.tables            # listar todas as tabelas
.schema            # DDL de todas as tabelas
.schema users      # DDL de uma tabela específica
.indexes           # listar todos os índices
.indexes users     # índices de uma tabela

# Bancos
.databases         # listar bancos conectados
.open novo.db      # abrir/criar outro banco
.attach 'outro.db' AS extra  # anexar banco externo

# Import/Export
.output resultado.txt    # redirecionar saída para arquivo
.output stdout           # voltar para terminal
.import data.csv tabela  # importar CSV
.dump                    # exportar DDL + dados como SQL
.dump users              # exportar tabela específica
.read comandos.sql       # executar arquivo SQL

# Configurações
.timeout 5000      # timeout em ms para lock
.nullvalue NULL    # como exibir NULL
.separator ","     # separador para .mode list

# Análise e diagnóstico
.stats on          # estatísticas após queries
.eqp on            # explain query plan automático
EXPLAIN QUERY PLAN SELECT ...  # plano de execução manual

# Encerrar
.quit
.exit
Ctrl+D

DDL — Definição de Estrutura

CREATE TABLE

-- Tabela básica
CREATE TABLE IF NOT EXISTS usuarios (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    nome       TEXT    NOT NULL,
    email      TEXT    NOT NULL UNIQUE,
    idade      INTEGER CHECK (idade >= 0 AND idade <= 150),
    ativo      INTEGER NOT NULL DEFAULT 1,
    criado_em  TEXT    NOT NULL DEFAULT (datetime('now')),
    atualizado TEXT
);

-- Tabela com chave estrangeira
CREATE TABLE IF NOT EXISTS pedidos (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    usuario_id  INTEGER NOT NULL,
    valor       REAL    NOT NULL CHECK (valor > 0),
    status      TEXT    NOT NULL DEFAULT 'pendente'
                        CHECK (status IN ('pendente', 'pago', 'cancelado')),
    criado_em   TEXT    NOT NULL DEFAULT (datetime('now')),

    FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- Tabela com chave primária composta
CREATE TABLE IF NOT EXISTS usuario_roles (
    usuario_id INTEGER NOT NULL,
    role_id    INTEGER NOT NULL,
    criado_em  TEXT    DEFAULT (datetime('now')),

    PRIMARY KEY (usuario_id, role_id),
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id)    REFERENCES roles(id)    ON DELETE CASCADE
);

-- Tabela temporária (destruída ao encerrar conexão)
CREATE TEMPORARY TABLE temp_resultados (
    id    INTEGER,
    valor REAL
);

Tipos de Dados e Type Affinity

-- SQLite usa 5 storage classes:
-- NULL, INTEGER (1,2,3,4,6,8 bytes), REAL (8 bytes IEEE float),
-- TEXT (UTF-8/16/21), BLOB (bytes brutos)

-- Type Affinity: SQLite converte baseado no nome do tipo
-- TEXT affinity: TEXT, CHAR, VARCHAR, CLOB
-- NUMERIC affinity: NUMERIC, DECIMAL, BOOLEAN, DATE, DATETIME
-- INTEGER affinity: INT, INTEGER, TINYINT, SMALLINT, BIGINT
-- REAL affinity: REAL, FLOAT, DOUBLE
-- BLOB / nenhuma: qualquer outro nome (ex: BLOB, VECTOR)

-- Exemplos práticos:
CREATE TABLE exemplos (
    -- Booleano: armazena 0 ou 1
    ativo       INTEGER NOT NULL DEFAULT 1,

    -- Data/hora: armazene como TEXT ISO-8601 ou INTEGER (Unix timestamp)
    criado_em   TEXT DEFAULT (datetime('now')),          -- '2024-01-15 14:30:00'
    criado_ts   INTEGER DEFAULT (strftime('%s', 'now')), -- 1705326600

    -- JSON: armazena como TEXT, acessa com JSON1
    metadata    TEXT DEFAULT '{}',

    -- UUID: armazene como TEXT
    uuid        TEXT DEFAULT (lower(hex(randomblob(16)))),

    -- Dinheiro: use INTEGER em centavos para evitar float issues
    preco_cents INTEGER NOT NULL DEFAULT 0
);

-- STRICT mode (SQLite 3.37+): tipos rigorosos como PostgreSQL
CREATE TABLE estrita (
    id    INTEGER PRIMARY KEY,
    nome  TEXT    NOT NULL,
    valor REAL    NOT NULL
) STRICT;
-- Agora valores serão verificados estritamente pelo tipo declarado

ALTER TABLE e DROP

-- SQLite tem suporte limitado a ALTER TABLE!
-- Apenas estas operações são suportadas:

-- Adicionar coluna
ALTER TABLE usuarios ADD COLUMN telefone TEXT;
ALTER TABLE usuarios ADD COLUMN ultimo_login TEXT DEFAULT NULL;

-- Renomear tabela
ALTER TABLE usuarios RENAME TO clientes;

-- Renomear coluna (SQLite 3.25+)
ALTER TABLE clientes RENAME COLUMN nome TO nome_completo;

-- Dropar coluna (SQLite 3.35+)
ALTER TABLE clientes DROP COLUMN telefone;

-- CUIDADO: não é possível:
-- - Mudar tipo de coluna
-- - Remover PRIMARY KEY / UNIQUE / CHECK existente
-- - Adicionar FK depois de criar a tabela
-- Solução: recriar a tabela
BEGIN;
CREATE TABLE clientes_new (/* nova definição */);
INSERT INTO clientes_new SELECT /* mapear colunas */ FROM clientes;
DROP TABLE clientes;
ALTER TABLE clientes_new RENAME TO clientes;
COMMIT;

-- Remover tabela
DROP TABLE IF EXISTS tabela_antiga;

-- Remover view
DROP VIEW IF EXISTS vw_usuarios_ativos;

-- Renomear índice: não tem — dropar e recriar

DML — Manipulação de Dados

INSERT

-- Insert simples
INSERT INTO usuarios (nome, email, idade)
VALUES ('Alice', 'alice@exemplo.com', 30);

-- Insert com returning (SQLite 3.35+)
INSERT INTO usuarios (nome, email)
VALUES ('Bob', 'bob@exemplo.com')
RETURNING id, nome, criado_em;

-- Insert de múltiplas linhas
INSERT INTO usuarios (nome, email, idade) VALUES
    ('Carol', 'carol@exemplo.com', 25),
    ('Dave',  'dave@exemplo.com',  35),
    ('Eve',   'eve@exemplo.com',   28);

-- Insert a partir de SELECT
INSERT INTO usuarios_arquivo (nome, email)
SELECT nome, email FROM usuarios
WHERE ativo = 0;

-- UPSERT — insert ou atualiza em conflito (SQLite 3.24+)
INSERT INTO usuarios (id, nome, email)
VALUES (1, 'Alice Atualizada', 'alice.nova@exemplo.com')
ON CONFLICT (id) DO UPDATE SET
    nome       = excluded.nome,
    email      = excluded.email,
    atualizado = datetime('now');

-- INSERT OR REPLACE — deleta e recria em conflito
INSERT OR REPLACE INTO configuracoes (chave, valor)
VALUES ('tema', 'dark');

-- INSERT OR IGNORE — ignora em conflito
INSERT OR IGNORE INTO categorias (nome)
VALUES ('Tecnologia');

-- Verificar id da última linha inserida
SELECT last_insert_rowid();

UPDATE

-- Update básico
UPDATE usuarios
SET nome = 'Alice Souza',
    atualizado = datetime('now')
WHERE id = 1;

-- Update com subquery
UPDATE pedidos
SET status = 'cancelado'
WHERE usuario_id IN (
    SELECT id FROM usuarios WHERE ativo = 0
);

-- Update com RETURNING (SQLite 3.35+)
UPDATE usuarios
SET ativo = 0
WHERE ultimo_login < datetime('now', '-1 year')
RETURNING id, nome, email;

-- Update com expressão condicional
UPDATE produtos
SET preco_cents = CASE
    WHEN categoria = 'premium' THEN ROUND(preco_cents * 1.1)
    WHEN categoria = 'básico'  THEN ROUND(preco_cents * 1.05)
    ELSE preco_cents
END;

-- Update limitado (requer compile option SQLITE_ENABLE_UPDATE_DELETE_LIMIT)
-- ou usar subquery
UPDATE pedidos SET status = 'expirado'
WHERE id IN (
    SELECT id FROM pedidos
    WHERE status = 'pendente'
    ORDER BY criado_em
    LIMIT 100
);

DELETE

-- Delete simples
DELETE FROM usuarios WHERE id = 42;

-- Delete com subquery
DELETE FROM pedidos
WHERE usuario_id IN (
    SELECT id FROM usuarios WHERE ativo = 0
);

-- Delete com RETURNING (SQLite 3.35+)
DELETE FROM sessoes
WHERE expira_em < datetime('now')
RETURNING id, usuario_id;

-- Apagar tudo (mais rápido que DELETE sem WHERE)
DELETE FROM logs;
-- ou
-- TRUNCATE não existe em SQLite, mas DELETE FROM tabela sem WHERE faz o mesmo
-- Para realmente truncar (sem passar pelo log de transação):
-- DROP TABLE + CREATE TABLE (ou usar VACUUM)

-- Apagar registros duplicados (manter o mais recente)
DELETE FROM logs
WHERE id NOT IN (
    SELECT MAX(id)
    FROM logs
    GROUP BY usuario_id, acao
);

SELECT Completo

Fundamentos

-- Básico com filtros
SELECT id, nome, email
FROM usuarios
WHERE ativo = 1
  AND idade BETWEEN 18 AND 65
  AND nome LIKE 'A%'         -- começa com A
  AND email NOT LIKE '%spam%'
  AND telefone IS NOT NULL
ORDER BY nome ASC, criado_em DESC
LIMIT 10 OFFSET 20;          -- paginação: página 3 com 10 por página

-- Aliases
SELECT
    u.id,
    u.nome AS nome_completo,
    strftime('%Y', u.criado_em) AS ano_cadastro,
    COUNT(p.id) AS total_pedidos
FROM usuarios u
LEFT JOIN pedidos p ON p.usuario_id = u.id
GROUP BY u.id
HAVING COUNT(p.id) > 0
ORDER BY total_pedidos DESC;

-- DISTINCT
SELECT DISTINCT status FROM pedidos;

-- Funções de texto
SELECT
    upper(nome),           -- MAIÚSCULAS
    lower(email),          -- minúsculas
    trim(nome),            -- remover espaços
    substr(nome, 1, 10),   -- substring
    length(nome),          -- comprimento
    replace(email, '@', ' at '),
    instr(email, '@'),     -- posição do @
    printf('R$ %.2f', valor / 100.0)  -- formatação
FROM usuarios;

-- Funções numéricas
SELECT
    abs(-42),
    round(3.14159, 2),     -- 3.14
    ceil(1.1),             -- 2.0
    floor(1.9),            -- 1.0
    max(10, 20, 5),        -- 20
    min(10, 20, 5)         -- 5
FROM (SELECT 1);

-- Funções de data
SELECT
    datetime('now'),                           -- '2024-01-15 14:30:00' (UTC)
    datetime('now', 'localtime'),              -- hora local
    date('now'),                               -- '2024-01-15'
    time('now'),                               -- '14:30:00'
    strftime('%d/%m/%Y', '2024-01-15'),        -- '15/01/2024'
    strftime('%s', 'now'),                     -- Unix timestamp
    datetime('now', '+7 days'),                -- uma semana à frente
    datetime('now', '-1 month'),               -- um mês atrás
    julianday('now') - julianday('2024-01-01') -- dias desde data
FROM (SELECT 1);

JOINs

-- INNER JOIN: apenas linhas com correspondência em ambas as tabelas
SELECT u.nome, p.id AS pedido_id, p.valor
FROM usuarios u
INNER JOIN pedidos p ON p.usuario_id = u.id
WHERE p.status = 'pago';

-- LEFT JOIN: todas as linhas da esquerda, com ou sem correspondência
SELECT u.nome, COUNT(p.id) AS total_pedidos
FROM usuarios u
LEFT JOIN pedidos p ON p.usuario_id = u.id
GROUP BY u.id, u.nome
ORDER BY total_pedidos DESC;

-- Encontrar usuários sem pedidos (anti-join pattern)
SELECT u.nome
FROM usuarios u
LEFT JOIN pedidos p ON p.usuario_id = u.id
WHERE p.id IS NULL;

-- CROSS JOIN: produto cartesiano
SELECT s.dia, h.hora
FROM dias_semana s
CROSS JOIN horarios h;

-- Self JOIN: tabela referencia a si mesma
SELECT e.nome AS empregado, g.nome AS gerente
FROM funcionarios e
LEFT JOIN funcionarios g ON g.id = e.gerente_id;

-- Join com múltiplas tabelas
SELECT
    u.nome,
    p.id AS pedido_id,
    prod.nome AS produto,
    ip.quantidade,
    ip.preco_unitario
FROM usuarios u
INNER JOIN pedidos p       ON p.usuario_id  = u.id
INNER JOIN itens_pedido ip ON ip.pedido_id  = p.id
INNER JOIN produtos prod   ON prod.id       = ip.produto_id
WHERE p.criado_em >= date('now', '-30 days');

Subqueries

-- Subquery no WHERE
SELECT nome, email
FROM usuarios
WHERE id IN (
    SELECT DISTINCT usuario_id
    FROM pedidos
    WHERE status = 'pago'
      AND valor > 500
);

-- Subquery correlacionada (referencia a tabela externa)
SELECT u.nome, u.email,
    (SELECT COUNT(*) FROM pedidos p WHERE p.usuario_id = u.id) AS total_pedidos,
    (SELECT SUM(valor) FROM pedidos p WHERE p.usuario_id = u.id) AS total_gasto
FROM usuarios u
WHERE EXISTS (
    SELECT 1 FROM pedidos p
    WHERE p.usuario_id = u.id
      AND p.status = 'pago'
);

-- Subquery na cláusula FROM (derived table)
SELECT avg_por_status.status, avg_por_status.media
FROM (
    SELECT status, AVG(valor) AS media, COUNT(*) AS total
    FROM pedidos
    GROUP BY status
) AS avg_por_status
WHERE avg_por_status.total > 5;

-- ANY / ALL (SQLite suporta via EXISTS/IN)
-- "usuários cujo menor pedido é maior que o maior pedido de ID 10"
SELECT nome FROM usuarios
WHERE id IN (
    SELECT usuario_id FROM pedidos p1
    WHERE NOT EXISTS (
        SELECT 1 FROM pedidos p2
        WHERE p2.usuario_id = 10
          AND p1.valor <= p2.valor
    )
);

CTEs (Common Table Expressions)

-- CTE simples — mais legível que subquery
WITH pedidos_recentes AS (
    SELECT *
    FROM pedidos
    WHERE criado_em >= date('now', '-30 days')
),
usuarios_ativos AS (
    SELECT DISTINCT usuario_id
    FROM pedidos_recentes
    WHERE status = 'pago'
)
SELECT u.nome, u.email
FROM usuarios u
WHERE u.id IN (SELECT usuario_id FROM usuarios_ativos);

-- CTE recursiva — hierarquias, árvores
WITH RECURSIVE hierarquia AS (
    -- Âncora: raiz da hierarquia (sem gerente)
    SELECT id, nome, gerente_id, 0 AS nivel, nome AS caminho
    FROM funcionarios
    WHERE gerente_id IS NULL

    UNION ALL

    -- Parte recursiva: filhos do nó atual
    SELECT f.id, f.nome, f.gerente_id,
           h.nivel + 1,
           h.caminho || ' > ' || f.nome
    FROM funcionarios f
    INNER JOIN hierarquia h ON f.gerente_id = h.id
)
SELECT nivel, caminho FROM hierarquia ORDER BY caminho;

-- CTE para paginação eficiente
WITH dados AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY criado_em DESC) AS rn
    FROM pedidos
    WHERE status = 'pago'
)
SELECT * FROM dados
WHERE rn BETWEEN 21 AND 30;   -- página 3, 10 por página

-- CTE para evitar repetição de subquery
WITH metricas AS (
    SELECT
        usuario_id,
        COUNT(*)   AS total_pedidos,
        SUM(valor) AS total_gasto,
        AVG(valor) AS ticket_medio,
        MAX(valor) AS maior_pedido
    FROM pedidos
    GROUP BY usuario_id
)
SELECT
    u.nome,
    m.total_pedidos,
    m.total_gasto / 100.0          AS total_gasto_reais,
    m.ticket_medio / 100.0         AS ticket_medio_reais,
    CASE
        WHEN m.total_gasto > 500000 THEN 'VIP'
        WHEN m.total_gasto > 100000 THEN 'Regular'
        ELSE 'Básico'
    END AS segmento
FROM usuarios u
INNER JOIN metricas m ON m.usuario_id = u.id
ORDER BY m.total_gasto DESC;

Window Functions (SQLite 3.25+)

-- ROW_NUMBER, RANK, DENSE_RANK
SELECT
    nome,
    valor,
    ROW_NUMBER() OVER (ORDER BY valor DESC) AS row_num,   -- único por linha
    RANK()       OVER (ORDER BY valor DESC) AS rank_pos,  -- empate = mesmo rank, pula próximo
    DENSE_RANK() OVER (ORDER BY valor DESC) AS dense_rank -- empate = mesmo rank, não pula
FROM pedidos
WHERE status = 'pago';

-- Particionado por categoria
SELECT
    categoria,
    nome,
    preco_cents,
    RANK() OVER (
        PARTITION BY categoria
        ORDER BY preco_cents DESC
    ) AS rank_na_categoria
FROM produtos;

-- Funções de agregação como window
SELECT
    usuario_id,
    valor,
    criado_em,
    SUM(valor) OVER (
        PARTITION BY usuario_id
        ORDER BY criado_em
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS total_acumulado,
    AVG(valor) OVER (
        PARTITION BY usuario_id
        ORDER BY criado_em
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS media_movel_3
FROM pedidos
WHERE status = 'pago'
ORDER BY usuario_id, criado_em;

-- LAG e LEAD — comparar com linha anterior/próxima
SELECT
    criado_em,
    valor,
    LAG(valor)  OVER (ORDER BY criado_em) AS valor_anterior,
    LEAD(valor) OVER (ORDER BY criado_em) AS proximo_valor,
    valor - LAG(valor) OVER (ORDER BY criado_em) AS variacao
FROM pedidos
WHERE usuario_id = 1
ORDER BY criado_em;

-- FIRST_VALUE, LAST_VALUE, NTH_VALUE
SELECT
    nome,
    salario,
    FIRST_VALUE(nome) OVER (ORDER BY salario DESC) AS maior_salario_nome,
    NTILE(4) OVER (ORDER BY salario)               AS quartil
FROM funcionarios;

Índices e Performance

-- Criar índice simples
CREATE INDEX IF NOT EXISTS idx_usuarios_email
ON usuarios(email);

-- Índice composto (ordem das colunas importa!)
CREATE INDEX IF NOT EXISTS idx_pedidos_usuario_status
ON pedidos(usuario_id, status);
-- Otimiza: WHERE usuario_id = ? AND status = ?
-- Otimiza: WHERE usuario_id = ?
-- NÃO otimiza: WHERE status = ? (sem usuario_id)

-- Índice único
CREATE UNIQUE INDEX IF NOT EXISTS idx_produtos_sku
ON produtos(sku);

-- Índice parcial (apenas linhas que atendem condição)
CREATE INDEX IF NOT EXISTS idx_pedidos_pendentes
ON pedidos(criado_em)
WHERE status = 'pendente';
-- Menor, mais rápido — ideal para estados específicos

-- Índice em expressão
CREATE INDEX IF NOT EXISTS idx_usuarios_email_lower
ON usuarios(lower(email));
-- Otimiza: WHERE lower(email) = lower(?)

-- Listar índices
SELECT * FROM sqlite_master WHERE type = 'index';

-- Remover índice
DROP INDEX IF EXISTS idx_usuarios_email;

-- Analisar uso de índices
EXPLAIN QUERY PLAN
SELECT * FROM pedidos WHERE usuario_id = 42 AND status = 'pago';
-- Procure por "USING INDEX" na saída — se não aparecer, considere criar índice

-- Analisar tabela para otimização do planejador
ANALYZE usuarios;
ANALYZE;  -- analisa todas as tabelas

-- Ver estatísticas geradas
SELECT * FROM sqlite_stat1;

PRAGMA Statements

PRAGMA são comandos de configuração específicos do SQLite.

-- Segurança e integridade
PRAGMA foreign_keys = ON;       -- ESSENCIAL: habilitar FK enforcement
PRAGMA foreign_keys;            -- verificar se está ativo (retorna 0 ou 1)
PRAGMA integrity_check;         -- verificar integridade do banco
PRAGMA quick_check;             -- verificação rápida (menos detalhada)
PRAGMA foreign_key_check;       -- verificar violações de FK existentes

-- Modo WAL (Write-Ahead Logging)
PRAGMA journal_mode = WAL;      -- melhor para concorrência
PRAGMA journal_mode;            -- verificar modo atual (delete, wal, memory, etc.)

-- Performance
PRAGMA cache_size = -64000;     -- cache de 64MB (negativo = kibibytes)
PRAGMA cache_size;              -- verificar valor atual (páginas)
PRAGMA page_size = 4096;        -- tamanho de página (definir ANTES de criar tabelas)
PRAGMA page_size;               -- verificar valor atual
PRAGMA mmap_size = 268435456;   -- memory-mapped I/O (256MB)
PRAGMA temp_store = MEMORY;     -- armazenar tabelas temp em memória
PRAGMA synchronous = NORMAL;    -- balanço performance/segurança
                                -- OFF=mais rápido, NORMAL=bom, FULL=mais seguro
PRAGMA wal_autocheckpoint = 1000; -- checkpoint a cada 1000 páginas

-- Informações do banco
PRAGMA database_list;           -- bancos conectados
PRAGMA table_info(usuarios);    -- colunas da tabela
PRAGMA index_info(idx_nome);    -- colunas do índice
PRAGMA table_xinfo(usuarios);   -- info extendida (inclui colunas ocultas)
PRAGMA index_list(usuarios);    -- índices de uma tabela

-- Compactar banco (desfragmentar, reduzir tamanho do arquivo)
VACUUM;                         -- recria o banco (lento, bloqueia)
VACUUM INTO 'backup.db';        -- cria cópia compactada (SQLite 3.27+)

-- Configurar para cada conexão (não persistem no arquivo)
PRAGMA auto_vacuum = INCREMENTAL; -- liberar espaço incrementalmente
PRAGMA incremental_vacuum(100);   -- liberar 100 páginas
PRAGMA optimize;                  -- otimizar após operações pesadas

Configuração recomendada de produção

# Aplicar ao abrir conexão
PRAGMA_SEQUENCE = [
    "PRAGMA journal_mode = WAL",        # melhor concorrência
    "PRAGMA foreign_keys = ON",         # enforçar FKs
    "PRAGMA synchronous = NORMAL",      # balanço segurança/performance
    "PRAGMA cache_size = -64000",       # cache de 64MB
    "PRAGMA temp_store = MEMORY",       # tabelas temp em RAM
    "PRAGMA mmap_size = 268435456",     # 256MB memory-mapped I/O
    "PRAGMA busy_timeout = 5000",       # esperar 5s por locks
]

Transações

-- Transação explícita
BEGIN;
UPDATE contas SET saldo = saldo - 100 WHERE id = 1;
UPDATE contas SET saldo = saldo + 100 WHERE id = 2;
COMMIT;

-- Rollback em caso de erro
BEGIN;
UPDATE estoque SET quantidade = quantidade - 5 WHERE produto_id = 42;
-- Verificar se ficou negativo
SELECT CASE WHEN quantidade < 0 THEN RAISE(ROLLBACK, 'Estoque insuficiente') END
FROM estoque WHERE produto_id = 42;
-- Se chegou até aqui, ok
COMMIT;

-- SAVEPOINT — pontos intermediários
BEGIN;
INSERT INTO log (evento) VALUES ('início');

SAVEPOINT passo1;
UPDATE usuarios SET ativo = 0 WHERE ultimo_login < date('now', '-1 year');
-- Se algo der errado nesta parte:
ROLLBACK TO passo1;  -- desfaz apenas até o savepoint
-- ou
RELEASE passo1;      -- confirma o savepoint (não commita ainda)

INSERT INTO log (evento) VALUES ('fim');
COMMIT;

-- Tipos de transações
BEGIN DEFERRED;    -- lock apenas quando necessário (padrão)
BEGIN IMMEDIATE;   -- lock de escrita imediato (evita SQLITE_BUSY)
BEGIN EXCLUSIVE;   -- lock exclusivo (nenhuma outra conexão pode ler)

JSON1 Extension

A extensão JSON1 está disponível por padrão no SQLite 3.9+.

-- Criar tabela com coluna JSON
CREATE TABLE eventos (
    id        INTEGER PRIMARY KEY,
    tipo      TEXT NOT NULL,
    payload   TEXT NOT NULL DEFAULT '{}',  -- JSON como TEXT
    criado_em TEXT DEFAULT (datetime('now'))
);

INSERT INTO eventos (tipo, payload) VALUES
    ('login', '{"usuario":"alice","ip":"192.168.1.1","device":"mobile"}'),
    ('compra', '{"produto":"Notebook","valor":2500.00,"parcelas":12}'),
    ('erro',   '{"codigo":500,"msg":"Database timeout","stack":"..."}');

-- Extrair valor do JSON
SELECT
    tipo,
    json_extract(payload, '$.usuario') AS usuario,
    json_extract(payload, '$.ip')      AS ip
FROM eventos
WHERE tipo = 'login';

-- Filtrar por campo JSON
SELECT * FROM eventos
WHERE json_extract(payload, '$.valor') > 1000;

-- Modificar JSON
UPDATE eventos
SET payload = json_set(payload, '$.processado', 1, '$.ts', datetime('now'))
WHERE tipo = 'compra';

UPDATE eventos
SET payload = json_remove(payload, '$.stack')
WHERE tipo = 'erro';

-- JSON_PATCH — merge de objetos (SQLite 3.42+)
UPDATE eventos
SET payload = json_patch(payload, '{"versao":2,"novo_campo":"valor"}')
WHERE id = 1;

-- Verificar se é JSON válido
SELECT json_valid('{"ok":true}');   -- 1
SELECT json_valid('invalido');      -- 0

-- JSON_EACH — desdobrar array em linhas
SELECT key, value
FROM json_each('["node","python","go","java"]');
-- key: 0,1,2,3 / value: node,python,go,java

-- JSON_TREE — árvore completa de um objeto
SELECT path, type, value
FROM json_tree('{"nome":"Alice","enderecos":[{"cidade":"SP"},{"cidade":"RJ"}]}');

-- Agregar em JSON
SELECT json_group_array(nome) FROM usuarios WHERE ativo = 1;
-- ["Alice","Bob","Carol"]

SELECT json_group_object(id, nome) FROM usuarios WHERE ativo = 1;
-- {"1":"Alice","2":"Bob","3":"Carol"}

-- Index em campo JSON (muito útil!)
CREATE INDEX idx_eventos_usuario
ON eventos(json_extract(payload, '$.usuario'))
WHERE tipo = 'login';

FTS5 — Full Text Search

FTS5 é a extensão de busca full-text do SQLite (substituiu FTS3/FTS4).

-- Criar tabela FTS5
CREATE VIRTUAL TABLE artigos_fts USING fts5(
    titulo,
    conteudo,
    autor,
    content='artigos',    -- tabela de conteúdo (optional: content table)
    content_rowid='id',   -- coluna rowid da tabela de conteúdo
    tokenize='porter ascii'  -- tokenizer: porter stemming + apenas ASCII
);

-- Tokenizers disponíveis:
-- unicode61 (padrão): suporte Unicode
-- ascii: apenas ASCII, mais rápido
-- porter: stemming inglês (run = runs = running)
-- trigram: busca por n-gramas (suporta LIKE)

-- Criar tabela de conteúdo + FTS sincronizados
CREATE TABLE artigos (
    id        INTEGER PRIMARY KEY,
    titulo    TEXT NOT NULL,
    conteudo  TEXT NOT NULL,
    autor     TEXT NOT NULL,
    criado_em TEXT DEFAULT (datetime('now'))
);

CREATE VIRTUAL TABLE artigos_fts USING fts5(
    titulo, conteudo, autor,
    content='artigos',
    content_rowid='id'
);

-- Trigger para manter FTS sincronizado
CREATE TRIGGER artigos_ai AFTER INSERT ON artigos BEGIN
    INSERT INTO artigos_fts(rowid, titulo, conteudo, autor)
    VALUES (new.id, new.titulo, new.conteudo, new.autor);
END;

CREATE TRIGGER artigos_ad AFTER DELETE ON artigos BEGIN
    INSERT INTO artigos_fts(artigos_fts, rowid, titulo, conteudo, autor)
    VALUES ('delete', old.id, old.titulo, old.conteudo, old.autor);
END;

CREATE TRIGGER artigos_au AFTER UPDATE ON artigos BEGIN
    INSERT INTO artigos_fts(artigos_fts, rowid, titulo, conteudo, autor)
    VALUES ('delete', old.id, old.titulo, old.conteudo, old.autor);
    INSERT INTO artigos_fts(rowid, titulo, conteudo, autor)
    VALUES (new.id, new.titulo, new.conteudo, new.autor);
END;

-- Busca simples
SELECT rowid, titulo FROM artigos_fts
WHERE artigos_fts MATCH 'python';

-- Busca por campo específico
SELECT rowid, titulo FROM artigos_fts
WHERE artigos_fts MATCH 'titulo:redis';

-- Operadores booleanos
SELECT rowid, titulo FROM artigos_fts
WHERE artigos_fts MATCH 'python AND django';
-- OR, NOT também funcionam

-- Prefix search
SELECT rowid, titulo FROM artigos_fts
WHERE artigos_fts MATCH 'python*';  -- pythonic, python3, etc.

-- Busca por frase exata
SELECT rowid, titulo FROM artigos_fts
WHERE artigos_fts MATCH '"machine learning"';

-- Ranking por relevância (BM25)
SELECT a.id, a.titulo,
       bm25(artigos_fts) AS score
FROM artigos_fts
INNER JOIN artigos a ON a.id = artigos_fts.rowid
WHERE artigos_fts MATCH 'banco dados sql'
ORDER BY bm25(artigos_fts);   -- menor = mais relevante (valor negativo)

-- Snippets com destaque
SELECT
    a.titulo,
    snippet(artigos_fts, 1, '<b>', '</b>', '...', 20) AS excerpt
FROM artigos_fts
INNER JOIN artigos a ON a.id = artigos_fts.rowid
WHERE artigos_fts MATCH 'redis cache';

-- Reconstruir índice FTS
INSERT INTO artigos_fts(artigos_fts) VALUES ('rebuild');

-- Otimizar índice
INSERT INTO artigos_fts(artigos_fts) VALUES ('optimize');

-- Verificar integridade
INSERT INTO artigos_fts(artigos_fts) VALUES ('integrity-check');

SQLite com Python

import sqlite3
import json
from contextlib import contextmanager
from typing import Optional

DATABASE_PATH = "app.db"

# Configuração recomendada
def get_connection(path: str = DATABASE_PATH) -> sqlite3.Connection:
    conn = sqlite3.connect(path, check_same_thread=False)
    conn.row_factory = sqlite3.Row       # acesso por nome de coluna
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    conn.execute("PRAGMA synchronous=NORMAL")
    conn.execute("PRAGMA cache_size=-64000")
    conn.execute("PRAGMA busy_timeout=5000")
    return conn

@contextmanager
def get_db():
    conn = get_connection()
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

# Operações básicas
with get_db() as db:
    # INSERT
    cursor = db.execute(
        "INSERT INTO usuarios (nome, email) VALUES (?, ?)",
        ("Alice", "alice@ex.com")
    )
    novo_id = cursor.lastrowid
    print(f"Inserido: {novo_id}")

    # INSERT muitos com executemany
    usuarios = [("Bob", "bob@ex.com"), ("Carol", "carol@ex.com")]
    db.executemany(
        "INSERT OR IGNORE INTO usuarios (nome, email) VALUES (?, ?)",
        usuarios
    )

    # SELECT
    cursor = db.execute(
        "SELECT id, nome, email FROM usuarios WHERE ativo = ?", (1,)
    )
    usuarios = cursor.fetchall()
    for u in usuarios:
        print(f"{u['id']}: {u['nome']} — {u['email']}")  # acesso por nome

    # SELECT one
    user = db.execute(
        "SELECT * FROM usuarios WHERE id = ?", (1,)
    ).fetchone()
    if user:
        print(dict(user))  # converter Row para dict

    # UPDATE
    db.execute(
        "UPDATE usuarios SET ativo = ? WHERE id = ?", (0, 42)
    )

    # DELETE
    db.execute("DELETE FROM logs WHERE criado_em < ?", ("2023-01-01",))

# Context manager para transações
with get_db() as db:
    db.execute("UPDATE contas SET saldo = saldo - ? WHERE id = ?", (100, 1))
    db.execute("UPDATE contas SET saldo = saldo + ? WHERE id = ?", (100, 2))
    # commit automático ao sair do with

# Adapters: salvar/recuperar tipos customizados
sqlite3.register_adapter(dict, lambda d: json.dumps(d))
sqlite3.register_converter("JSON", lambda s: json.loads(s))

conn = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
conn.execute("CREATE TABLE t (data JSON)")
conn.execute("INSERT INTO t VALUES (?)", ({"key": "value"},))  # dict vira JSON
row = conn.execute("SELECT data FROM t").fetchone()
print(type(row[0]))  # dict automaticamente

# Banco em memória com schema
conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE test (id INTEGER PRIMARY KEY, val TEXT);
    INSERT INTO test VALUES (1, 'hello');
""")

SQLite com Node.js (better-sqlite3)

better-sqlite3 é síncrono (sem callbacks/promises) — mais simples e mais rápido que node-sqlite3.

npm install better-sqlite3
# npm install -D @types/better-sqlite3  # TypeScript

const Database = require('better-sqlite3');

// Abrir banco com configurações otimizadas
const db = new Database('app.db', {
  verbose: process.env.NODE_ENV === 'development' ? console.log : null
});

// Configurar PRAGMAs
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');
db.pragma('synchronous = NORMAL');
db.pragma('cache_size = -64000');
db.pragma('busy_timeout = 5000');

// Preparar statements (compilados uma vez, reutilizados)
const insertUser = db.prepare(
  'INSERT INTO usuarios (nome, email) VALUES (@nome, @email)'
);
const findById = db.prepare(
  'SELECT * FROM usuarios WHERE id = ?'
);
const findAll = db.prepare(
  'SELECT * FROM usuarios WHERE ativo = ? ORDER BY nome'
);
const updateUser = db.prepare(
  'UPDATE usuarios SET nome = @nome, atualizado = datetime(\'now\') WHERE id = @id'
);

// INSERT — retorna { changes, lastInsertRowid }
const result = insertUser.run({ nome: 'Alice', email: 'alice@ex.com' });
console.log('Inserido ID:', result.lastInsertRowid);

// SELECT one
const user = findById.get(1); // null se não encontrado
if (user) console.log(user.nome);

// SELECT many
const users = findAll.all(1); // array de objetos
users.forEach(u => console.log(u.nome));

// UPDATE/DELETE — retorna { changes }
const updated = updateUser.run({ nome: 'Alice Nova', id: 1 });
console.log('Linhas afetadas:', updated.changes);

// Transação — muito eficiente no better-sqlite3
const transferir = db.transaction((deId, paraId, valor) => {
  const decrement = db.prepare('UPDATE contas SET saldo = saldo - ? WHERE id = ?');
  const increment = db.prepare('UPDATE contas SET saldo = saldo + ? WHERE id = ?');

  decrement.run(valor, deId);
  increment.run(valor, paraId);
});

transferir(1, 2, 100);  // atômico — rollback automático em erro

// INSERT em massa com transação (muito mais rápido que um por um)
const insertMany = db.transaction((items) => {
  const stmt = db.prepare('INSERT INTO produtos (nome, preco) VALUES (@nome, @preco)');
  for (const item of items) {
    stmt.run(item);
  }
});

insertMany(produtosArray); // 1000x mais rápido que 1 insert por vez

// Iterator — processar grandes datasets sem carregar tudo na memória
const stmt = db.prepare('SELECT * FROM logs ORDER BY criado_em');
for (const row of stmt.iterate()) {
  processar(row); // processa uma linha por vez
}

// Custom functions
db.function('lowercase', (str) => str?.toLowerCase());
db.aggregate('sum_custom', {
  start: 0,
  step: (acc, val) => acc + (val || 0),
  result: (acc) => acc
});

const result2 = db.prepare("SELECT lowercase(nome) FROM usuarios").all();

// Fechar banco (graceful shutdown)
process.on('exit', () => db.close());
process.on('SIGINT', () => { db.close(); process.exit(0); });

SQLite com Java

// build.gradle
// implementation 'org.xerial:sqlite-jdbc:3.45.0.0'

import java.sql.*;
import java.util.*;

public class SQLiteExample {

    private static final String DB_URL = "jdbc:sqlite:app.db";

    // Configuração recomendada
    public static Connection getConnection() throws SQLException {
        var conn = DriverManager.getConnection(DB_URL);
        try (var stmt = conn.createStatement()) {
            stmt.execute("PRAGMA journal_mode=WAL");
            stmt.execute("PRAGMA foreign_keys=ON");
            stmt.execute("PRAGMA synchronous=NORMAL");
            stmt.execute("PRAGMA cache_size=-64000");
            stmt.execute("PRAGMA busy_timeout=5000");
        }
        return conn;
    }

    // INSERT
    public static long insertUser(String nome, String email) throws SQLException {
        var sql = "INSERT INTO usuarios (nome, email) VALUES (?, ?)";
        try (var conn = getConnection();
             var stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            stmt.setString(1, nome);
            stmt.setString(2, email);
            stmt.executeUpdate();
            var keys = stmt.getGeneratedKeys();
            return keys.next() ? keys.getLong(1) : -1;
        }
    }

    // SELECT
    public static Optional<Map<String, Object>> findById(long id) throws SQLException {
        var sql = "SELECT * FROM usuarios WHERE id = ?";
        try (var conn = getConnection();
             var stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, id);
            var rs = stmt.executeQuery();
            if (rs.next()) {
                var user = new HashMap<String, Object>();
                user.put("id", rs.getLong("id"));
                user.put("nome", rs.getString("nome"));
                user.put("email", rs.getString("email"));
                user.put("ativo", rs.getBoolean("ativo"));
                return Optional.of(user);
            }
            return Optional.empty();
        }
    }

    // Transação
    public static void transferir(long deId, long paraId, int valor) throws SQLException {
        try (var conn = getConnection()) {
            conn.setAutoCommit(false);
            try {
                var decr = conn.prepareStatement(
                    "UPDATE contas SET saldo = saldo - ? WHERE id = ?"
                );
                decr.setInt(1, valor);
                decr.setLong(2, deId);
                decr.executeUpdate();

                var incr = conn.prepareStatement(
                    "UPDATE contas SET saldo = saldo + ? WHERE id = ?"
                );
                incr.setInt(1, valor);
                incr.setLong(2, paraId);
                incr.executeUpdate();

                conn.commit();
            } catch (SQLException e) {
                conn.rollback();
                throw e;
            }
        }
    }

    // Batch insert
    public static void insertMany(List<String[]> items) throws SQLException {
        var sql = "INSERT INTO produtos (nome, preco) VALUES (?, ?)";
        try (var conn = getConnection();
             var stmt = conn.prepareStatement(sql)) {
            conn.setAutoCommit(false);
            for (var item : items) {
                stmt.setString(1, item[0]);
                stmt.setDouble(2, Double.parseDouble(item[1]));
                stmt.addBatch();
            }
            stmt.executeBatch();
            conn.commit();
        }
    }
}

WAL Mode e Concorrência

# Por padrão, SQLite usa DELETE journal mode:
# - Apenas 1 writer por vez
# - Leitura bloqueia escrita e vice-versa

# WAL mode (Write-Ahead Logging):
# - Múltiplos leitores simultâneos (não bloqueiam escrita)
# - 1 writer por vez (mas não bloqueia leitores)
# - Melhor performance geral
# - Arquivo WAL separado: app.db-wal, app.db-shm

PRAGMA journal_mode=WAL;

-- Verificar modo atual
PRAGMA journal_mode;  -- retorna "wal" ou "delete"

-- WAL checkpoint: consolidar WAL no banco principal
PRAGMA wal_checkpoint;                 -- passivo (não bloqueia)
PRAGMA wal_checkpoint(TRUNCATE);       -- trunca o arquivo WAL
PRAGMA wal_checkpoint(FULL);           -- aguarda todos os leitores
PRAGMA wal_checkpoint(RESTART);        -- reinicia escritores após checkpoint

-- Auto-checkpoint: acontece automaticamente a cada 1000 páginas
PRAGMA wal_autocheckpoint = 1000;

-- Monitorar WAL
PRAGMA wal_checkpoint;
-- retorna: busy | log | checkpointed

Concorrência: múltiplos processos

import sqlite3
import time

def get_conn():
    conn = sqlite3.connect("app.db", timeout=30)  # esperar até 30s pelo lock
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA busy_timeout=30000")  # em ms
    return conn

# SQLite com WAL suporta:
# - Múltiplos processos lendo simultaneamente
# - Apenas 1 processo escrevendo por vez
# - Leitores não bloqueiam escritores (no WAL mode)
# - Escritores esperam outros escritores (SQLITE_BUSY)

# Para alta concorrência de escrita, prefira PostgreSQL

Backup e Migração

# Backup online (enquanto o banco está em uso)
sqlite3 app.db ".backup backup.db"
sqlite3 app.db "VACUUM INTO 'backup-compacto.db'"

# Backup com timestamp
sqlite3 app.db ".backup backup-$(date +%Y%m%d-%H%M%S).db"

# Dump como SQL (texto, portável)
sqlite3 app.db .dump > backup.sql
sqlite3 app.db ".dump usuarios pedidos" > partial-backup.sql  # tabelas específicas

# Restaurar de dump SQL
sqlite3 novo.db < backup.sql

# Copiar schema (sem dados)
sqlite3 app.db .schema > schema.sql

# Backup online via Python (sqlite3.Connection.backup)
import sqlite3

def backup_db(source_path: str, dest_path: str):
    source = sqlite3.connect(source_path)
    dest = sqlite3.connect(dest_path)

    def progress(status, remaining, total):
        print(f"Backup: {total - remaining}/{total} páginas")

    with dest:
        source.backup(dest, pages=100, progress=progress)

    dest.close()
    source.close()
    print("Backup concluído!")

backup_db("app.db", "backup.db")

Migrações com versão

# Sistema de migrações simples
import sqlite3

MIGRATIONS = [
    # (versão, sql)
    (1, """
        CREATE TABLE usuarios (
            id INTEGER PRIMARY KEY,
            nome TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL
        );
    """),
    (2, """
        ALTER TABLE usuarios ADD COLUMN telefone TEXT;
        ALTER TABLE usuarios ADD COLUMN ativo INTEGER DEFAULT 1;
    """),
    (3, """
        CREATE INDEX idx_usuarios_email ON usuarios(email);
        CREATE TABLE sessoes (
            id TEXT PRIMARY KEY,
            usuario_id INTEGER NOT NULL,
            expira_em TEXT NOT NULL,
            FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
        );
    """),
]

def migrate(db_path: str):
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA foreign_keys=ON")

    # Tabela de versão
    conn.execute("""
        CREATE TABLE IF NOT EXISTS schema_version (
            version INTEGER PRIMARY KEY,
            applied_at TEXT DEFAULT (datetime('now'))
        )
    """)

    # Versão atual
    current = conn.execute(
        "SELECT MAX(version) FROM schema_version"
    ).fetchone()[0] or 0

    print(f"Versão atual do schema: {current}")

    for version, sql in MIGRATIONS:
        if version <= current:
            continue

        print(f"Aplicando migração {version}...")
        try:
            conn.executescript(sql)
            conn.execute("INSERT INTO schema_version (version) VALUES (?)", (version,))
            conn.commit()
            print(f"Migração {version} aplicada com sucesso!")
        except Exception as e:
            conn.rollback()
            print(f"Erro na migração {version}: {e}")
            raise

    conn.close()

migrate("app.db")

---

## VACUUM e Modos de Vacuum

O SQLite não libera espaço em disco automaticamente quando rows são deletadas — o espaço fica marcado como disponível internamente, mas o arquivo `.db` não encolhe. O `VACUUM` reescreve o banco inteiro em um novo arquivo, recuperando o espaço.

### VACUUM completo

```sql
-- Reescreve o banco inteiro, compacta o arquivo e reorganiza as páginas
-- Operação bloqueante — nenhuma outra conexão pode escrever durante o processo
-- Para bancos grandes, pode levar vários segundos ou minutos
VACUUM;

-- VACUUM INTO: cria uma cópia compactada em outro arquivo (banco original inalterado)
-- Útil para backup online sem parar a aplicação
VACUUM INTO '/tmp/backup-compactado.db';

Modos de auto_vacuum

-- Ver modo atual
PRAGMA auto_vacuum;

-- NONE (padrão): sem vacuum automático
-- Arquivo nunca encolhe; VACUUM manual necessário periodicamente
PRAGMA auto_vacuum = NONE;

-- FULL: a cada DELETE, páginas livres são devolvidas ao SO imediatamente
-- Custo: escrita mais lenta (overhead por operação)
-- Bom para: bancos com muitas deleções frequentes e tamanho controlado
PRAGMA auto_vacuum = FULL;

-- INCREMENTAL: marca páginas livres mas não as devolve automaticamente
-- Você controla quando recuperar espaço com incremental_vacuum
PRAGMA auto_vacuum = INCREMENTAL;

-- Executar vacuum incremental: liberar N páginas por vez
-- Menos impactante que VACUUM completo
PRAGMA incremental_vacuum(100);  -- libera 100 páginas
PRAGMA incremental_vacuum;       -- libera todas as páginas livres marcadas

Quando usar cada modo

# Agendar VACUUM fora do horário de pico (via cron, por exemplo)
sqlite3 app.db "VACUUM;"

# Ou via incremental_vacuum em batches durante a madrugada
sqlite3 app.db "PRAGMA incremental_vacuum(500);"

Impacto em performance: VACUUM completo bloqueia escritas e precisa de espaço temporário equivalente ao tamanho do banco. Em bancos maiores que alguns GB, planeje uma janela de manutenção.

PRAGMAs de Tuning

SQLite expõe diversas configurações de performance via PRAGMA. Ao contrário de outros bancos, essas configurações se aplicam por conexão e precisam ser definidas a cada abertura do banco.

journal_mode — WAL (Write-Ahead Logging)

-- Modo padrão: DELETE (journal de rollback)
-- Problema: writers bloqueiam readers e vice-versa

-- WAL: writes não bloqueiam reads — múltiplos readers simultâneos
-- Essencial para aplicações com leitura concorrente (APIs, etc.)
PRAGMA journal_mode = WAL;
-- Retorna: "wal" (confirma ativação)

-- WAL cria dois arquivos auxiliares: app.db-wal e app.db-shm
-- Eles são seguros para ignorar no .gitignore

Por que usar WAL em produção: o modo DELETE trava o arquivo inteiro para cada escrita. Com WAL, leituras continuam enquanto uma escrita acontece — imprescindível para APIs com múltiplas requisições simultâneas, mesmo com SQLite.

synchronous — equilíbrio entre durabilidade e velocidade

-- FULL (padrão no modo DELETE): fsync após cada operação
-- Máxima durabilidade, mas lento em HDD/SSD com latência alta
PRAGMA synchronous = FULL;

-- NORMAL: fsync em checkpoints do WAL, não em cada transação
-- Risco mínimo de perda de dados (apenas em crash do SO, não da app)
-- Recomendado para WAL mode — boa durabilidade com boa performance
PRAGMA synchronous = NORMAL;

-- OFF: sem fsync — perigo de corrupção em crash do SO
-- Útil apenas para testes ou dados descartáveis
PRAGMA synchronous = OFF;

cache_size, temp_store e mmap_size

-- Tamanho do cache de páginas em memória (valor negativo = KB)
-- Padrão: -2000 (2 MB). Aumentar melhora reads repetidos.
PRAGMA cache_size = -64000;  -- 64 MB de cache

-- Armazenar tabelas temporárias em memória (mais rápido que disco)
PRAGMA temp_store = MEMORY;

-- Memory-mapped I/O: mapeia o banco diretamente na memória virtual do processo
-- Leituras sequenciais grandes ficam muito mais rápidas
-- Valor em bytes (256 MB no exemplo)
PRAGMA mmap_size = 268435456;

-- Tamanho das páginas (deve ser definido ANTES de criar o banco)
-- Padrão: 4096. Para bancos com blobs grandes, 8192 ou 16384 são melhores.
PRAGMA page_size = 8192;  -- só funciona em banco novo ou após VACUUM

Combinação recomendada para produção

-- Executar logo após abrir a conexão (toda vez que o banco for aberto)
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -64000;    -- 64 MB
PRAGMA temp_store = MEMORY;
PRAGMA mmap_size = 268435456;  -- 256 MB
PRAGMA foreign_keys = ON;

# Python — aplicar PRAGMAs de produção automaticamente
import sqlite3

def open_db(path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(path, check_same_thread=False)
    conn.executescript("""
        PRAGMA journal_mode = WAL;
        PRAGMA synchronous = NORMAL;
        PRAGMA cache_size = -64000;
        PRAGMA temp_store = MEMORY;
        PRAGMA mmap_size = 268435456;
        PRAGMA foreign_keys = ON;
    """)
    return conn

FTS5 — Full-Text Search

O FTS5 é a extensão de busca full-text nativa do SQLite (substituto do FTS4). Permite pesquisa textual eficiente com ranking por relevância, highlight e snippet de contexto.

Criar tabela FTS5

-- Tabela FTS5 simples (armazena o conteúdo internamente)
CREATE VIRTUAL TABLE artigos_fts USING fts5(
    titulo,
    conteudo,
    autor
);

-- FTS5 com content table (não duplica dados — referencia tabela existente)
CREATE TABLE artigos (
    id      INTEGER PRIMARY KEY,
    titulo  TEXT NOT NULL,
    conteudo TEXT,
    autor   TEXT
);

CREATE VIRTUAL TABLE artigos_fts USING fts5(
    titulo,
    conteudo,
    autor,
    content='artigos',   -- tabela de origem
    content_rowid='id'   -- coluna ID da tabela de origem
);

-- Popular o índice FTS a partir da tabela existente
INSERT INTO artigos_fts(artigos_fts) VALUES('rebuild');

Inserir e pesquisar

-- Inserir (na tabela FTS autônoma)
INSERT INTO artigos_fts (titulo, conteudo, autor)
VALUES ('Guia SQLite', 'SQLite é um banco embarcado leve e rápido', 'Rafael');

-- Busca simples — retorna rows que contenham "embarcado"
SELECT * FROM artigos_fts WHERE artigos_fts MATCH 'embarcado';

-- Busca em coluna específica
SELECT * FROM artigos_fts WHERE artigos_fts MATCH 'titulo:SQLite';

-- Busca com prefixo (palavra que começa com "embar")
SELECT * FROM artigos_fts WHERE artigos_fts MATCH 'embar*';

-- Frase exata (palavras adjacentes nessa ordem)
SELECT * FROM artigos_fts WHERE artigos_fts MATCH '"banco embarcado"';

Operadores de busca

-- AND: ambos os termos presentes (padrão quando há espaço)
SELECT * FROM artigos_fts WHERE artigos_fts MATCH 'SQLite AND rápido';

-- OR: pelo menos um dos termos
SELECT * FROM artigos_fts WHERE artigos_fts MATCH 'SQLite OR PostgreSQL';

-- NOT: exclui documentos com o termo
SELECT * FROM artigos_fts WHERE artigos_fts MATCH 'banco NOT MySQL';

-- ^ ancora no início da coluna (primeiro token)
SELECT * FROM artigos_fts WHERE artigos_fts MATCH '^guia';

-- * prefixo (qualquer sufixo)
SELECT * FROM artigos_fts WHERE artigos_fts MATCH 'embed*';

Ranking por relevância com rank e bm25()

-- rank: coluna virtual que retorna o score BM25 (menor = mais relevante)
-- Ordenar por relevância:
SELECT titulo, rank
FROM artigos_fts
WHERE artigos_fts MATCH 'SQLite'
ORDER BY rank;

-- bm25() com pesos por coluna (título vale mais que conteúdo)
-- Argumentos: peso de cada coluna na ordem de criação
SELECT titulo, bm25(artigos_fts, 10.0, 1.0, 0.5) AS score
FROM artigos_fts
WHERE artigos_fts MATCH 'SQLite'
ORDER BY score;

Highlight e snippet

-- highlight(): destaca termos encontrados com marcadores HTML
-- Parâmetros: tabela, índice da coluna (0-based), marcador abertura, marcador fechamento
SELECT highlight(artigos_fts, 1, '<b>', '</b>') AS conteudo_destacado
FROM artigos_fts
WHERE artigos_fts MATCH 'embarcado';
-- Resultado: "SQLite é um banco <b>embarcado</b> leve e rápido"

-- snippet(): retorna trecho do texto ao redor do match
-- Parâmetros: tabela, índice da coluna, marcador abertura, fechamento, elipse, nº tokens
SELECT snippet(artigos_fts, 1, '<mark>', '</mark>', '...', 10) AS trecho
FROM artigos_fts
WHERE artigos_fts MATCH 'banco embarcado';

Manutenção do índice FTS5

-- Otimizar o índice (mescla segmentos internos — melhora performance de busca)
INSERT INTO artigos_fts(artigos_fts) VALUES('optimize');

-- Reconstruir o índice do zero (necessário após updates na content table)
INSERT INTO artigos_fts(artigos_fts) VALUES('rebuild');

-- Verificar integridade do índice FTS
INSERT INTO artigos_fts(artigos_fts) VALUES('integrity-check');

SQLite em Produção — Boas Práticas

SQLite é frequentemente subestimado para produção. Com as configurações corretas, suporta cargas consideráveis, especialmente workloads com muitas leituras.

Configuração base para produção

-- Aplicar sempre ao abrir a conexão:
PRAGMA journal_mode = WAL;       -- reads não bloqueiam writes
PRAGMA synchronous = NORMAL;    -- durabilidade adequada sem fsync por transação
PRAGMA cache_size = -64000;     -- 64 MB de cache em memória
PRAGMA temp_store = MEMORY;     -- tabelas temporárias na RAM
PRAGMA mmap_size = 268435456;   -- 256 MB de mmap para reads rápidos
PRAGMA foreign_keys = ON;       -- garantir integridade referencial

Tamanho máximo recomendado

SQLite lida bem com arquivos de dezenas de GB para leitura. O gargalo é sempre na escrita concorrente — um único writer por vez, sem paralelismo.

Backup online

-- VACUUM INTO: cópia compactada e consistente sem bloquear reads
-- Pode ser executado com o banco em uso
VACUUM INTO '/backups/app-2025-05-06.db';

-- Via CLI (equivalente, usando .backup)
-- sqlite3 app.db ".backup /backups/app-backup.db"

# Script de backup diário
#!/bin/bash
DATE=$(date +%Y-%m-%d)
sqlite3 /data/app.db "VACUUM INTO '/backups/app-${DATE}.db';"
# Manter apenas os últimos 7 dias
find /backups -name "app-*.db" -mtime +7 -delete

SQLite em aplicações multi-thread

SQLite suporta múltiplas threads lendo simultaneamente com WAL, mas apenas uma thread escreve por vez.

# Python — uma conexão por thread (check_same_thread=False com pool)
import sqlite3
import threading

# Thread-local storage: cada thread tem sua própria conexão
_local = threading.local()

def get_conn(db_path: str) -> sqlite3.Connection:
    if not hasattr(_local, "conn"):
        _local.conn = sqlite3.connect(db_path, check_same_thread=False)
        _local.conn.executescript("""
            PRAGMA journal_mode = WAL;
            PRAGMA synchronous = NORMAL;
            PRAGMA cache_size = -32000;
            PRAGMA foreign_keys = ON;
        """)
    return _local.conn

# Com connection pool (usando sqlalchemy)
from sqlalchemy import create_engine, event

engine = create_engine(
    "sqlite:///app.db",
    connect_args={"check_same_thread": False},
    pool_size=10,        # máximo de conexões simultâneas
    pool_pre_ping=True,
)

@event.listens_for(engine, "connect")
def set_sqlite_pragma(dbapi_conn, connection_record):
    cursor = dbapi_conn.cursor()
    cursor.execute("PRAGMA journal_mode=WAL")
    cursor.execute("PRAGMA synchronous=NORMAL")
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

Checklist de produção

✓ PRAGMA journal_mode = WAL habilitado em toda conexão
✓ PRAGMA synchronous = NORMAL (não OFF — risco de corrupção)
✓ PRAGMA foreign_keys = ON (desabilitado por padrão!)
✓ Backup diário com VACUUM INTO (cópia consistente e compactada)
✓ Uma conexão por thread — nunca compartilhar entre threads sem pool
✓ Timeout de busy configurado (PRAGMA busy_timeout = 5000) para evitar SQLITE_BUSY
✓ Índices nas colunas usadas em WHERE, JOIN e ORDER BY
✓ ANALYZE após cargas de dados grandes (atualiza estatísticas do planner)
✓ Monitorar tamanho do arquivo — VACUUM periódico se crescer demais
✓ Banco no mesmo filesystem que a aplicação (sem NFS ou SMB — corrupção garantida)

-- PRAGMA adicional importante: busy_timeout
-- Define quanto tempo (ms) esperar por um lock antes de retornar SQLITE_BUSY
PRAGMA busy_timeout = 5000;  -- espera até 5 segundos antes de falhar

-- ANALYZE: atualiza estatísticas para o query planner
-- Executar após inserir grandes volumes de dados
ANALYZE;