Turso / libSQL Dialect

Turso / libSQL Dialect Overview Turso is a hosted database service built on libSQL, an open-source fork of SQLite. libSQL keeps SQLite's storage format and SQL grammar but adds the one thing SQLite lacks natively: a network protocol, so the same database can be a local file, an in-memory instance, or a remote server reached over the wire. Because libSQL's SQL is SQLite's SQL, this ORM's TursoDialect extends SQLiteDialect and inherits every pure SQL-generation method verbatim — DDL, JSON1 helpers, upsert syntax, LIMIT/OFFSET pagination, and identifier quoting are all produced by the SQLite code paths. Only the pieces that genuinely differ — the driver, connection lifecycle, query execution, streaming, and transactions — are overridden. Key facts (from src/dialects/turso/index.ts): Dialect name is 'turso'; the driver library is '@libsql/client' (not better-sqlite3, which the base SQLite dialect uses). The whole reason this dialect exists separately from SQLiteDialect is the driver. @libsql/client works identically for a local embedded replica (file: / :memory: URLs) and a remote hosted Turso database (libsql: / https: URLs plus an authToken). better-sqlite3 is local-only. Because it reuses SQLiteDialect's SQL generation, identifiers are double-quoted (escapeId('mycol') → "mycol"), exactly like SQLite — not backtick-quoted like MySQL. The dialect keeps entirely separate connection/transaction state from its parent, since SQLiteDialect reaches its own connection through private fields a subclass can't touch. Connection The dialect is created with a url (and, for hosted databases, an authToken), not the host/port/username used by server dialects. For a local libSQL file, or an ephemeral in-memory database, drop the authToken and point url at a file: or :memory: target: TursoDialectOptions (all optional): Option | Purpose | --------------- | ----------------------------------------------------------------------- | url | libsql://… / https://… (hosted), file:./… (local), :memory:. | authToken | Auth token for a hosted Turso database. Unused for file:/:memory:. | encryptionKey | Encryption key for an encrypted local database file. | syncUrl | Remote database to sync an embedded replica against (see below). | syncInterval | Sync interval in seconds when syncUrl is set. | Notes: If url is omitted, it defaults to :memory:. connect() eagerly verifies the connection by running SELECT 1 and then PRAGMA foreignkeys = ON, so a bad URL or token fails at connect time rather than on your first query. A failed connection is wrapped as Failed to connect to Turso/libSQL database: <error>. SQLite compatibility Everything the ORM generates for Turso is SQLite SQL, inherited unchanged from SQLiteDialect. Standard CRUD, table creation, and JSON1 helpers all behave exactly as documented for the SQLite dialect. Query execution accepts positional or named parameters. The dialect reads options.replacements (falling back to options.bind), so both work: For a SELECT (or PRAGMA), query() returns { rows, rowCount, fields }, where each row is a plain object keyed by column name. For a write it returns { rows: [], rowCount, lastInsertRowid }, where rowCount is the number of rows affected and lastInsertRowid is the numeric rowid of the last insert: Upserts and RETURNING Because upsert generation is inherited from SQLite, Turso uses SQLite's INSERT … ON CONFLICT (…) DO UPDATE form. libSQL, like modern SQLite, supports RETURNING, so the SQLite dialect's RETURNING support applies here too — this is a real difference from the MySQL-family dialects (TiDB, MySQL) which have no RETURNING. Edge and embedded replicas The headline libSQL feature is the embedded replica: a local libSQL file that a background process keeps in sync with a remote Turso database. Reads hit the local copy at SQLite speed; writes and periodic pulls go to the remote. Configure it by giving both a local url and a syncUrl pointing at the remote: To force an immediate pull of the latest remote changes into the local replica, call sync(): sync() requires a syncUrl to have been configured at connect time — otherwise it throws sync() requires a syncUrl to have been configured on connect. It also throws Not connected to database if called before connect(). For an encrypted local database, pass encryptionKey: Data types Type mapping is inherited from SQLiteDialect (getDataTypeSql). SQLite uses dynamic typing, so these declared types are affinity hints rather than strict constraints. Notable mappings: ORM type descriptor | Generated SQL | ------------------------------------------- | --------------------------------- | { key: 'STRING' } | VARCHAR(255) | { key: 'STRING', length: 100 } | VARCHAR(100) | { key: 'CHAR' } | CHAR(1) | { key: 'TEXT' } | TEXT | { key: 'INTEGER' } | INTEGER | { key: 'BIGINT' } | BIGINT | { key: 'DECIMAL', precision: 12, scale: 4 } | DECIMAL(12,4) | { key: 'BOOLEAN' } | INTEGER (SQLite has no boolean) | { key: 'DATE' } | DATETIME | { key: 'DATEONLY' } | DATE | { key: 'BLOB' } | BLOB | { key: 'JSON' } / { key: 'JSONB' } | TEXT (JSON stored as text) | { key: 'UUID' } | TEXT | { key: 'ENUM', values: ['a', 'b'] } | TEXT CHECK("col" IN ('a','b')) | { key: 'GEOMETRY' } | BLOB | ENUM has no native SQLite type; it is emulated as TEXT with a CHECK constraint referencing the actual column name. JSON/JSONB are stored as TEXT and manipulated through SQLite's JSON1 functions (jsonextract, jsongrouparray, jsonpatch, jsoneach, etc.), all inherited from the SQLite dialect. Transactions Transactions are driven directly against the @libsql/client connection. The first startTransaction() issues BEGIN TRANSACTION; nested calls open SAVEPOINTs, so nested transactions map onto savepoint semantics: Committing the outermost transaction emits COMMIT; committing a nested one emits RELEASE SAVEPOINT. Rollback follows the same split (ROLLBACK vs ROLLBACK TO SAVEPOINT). Quirks and limitations No .advanced extension API. SQLiteDialect exposes an .advanced getter backed by better-sqlite3's synchronous Database handle. Turso wraps an async @libsql/client connection with no such handle, so accessing db.dialect.advanced throws: Use the standard Dialect methods (query, createTable, etc.) instead. No native server-side cursor. @libsql/client's execute() always returns the full result set, so queryStream() falls back to the generic LIMIT/OFFSET-paginated stream helper (the pagination SQL is inherited from SQLite unchanged). It still streams row-by-row across multiple round-trips, but the batching happens client-driven rather than through a real cursor. Provide a batchSize to tune the page size: getDatabaseVersion() reports the SQLite version, via SELECT sqliteversion() — it reflects the SQLite grammar level libSQL implements, not a Turso server build number. foreignkeys is enabled on connect. Unlike bare SQLite (which defaults foreign-key enforcement off), connect() runs PRAGMA foreignkeys = ON, so foreign-key constraints are enforced by default. ATTACH/DETACH are available via attachDatabase(path, alias) and detachDatabase(alias), though attaching across databases is subject to libSQL's own restrictions on a hosted deployment. Verification status The behavior above is exercised in tests/dialects/turso.test.ts, which runs against a real :memory: libSQL instance (there is no separate driver to mock — @libsql/client is identical for local and remote). Connection, DDL, CRUD with positional and named parameters, rowCount/lastInsertRowid, commit/rollback/savepoint transactions, the paginated queryStream fallback, and the .advanced guard are all verified end-to-end locally. What is not exercised in the suite is behavior that requires a live remote Turso deployment: hosted authToken auth, embedded-replica syncUrl syncing, and encryptionKey handling are wired through to @libsql/client but would need a real Turso database (or a running libSQL server) to confirm end-to-end. Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL