Moving from SQLite to PostgreSQL

Django's development default is SQLite — a database in a single file, zero setup, perfect for building. It is the wrong choice for production, and the switch to PostgreSQL is a step every real Django deployment makes. The good news is that because you used the ORM throughout, your code barely changes; the switch is mostly configuration. This lesson is why SQLite does not suit production, how to move to PostgreSQL, and the small differences to be aware of.

Why SQLite is for development, not production

SQLite is genuinely excellent — for its purpose. Its limitation is concurrency: it is a single file, and it locks the whole database for writes, so concurrent writes serialise and, under load, produce "database is locked" errors. A production web app has many simultaneous requests, several of them writing, and SQLite was not built for that. It also lacks features a real app leans on (rich types, advanced indexing, true concurrent access) and does not suit running across multiple servers. So SQLite is the right default for development (nothing to install, fast to reset) and the wrong choice the moment real users arrive concurrently.

Why PostgreSQL

PostgreSQL is the community's default production database for Django, for good reasons: genuine concurrent read/write access (many writers without locking the whole database), robustness and data integrity, rich data types (JSON, arrays, full-text search) that Django has first-class support for, and battle-tested scale. Django and PostgreSQL are a well-worn, well-supported pairing — django.contrib.postgres even adds Postgres-specific fields and features. For almost any production Django app, PostgreSQL is the sound default; you would only choose otherwise for a specific reason.

Making the switch: mostly configuration

Because you wrote everything through the ORM — models, querysets, migrations — your application code is database-agnostic and does not change. The switch is the DATABASES setting plus a driver:

pip install psycopg[binary]          # the PostgreSQL driver for Python

# settings.py — from the environment (settings-and-secrets lesson)
import os
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": os.environ["DB_NAME"],
        "USER": os.environ["DB_USER"],
        "PASSWORD": os.environ["DB_PASSWORD"],
        "HOST": os.environ["DB_HOST"],
        "PORT": os.environ.get("DB_PORT", "5432"),
    }
}

(With django-environ, this collapses to DATABASES = {"default": env.db()} reading a single DATABASE_URL.) Then, against the PostgreSQL database, you run the same migrations you already have:

python manage.py migrate            # creates your schema in PostgreSQL from the same migration files

This is the pay-off of migrations being the source of truth for your schema: the identical migration files that built your SQLite schema build the PostgreSQL one. Your models, queries and migrations are unchanged — only the connection settings differ.

The differences that can bite

The ORM smooths over most database differences, but a few leak through, and knowing them saves confusion:

• Do not develop on SQLite and deploy on PostgreSQL blindly. Because the databases differ subtly, a query or migration that works on SQLite can behave differently on PostgreSQL. The safest practice is to develop on the same database you deploy on — run PostgreSQL locally (easily, via Docker — the next lesson) so dev and production match. "Works on my SQLite" is a real trap.
• Type and constraint strictness differs. PostgreSQL is stricter about types and enforces constraints more rigorously than SQLite (which is famously lax). Something SQLite quietly allowed, PostgreSQL may correctly reject — usually surfacing a real bug SQLite was hiding.
• Case sensitivity and ordering can differ (string comparisons, default sort orders), so relying on SQLite's specific behaviour is unwise.
• Postgres-only features (JSONField querying, array fields, full-text search via django.contrib.postgres) work only on PostgreSQL — fine to use, but they tie you to it.

None of these change your code; they are reasons to match your development database to production so you meet the differences while building, not on launch day.

Managing the database itself

Two operational notes, expanded in later lessons but worth flagging:

• Backups. A production database holds data you cannot recreate — a clinic's patient and appointment records. Regular, tested backups (pg_dump, or your host's managed backups) are not optional. "Tested" matters: a backup you have never restored is a hope, not a backup.
• Managed versus self-hosted. You can run PostgreSQL yourself or use a managed service (many hosts offer one). A managed database handles backups, updates and failover for you — usually worth it for a small team, so you spend your time on the app, not on database administration. The cost-conscious default is a small managed instance sized to your actual load.

Check your work

Why not SQLite in production. It locks the whole database for writes, so concurrent writes serialise and error under load ("database is locked"); it is a single file built for development, not many concurrent users.

Why PostgreSQL. True concurrent read/write, robustness, rich types (JSON, arrays, full-text search with Django support), and proven scale — the community default for production Django.

The switch. Mostly configuration — install psycopg, set DATABASES (from the environment), and run the same migrations; your models/queries/migrations are unchanged because you used the ORM.

The differences that bite. SQLite and PostgreSQL differ subtly (type/constraint strictness, case, order, Postgres-only features) — so develop on the same database you deploy on, rather than dev-on-SQLite, prod-on-Postgres.

Operations. Tested backups are mandatory for irreplaceable data; a small managed PostgreSQL instance is usually the sensible, cost-effective choice for a small team.

Practice

1. Install psycopg, configure DATABASES for PostgreSQL from environment variables, and run migrate against a Postgres database; confirm your schema is created from the same migrations.
2. Run PostgreSQL locally (via Docker) and point development at it, so dev matches production.
3. Find a constraint or type case that SQLite allowed but PostgreSQL rejects (or vice versa); reason about which behaviour is correct.
4. Use a PostgreSQL-only feature (JSONField querying) and note that it ties the app to Postgres.
5. Take a pg_dump backup and restore it into a fresh database; confirm the data is intact — a tested backup.
6. Compare a small managed PostgreSQL instance versus self-hosting for Nidaan, and justify the choice on cost and effort.

Official documentation

• Django — Databases (PostgreSQL notes) — Configuration and Postgres specifics.
• Django — django.contrib.postgres — Postgres-only fields and features.
• Django — Settings: DATABASES — The connection settings.

Next: static files and media in production.