SQLite advanced features

SQLiteAdvanced exposes the parts of SQLite that have no cross-dialect
equivalent: FTS5 full-text search, the JSON1 functions, generated columns,
window functions, PRAGMA tuning, VACUUM/ANALYZE, savepoints, extension
loading, and SQLite's ON CONFLICT clause vocabulary.

import { createSQLiteAdvanced } from 'ts-prorm-orm';

const sqlite = createSQLiteAdvanced(prorm.getDialectInstance() as any);

Everything here is SQLite-only by construction — it takes a SQLiteDialect.
Use it where SQLite is the target (embedded apps, tests, local development,
Turso/libSQL) and keep it out of code that has to run on several engines.

Full-text search (FTS5)

await sqlite.createFTS5Table(
  'docs_fts',
  [{ name: 'title' }, { name: 'body' }, { name: 'doc_id', notindexed: true }],
  { tokenizer: 'porter', content: 'docs', prefix: [2, 3] },
);

Option	Purpose
`tokenizer`	`porter`, `unicode61`, `trigram`, `ascii` — with per-tokenizer options
`content`	External-content table, so the FTS index shadows a real table
`contentRowId`	Which column links back to it
`prefix`	Prefix-index lengths, for `term*` queries
`notindexed`	A column stored but not searched

Searching:

const { rows } = await sqlite.fullTextSearch('docs_fts', 'database AND index', {
  columns: ['title', 'body'],
  bm25: true,
  highlight: { column: 'body', before: '<mark>', after: '</mark>' },
  limit: 20,
});

The search term is a bound parameter, so user input cannot alter the
statement — though it is still parsed as an FTS5 query expression, so AND,
OR, NEAR and * are operators to the matcher.

A search index that keeps itself current

An external-content FTS5 table stores only the index; the text stays in
your real table and is read back through content_rowid. Three triggers keep
the two in step, so application code writes to articles and never touches
the index:

Create the index over the table

import { Prorm, DataTypes, createSQLiteAdvanced } from 'ts-prorm-orm';

const prorm = new Prorm({ dialect: 'sqlite', storage: './app.db' });
await prorm.connect();

const Article = prorm.define(
  'Article',
  { title: { type: DataTypes.STRING }, body: { type: DataTypes.TEXT } },
  { tableName: 'articles' },
);
await prorm.sync();

const sqlite = createSQLiteAdvanced(prorm.getDialectInstance() as any);

await sqlite.createFTS5Table('articles_fts', [{ name: 'title' }, { name: 'body' }], {
  tokenizer: 'porter',
  content: 'articles',
  contentRowId: 'id',
  prefix: [2, 3],
});

Add the sync triggers

An external-content index is told about a removed row with the special
'delete' command, which carries the old values so FTS5 can find the tokens
to drop. The body is raw SQL here because of that command:

await sqlite.createAfterInsertTrigger('articles_fts_ai', 'articles',
  'INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);');

await sqlite.createAfterDeleteTrigger('articles_fts_ad', 'articles',
  "INSERT INTO articles_fts(articles_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);");

await sqlite.createAfterUpdateTrigger('articles_fts_au', 'articles', `
  INSERT INTO articles_fts(articles_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
  INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);`);

Index what is already there

Rows written before the triggers existed are not in the index yet:

await sqlite.rebuildFTS5('articles_fts');

Search

await Article.create({ title: 'Indexes everywhere', body: 'Covering indexes avoid the table lookup.' });

const { rows } = await sqlite.fullTextSearch('articles_fts', 'index*', {
  columns: ['title', 'body'],
  bm25: true,
  orderBy: [{ column: 'score' }],   // bm25 is lower-is-better
  highlight: { column: 'title', before: '[', after: ']' },
  limit: 5,
});

[
  { "score": -0.0000014, "highlighted": "[Indexes] everywhere", "title": "Indexes everywhere", "body": "Covering indexes avoid the table lookup." },
  { "score": -0.0000013, "highlighted": "[Indexing] in SQLite", "title": "Indexing in SQLite", "body": "A B-tree index speeds up lookups by key." }
]

The row created after the triggers is found without any extra call, and the
porter tokenizer matches Indexing for index*.

[!WARNING]
Without the 'delete' command, an external-content index keeps the old
tokens of every updated or deleted row, and searches return rowids that no
longer match their text. If the triggers were ever missing, run
rebuildFTS5() to regenerate the index from the content table.

Housekeeping: rebuildFTS5(table) and optimizeFTS5(table) issue the
INSERT INTO t(t) VALUES('rebuild' | 'optimize') incantations. Rebuild after a
bulk load of the content table; optimize occasionally to merge index segments.

JSON (JSON1)

Two kinds of helper: expression builders that return SQL fragments, and
methods that run a query.

sqlite.jsonExtract('data', '$.user.name');    // json_extract("data", '$.user.name')
sqlite.jsonSet('data', '$.seen', true);
sqlite.jsonArrayAppend('data', '$.tags', 'new');
sqlite.jsonRemove('data', '$.secret');
sqlite.jsonArrayLength('data', '$.tags');
sqlite.jsonContains('data', 'red', '$.colours');
sqlite.jsonType('data', '$.count');
sqlite.jsonValid('data');

Drop them into a where or attributes with prorm.literal(), or into raw SQL.

The table-valued functions expand a document into rows:

await sqlite.jsonEachAsTable('data', '$.tags');   // json_each
await sqlite.jsonTreeAsTable('data');             // json_tree, recursive

which is how you filter on array membership without a separate table.

Generated columns

sqlite.createVirtualColumn('full_name', 'TEXT', "first_name || ' ' || last_name");
// "full_name" TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL

sqlite.createStoredColumn('total', 'REAL', 'price * quantity');
// … STORED

VIRTUAL computes on read and costs no space; STORED writes the value and can
be indexed. createTableWithGeneratedColumns() builds the whole CREATE TABLE.

These are database-computed, unlike Prorm's
virtual fields, which are computed in JavaScript and have
no column at all.

Window functions and CTEs

sqlite.rowNumber({ partitionBy: ['team'], orderBy: [{ column: 'score', desc: true }] });
sqlite.rank(...); sqlite.denseRank(...); sqlite.ntile(4, ...);
sqlite.lag('score', 1, 0); sqlite.lead('score');
sqlite.firstValue('score'); sqlite.lastValue('score'); sqlite.nthValue('score', 3);

sqlite.buildCTE('ancestors', ['id', 'parent_id'], 'SELECT … UNION ALL SELECT …', true);
await sqlite.executeRecursiveCTE(/* … */);
sqlite.generateSequence(1, 100);   // the recursive-CTE integer-series trick

generateSeries() runs the same trick and returns the rows, which is the
usual way to fill gaps in a report (every day, even days with no orders):

const { rows } = await sqlite.generateSeries(1, 5);
// [{ x: 1 }, { x: 2 }, { x: 3 }, { x: 4 }, { x: 5 }]

For portable window functions, the package-root builders (rowNumber, rank,
lag, sumOver, …) compile for every dialect — prefer those unless you need
SQLite specifics.

Triggers and views

await sqlite.createTrigger(
  'audit_users',            // name
  'AFTER',                  // timing: BEFORE | AFTER | INSTEAD OF
  ['UPDATE'],               // events
  'users',                  // table
  'INSERT INTO audit …',    // body
  { whenCondition: 'OLD.email <> NEW.email', forEachRow: true, replace: true },
);
await sqlite.createAfterInsertTrigger(/* … */);
await sqlite.createInsteadOfTrigger(/* … */);   // the only way to write to a view
await sqlite.listTriggers('users');
await sqlite.getTriggerDefinition('audit_users');
await sqlite.dropTrigger('audit_users', { ifExists: true });

await sqlite.createView('active_users', { from: User, where: { status: 'active' } });
await sqlite.listViews();
await sqlite.getViewDefinition('active_users');

SQLite has no CREATE OR REPLACE TRIGGER, so replace: true drops any
same-named trigger first. Convenience wrappers cover the common pairs —
createAfterInsertTrigger, createAfterUpdateTrigger,
createAfterDeleteTrigger, createBeforeInsertTrigger,
createBeforeUpdateTrigger, createInsteadOfTrigger — plus
createRecursiveTrigger for a trigger that re-fires (needs
PRAGMA recursive_triggers = ON).

Conflict resolution

SQLite's per-constraint ON CONFLICT algorithms — ROLLBACK, ABORT, FAIL,
IGNORE, REPLACE:

sqlite.createNotNullConstraint('email', 'ABORT');
sqlite.createUniqueConstraint(['org_id', 'email']);
sqlite.addOnConflictClause('UNIQUE (email)', 'IGNORE');
sqlite.createTableWithOnConflict(/* … */);

await sqlite.upsert('users', { id: 1, email: 'a@x' }, ['id']);  // conflict columns
sqlite.buildUpsertQuery('users', ['id','email'], ['id'], undefined, { ignore: true });

The three shapes buildUpsertQuery() produces, for
buildUpsertQuery('users', ['id', 'email', 'name'], ['id'], undefined, options):

Updates the existing row in place: same rowid, no delete triggers. Pass
updateColumns to update only some of them.

Keeps the existing row and silently skips the new one. It also skips a row
that breaks any other UNIQUE, NOT NULL or CHECK constraint.

Deletes the conflicting row, then inserts. Columns you did not list fall back
to their defaults.

[!CAUTION]
ON CONFLICT REPLACE deletes the conflicting row and inserts a new one. That
fires delete triggers, can cascade through ON DELETE CASCADE foreign keys,
and resets every column you did not supply. It is rarely what you want;
IGNORE or a real upsert usually is.

Maintenance and introspection

await sqlite.vacuum({ analyze: true });
await sqlite.analyze('users');
await sqlite.reindex();

await sqlite.getTableInfo('users');       // PRAGMA table_info
await sqlite.getIndexList('users');       // PRAGMA index_list
await sqlite.getForeignKeyList('users');  // PRAGMA foreign_key_list
await sqlite.getDatabaseInfo();
await sqlite.getTableStats('users');

[!WARNING]
VACUUM rewrites the whole file. It reclaims space after large deletes, but
it takes an exclusive lock for the duration and needs free disk of up to twice
the database's size while it runs, so schedule it.

PRAGMA

await sqlite.pragma('journal_mode', 'WAL');
await sqlite.pragma('synchronous', 'NORMAL');
await sqlite.setDatabaseOptions({ /* several at once */ });

journal_mode = WAL is the single highest-value setting for a concurrent
application: readers stop blocking the writer. Pair it with a busy timeout.

A start-up routine for a file database that several requests share:

import { Prorm, createSQLiteAdvanced } from 'ts-prorm-orm';

export const prorm = new Prorm({ dialect: 'sqlite', storage: './data/app.db' });
await prorm.connect();

const sqlite = createSQLiteAdvanced(prorm.getDialectInstance() as any);
await sqlite.setDatabaseOptions({
  journalingMode: 'WAL',   // PRAGMA journal_mode = WAL
  synchronous: 'NORMAL',   // PRAGMA synchronous = NORMAL (safe with WAL)
  foreignKeys: true,       // PRAGMA foreign_keys = 1
  cacheSize: -20000,       // PRAGMA cache_size = -20000, i.e. ~20 MB
});
await sqlite.pragma('busy_timeout', 5000);

console.log(await sqlite.getDatabaseInfo());
// { schemaVersion: 9, userVersion: 0, foreignKeys: true, journalingMode: 'wal',
//   synchronous: 1, cacheSize: -20000, pageSize: 4096 }

[!NOTE]
An in-memory database (storage: ':memory:') cannot use WAL: SQLite keeps
answering memory for journal_mode, and getDatabaseInfo() says so.

Savepoints and extensions

await sqlite.savepoint('sp1');
await sqlite.rollbackToSavepoint('sp1');
await sqlite.releaseSavepoint('sp1');

withSavepoint() wraps the three: it opens the savepoint, rolls back to it if
the callback throws (and rethrows), and releases it either way. Inserting a
batch where one bad row undoes only that batch:

try {
  await sqlite.withSavepoint('import_batch', async () => {
    await Article.create({ title: 'ok', body: '…' });
    throw new Error('bad row');         // the insert above is rolled back
  });
} catch (e) {
  console.warn('batch skipped:', (e as Error).message);
}


sqlite.setExtensionLoading(true);
sqlite.loadExtension('/usr/lib/sqlite/spellfix1.so');
sqlite.getLoadedExtensions();

Nested transaction control is also available on the connection —
prorm.createSavepoint() / rollbackToSavepoint() / releaseSavepoint() — and
works on every dialect that has savepoints; see
Transactions.

[!CAUTION]
Loading an extension executes native code from that path with the process's
privileges. Load only paths you control, and never a path taken from user
input.

Column affinity

sqlite.getColumnAffinity('VARCHAR(255)');   // 'TEXT'
sqlite.createColumnWithAffinity('price', 'DECIMAL(10,2)');
sqlite.createIntegerPrimaryKey('id', true); // INTEGER PRIMARY KEY AUTOINCREMENT

SQLite has type affinity, not types: a column declared VARCHAR(255) will
happily store an integer. That is why DataTypes maps several types to TEXT
here — see Data types.

SQLite dialect

Turso / libSQL

Data types

Indexes & constraints

Transactions

SQLite advanced features

Full-text search (FTS5)#

A search index that keeps itself current#

Create the index over the table#

Add the sync triggers#

Index what is already there#

Search#

JSON (JSON1)#

Generated columns#

Window functions and CTEs#

Triggers and views#

Conflict resolution#

Maintenance and introspection#

PRAGMA#

Savepoints and extensions#

Column affinity#

Related reading#