# @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 <value>` (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.
