SQLite

UQL runs on a local SQLite file through two pools that produce the same SQL and results. For a remote database, see Turso & libSQL or Cloudflare D1. If SQLite stands in for a production Postgres, PGlite is real Postgres with no server.

Pool	Driver	Install
NodeSqliteQuerierPool	Node’s built-in node:sqlite	nothing
SqliteQuerierPool	better-sqlite3, or bun:sqlite under Bun	npm i better-sqlite3 (Bun needs nothing)

Install and connect

NodeSqliteQuerierPool needs no package and no native build, which helps in slim images and CI:

import { NodeSqliteQuerierPool } from 'uql-orm/sqlite';

export const pool = new NodeSqliteQuerierPool('app.db');

SqliteQuerierPool is faster on reads (about 1.3x on point reads in our tests, though node:sqlite inserts about 20% faster), so prefer it when throughput matters. The same code uses bun:sqlite under Bun:

import { SqliteQuerierPool } from 'uql-orm/sqlite';

export const pool = new SqliteQuerierPool('app.db');

Both default to ':memory:' and take the driver’s options second. Each connection opens with journal_mode = WAL and foreign_keys = ON, so onDelete: 'CASCADE' works. Runnable apps: Node and Bun.

Vector and full-text search

SQLite has no vector functions, so semantic search needs the sqlite-vec extension, loaded through extensions:

import { getLoadablePath } from 'sqlite-vec';

const pool = new NodeSqliteQuerierPool('app.db', {
  extensions: [getLoadablePath()],
});

What is not supported

• SQLite in the browser, such as sql.js, but you can instead run Postgres in the browser with PGlite.

$text matches an FTS5 virtual table. Migrations do not create one: create it yourself and declare an entity over it, and $text and $sort: { $text } (ranked by BM25) work as on other engines. See Full-text search.

Good to know

A pool holds one connection, so a transaction locks the whole database and other queriers wait until it ends. Give work that must run concurrently its own pool.

$regex compiles to REGEXP, which UQL defines on better-sqlite3, node:sqlite and, from Bun 1.4.4, bun:sqlite.

Ordering a populated to-many needs SQLite 3.44. A populated float keeps 15 significant digits on SQLite 3.51 and 17 on 3.53. Changing a column’s type, nullability or default rebuilds the table.