Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 77 ยท Models and the ORM

Databases: PostgreSQL, MySQL and Multiple Databases

Configure Django for PostgreSQL or MySQL, connect to managed databases such as Amazon RDS, and use several databases with DATABASE_ROUTERS, using() and read replicas, with live examples.

Every lesson so far has used SQLite in memory because it needs nothing installed. Real projects usually run PostgreSQL or MySQL, often hosted by a cloud provider, and some projects talk to more than one database. Django supports all of this through one setting, DATABASES, and a small routing mechanism. The ORM code you have already learned does not change at all: only the configuration does.

This lesson covers the engine settings, the drivers, managed databases, and then multi-database setups with routers, which you can run in the browser because Django lets you define several SQLite databases at once.

Choosing and configuring a database engine

Django ships backends for SQLite, PostgreSQL, MySQL/MariaDB and Oracle. For a new production project, PostgreSQL is the usual recommendation: it has the richest feature support in the ORM (JSONField lookups, ArrayField, full-text search, DISTINCT ON, constraints and indexes of many kinds).

PostgreSQL

Install a driver (pip install "psycopg[binary]", which is psycopg 3) and describe the connection:

Python
# settings.py (read-only)
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "shop",
        "USER": "shop_app",
        "PASSWORD": os.environ["DB_PASSWORD"],
        "HOST": "127.0.0.1",
        "PORT": "5432",
        "CONN_MAX_AGE": 60,          # reuse connections for up to 60 seconds
        "CONN_HEALTH_CHECKS": True,  # drop a stale reused connection before use
    }
}

MySQL and MariaDB

Install mysqlclient (a C extension; it needs the MySQL client library on the machine) or a pure-Python alternative like PyMySQL. Use utf8mb4 so emoji and all of Unicode work:

Python
# settings.py (read-only)
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.mysql",
        "NAME": "shop",
        "USER": "shop_app",
        "PASSWORD": os.environ["DB_PASSWORD"],
        "HOST": "127.0.0.1",
        "PORT": "3306",
        "OPTIONS": {"charset": "utf8mb4"},
    }
}

One URL instead of five keys

Hosting platforms hand you a single DATABASE_URL such as postgres://user:pass@host:5432/dbname. The package dj-database-url (or django-environ, covered in Managing SECRET_KEY and Environment Variables) turns it into the dictionary above, which keeps secrets out of the source code:

Python
# settings.py (read-only)
import dj_database_url

DATABASES = {"default": dj_database_url.config(conn_max_age=60, ssl_require=True)}

Managed databases (Amazon RDS and similar)

A managed service runs the database server for you (backups, patching, failover). From Django's point of view it is an ordinary PostgreSQL or MySQL server at a different HOST. What changes in practice:

  • Use the endpoint the provider gives you (for RDS, a name like mydb.abc123.eu-west-1.rds.amazonaws.com) as HOST.
  • Require TLS. For PostgreSQL pass OPTIONS: {"sslmode": "require"} (or verify-full with the provider's CA bundle).
  • Allow only your application servers in the provider's firewall or security group; never expose the database port to the whole internet.
  • Keep CONN_MAX_AGE modest and, with many app processes, consider a connection pooler (PgBouncer, or Django's built-in OPTIONS: {"pool": True} for psycopg 3). Every connection costs the server memory.
  • Run python manage.py migrate as a deploy step with a user that may change the schema, and run the app as a user that may not.

Everything else (models, migrations, queries) is identical to the SQLite you used while learning. If a feature behaves differently, Raw SQL, Database Functions and JSON Fields lists the database-specific parts.

Using more than one database

DATABASES is a dictionary, so you can list several aliases. default is mandatory; the others have any name. Common reasons are:

  • a read replica to take heavy reads off the primary,
  • a separate analytics or legacy database,
  • separating a tenant's data (advanced).

Django does not join across databases, cannot make a ForeignKey between them, and a transaction covers one database only (transaction.atomic(using="alias")). Plan boundaries accordingly.

Choosing the database by hand: using()

Without any configuration beyond DATABASES, every query goes to default. You can direct a single query elsewhere with .using(alias) and a single save with save(using=alias). Each database needs its own tables, created by migrate --database=alias, which this example mimics with the schema editor.

Python Two databases and using()

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Each alias is an independent connection: the count in archive changed while default did not. Calling .using("archive") on a queryset is also how a management command targets one database in a loop.

Database routers

Writing using() everywhere is error-prone. A router is a plain class that Django asks "which database for this model?" every time it reads, writes or migrates. List it in DATABASE_ROUTERS (a list of dotted paths; the first router that returns an alias wins, and returning None passes the decision on). A router can define four methods:

Method Decides
db_for_read(model, **hints) The alias for reads
db_for_write(model, **hints) The alias for writes
allow_relation(obj1, obj2, **hints) Whether a relation (FK, M2M) between two objects is allowed
allow_migrate(db, app_label, model_name=None, **hints) Whether a migration may create this model's table in this database

Primary and read replica

A read replica is a copy of the primary database that is kept up to date by the database server itself (replication). Sending reads to the replica and writes to the primary spreads the load. The router below also sends a whole metrics app to its own analytics database.

Because replication takes a moment, the replica can briefly lag behind. In this demo the replica is a separate empty SQLite database that nothing replicates into, which makes the lag impossible to miss: a row just written to the primary is not yet visible to a plain read.

Python A router with a primary, a replica and an analytics database

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Follow the first three lines. The create hit default (the primary), the plain count was routed to the replica and returned 0, and the same count with .using("default") found the row. That is the read-your-writes problem: right after a write, a read that must see it should use the primary. In a real replica setup the delay is milliseconds, so most code never notices, but screens that redirect right after saving (the "post/redirect/get" pattern) can.

Practical advice for replicas:

  • Start simple: route only specific heavy views (reports, search, exports) to the replica with .using("replica") before you introduce a global router.
  • Use .using("default") or a transaction.atomic() block for reads that must see the latest write.
  • Do not run migrate against replicas; the database server replicates schema changes itself. The allow_migrate method above is for the demo's separate analytics database.
  • In tests, tell Django a replica is a mirror of the primary with "TEST": {"MIRROR": "default"}; otherwise the test suite would create an empty second database.

Migrations with several databases

migrate only touches one database at a time: python manage.py migrate uses default, and migrate --database=analytics uses the other. The allow_migrate method is what stops a table from being created everywhere. Keep each model's home database in one place (the router) and document it, because a missing allow_migrate is the most common reason an app's tables appear in the wrong database.

Sharding: splitting one table across databases

A read replica scales reads; when a single primary can no longer hold the data or absorb the writes, you can shard: split the rows of a table across several databases so each one holds a slice. Every shard has the same schema, and a shard key (often the customer or tenant id) decides where a row lives. Django has no built-in sharding, but it gives you the pieces: several entries in DATABASES, using() to pick one, and routers to automate the choice.

Python A shard-key helper with using()

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Customer 4 always lands on shard1, so all of that customer's orders sit together and a lookup by customer touches exactly one database. A query without the shard key (the last lines) has to ask every shard and combine the results ("scatter-gather"), which is slower and is where sharding hurts.

Things to know before you choose it:

  • Ids and joins. Primary keys are only unique inside one shard, so use UUIDs or a central id generator if rows must be identified globally. Joins and foreign keys cannot cross shards; keep related rows on the same shard (for example everything of a tenant).
  • Resharding (adding a fourth shard changes % 3) means moving data. Consistent hashing or a lookup table from tenant to shard makes this easier.
  • Transactions do not span databases.
  • Routers. You can call a shard_for() helper from a router's db_for_read and db_for_write, passing the key through hints, but the explicit using() shown here is easier to follow and to test.
  • Exhaust cheaper options first: indexes, caching, read replicas, partitioned tables in PostgreSQL, or a bigger machine. Many applications never need to shard.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which setting lists several databases for Django?

    DATABASES maps alias names to connection settings and must contain default. DATABASE_ROUTERS decides which alias a query uses.
  2. 2. What does Product.objects.using('archive') do?

    using() affects only that queryset, selecting the database alias for it.
  3. 3. What is the read-your-writes problem with a read replica?

    Replication takes time, so a read routed to the replica right after a write can miss the new data. Read from the primary when you must see the latest write.
  4. 4. What can a Django ForeignKey NOT do?

    Django does not support relations or joins across databases because the database engines cannot enforce them. Keep related models in one database.

Summary

  • Switching to PostgreSQL or MySQL changes only DATABASES and the installed driver (psycopg, mysqlclient); your models and queries stay the same.
  • Keep credentials in environment variables, require TLS for managed databases such as Amazon RDS, and use CONN_MAX_AGE or pooling to limit connections.
  • DATABASES can hold several aliases; select one with .using(), save(using=) and transaction.atomic(using=).
  • A router class in DATABASE_ROUTERS automates the choice with db_for_read, db_for_write, allow_relation and allow_migrate.
  • Replicas lag slightly, so read from the primary when you must see your own write; there are no cross-database joins or foreign keys.

Next: Django and NoSQL (MongoDB).