Busca Full-Text no SQLite: FTS5 e operador MATCH

LIKE não escala

Se você já fez busca em texto no SQLite, provavelmente apelou pro LIKE '%palavra%'. Funciona numa tabela pequena, mas desmorona quando ela cresce. Não existe índice que ajude — o SQLite precisa varrer linha por linha, passar pra minúsculo e procurar a substring. Tratar limites de palavra, ordenação por relevância, consultas com múltiplas palavras e busca por prefixo fica tudo por sua conta.

O FTS5 é a resposta nativa pra isso. É um tipo de tabela virtual que mantém um índice invertido sobre as suas colunas de texto, entende uma pequena linguagem de consulta e ordena os resultados com BM25. Já vem incluso no SQLite por padrão — não precisa instalar extensão nenhuma.

Criando uma virtual table FTS5

Você cria uma tabela FTS5 com CREATE VIRTUAL TABLE ... USING fts5(...), listando as colunas de texto que quer indexar:

CREATE VIRTUAL TABLE posts USING fts5(title, body);

INSERT INTO posts (title, body) VALUES
  ('Busca de texto completo no SQLite', 'O FTS5 é rápido e já vem embutido.'),
  ('Índices no SQLite', 'As B-trees são a base da maioria das buscas no SQLite.'),
  ('Por que usar o SQLite', 'Ele roda em processo e armazena os dados em um único arquivo.');

SELECT title FROM posts WHERE posts MATCH 'sqlite';

Três coisas valem a pena destacar. As colunas não têm tipo — o FTS5 trata tudo como texto. O operador MATCH é aplicado ao nome da tabela (posts MATCH ...), não a uma coluna. E a busca é case-insensitive e tokenizada, então 'sqlite' encontra SQLite em qualquer uma das linhas.

A linguagem de consulta do MATCH

O MATCH aceita bem mais do que uma única palavra. A string de consulta tem sua própria mini-gramática:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Busca de texto completo', 'O FTS5 suporta consultas de frase e de prefixo.'),
  ('Índices', 'Um índice B-tree acelera buscas por igualdade.'),
  ('Gatilhos', 'Use gatilhos para manter o FTS sincronizado com a tabela de origem.');

SELECT title FROM posts WHERE posts MATCH 'fts5 AND prefixo';
SELECT title FROM posts WHERE posts MATCH '"manter o fts"';
SELECT title FROM posts WHERE posts MATCH 'gatil*';
SELECT title FROM posts WHERE posts MATCH 'índice NOT gatilho';

O que cada um faz:

• 'fts5 AND prefixo' — os dois termos precisam aparecer (em qualquer ordem, em qualquer lugar da linha).
• '"manter o fts"' — frase exata, nessa ordem.
• 'gatil*' — busca por prefixo, casa com trigger, triggers, trigonometry...
• 'índice NOT gatilho' — contém index, mas não contém trigger.

Também dá pra restringir a busca a uma coluna específica usando column:term, tipo '(consistent with Portuguese column names if applicable)'. A gramática completa ainda inclui parênteses pra agrupar expressões e OR pra alternativas — basicamente o que você esperaria de qualquer mecanismo de busca.

Ranqueando com BM25

Por padrão, o FTS5 adiciona uma coluna oculta rank em cada linha. É a pontuação de relevância BM25 — quanto menor o número, melhor o match. Ordene por ela pra trazer os resultados mais relevantes primeiro:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Tutorial de SQLite', 'Aprenda SQLite do zero.'),
  ('SQLite vs Postgres', 'Comparando SQLite com Postgres para aplicações pequenas.'),
  ('SQLite SQLite SQLite', 'Uma postagem que menciona SQLite várias vezes.');

SELECT title, rank
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY rank;

Quer dar mais peso a algumas colunas que outras? Basta passar os pesos para bm25() — um para cada coluna, na mesma ordem da declaração:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Índices no SQLite', 'Uma breve nota sobre árvores B.'),
  ('Notas do Postgres', 'Inclui um parágrafo sobre sqlite para comparação.');

SELECT title, bm25(posts, 10.0, 1.0) AS score
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY score;

O primeiro post vence porque sqlite aparece no title (peso 10×) e não só no body (peso 1×). Escolha pesos que reflitam como sua aplicação realmente quer ranquear os resultados.

Mantendo o índice sincronizado

A forma mais simples de usar uma tabela FTS5 é deixar ela guardar a própria cópia do texto. Isso funciona bem para dados estilo log, em que você só faz insert, mas a maioria das aplicações já tem uma tabela "de verdade" e quer que o FTS acompanhe ela. O padrão mais limpo nesse caso é uma tabela FTS com external content junto com três triggers.

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT,
    body TEXT
);

CREATE VIRTUAL TABLE articles_fts USING fts5(
    title, body,
    content='articles',
    content_rowid='id'
);

CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

INSERT INTO articles (title, body) VALUES
  ('Gatilhos no SQLite', 'Como usar gatilhos AFTER INSERT.'),
  ('Padrões do FTS5', 'O conteúdo externo mantém os dados em um único lugar.');

SELECT a.id, a.title
FROM articles a
JOIN articles_fts f ON f.rowid = a.id
WHERE articles_fts MATCH 'triggers';

content='articles' diz ao FTS5 para não guardar o texto em si — ele vai buscar na tabela articles quando precisar. Os triggers replicam as escritas no índice FTS. Assim, articles passa a ser a fonte da verdade e articles_fts fica só como estrutura de busca ao lado.

Aquele INSERT INTO articles_fts(articles_fts, ...) VALUES ('delete', ...) de cara estranha é a sintaxe de comando do FTS5 para mandar o índice remover uma linha.

Snippet e highlight nos resultados

Em geral, queremos exibir um trecho do resultado com os termos da busca destacados. O FTS5 oferece duas funções justamente para isso:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Primeiros passos', 'SQLite é um mecanismo de banco de dados pequeno, rápido e embutido que armazena dados em um único arquivo no disco.');

SELECT
    highlight(posts, 0, '<b>', '</b>') AS title,
    snippet(posts, 1, '<mark>', '</mark>', '...', 10) AS preview
FROM posts
WHERE posts MATCH 'sqlite database';

• highlight(tabela, indice_coluna, abre, fecha) retorna o texto completo da coluna com os termos encontrados envolvidos pelas marcações.
• snippet(tabela, indice_coluna, abre, fecha, reticencias, qtd_tokens) retorna um trecho curto centralizado no termo encontrado.

Os índices das colunas começam em zero, seguindo a ordem de declaração. São esses os blocos básicos para aquele efeito de "termos buscados em amarelo" que toda interface de busca precisa ter.

Armadilhas que vale a pena conhecer

Algumas coisas que costumam pegar a galera de surpresa:

• MATCH só funciona em tabelas FTS. Não dá para usar MATCH em uma coluna comum. Se você precisa buscar dentro de uma tabela já existente, use o padrão de conteúdo externo (external content) que mostrei acima.
• Não esqueça de ordenar por rank. Sem isso, o FTS5 devolve as linhas na ordem de armazenamento, que não tem nada a ver com relevância.
• O tokenizer faz toda a diferença. O tokenizer padrão (unicode61) quebra nas fronteiras de palavra Unicode e passa tudo para minúsculas. Para ter stemming (fazer correr casar com correndo), use o tokenizer porter: USING fts5(body, tokenize='porter').
• FTS5 não tolera erros de digitação. Ele faz busca por prefixo, não busca aproximada (fuzzy). Se você precisa daquele "você quis dizer...", isso é uma camada acima do FTS5.
• Tabelas sem conteúdo (content='') são menores, mas perdem informação. Você consegue buscar nelas, mas não consegue recuperar o texto original — só o rowid. Útil quando o texto está guardado em outro lugar.

A seguir: window functions

O FTS5 cobre a parte de busca em texto. A próxima página fala de um outro tipo de consulta avançada — as window functions, que permitem calcular totais acumulados, rankings e análises por grupo sem precisar colapsar suas linhas em agregações.