ducksassy

Documentation

Approximate string matching inside DuckDB.

DuckHTS exposes FASTA and FASTQ records as DuckDB relations. Sassy supplies the approximate matching kernel. ducksassy connects them: file searches return matches with edit cost, coordinates, strand and CIGAR alongside the input record’s columns. Scalar functions search sequence values from any relation. They also search ordinary ASCII text columns, so fuzzy grep can be a SQL query.

This follows DuckHTS’s composability model: the file reader and native matcher handle their respective mechanics; DuckDB handles the join, filtering and aggregation. For example, give each guide its own PAM length and search a FASTA relation:

WITH guides(guide_id, guide, pam_length) AS (
    VALUES ('g1', 'ACGTNGG', 3), ('g2', 'ACGTAGG', 2)
)
SELECT r.name AS reference_name, g.guide_id,
       hit.text_start, hit.text_end, hit.strand
FROM guides AS g
CROSS JOIN read_fasta('test/data/references.fasta', scan_mode := 'sequential') AS r
CROSS JOIN LATERAL unnest(sassy_crispr_matches(g.guide, r.sequence, 0,
    pam_length := g.pam_length)) AS matches(hit)
ORDER BY reference_name, guide_id, hit.text_start, hit.text_end, hit.strand;

pam_length comes from each guide row. The lateral join expands its matches; the output can be joined to other annotations or grouped by guide without another file format. Use LEFT JOIN LATERAL ... ON true to retain rows with no hit.

What it does

• Search with up to k substitutions, insertions or deletions. IUPAC ambiguity codes work in patterns and targets; reverse-complement search is on by default.
• Search one pattern, a panel, or a guide with a PAM. CRISPR search applies Sassy’s exact PAM endpoint filter and excludes targets above the configured N fraction. The CRISPR semantics below spell out the filter.
• Search files through DuckHTS, or call the scalar functions on any sequence column. Results stay in DuckDB for joins, counts and downstream queries.

The library and adapter comparison measures direct upstream Sassy calls, the Rust C ABI and SQL over identical resident inputs, checking the complete hit multisets. The buffer reuse measurements report the current SQL overhead and the effect of reusing native result storage.

The measured workloads include eight 23-base guides against a 4.6 Mb E. coli reference and 262,144 sequence rows. The timed comparison includes Ducksassy’s scalar and AVX2 backends and an upstream Sassy CLI baseline for the same CRISPR workload. The report records startup, threading, output work and cache conditions: persistent SQL aggregates hits, while the upstream CLI starts a process and writes TSV. The 4.6 Mb input is one bacterial record; these results do not establish whole-human-genome throughput or peak memory use.

Quick start

Released DuckDB v1 host

DuckDB v1.5.5 can load the stable v1.2.0 C API artifact without DuckHTS or an external SQL bootstrap:

make sdk-v1 release-v1
.deps-v1/cli/duckdb -unsigned -no-init

LOAD 'build-v1/ducksassy.duckdb_extension';
SELECT sassy_count('ACGT', 'TTACGT', 0, 'dna', false);
SELECT * FROM sassy_grep('error', 'error: disk full', 0);
SELECT * FROM sassy_backend_info();

Download the CLI separately as described in host architecture, setup and measurements. Every v1 function, with signatures and examples, is listed in the function reference. V1 LOAD writes no catalog entries and works on a read-only database. Scalars use positional trailing options: alphabet, rc, all_endpoints, cigar_format for matches, and pam_length, allow_pam_edits, max_n_frac, rc, cigar_format for CRISPR. Defaults and VARCHAR/BLOB support match v2. V1 has no file or relation-name helpers; compose native calls with a table, CTE or explicitly loaded DuckHTS reader:

LOAD '/absolute/path/duckhts.duckdb_extension';
SELECT r.*, hit
FROM read_fasta('reference.fa', scan_mode := 'sequential') r
CROSS JOIN LATERAL unnest(
    sassy_matches('ACGTAGG', r.sequence, 1, 'iupac', false, false, 'both')
) AS matches(hit);

Use read_fastq(...) for reads, _many with a pattern list for panels, or sassy_crispr_matches(guide, r.sequence, k) for CRISPR. The v1 build uses DUCKSASSY_HOST=v1; standalone CMake defaults to v2. Keep their build directories separate. Distribution CI uses make configure release test_release, which produces the v1 artifact under build/release/; see the CI contract. The distribution matrix covers Linux and macOS x86-64/ARM64, Windows x86-64 MinGW/Rtools, and the default DuckDB-Wasm MVP/EH bundles. MSVC is unsupported. Wasm uses scalar search and a fail-stop Rust panic ABI: an internal panic invalidates that worker, which the caller terminates before starting a fresh worker that can load and query the extension. The optional COI/threads bundle requires an atomics-enabled Rust standard library. See the portable build evidence.

Pinned C API v2 host

The v2 artifact targets the DuckDB C API v2 preview. make setup fetches the matching CLI and checks the bundled SDK. On Linux x86-64 you need C/C++ compilers, CMake ≥ 3.20, Python ≥ 3.11, Git, R ≥ 4.1 and rustup.

make setup JOBS=4
make release-v2
.deps/duckdb-build/duckdb -unsigned -no-init

Then, in the DuckDB CLI:

SET GLOBAL lambda_syntax='ENABLE_SINGLE_ARROW';
INSTALL '.deps/duckhts.duckdb_extension';
INSTALL 'build/ducksassy.duckdb_extension';
.read sql/ducksassy.sql

The lambda setting is required by DuckHTS 1.5.2 and applies to the whole database instance, so use a dedicated one. V2 keeps its connection-scoped macros, := scalar options and file/relation helpers. Positional trailing options are v1-only: a same-named macro shadows a native function in v2 (binding probe). Supported host versions are pinned in ducksassy-package.json. When upgrading, move an outdated .deps/sassy-source checkout aside before rerunning make setup.

R package

Rducksassy builds the stable C API v1 extension from bundled sources and provides rducksassy_connect() and rducksassy_load() for the CRAN duckdb package.

install.packages("Rducksassy")
con <- Rducksassy::rducksassy_connect()
DBI::dbGetQuery(con, "SELECT * FROM sassy_grep('timeout', 'request timedout', 1)")
DBI::dbDisconnect(con, shutdown = TRUE)

Install and load the signed DuckHTS community extension when FASTA, FASTQ or BAM readers are needed; the Ducksassy scalar functions accept its sequence columns.

make vendor-rust refreshes the shared Rust source archive using the committed lockfile. Native and R package builds use it offline. make r-package stages the R package’s sources and builds its source tarball.

Examples

Every example below runs against the bundled test fixtures and is rendered live from this README.

Fuzzy grep over text rows

Search a text column with one allowed edit. ASCII mode requires rc := false; matches return byte offsets within each value.

WITH lines(line_id, message) AS (
    VALUES (1, 'error: disk full'),
           (2, 'errot: disk full'),
           (3, 'ready')
)
SELECT line_id, message, hit.text_start, hit.text_end, hit.cost
FROM lines
CROSS JOIN LATERAL unnest(sassy_matches('error', message, 1,
    alphabet := 'ascii', rc := false)) AS matches(hit)
ORDER BY line_id, hit.text_start;

The input can also be a CSV, Parquet file or any other DuckDB relation with a text column. ASCII matching is case-insensitive; it is not Unicode text search.

For one long ASCII value, sassy_grep returns rows from a stateful scan. It searches overlapping 1 KiB regions and gives each match endpoint to one region. DuckDB can stop requesting regions once an outer LIMIT is satisfied:

SELECT text_start, text_end, cost, cigar
FROM sassy_grep('error', 'error: ' || repeat('ready ', 100000), 1)
LIMIT 1;

This table function accepts constant expressions, as DuckDB requires for table function arguments. It returns all qualifying endpoints, including nearby alternative alignments; sassy_matches defaults to rightmost local minima. The scalar function above still accepts a different text value on every row. DuckDB constructs the constant text argument before the scan starts, so LIMIT saves search work but does not avoid holding that argument in memory.

Search a FASTA reference

Find a pattern in each FASTA record, with coordinates, strand and CIGAR:

SELECT name AS reference_name, hit.text_start, hit.text_end, hit.strand, hit.cigar
FROM sassy_search_fasta('test/data/references.fasta', 'ACGTAGG', 0,
                       alphabet := 'iupac', rc := false)
ORDER BY name, hit.text_start, hit.text_end;

With alphabet := 'iupac', ambiguous bases in the reference (including N) match any base they could stand for. sassy_panel_search_fasta() takes a list of patterns instead. Both stream the file sequentially and need no index.

Search for CRISPR sites

Allow one edit over the full guide and apply the exact PAM endpoint filter. This synthetic sequence illustrates the API; it is not an experimental guide.

SELECT name AS reference_name, hit.text_start, hit.text_end, hit.cost, hit.strand, hit.cigar
FROM sassy_crispr_search_fasta('test/data/references.fasta', 'ACGTNGG', 1)
ORDER BY name, hit.text_start, hit.text_end, hit.strand;

Use sassy_crispr_panel_search_fasta() for a guide panel, or sassy_crispr_matches() / sassy_crispr_matches_many() on sequence values, as in the join at the top.

Search FASTQ reads

SELECT name AS read_name, hit.text_start, hit.text_end, hit.cost, hit.strand, hit.cigar
FROM sassy_search_fastq('test/data/reads.fastq', 'ACGA', 0, rc := false)
ORDER BY name, hit.text_start, hit.text_end;

Count hits for a whole panel at once. Hits carry the zero-based pattern_idx, so duplicate patterns remain distinct:

SELECT hit.pattern_idx, count(*) AS observations
FROM sassy_panel_search_fastq('test/data/reads.fastq', ['ACGA', 'TTGC'], 0, rc := false)
GROUP BY hit.pattern_idx
ORDER BY hit.pattern_idx;

Search a table or view

Anything with a sequence column works, including views:

CREATE TEMP VIEW reads AS
SELECT * FROM read_fastq('test/data/reads.fastq', scan_mode := 'sequential');

SELECT name AS read_name, hit.text_start, hit.text_end, hit.cigar
FROM sassy_search_table('reads', 'ACGA', 0, rc := false)
ORDER BY name, hit.text_start, hit.text_end;

For other column names or computed sequences, use the scalar functions directly:

SELECT r.name AS read_name, hit.text_start, hit.text_end, hit.cigar
FROM reads AS r
CROSS JOIN LATERAL unnest(sassy_matches('ACGA', r.sequence, 0,
    rc := false)) AS matches(hit)
ORDER BY read_name, hit.text_start, hit.text_end;

Search sequence values

SELECT sassy_contains('ACGA', 'TTACGATT', 0, rc := false) AS contains;

SELECT unnest(sassy_matches('ACGA', 'TTACGATT', 0, rc := false), recursive := true);

Function cheat sheet

Value functions accept VARCHAR or BLOB (panels: VARCHAR[] or BLOB[]); pattern and text must use the same type. BLOBs hold plain sequence bytes, so decode DuckHTS nt16/nt4 packed symbols first. sassy_count and sassy_contains skip building CIGAR strings.

Packed CIGAR

Set cigar_format := 'packed' on the general or CRISPR match functions for typed cigar_ops UINTEGER[] without formatting text CIGAR (cigar is NULL). Set cigar_format := 'both' for both representations. The default 'text' returns text CIGAR and NULL cigar_ops. All formats share a fixed result struct with both nullable fields. Packed ops include soft clips for any partial pattern coverage returned by the search.

SELECT hit.text_start, hit.cigar_ops
FROM UNNEST(sassy_matches('ACGTTGCA', 'GGTGCAAACGTCC', 1,
    alphabet := 'dna', cigar_format := 'packed')) AS matches(hit)
WHERE hit.strand = '-';

When a DuckHTS build provides cigar_aligned_blocks(cigar, pos), the typed result can be passed directly (this snippet is not an executed example):

SELECT cigar_aligned_blocks(hit.cigar_ops, hit.text_start)
FROM UNNEST(sassy_matches('ACGTTGCA', 'GGTGCAAACGTCC', 1,
    alphabet := 'dna', cigar_format := 'packed')) AS matches(hit)
WHERE hit.strand = '-';

The fine print

SELECT * FROM sassy_backend_info() ORDER BY name;

Development

make test-v2 sql-test oracle-test readme

oracle-test compares complete CRISPR hit multisets against the Sassy 0.2.6 CLI across 36 profiles and 864 guide/record pairs. make r-test runs DBI integration against the R preview host pinned in ducksassy-package.json, installed separately under .deps/Rlib/. make benchmarks re-renders the benchmark report.

License and credits

ducksassy is licensed under the GNU GPL, version 2 or later. Sassy is by Rick Beeloo and Ragnar Groot Koerkamp (MIT). File reading comes from DuckHTS.