Migrate PostgreSQL to SQLite
Certified — live-tested pathThe converter maps 18 PostgreSQL column types to SQLite. 8 of them change kind on the way (boolean becomes INTEGER, date becomes TEXT, timestamp becomes TEXT, and more); the table below lists every one. DBShifts converts the schema, copies the rows in parallel batches and checks the result with row counts and checksums.
Free tier · no credit card
PostgreSQL vs SQLite
PostgreSQL
the most advanced open-source relational database.
SQLite
the embedded database that ships inside everything.
PostgreSQL to SQLite data type mapping
Pulled directly from DBShifts's own conversion rules — not a general reference table, this is what actually runs.
| PostgreSQL | SQLite |
|---|---|
| integer | INTEGER |
| bigint | INTEGER |
| smallint | INTEGER |
| boolean | INTEGER |
| numeric(10,2) | NUMERIC |
| real | REAL |
| double precision | REAL |
| varchar(255) | TEXT |
| text | TEXT |
| char(10) | TEXT |
| date | TEXT |
| timestamp | TEXT |
| timestamptz | TEXT |
| time | TEXT |
| bytea | BLOB |
| json | TEXT |
| jsonb | TEXT |
| uuid | TEXT |
Example: PostgreSQL to SQLite schema conversion
PostgreSQL
CREATE TABLE orders ( id bigserial PRIMARY KEY, customer_id integer NOT NULL, total numeric(10,2) NOT NULL, is_gift boolean NOT NULL DEFAULT false, notes text, metadata jsonb, placed_at timestamptz NOT NULL );
SQLite
CREATE TABLE IF NOT EXISTS "orders" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"customer_id" INTEGER NOT NULL,
"total" NUMERIC NOT NULL,
"is_gift" INTEGER NOT NULL DEFAULT 0,
"notes" TEXT,
"metadata" TEXT,
"placed_at" TEXT NOT NULL
);One writer at a time
PostgreSQL handles many concurrent writers. SQLite allows exactly one at a time and makes the others wait or fail with database is locked. That is fine for an embedded copy, a test fixture or a single-user app, and wrong for a multi-user write workload, whatever the data looks like.
Turn on WAL mode, PRAGMA journal_mode = WAL, so readers keep working while one connection writes, and set a busy timeout so a waiting writer retries instead of failing straight away.
boolean becomes INTEGER, timestamps become text
boolean becomes INTEGER holding 0 or 1. SQLite 3.23 and later understands TRUE and FALSE as those numbers, so most boolean queries keep working unchanged.
date, timestamp and timestamptz all become TEXT. timestamptz values are converted to UTC and written without an offset, so their text order and their time order agree; whatever reads them back has to know they are UTC, because the text no longer says so.
jsonb and arrays become text
json and jsonb become TEXT, and arrays are serialised to JSON text. SQLite's json functions query them in place, and from SQLite 3.38 the -> and ->> operators exist too: metadata->>'plan' works on both engines, with ->> returning an SQL value rather than JSON.
There is no GIN index. To search inside an array, use json_each() in a join, and index an expression such as json_extract(metadata, '$.plan') where a lookup needs to be fast.
PostgreSQL → SQLite conversion notes
Engine-specific rules baked into the conversion and transfer pipeline — verified by live certification of this exact pair.
- Arrays (integer[], text[]) are preserved natively on PostgreSQL targets and serialized to JSON elsewhere.
- JSONB, UUID, INET and other rich types fall back to portable representations with every downgrade recorded in the fidelity report.
- SERIAL/identity sequences are recreated and reseeded on the target.
- High-precision decimals land in TEXT columns on purpose — SQLite's NUMERIC affinity would silently convert them to lossy REAL.
- Unsigned BIGINT values beyond the signed 64-bit range are stored as exact text rather than corrupted floats.
- Dates and times are stored as ISO-8601 text, the SQLite convention.
Frequently asked questions
Is PostgreSQL to SQLite migration production-ready in DBShifts?
Yes. PostgreSQL to SQLite is a certified pair: it passes the live certification suite on real PostgreSQL and SQLite servers, which checks schema fidelity, row counts, type-aware checksums and paging across batches.
What happens to PostgreSQL types that SQLite doesn't have?
Each gets the nearest SQLite type the converter treats as safe: boolean becomes INTEGER, date becomes TEXT, timestamp becomes TEXT, timestamptz becomes TEXT, time becomes TEXT. Every such decision is in the migration plan before anything runs, and any mapping can be overridden per column.
Can I keep PostgreSQL and SQLite in sync after the initial migration?
Yes, until you cut over. DBShifts uses logical decoding through a replication slot, which needs wal_level = logical and the wal2json plugin on the server. With zero-downtime mode on, the slot is created before the first row is copied, so it holds every change made during the copy.
Which PostgreSQL data types need a second look when moving to SQLite?
The table above carries a note on 3 of them: timestamptz (TEXT), bytea (BLOB), jsonb (TEXT). Analysis flags the same columns on your own schema before any data moves.
Go deeper
How We Live-Certified All 49 Migration Paths
The certification harness behind this page's claims.
Masking PII in Flight: Production Data Without Production Risk
Mask during the migration, not after, so real PII never sits in a lower environment.
Reference
Compared with other tools
Ready to move PostgreSQL to SQLite?
Set up in under two minutes. Validation and rollback included.
Start Free Migration