Skip to content

fix(swarm-mail): FTS5 MATCH incompatible with Drizzle sql template parameterization - #201

Closed
ddukki wants to merge 1 commit into
joelhooks:mainfrom
ddukki:main
Closed

ddukki wants to merge 1 commit into
joelhooks:mainfrom
ddukki:main

Conversation

@ddukki

@ddukki ddukki commented Jun 21, 2026 •

Copy link
Copy Markdown

Problem

hivemind_find always returns { results: [], count: 0 } for FTS searches. Two bugs in store.ts:

Bug 1: FTS5 MATCH uses double-quoted identifiers instead of string literals

packages/swarm-mail/src/memory/store.ts:336:

const quotedQuery = `"${searchQuery.replace(/"/g, '""')}"`;

In SQLite, MATCH "hivemind" treats "hivemind" as a column reference (identifier), not a string literal. Should be MATCH 'hivemind' (single-quoted string literal).

Bug 2: Drizzle sql template parameterization incompatible with FTS5 MATCH

packages/swarm-mail/src/memory/store.ts:368:

const results = await db.all<...>(sql`
  ...
  WHERE fts.content MATCH ${quotedQuery}
  ...
`);

Drizzle's sql template tag parameterizes interpolated values as ? placeholders. FTS5's MATCH operator does not support bound parameters -- the expression must be a literal string in the SQL text. The query silently returns 0 rows.

Changes

packages/swarm-mail/src/memory/store.ts -- Fix FTS5 MATCH quoting + parameterization

Two changes:

  1. Fix MATCH quoting (double-quote to single-quote)
  2. Use sql.raw() to inline the value directly into SQL text, bypassing Drizzle's ? parameterization
- const quotedQuery = `"${searchQuery.replace(/"/g, '""')}"`;
+ const escapedQuery = searchQuery.replace(/'/g, "''");
- WHERE fts.content MATCH ${quotedQuery}
+ WHERE fts.content MATCH ${sql.raw("'" + escapedQuery + "'")}

Testing

  • FTS search returns results after fix (confirmed against local DB)
  • Vector search continues to work (unchanged)
  • SQL injection prevented (single quotes escaped)
  • No regressions in existing tests

Summary by CodeRabbit

  • Bug Fixes
    • Improved Full Text Search (FTS) query handling to properly process special characters in search queries, particularly single quotes, ensuring more reliable search results.

FTS5 MATCH operator rejects bound parameters (question-mark
placeholders). Drizzle's sql template tag parameterizes interpolated
values, causing ftsSearch to silently return 0 rows.

Also fixes double-quote identifier quoting: MATCH needs single-quoted
string literals, not double-quoted identifiers.

Use sql.raw() to inline the MATCH value as a SQL string literal.
@ddukki
ddukki requested a review from joelhooks as a code owner June 21, 2026 03:19
@vercel

vercel Bot commented Jun 21, 2026

Copy link
Copy Markdown

@ddukki is attempting to deploy a commit to the joelhooks' projects Team on Vercel.

A member of the Team first needs to authorize it.

@coderabbitai

coderabbitai Bot commented Jun 21, 2026 •

Copy link
Copy Markdown

Review Change Stack

Caution

Review failed

The pull request is closed.

ℹ️ Recent review info
⚙️ Run configuration

Configuration used: defaults

Review profile: CHILL

Plan: Pro

Run ID: 2a623cda-9e31-452c-b3e6-7ad1baf92a91

📥 Commits

Reviewing files that changed from the base of the PR and between b685253 and 5ad72ff.

📒 Files selected for processing (1)
  • packages/swarm-mail/src/memory/store.ts

📝 Walkthrough

Walkthrough

In ftsSearch within createMemoryStore, the FTS5 query escaping strategy is updated: the prior double-quote-based quotedQuery variable is replaced with an escapedQuery that escapes single quotes, and the MATCH clause now uses sql.raw("'" + escapedQuery + "'") to embed the query as a SQL string literal.

Changes

FTS5 Query Escaping Fix

Layer / File(s) Summary
FTS5 single-quote escaping and MATCH predicate
packages/swarm-mail/src/memory/store.ts
Replaces double-quote-based quotedQuery with escapedQuery (single-quote escaping) and updates the WHERE fts.content MATCH clause to inject the escaped value via sql.raw("'" + escapedQuery + "'").

Estimated code review effort

🎯 2 (Simple) | ⏱️ ~5 minutes

Possibly related issues

Poem

🐇 A quote in the query caused quite a fuss,
The FTS5 engine made quite a fuss.
A single swap: escape apostrophes now,
sql.raw takes the stage with a flourish and bow.
No more broken searches, the rabbit hops free! 🌿

✨ Finishing Touches
🧪 Generate unit tests (beta)
  • Create PR with unit tests

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

Comment @coderabbitai help to get the list of available commands and usage tips.

@github-actions

Copy link
Copy Markdown
Contributor

Hi @ddukki, thanks for your interest in contributing!

This project requires that pull request authors are vouched, and you are not in the list of vouched users.

This PR will be closed automatically. See https://github.com/joelhooks/swarm-tools/blob/main/CONTRIBUTING.md for more details.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant