Búsqueda full-text en SQLite con FTS5 y MATCH

LIKE no escala

Si alguna vez has buscado texto en SQLite, seguro que has tirado de LIKE '%palabra%'. Funciona bien con tablas pequeñas y se cae a pedazos en cuanto crecen. No hay ningún índice que pueda echar una mano: SQLite tiene que recorrer fila por fila, pasarla a minúsculas y comprobar si contiene la subcadena. Los límites entre palabras, el ranking, las consultas con varias palabras y la búsqueda por prefijo corren por tu cuenta.

Aquí es donde entra FTS5, la respuesta que ya viene incluida. Es un tipo de tabla virtual que mantiene un índice invertido sobre tus columnas de texto, entiende un pequeño lenguaje de consulta y ordena los resultados con BM25. Viene de serie con SQLite, así que no hay nada que instalar.

Cómo crear una tabla virtual FTS5 en SQLite

Las tablas FTS5 se crean con CREATE VIRTUAL TABLE ... USING fts5(...), indicando las columnas de texto que quieres indexar:

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

INSERT INTO posts (title, body) VALUES
  ('Búsqueda de texto completo en SQLite', 'FTS5 es rápido y está integrado.'),
  ('Índices en SQLite', 'Los árboles B impulsan la mayoría de las búsquedas en SQLite.'),
  ('Por qué usar SQLite', 'Se ejecuta en proceso y almacena los datos en un solo archivo.');

SELECT title FROM posts WHERE posts MATCH 'sqlite';

Tres detalles que vale la pena destacar. Las columnas no llevan tipo: FTS5 trata todo como texto. El operador MATCH se aplica contra el nombre de la tabla (posts MATCH ...), no contra una columna. Y la consulta no distingue mayúsculas y se tokeniza, así que 'sqlite' encuentra SQLite en cualquiera de las filas.

El lenguaje de consultas del operador MATCH

MATCH admite mucho más que una sola palabra. La cadena de búsqueda tiene su propia mini-gramática:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Búsqueda de texto completo', 'FTS5 admite consultas por frase y por prefijo.'),
  ('Índices', 'Un índice B-tree acelera las búsquedas por igualdad.'),
  ('Disparadores', 'Usa disparadores para mantener FTS sincronizado con la tabla de origen.');

SELECT title FROM posts WHERE posts MATCH 'fts5 AND prefix';
SELECT title FROM posts WHERE posts MATCH '"keep fts"';
SELECT title FROM posts WHERE posts MATCH 'trig*';
SELECT title FROM posts WHERE posts MATCH 'index NOT trigger';

Qué hace cada uno:

• 'fts5 AND prefix' — ambos términos deben aparecer (en cualquier orden, en cualquier parte de la fila).
• '"keep fts"' — frase exacta, en ese orden.
• 'trig*' — búsqueda por prefijo, encuentra trigger, triggers, trigonometry...
• 'index NOT trigger' — contiene index, pero no trigger.

También puedes apuntar a una sola columna con columna:término, por ejemplo 'title:sqlite'. La gramática completa admite paréntesis para agrupar y OR para alternativas — la misma forma que esperarías de cualquier motor de búsqueda.

Ranking con BM25 en SQLite

Por defecto, FTS5 añade una columna oculta rank a cada fila. Es la puntuación de relevancia BM25 — cuanto más bajo el número, mejor el match. Ordena por esa columna para obtener primero los resultados más relevantes:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Tutorial de SQLite', 'Aprende SQLite desde cero.'),
  ('SQLite vs Postgres', 'Comparando SQLite con Postgres para aplicaciones pequeñas.'),
  ('SQLite SQLite SQLite', 'Una publicación que menciona SQLite muchas veces.');

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

¿Quieres dar más peso a unas columnas que a otras? Pásale los pesos a bm25() —uno por cada columna, en el mismo orden en que las declaraste—:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Índices de SQLite', 'Una breve nota sobre los árboles B.'),
  ('Notas sobre Postgres', 'Incluye un párrafo sobre sqlite a modo de comparación.');

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

El primer post gana porque sqlite aparece en title (con peso 10×) en lugar de solo en body (peso 1×). Elige los pesos según cómo quiera rankear realmente tu aplicación.

Mantener el índice sincronizado

La tabla FTS5 más sencilla guarda su propia copia del texto. Eso está bien para datos tipo log en los que solo haces inserciones, pero la mayoría de las aplicaciones ya tienen una tabla real y lo que quieren es que el FTS la siga. El patrón limpio para esto es una tabla FTS de tipo external content acompañada de tres 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
  ('Triggers en SQLite', 'Cómo usar triggers AFTER INSERT.'),
  ('Patrones de FTS5', 'El contenido externo mantiene los datos en un solo 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' le indica a FTS5 que no guarde el texto otra vez: lo recuperará de la tabla articles cuando haga falta. Los triggers se encargan de replicar las escrituras en el índice FTS. Así, articles pasa a ser la fuente de verdad y articles_fts queda como una estructura de búsqueda paralela.

La sentencia INSERT INTO articles_fts(articles_fts, ...) VALUES ('delete', ...), que se ve algo rara a primera vista, es la sintaxis de comandos propia de FTS5 para pedirle al índice que elimine una fila.

Snippets y resaltado de coincidencias en SQLite FTS5

En los resultados de búsqueda casi siempre queremos mostrar una vista previa con los términos coincidentes destacados. Para eso, FTS5 trae dos funciones:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Primeros pasos', 'SQLite es un motor de base de datos pequeño, rápido e integrado que almacena los datos en un único archivo en 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(tabla, indice_columna, apertura, cierre) devuelve el texto completo de la columna con los tokens coincidentes envueltos.
• snippet(tabla, indice_columna, apertura, cierre, puntos_suspensivos, num_tokens) devuelve un fragmento corto centrado en la coincidencia.

Los índices de columna empiezan en cero, en el orden en que las declaraste. Estas son las piezas básicas para conseguir ese efecto de "términos coincidentes resaltados en amarillo" que toda interfaz de búsqueda necesita.

Tropiezos que conviene tener presentes

Hay un par de detalles que suelen pillar a la gente desprevenida:

• MATCH solo funciona sobre tablas FTS. No puedes hacer MATCH contra una columna normal. Si necesitas búsqueda sobre una tabla ya existente, usa el patrón de external content que vimos antes.
• No olvides ordenar por rank. Sin eso, FTS5 te devuelve las filas en el orden en que están almacenadas, que no tiene nada que ver con la relevancia.
• El tokenizer importa, y mucho. El tokenizer por defecto (unicode61) parte por los límites de palabra Unicode y pasa todo a minúsculas. Si quieres stemming (que run coincida con running), usa el tokenizer porter: USING fts5(body, tokenize='porter').
• FTS5 no tolera erratas. Hace coincidencia por prefijo, no búsqueda difusa. Si quieres un "¿quizás quisiste decir...?", eso es una capa por encima de FTS5.
• Las tablas sin contenido (content='') ocupan menos pero pierden información. Puedes buscar en ellas, pero no recuperar el texto original — solo el rowid. Útil cuando guardas el texto en otro sitio.

Lo que viene: funciones de ventana

Con FTS5 ya tienes cubierta la búsqueda de texto. La siguiente página entra en otro tipo de consulta avanzada: las funciones de ventana, que te permiten calcular totales acumulados, rankings y analíticas por grupo sin colapsar las filas en agregados.