Migrating from SQLite to PostgreSQL

SQLite-to-PostgreSQL is usually not a database migration so much as an architecture change: from an embedded, in-process file to a client/server system with network access, roles, and concurrent writers. The data transfer is the easy part — pgloader does it in one line. The work is in what SQLite's flexibility let you get away with.

First, the honest question: do you need to migrate? SQLite in WAL mode handles surprising write loads and essentially unlimited concurrent reads, and the "SQLite is only for testing" reflex is a decade out of date. The real triggers are: multiple application servers needing the same database over a network, more than one process writing heavily and colliding on the single-writer lock, need for roles and row-level access control, or need for types and extensions SQLite doesn't have. If you're on one box and writes are fine, staying put is a legitimate decision — see our SQLite partitioning alternatives guide for how far the single-file model stretches.

The Core Problem: Type Affinity

PostgreSQL columns have types. SQLite columns have affinities — suggestions, essentially. Unless a table was declared STRICT (available since SQLite 3.37), any column can hold any type: a TEXT value in an INTEGER column, a number in a date column, NULLs where you never expected them. Twenty years of application bugs may be preserved in your file like flies in amber, and PostgreSQL will reject every one of them at load time.

Audit before migrating. typeof() tells you what's actually in each column:

-- What types actually live in this column?
SELECT typeof(user_id), COUNT(*) FROM orders GROUP BY typeof(user_id);
-- Expect: integer | 48213
-- Fear:   integer | 48211
--         text    | 2

Run this on every column that matters. Two stray text values in an integer column will fail the load — or worse, silently coerce. Fix them in SQLite first, where UPDATE orders SET user_id = CAST(user_id AS INTEGER) WHERE typeof(user_id) = 'text' is a one-liner.

Type Mapping

SQLite has five storage classes; PostgreSQL has dozens of types. The mapping is mostly about deciding what your data meant:

Datetimes are the archaeology project. SQLite has no datetime type; every project picked a convention, and many picked several. You may find ISO-8601 strings (2024-03-15 10:30:00), Unix epoch seconds, epoch milliseconds (JavaScript did this), and Julian day numbers — sometimes in the same column. Check what you have:

SELECT created_at, typeof(created_at) FROM users LIMIT 5;

ISO strings load straight into timestamp. Epoch integers need to_timestamp() conversion after load, or a cast rule. Milliseconds need dividing by 1000 first — loading epoch-millis as epoch-seconds puts your data 50,000 years in the future, which at least makes the validation step easy.

Booleans: SQLite stores them as integers 0/1 regardless of what the column declaration says. pgloader's SQLite defaults do not assume every integer is a boolean (unlike its MySQL tinyint rule), so declare it per column in a cast rule if you want real boolean columns.

Moving the Data with pgloader

pgloader reads the SQLite file directly — no dump step. With pgloader v4 (single JAR, Java 21+; the 2022-era v3.6.9 native binary from apt also handles SQLite fine):

createdb appdb
java -jar pgloader.jar ./app.sqlite postgresql:///appdb

That creates tables, converts types by the default cast rules, loads data, creates indexes, and resets sequences. For control, use a load file:

LOAD DATABASE
     FROM sqlite:///path/to/app.sqlite
     INTO postgresql://app@localhost/appdb

WITH include drop, create tables, create indexes, reset sequences

CAST column users.is_active to boolean using tinyint-to-boolean,
     column events.created_at to timestamptz using unix-timestamp-to-timestamptz;

For tiny databases or when you want to hand-write the target schema, the dump-and-edit route also works: sqlite3 app.sqlite .dump produces SQL, but expect to fix AUTOINCREMENT, backtick-free but double-quote-heavy identifiers, and PRAGMA statements by hand. pgloader is less typing for a better result in almost every case.

After the load, verify sequences. If you loaded data any way other than pgloader's reset sequences, your identity columns will hand out key 1 to the next insert and collide immediately:

SELECT setval(pg_get_serial_sequence('users', 'id'), (SELECT MAX(id) FROM users));

What Changes in Your Application

• Connection model. A file path becomes a connection string, and "open the file" becomes a pooled network connection. Every driver guide in our connect-from series covers pooling; the short version is: use a pool, size it against max_connections.
• Concurrency semantics. SQLite serializes writes with a database-level lock; SQLITE_BUSY was your contention signal. PostgreSQL gives you MVCC, row-level locks, and genuine parallel writes — plus new failure modes (deadlocks, serialization errors under SERIALIZABLE) that your retry logic should handle.
• Query dialect. Less painful than MySQL→PostgreSQL. Both support CTEs, window functions, RETURNING, and ON CONFLICT upserts with near-identical syntax. The gaps: SQLite's flexible GROUP BY (bare columns that aren't aggregated or grouped) becomes an error; strftime() becomes to_char(); SQLite's dynamic typing tricks (WHERE id = '42') start raising type errors.
• Case-insensitive LIKE. SQLite's LIKE is case-insensitive for ASCII by default; PostgreSQL's is case-sensitive. Queries that relied on it silently return fewer rows — use ILIKE or citext.

Validation

Same discipline as any migration — aggregate checksums on both sides, not just row counts:

SELECT COUNT(*), SUM(amount_cents), MIN(created_at), MAX(created_at) FROM orders;

Run identical queries against the SQLite file and the new PostgreSQL database and diff the output. Pay special attention to any column the typeof() audit flagged and to datetime columns (epoch-vs-string mistakes produce values that are wrong by decades, so MIN/MAX catches them instantly). Mako connects to both SQLite and PostgreSQL side-by-side, which makes the run-the-same-query-twice loop quick — one connection per database, same editor.

Common Mistakes

If you're deciding rather than migrating, our PostgreSQL vs SQLite comparison covers the tradeoffs. For the new connection code, see connecting to PostgreSQL from Python and the rest of the connect-from series.

Mako connects to SQLite and PostgreSQL side-by-side with AI-powered autocomplete — handy for validating a migration table by table. Try it free at mako.ai.