SQLite advanced features

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. 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) 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: 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. 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. Drop them into a where or attributes with prorm.literal(), or into raw SQL. The table-valued functions expand a document into rows: which is how you filter on array membership without a separate table. Generated columns 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 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 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 recursivetriggers = ON). Conflict resolution SQLite's per-constraint ON CONFLICT algorithms — ROLLBACK, ABORT, FAIL, IGNORE, REPLACE: ON CONFLICT REPLACE deletes the conflicting row and inserts a new one — which fires delete triggers and changes the rowid. Rarely what you want; IGNORE or a real upsert usually is. Maintenance and introspection VACUUM rewrites the whole file — it reclaims space after large deletes and takes an exclusive lock for the duration, so schedule it. PRAGMA journalmode = WAL is the single highest-value setting for a concurrent application: readers stop blocking the writer. Pair it with a busy timeout. Savepoints and extensions Nested transaction control is also available on the connection — prorm.createSavepoint() / rollbackToSavepoint() / releaseSavepoint() — and works on every dialect that has savepoints; see Transactions. Loading an extension executes native code from that path. Load only paths you control. Column affinity 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. Related reading SQLite dialect Turso / libSQL Data types Indexes & constraints Transactions