# @profullstack/libsql-pg A drop-in replacement for `@libsql/client` that talks to Postgres, plus the two tools that move a Turso/libSQL database across: a schema converter and a copier. Porting an app off Turso becomes three steps: ```sh npx libsql-pg convert-schema db/schema.sql -o migrations-pg/0001_schema.sql # 1. DDL psql "$DATABASE_URL" -f migrations-pg/0001_schema.sql npx libsql-pg copy --from "$TURSO_DATABASE_URL" --token "$TURSO_AUTH_TOKEN" \ --to "$DATABASE_URL" --verify # 2. rows # 3. in the app: import { createClient } from '@profullstack/libsql-pg' ``` The pattern comes from rssamplifier.com's port (2026-09-25), where a libSQL surface over `pg` let the app keep every query and change one import. ## Client ```js import { createClient } from '@profullstack/libsql-pg'; const db = createClient({ url: process.env.DATABASE_URL }); // postgres://... const rs = await db.execute({ sql: 'select * from feeds where id = ?', args: [id] }); rs.rows[0].title; rs.rows[0][1]; rs.rowsAffected; rs.lastInsertRowid; await db.batch([{ sql: 'insert into a (x) values (?)', args: [1] }, 'delete from b'], 'write'); const tx = await db.transaction('write'); await tx.execute(...); await tx.commit(); ``` - The `@libsql/client` surface: `execute`, `batch`, `transaction`, `executeMultiple`, `close`, `sync` (no-op), `protocol` = `'postgres'`. Positional `?` and named `:name` / `@name` / `$name` arguments. - `ResultSet` shape matches libsql: `columns`, `columnTypes`, `rows` (objects with column keys AND numeric indexes, plus `length`), `rowsAffected`, and `lastInsertRowid` as a BigInt for an INSERT into a table with an identity or serial primary key (a `RETURNING` of that key is appended when the statement has none; the key is looked up once per table and cached). - Errors keep the messages SQLite code matches on: `UNIQUE constraint failed: t.col`, `FOREIGN KEY constraint failed`, `NOT NULL constraint failed: t.col`; the Postgres `code` is preserved. - `dialect: 'sqlite'` (the default) rewrites each statement before binding, with a cache keyed by SQL text. `dialect: 'postgres'` sends SQL as-is. - `url` must be `postgres://` or `postgresql://`; `libsql://` and `file:` throw. - Options: `pool: { max }`, `dialect`. ## What the rewriter changes for you | SQLite | Postgres | | --- | --- | | `INSERT OR IGNORE INTO t ...` | `INSERT INTO t ... ON CONFLICT DO NOTHING` | | `INSERT OR REPLACE INTO t (cols) ...`, `REPLACE INTO` | `... ON CONFLICT (pk or unique cols) DO UPDATE SET col = EXCLUDED.col` (keys looked up in `pg_index`) | | `datetime('now')`, `datetime('now','localtime')` | `now()` | | `date('now')` | `current_date` | | `strftime('%s','now')`, `unixepoch()` | `extract(epoch from now())::bigint` | | `strftime(fmt, x)` (common formats) | `to_char(x at time zone 'utc', ...)` | | `json_extract(col, '$.a.b[0]')` | `(col #>> '{a,b,0}')` | | `lower(hex(randomblob(16)))`, `hex(randomblob(n))` | `encode(gen_random_bytes(n), 'hex')` | | `group_concat(x, sep)` | `string_agg(x::text, sep)` | | `ifnull(a, b)` | `coalesce(a, b)` | | `x LIKE y` | `x ILIKE y` (SQLite's LIKE is case-insensitive; `NOT LIKE` and `ESCAPE` kept) | | `CAST(x AS INTEGER)` | `CAST(x AS BIGINT)` (SQLite's integer is 64-bit) | | `` `backticked` `` identifiers | `"quoted"` | | `PRAGMA ...` | no-op, empty result | | `CREATE TABLE`/`ALTER TABLE` inline | passed through the schema converter | | `... MATCH ...` against an FTS5 table | throws, naming the table (see FTS below) | `unsupportedIdioms(sql)` lists what a statement still needs by hand: `printf`, `format`, `typeof`, `last_insert_rowid()`, `changes()`, `total_changes()`, `COLLATE NOCASE` (use `citext` or `lower()`), `GLOB` (use `LIKE` or `~`), `IS NOT ` (use `IS DISTINCT FROM`), `random()` (double in [0,1) in Postgres, 64-bit integer in SQLite), and bare `rowid` (Postgres tables have no implicit rowid; give the table an identity column named `rowid` or use the key). Also by hand: `json_each`/`json_tree` (use `jsonb_array_elements`), `?NNN` numbered parameters, integer booleans in comparisons (`= 1` against a boolean column), and `LIMIT -1`. ## `convert-schema` ```sh libsql-pg convert-schema schema.sqlite.sql [-o out.sql] [--json jsonb] [--search-column name] [--ts-config english] ``` `INTEGER PRIMARY KEY [AUTOINCREMENT]` becomes `bigint generated by default as identity primary key`; `INTEGER` -> `bigint`, `REAL` -> `double precision`, `BLOB` -> `bytea`, `DATETIME`/`TIMESTAMP` -> `timestamptz`, `BOOLEAN` -> `boolean`, `TEXT` stays; `DEFAULT (datetime('now'))`/`CURRENT_TIMESTAMP` -> `default now()`, `DEFAULT (strftime('%s','now'))` -> epoch default; `WITHOUT ROWID` dropped; indexes kept. `CREATE VIRTUAL TABLE x USING fts5(...)` becomes a generated `tsvector` column plus a GIN index on the content table when the converter can find it (and a commented TODO otherwise); triggers are emitted as commented TODOs. Fixtures in `test/fixtures/`. ## `copy` and `verify` ```sh libsql-pg copy --from libsql://... --token ... --to postgres://... \ [--tables a,b] [--exclude x,y] [--truncate] [--upsert] [--batch N] [--workers N] [--verify] [--dry-run] libsql-pg verify --from ... --to ... ``` Discovers tables from `sqlite_master`, orders them so foreign-key parents load first, streams rows in batches (600 s read timeout with retries; min/max rowid are two lookups, never one full scan), coerces SQLite integers into `boolean` and `timestamptz` columns using the target's `information_schema`, resets identity sequences with `setval(max)`, and `--verify` compares `count(*)` per table. `--truncate` is `TRUNCATE ONLY` (a parent with children errors: that is the point, use `--upsert`). `--upsert` refreshes in place by primary key and mirrors deletes, so a second run before cutover closes the gap without a rebuild. ## Traps (all met in the rssamplifier port) - `TRUNCATE ... CASCADE` empties the child tables too; the copier never uses it. - An identity column must not appear in the `DO UPDATE SET` list. - A fresh Postgres has no statistics: run `ANALYZE` after the load or the first plans are terrible. - Feed-style queries (`where id in (select ...) order by created_at limit n`) want `with picked as materialized ... join lateral (...)`; the planner otherwise scans the whole child table. - Postgres rejects `sslmode=no-verify` in `pg` older than 8.x and Bun.SQL forwards unknown URL parameters to the server; pass `ssl` options explicitly. ## Development Node >= 20, ESM, `pg` is the only runtime dependency (`@libsql/client` is an optional peer for the copier's source side). `node --test` runs the unit tests without a database; the integration tests skip unless `TEST_DATABASE_URL` is set (CI runs them against `postgres:17-alpine`). MIT.