SQLite in Development, PostgreSQL in Production: What Breaks

SQLite is a fine database, but it is not a small PostgreSQL. It stores whatever you insert regardless of the declared type, it cannot alter most of a table in place, and it ignores foreign keys unless every connection asks it not to. Each of those differences lets a bug through locally that only shows up in production. This article lists what breaks, why, and the one habit that removes the whole class of problems.

Why teams develop on SQLite anyway#

The appeal is real. There is nothing to install, the database is a single file you can delete, tests start in milliseconds, and every ORM in every language speaks it. For a prototype, or for a product that will ship with SQLite embedded, it is the right choice. The trouble starts when the production engine is PostgreSQL and SQLite is only a stand-in. From that moment on, every test that passes locally is a statement about SQLite, not about the system you are going to run.

Type affinity: SQLite stores what you give it#

PostgreSQL enforces column types. Insert the string 'abc' into an integer column and the statement fails. SQLite uses type affinity instead: the declared type is a hint about how to store a value, not a rule about what to accept. A column declared VARCHAR(50) has TEXT affinity, and it will take a 5,000 character string or an integer without complaint.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email VARCHAR(50) NOT NULL,
    age   INTEGER
);

INSERT INTO users (email, age) VALUES (12345, 'not a number');
-- SQLite: 1 row inserted. PostgreSQL: two errors.

The consequences are quiet. A length limit that no test exercised turns into a value too long for type character varying(50) error on the first real user. Validation you thought the database was doing was never happening.

SQLite 3.37 added STRICT tables, which reject values that do not match the declared storage type. They help, but they are opt-in, the allowed types are only INT, INTEGER, REAL, TEXT, BLOB and ANY, and no migration tool or ORM generates them for you by default.

ALTER TABLE is a different feature#

PostgreSQL can change almost anything about a table in place: column types, defaults, nullability, constraints, foreign keys. SQLite's ALTER TABLE supports renaming a table, renaming a column, adding a column and dropping a column. That is the entire list. You cannot change a column's type, add a constraint to an existing column, add a foreign key or add a primary key after the fact.

The documented workaround is to create a new table with the desired definition, copy the rows across, drop the old table and rename the new one. Tools that support SQLite have to do this for you. Alembic calls it batch mode:

def upgrade():
    with op.batch_alter_table("pets") as batch_op:
        batch_op.alter_column("name", type_=sa.String(100), nullable=False)
        batch_op.create_foreign_key("fk_pets_owners", "owners", ["owner_id"], ["id"])

On PostgreSQL the same operations become two ordinary ALTER TABLE statements; on SQLite they rebuild the table. Django does the same thing internally when its SQLite schema editor meets an operation the engine cannot express. It works, but it means the migration you tested locally is not the migration that runs in production. The SQL is different, the locking behaviour is different, and a mistake in a constraint definition that the rebuild tolerated will be an error on PostgreSQL.

A second-order effect: because rebuilds are awkward, teams on SQLite tend to avoid adding constraints at all.

Foreign keys are off unless you turn them on#

SQLite parses REFERENCES clauses and stores them, but it does not enforce them unless the connection has run:

PRAGMA foreign_keys = ON;

The setting is per connection and defaults to off in a standard build. Django turns it on for you. SQLAlchemy does not; you have to attach an event listener to every connection:

from sqlalchemy import event

@event.listens_for(engine, "connect")
def enable_foreign_keys(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

Without it, a test can delete a parent row that still has children, insert an orphan, or reference an id that does not exist, and everything passes. PostgreSQL rejects all three. Test fixtures written against a lenient SQLite are a classic source of failures that only happen in CI, because the fixture itself violates a constraint.

Even with enforcement on, the details differ. SQLite stores a constraint name if you give one, but you cannot drop or alter the constraint later without rebuilding the table, and its own metadata does not report the name back. If your schema documentation relies on constraint names, SQLite has nothing to give you. See Foreign Keys Done Right for what the constraints should look like on the real engine.

Booleans, dates and other types that do not exist#

SQLite has five storage classes: NULL, INTEGER, REAL, TEXT and BLOB. Everything else is a convention on top of them.

The common failure is not a crash but a query that returns different rows. A date range comparison that works on ISO 8601 strings in SQLite gives the same answer on PostgreSQL only as long as every value was written in exactly the same format. The moment a driver writes a timestamp with microseconds or a trailing offset, the SQLite ordering and the PostgreSQL ordering diverge. The guide to choosing column types covers what to pick on the real engine.

Concurrency: one writer at a time#

SQLite locks the whole database file for writing. In the default journal mode readers wait while a write is committing; in WAL mode readers proceed alongside a single writer, but there is still only one writer. PostgreSQL uses row-level locks and multi-version concurrency control, so two transactions updating different rows of the same table never wait on each other.

This means an entire category of bugs cannot be reproduced on SQLite: lost updates, deadlocks between transactions taking locks in different orders, or a long-running report blocking a migration. Conversely, tests that pass on SQLite may hit database is locked as soon as they run in parallel.

Functions, defaults and SQL dialect gaps#

Any SQL that reaches the engine unchanged, which includes every hand-written migration, raw query and column default, meets a different dialect:

• now(), gen_random_uuid(), ILIKE, :: casts, SERIAL, TRUNCATE and CREATE SCHEMA do not exist in SQLite, and RETURNING only arrived in recent versions. The SQLite spellings are CURRENT_TIMESTAMP, datetime('now') and INTEGER PRIMARY KEY for an auto-incrementing key.
• LIKE is case-insensitive for ASCII in SQLite and case-sensitive in PostgreSQL, so a search test can pass locally and fail in production, or the other way round.
• String concatenation, integer division and GROUP BY rules differ. SQLite lets you select a column that is neither grouped nor aggregated; PostgreSQL does not.
• Schemas do not exist. A PostgreSQL migration that creates tables in billing.invoices has no SQLite equivalent short of attaching a second database file.
• Both engines have transactional DDL, which is one thing they share, but PostgreSQL's CREATE INDEX CONCURRENTLY and its lock modes have no counterpart. Transactional DDL explained covers the differences between engines.

ORMs paper over some of this. They do not paper over the migration files, which are exactly the code that runs once, in production, with no retry.

What to do instead: run the real engine#

The fix is not clever. Run PostgreSQL locally and in CI, in the version production uses, and throw it away after each run. Docker makes the cost close to zero:

docker run --rm -d --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 postgres:18

A fresh PostgreSQL container accepts connections a few seconds after it starts, which is slower than opening a SQLite file and much faster than debugging a production incident. In test suites, Testcontainers or a service container in your CI workflow gives every run a clean database of exactly the right version; Testing database migrations in CI with Docker walks through both.

If you must keep SQLite for a fast inner loop, keep it honest:

• Turn on PRAGMA foreign_keys on every connection.
• Use STRICT tables where your tooling allows it.
• Run the full suite against PostgreSQL at least in CI, and run every migration there before it merges.
• Never generate a migration by diffing against SQLite. Generate it against the real engine, or write it by hand and test it there.

Where SchemaDrawer fits#

SchemaDrawer runs your migration files inside Docker against a throw-away database of the engine and version you name, reads the resulting schema over JDBC and returns an SVG diagram. It supports both SQLite and PostgreSQL, which makes the differences above visible: draw the same migrations twice and the PostgreSQL diagram reports int4, varchar and timestamptz where the SQLite one reports whatever the migration declared. If a foreign key exists on one engine and not on the other, the arrows show it. Since the service never parses SQL itself, whatever your migration tool accepts on the real engine is what gets drawn.

Key takeaways#

• SQLite accepts values of the wrong type and length; PostgreSQL rejects them. Tests on SQLite do not exercise your column definitions.
• Most schema changes on SQLite are table rebuilds in disguise, so the migration you test is not the migration you ship.
• Foreign keys are unenforced unless every connection turns them on, and fixtures written under that leniency break on PostgreSQL.
• Booleans, dates, decimals and UUIDs are conventions in SQLite, with different comparison and ordering semantics.
• Run the real engine in Docker locally and in CI. It costs seconds and removes the whole category of surprises.