Skip to main content

@sqb/sqlite-dialect

SQB's dialect plugin for SQLite — teaches @sqb/builder how to render SQLite's SQL syntax. It's shared by both SQLite adapters: @sqb/sqlite (native, node:sqlite/bun:sqlite) and @sqb/sqljs (WebAssembly, sql.js) — since sql.js is SQLite-flavored, it registers under the same 'sqlite' dialect name rather than SQB shipping a separate sqljs-dialect package.

Registration​

Importing this package constructs an internal SqliteSerializer (not exported) and registers it with SerializerRegistry for dialect: 'sqlite'.

import '@sqb/sqlite-dialect';

Besides SQLite's LIMIT/OFFSET syntax and reserved-word list, the serializer suppresses the base RETURNING clause (both SQLite adapters emulate it with a follow-up SELECT) and renders the Match operator as an FTS5 full-text search (since v6.1.0) — see below.

@sqb/sqlite imports this package internally as part of its own entry point; @sqb/sqljs does not import it automatically, so importing @sqb/sqlite-dialect yourself is required alongside @sqb/sqljs. Either way, you don't need to import it yourself when only using @sqb/builder standalone (no @sqb/connect/adapter) and you still want .generate({ dialect: 'sqlite' }) to render SQLite SQL.

Full-text search (FTS5)​

The Match operator, which falls back to = in other dialects, serializes as an FTS5 MATCH under 'sqlite'. The right-hand side is an FTS5 query string; the optional third Match() argument (customArgs) selects one of three shapes:

CallSQLUse when
Match('message', value)message MATCH ?Querying the FTS5 virtual table itself; message is one of its columns.
Match('message', value, 'logs_fts')rowid IN (SELECT rowid FROM logs_fts WHERE message MATCH ?)Querying a regular table indexed by an external-content FTS5 table (content='logs', content_rowid='id'), whose rowids are the queried table's.
Match('l.message', value, { table: 'logs_fts', key: 'l.id' })l.id IN (SELECT rowid FROM logs_fts WHERE message MATCH ?)The same, but comparing the FTS5 rowids with key instead of rowid — for an aliased table in a join, or an INTEGER PRIMARY KEY referenced by name.

Only the last segment of a qualified left-hand side (l.message → message) is used inside the sub-select, since it names a column of the FTS5 table, whatever the queried table is aliased as.

import { Match, Param, Select } from '@sqb/builder';
import { toFtsQuery } from '@sqb/sqlite-dialect';

Select('id').from('logs').where(Match('message', Param('text'), 'logs_fts'));
// select id from logs where rowid IN (select rowid from logs_fts where message MATCH :text)

const params = { text: toFtsQuery('ORU^R01 refused') }; // '"ORU^R01" "refused"'

toFtsQuery()​

function toFtsQuery(text: string): string

Turns text a person typed into an FTS5 query that searches for its words as they are, the way PostgreSQL's plainto_tsquery does: each whitespace-separated word becomes a double-quoted string (embedded " doubled), so FTS5 operators and punctuation (AND, NOT, -, ^, :, *, ") are searched for rather than interpreted, and a row matches only when it contains every word. Returns an empty string for blank input.

toFtsQuery('ORU^R01 refused'); // '"ORU^R01" "refused"'
toFtsQuery('   ');             // ''

Pass the raw FTS5 syntax through unchanged instead when you want to expose prefix (term*), column-filter (col:term), NEAR(), or boolean queries to the caller.

Other exports​

ExportDescription
versionThis package's own version string (e.g. "6.3.0"), kept in step with package.json.

See also​