pg2sqlite

Postgres → SQLite converter for Directus dumps

Convert a PostgreSQL plain-text pg_dump (with COPY data) into a SQLite database — no Postgres server required. It reads the .sql dump file directly and writes a .db file.

Originally written for Directus dumps from Postgres 13, but it works for any dump that uses the same common features: standard column types, COPY ... FROM stdin data, and PRIMARY KEY / UNIQUE / FOREIGN KEY constraints driven by sequences.

Requirements

• Python 3 (standard library only — no pip installs)
• sqlite3 (bundled with Python)

Usage

# pg2sqlite.py <dump.sql> <database.db>
python3 pg2sqlite.py dump.sql database.db

The converter prints per-table row counts and exits non-zero if any table's row count doesn't match the dump.

Verifying a conversion

verify.py independently re-parses the raw dump and checks the SQLite output:

# verify.py <dump.sql> <database.db>
python3 verify.py dump.sql database.db

It checks table/column parity, validates every JSON value, confirms booleans are limited to 0/1/NULL, checks primary-key uniqueness, and reports any foreign-key orphans present in the source data.

Conversion rules

PostgreSQL	SQLite
integer, bigint, smallint	INTEGER
boolean	INTEGER — values t/f → 1/0
varchar, text, uuid, json, date, timestamp*, time	TEXT (stored verbatim)
numeric	NUMERIC
double precision, real	REAL
bytea	BLOB
serial column (nextval default)	INTEGER PRIMARY KEY AUTOINCREMENT

Details:

• COPY escapes (\n, \t, \\, octal \OOO, hex \xHH, and \N → NULL) are fully decoded. Data is inserted with parameterized queries, so there is no manual SQL-string escaping to get wrong.
• DEFAULT clauses are translated: ::type casts are stripped, true/false become 1/0, and nextval() defaults are dropped (handled by AUTOINCREMENT). CURRENT_TIMESTAMP is preserved.
• Sequence position is carried over via AUTOINCREMENT, so new inserts continue from where the source left off.
• Foreign keys (including ON DELETE CASCADE/SET NULL) are written into the schema but, per SQLite's default, are not enforced unless you run PRAGMA foreign_keys = ON; on the connection.
• JSON is stored as TEXT and stays queryable via SQLite's json_*() functions.

Scope / limitations

Handles the common subset emitted by pg_dump for typical application schemas. It does not translate stored procedures, triggers, views, materialized views, CHECK constraints, generated columns, arrays, or custom/extension types (e.g. PostGIS). Dumps must be the plain text format with inline COPY data (the pg_dump default), not the custom/-Fc archive format.

License

MIT — see LICENSE.