SQLite FTS5: fundamentos

Objetivos

Nesta aula você vai:

• Criar tabelas virtuais FTS5 e escolher tokenizers adequados ao domínio
• Escrever consultas com o operador MATCH e combinar FTS com filtros SQL
• Distinguir modos contentful, contentless e external content tables

Introdução

SQLite embute FTS5 (Full-Text Search versão 5) como extensão virtual table. Em vez de varrer LIKE '%termo%', o motor indexa tokens e resolve consultas por índice invertido — típico de motores de busca, porém dentro do mesmo arquivo .db.

Para apps embarcados, CLIs, edge (Cloudflare D1) e protótipos, FTS5 oferece relevância lexical sem um cluster Elasticsearch. O preço: você precisa modelar a tabela virtual, sincronizar conteúdo e entender tokenizers.

Conteúdo

Tabela virtual FTS5

Uma tabela FTS5 não é uma tabela B-tree comum. Ela é criada com CREATE VIRTUAL TABLE ... USING fts5(...):

CREATE VIRTUAL TABLE artigos_fts USING fts5(
  titulo,
  corpo,
  tokenize = 'unicode61 remove_diacritics 2'
);

Colunas em FTS5 são, por padrão, indexadas e armazenadas. Você pode marcar colunas só para filtragem/armazenamento sem tokenização:

CREATE VIRTUAL TABLE produtos_fts USING fts5(
  nome,
  descricao,
  categoria UNINDEXED,
  tokenize = 'porter unicode61'
);

UNINDEXED evita tokenizar categoria, mas o valor ainda pode ser retornado no SELECT.

Tokenizers

O tokenizer define como o texto vira tokens:

Para português, unicode61 com remove_diacritics 2 ajuda a casar "ação" e "acao". Stemming nativo robusto para PT-BR não existe no FTS5 padrão — em produção avançada costuma-se normalizar no aplicativo ou usar motor dedicado.

Operador MATCH

A busca lexical usa MATCH na coluna da tabela virtual (ou na tabela inteira):

SELECT rowid, titulo
FROM artigos_fts
WHERE artigos_fts MATCH 'sqlite AND fts5';

Sintaxe útil da query FTS5:

• termo — token simples
• "frase exata" — frase
• prefix* — prefixo
• A AND B / A OR B / NOT C — booleanos
• titulo:sqlite — restringe à coluna
• NEAR(a b, 5) — proximidade

Caracteres especiais (", *, AND, etc.) na entrada do usuário devem ser escapados ou sanitizados no app — nunca concatene a string crua do usuário na query FTS.

Modos de conteúdo

1. Contentful (padrão): a própria FTS armazena o texto. INSERT/UPDATE/DELETE na virtual table.
2. Contentless: content='' — só índice; recupera texto de outra fonte.
3. External content: content='tabela' content_rowid='id' — a FTS indexa uma tabela real; sync via triggers (próximas aulas / D1).

CREATE TABLE artigos (
  id INTEGER PRIMARY KEY,
  titulo TEXT NOT NULL,
  corpo TEXT NOT NULL
);

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

Sem triggers, a FTS não atualiza sozinha quando você muda artigos.

Combinação com SQL relacional

MATCH filtra candidatos; o restante é SQL normal:

SELECT a.id, a.titulo, a.publicado_em
FROM artigos a
JOIN artigos_fts f ON f.rowid = a.id
WHERE artigos_fts MATCH 'cloudflare OR workers'
  AND a.publicado_em >= '2024-01-01'
ORDER BY a.publicado_em DESC
LIMIT 20;

Em D1/Workers o nome da extensão é fts5 (minúsculo), o mesmo do SQLite.

Exemplos práticos

-- Configuração mínima contentful
CREATE VIRTUAL TABLE docs USING fts5(titulo, corpo);

INSERT INTO docs(titulo, corpo) VALUES
  ('FTS5 no SQLite', 'Índice invertido e operador MATCH'),
  ('Workers e D1', 'SQL na edge com SQLite compatível');

-- Busca por prefixo
SELECT * FROM docs WHERE docs MATCH 'sql*';

-- Coluna específica
SELECT * FROM docs WHERE docs MATCH 'titulo:workers';

// Sanitização básica de tokens (exemplo didático)
function ftsQueryFromUser(input) {
  const tokens = String(input)
    .toLowerCase()
    .replace(/[^\p{L}\p{N}\s]/gu, ' ')
    .trim()
    .split(/\s+/)
    .filter(Boolean)
    .slice(0, 8);
  if (!tokens.length) return null;
  return tokens.map((t) => `"${t}"`).join(' AND ');
}

Problemas e como resolver

Resumo

FTS5 transforma SQLite em um índice de texto lexical via tabela virtual, tokenizers e MATCH. Escolha o modo de conteúdo certo, sanitize a query do usuário e combine o índice com filtros relacionais. Nas próximas aulas: ranking BM25, snippets e o fluxo completo no Cloudflare D1.