Skip to content

DuckDB Extension

Marisa

Build compact MARISA tries from strings in DuckDB. Store a trie as a BLOB, then check membership or retrieve names with a shared prefix.

38,576
extension loads · last 90 days
On this page
Sample data for the examples

Each browser example starts with these sample tables; sample files are shared across examples on this page. Run this setup first when trying the examples in your own DuckDB session.

-- Fixtures for marisa example snippets. Idempotent: uses CREATE OR REPLACE.

-- Source: cookbook.mdx "Build a trie"
CREATE OR REPLACE TABLE employees(name TEXT);
INSERT INTO employees VALUES
  ('Alice'),('Bob'),('Charlie'),('David'),('Eve'),('Frank'),
  ('Mallory'),('Megan'),('Oscar'),('Melissa');

-- Trie built from employees, referenced by lookup/predictive examples.
CREATE OR REPLACE TABLE employees_trie AS
  SELECT marisa_trie(name) AS trie FROM employees;

-- Source: cookbook.mdx "Longest-prefix match"
CREATE OR REPLACE TABLE country_codes(code TEXT);
INSERT INTO country_codes VALUES ('U'), ('US'), ('USA');

CREATE OR REPLACE TABLE country_codes_trie AS
  SELECT marisa_trie(code) AS trie FROM country_codes;

-- Stand-in for the /usr/share/dict/words wordlist used by the
-- "Big static dictionary" cookbook section. Small, deterministic
-- set with shared "duck" prefixes so marisa_predictive(..., 'duck', ...)
-- still returns plausible completions.
CREATE OR REPLACE TABLE words(word TEXT);
INSERT INTO words VALUES
  ('duck'), ('duckbill'), ('duckboard'), ('ducker'), ('ducking'),
  ('duckling'), ('ducks'), ('ducky'),
  ('rouge'), ('rough'), ('round'), ('route'), ('routine'),
  ('apple'), ('banana'), ('cherry');

CREATE OR REPLACE TABLE words_trie AS
  SELECT marisa_trie(word) AS trie FROM words;

-- "dictionary" alias used by technical-details.mdx "What you can do with one query".
CREATE OR REPLACE TABLE dictionary(word TEXT);
INSERT INTO dictionary SELECT word FROM words;

-- Source: cookbook.mdx "One trie per partition / tenant"
CREATE OR REPLACE TABLE signups(day DATE, name TEXT);
INSERT INTO signups VALUES
  (DATE '2024-01-01', 'Alice'),
  (DATE '2024-01-01', 'Megan'),
  (DATE '2024-01-02', 'Bob'),
  (DATE '2024-01-02', 'Melissa'),
  (DATE '2024-01-03', 'Charlie');

CREATE OR REPLACE TABLE name_tries AS
SELECT day,
       marisa_trie(name) AS trie
FROM signups
GROUP BY day;

Build a compact dictionary with prefix lookups

A trie organizes strings by their shared prefixes. Marisa builds one from a DuckDB column and stores the dictionary as a BLOB. Queries can check membership, find strings beginning with a prefix, or find a prefix that matches a longer input.

Use it for a reusable vocabulary, a lookup dictionary, or autocomplete suggestions over a known list. The built dictionary is static: create a new trie when its contents change. For approximate spelling matches, use RapidFuzz.

Example

Find names with a common prefix

Build a trie from four names and request up to ten completions for Me.

Tested with DuckDB 1.5.6. Browser examples use Haybarn-Wasm 2.0.0-rc12.

SET allow_extension_repositories = 'allowed';
CREATE EXTENSION REPOSITORY IF NOT EXISTS query_farm
  WITH PREFIX 'https://duckdb-extensions.query.farm';
INSTALL marisa FROM query_farm;
LOAD marisa FROM query_farm;

WITH names(name) AS (
  VALUES ('Alice'), ('Bob'), ('Megan'), ('Melissa')
), dictionary AS (
  SELECT marisa_trie(name) AS trie FROM names
)
SELECT marisa_predictive(trie, 'Me', 10) AS suggestions
FROM dictionary;

Expected result

suggestions
["Megan","Melissa"]

A MARISA trie is static. Rebuild it when the source set changes. Prefix lookup is different from fuzzy matching: it does not correct spelling mistakes.

Common tasks

Deep Dive

Technical Details

Reference

Extension Contents

Quick reference to all available functions and settings organized by category.

Build
marisa_trie() Object type: Aggregate function Aggregate that builds a MARISA trie from a column of strings.
Query
marisa_common_prefix() Object type: Scalar function Returns VARCHAR[] — every string in the trie that is a prefix of the search string, capped at max_results.
marisa_lookup() Object type: Scalar function Returns BOOLEAN — true iff the search string is in the trie.
marisa_predictive() Object type: Scalar function Returns VARCHAR[] — every string in the trie that starts with the given prefix, capped at max_results.

API Reference

Function Reference

Practical Examples

Cookbook

Real-world recipes and patterns for common use cases.

Platform Support

Compatibility

Extension availability may vary by platform and DuckDB version. Check below to ensure this extension supports your environment before installation.

Quick Facts

Release status Stable
Software License MIT
Pricing Free
Written In C++
Source Available Yes
View on GitHub
Usage
38,576
loads · last 90 days

Platforms

DuckDB 2.0.0

  • Linux x86_64aarch64
  • Linux (musl) Not available
  • macOS Apple Silicon
  • Windows x86_64x86_64 (MinGW)
  • WASM Not available
Compiled binary sizes
Platform Architecture Size
Linux x86_64 4.03 MB
Linux aarch64 3.39 MB
macOS Apple Silicon 2.85 MB
Windows x86_64 10.24 MB
Windows x86_64 (MinGW) 17.86 MB

Compressed download sizes for the release shown above.

DuckDB & Haybarn

Release calendar
  • DuckDB v2.0.0 Query.Farm repository Supported
  • Haybarn v1.5.6 Built-in community repository Supported

Browser examples use Haybarn-Wasm 2.0.0-rc12.

Checked .