Full-text search

DuckDB's fts extension adds full-text indexes and BM25 ranking. QuackDB provides small SQL helpers for index management and Ecto helpers for search expressions.

Load the extension

alias QuackDB.FTS

QuackDB.query!(conn, FTS.install())
QuackDB.query!(conn, FTS.load())

DuckDB can autoload extensions in many environments, but explicit install/load keeps setup scripts and examples predictable.

Create an index

QuackDB.query!(
  conn,
  FTS.create_index("documents", :id, [:title, :body],
    stemmer: :porter,
    stopwords: :english,
    overwrite: true
  )
)

Use :all to index all VARCHAR columns:

QuackDB.query!(conn, FTS.create_index("documents", :id, :all, overwrite: true))

Drop the generated index schema with:

QuackDB.query!(conn, FTS.drop_index("documents"))

The index is a snapshot of the table when it was created; rows added later are not searchable until the index is recreated with overwrite: true. DuckDB 2.0 replays a recreated or dropped index from the write-ahead log, so a database closed without a checkpoint reopens with the new index.

DuckDB creates a schema for each index. For main.documents, the generated schema is fts_main_documents; an unqualified documents lives in main and gets the same schema. Use FTS.schema_name/1 when building raw SQL fragments.

Index a materialized source

DuckDB FTS indexes operate on tables, so source scans are often materialized first. This example uses a persistent table so subsequent pooled calls can query it. Temporary tables are session-local: their entire create/index/search/cleanup workflow must stay on one checked-out connection, for example inside a transaction.

use QuackDB.Ecto

alias QuackDB.{DDL, FTS, Source}

source = Source.parquet("s3://analytics/documents/*.parquet")

query =
  from doc in source,
    select: %{id: doc.id, title: doc.title, body: doc.body}

QuackDB.query!(conn, DDL.create_table("docs", as: query))
QuackDB.query!(conn, FTS.create_index("docs", :id, [:title, :body], overwrite: true))

The same pattern works for CSV, JSON, Delta, Iceberg, Hugging Face datasets, or any source expression DuckDB can read. DDL.create_table/2 accepts Ecto queries without pinned parameters because DDL helpers return SQL iodata rather than {sql, params} tuples. Use literal query expressions in the CTAS query, or materialize parameterized results before indexing. Use QuackDB.Secret.create/2 and QuackDB.Extension.load/1 when the source needs credentials or an extension such as httpfs.

Search from direct SQL

schema = FTS.schema_name("main.documents")
score = FTS.match_bm25(~s|"id"|, "duckdb analytics", schema: schema)

QuackDB.query!(conn, [
  "SELECT id, title, ", score, " AS score ",
  "FROM documents WHERE ", score, " > 0 ",
  "ORDER BY score DESC"
])

match_bm25/3 accepts DuckDB's BM25 options. bm25/3 and search_score/3 are aliases when those read better in query-building code:

FTS.match_bm25(~s|"id"|, "duckdb analytics",
  schema: "fts_main_documents",
  fields: [:title, :body],
  k: 1.2,
  b: 0.75,
  conjunctive: false
)

Search from Ecto

Import QuackDB.Ecto.FTS or use QuackDB.Ecto:

import Ecto.Query
import QuackDB.Ecto.FTS

scored =
  from doc in "documents",
    select: %{
      id: doc.id,
      title: doc.title,
      score: match_bm25("fts_main_documents", doc.id, ^"duckdb analytics")
    }

query =
  from doc in subquery(scored),
    where: doc.score > 0,
    order_by: [desc: doc.score],
    limit: 50,
    select: %{id: doc.id, title: doc.title, score: doc.score}

MyApp.AnalyticsRepo.all(query)

The subquery shape keeps the BM25 expression named once and lets the outer query filter, order, and limit by score without repeating the helper call. For large source-text tables, use the same Ecto pattern at a coarser grain first, then join the top matches to detailed rows:

file_scores =
  from file in "files",
    select: %{
      file_id: file.file_id,
      score: match_bm25("fts_main_files", file.file_id, ^"duckdb analytics")
    }

top_files =
  from file in subquery(file_scores),
    where: file.score > 0,
    order_by: [desc: file.score],
    limit: 25

query =
  from fragment in "fragments",
    join: file in subquery(top_files),
    on: fragment.file_id == file.file_id,
    order_by: [desc: file.score, asc: fragment.path],
    select: %{
      file_id: fragment.file_id,
      path: fragment.path,
      content: fragment.content,
      file_score: file.score
    }

MyApp.AnalyticsRepo.all(query)

For static query modules, pass the generated FTS schema as a literal string. For runtime table names, pin the schema name and the helper will emit an Ecto identifier(^schema) fragment:

schema = QuackDB.FTS.schema_name("main.documents")

from doc in "documents",
  where: match_bm25(^schema, doc.id, ^"duckdb analytics") > 0,
  select: %{score: search_score(^schema, doc.id, ^"duckdb analytics")}

Text stemming

DuckDB's FTS extension also exposes stem:

QuackDB.query!(conn, ["SELECT ", FTS.stem("'running'", :english)])

In Ecto:

from doc in "documents", select: stem(doc.body, ^"english")