Transactions, Raw SQL & Multiple Databases

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

This page is written against Django 6.1 (6.1.1, released 2026-09-02; 6.1.2 is scheduled for 2026-10-06), Python 3.12-3.14 and PostgreSQL 15 or newer, all checked on 2026-10-03; where the 5.2 LTS (5.2.17) behaves differently the text says so. Every example was run in a scratch project on SQLite (two and three SQLite files stand in for several servers), because no PostgreSQL, MySQL or Redis server was available: statements that need PostgreSQL (tablespaces, callproc on Oracle, real row locks) are marked as such and were validated by reading the official pages and, where possible, with manage.py check, not by connecting. The ORM turns every save() into SQL, and the guarantees you get from that SQL (all of it or none of it, visible to others only when finished, sent to the right server) come from three things this page covers: transactions, the escape hatches to raw SQL, and the routing of queries across databases. In the Bookshelf application that grows across this section, this is what keeps "decrement the stock, record the purchase, tell the buyer" consistent, what lets a report run a hand-written query, and what moves old reviews into an archive database. The page covers the transaction API and ATOMIC_REQUESTS, on_commit hooks, raw queries and cursors, database routers and the multi-database API, tablespaces, and generating models for a legacy database with inspectdb.

In a hurry: what goes where

Need Rule of thumb Section

Need	Rule of thumb	Section
Make several writes all-or-nothing	Wrap them in `transaction.atomic()`; nest it for a savepoint; catch database errors outside the block.	atomic: the single API
One transaction per request	`ATOMIC_REQUESTS = True` per database; opt a view out with `@transaction.non_atomic_requests`.	ATOMIC_REQUESTS: one transaction per request
Send email, enqueue a task or invalidate a cache after a write	`transaction.on_commit(callback)`, never inside the transaction body.	on_commit: do things after the data is safe
A query the ORM cannot express	Try the ORM and expressions first; then `RawSQL`, `Manager.raw()` or a cursor, always with `params`.	Raw SQL
More than one database	`DATABASES` aliases, `DATABASE_ROUTERS`, `migrate --database`, `using()`; no foreign keys across databases.	Multiple databases
Disk layout (PostgreSQL, Oracle)	`Meta.db_tablespace`, `db_tablespace` on fields and indexes, `DEFAULT_TABLESPACE`.	Tablespaces
An existing schema	`inspectdb` to draft unmanaged models, then review them by hand.	Legacy databases with inspectdb

Make several writes all-or-nothing

Wrap them in transaction.atomic(); nest it for a savepoint; catch database errors outside the block.

atomic: the single API

One transaction per request

ATOMIC_REQUESTS = True per database; opt a view out with @transaction.non_atomic_requests.

ATOMIC_REQUESTS: one transaction per request

Send email, enqueue a task or invalidate a cache after a write

transaction.on_commit(callback), never inside the transaction body.

on_commit: do things after the data is safe

A query the ORM cannot express

Try the ORM and expressions first; then RawSQL, Manager.raw() or a cursor, always with params.

Raw SQL

More than one database

DATABASES aliases, DATABASE_ROUTERS, migrate --database, using(); no foreign keys across databases.

Multiple databases

Disk layout (PostgreSQL, Oracle)

Meta.db_tablespace, db_tablespace on fields and indexes, DEFAULT_TABLESPACE.

Tablespaces

An existing schema

inspectdb to draft unmanaged models, then review them by hand.

Legacy databases with inspectdb

The running example

The examples use two small apps of the scratch project: bookshelf (a Book with a stock counter and a Purchase that points at it) and archive (an ArchivedReview that lives in a second database). Models, kept deliberately small:

# bookshelf/models.py
from django.db import models


class Book(models.Model):
    title = models.CharField(max_length=200)
    isbn = models.CharField(max_length=13, unique=True)
    stock = models.PositiveIntegerField(default=0)

    def __str__(self):
        return self.title


class Purchase(models.Model):
    book = models.ForeignKey(Book, on_delete=models.PROTECT, related_name="purchases")
    quantity = models.PositiveIntegerField()
    created = models.DateTimeField(auto_now_add=True)

Source: Django: Models

Their relationships, fields and Meta options are explained in Models & Fields and Relationships; the ORM calls used below are covered in Making Queries.

Transactions

A transaction is a group of SQL statements that the database applies atomically: either every change is committed or none is, and other connections do not see the changes before the commit. Django runs in autocommit mode by default (PEP 249 asks for the opposite, and Django overrides it): when no transaction is open, every statement is its own transaction and is committed at once. That is why a plain save() needs no ceremony, and why a view that performs two save() calls can leave the database half-updated if the second one fails. This is the "orders and payments without `transaction.atomic()`" class of defect found in older tutorials, and the cure is explicit.

Source: Django: Database transactions

atomic: the single API

transaction.atomic(using=None, savepoint=True, durable=False) is "the" transaction API. It is both a decorator and a context manager. On entry to the outermost block Django opens a transaction; on a normal exit it commits; on an exception it rolls back and re-raises. The service function below is the heart of Bookshelf's checkout: it locks the book row, checks the stock, decrements it with an F() expression (so the arithmetic happens in SQL) and records the purchase, all or nothing:

# bookshelf/services.py
from django.db import transaction
from django.db.models import F

from .models import Book, Purchase


class OutOfStock(Exception):
    pass


@transaction.atomic
def buy_book(book_id, quantity):
    """Decrement stock and record the purchase, all or nothing."""
    book = Book.objects.select_for_update().get(pk=book_id)
    if book.stock < quantity:
        raise OutOfStock(book.title)
    Book.objects.filter(pk=book_id).update(stock=F("stock") - quantity)
    return Purchase.objects.create(book=book, quantity=quantity)

Source: Django: Controlling transactions explicitly

The test that exercised it asks for five copies of a book with one in stock, and checks that nothing was written:

class AtomicTests(TestCase):
    def test_atomic_rolls_back_on_exception(self):
        book = Book.objects.create(title="Dune", isbn="1", stock=1)
        with self.assertRaises(services.OutOfStock):
            services.buy_book(book.pk, 5)
        self.assertEqual(Purchase.objects.count(), 0)

Source: Django: TestCase

Three details matter. select_for_update() takes a row lock on PostgreSQL, MySQL and Oracle and must run inside a transaction; SQLite ignores it (SQLite serialises writers at the file level), so the scratch run proves the all-or-nothing behaviour, not the locking. using names the database alias the transaction belongs to (default "default"): a transaction never spans two databases (see Transactions and raw SQL per database). And on exit from the outermost block Django disables the low-level commit, rollback and autocommit calls inside it, so code that calls rollback() by hand while an atomic block is open raises an exception instead of silently breaking atomicity.

Nesting and savepoints

atomic blocks nest. Django’s own description of the algorithm is short: open a transaction on entering the outermost block; create a savepoint on entering an inner block; release it or roll back to it when the inner block exits; commit or roll back when the outermost block exits. A savepoint is a marker inside a transaction that lets the database undo part of the work. The classic use is "try one row at a time": a duplicate ISBN undoes that row only.

from django.db import IntegrityError, transaction

from .models import Book


def add_books_skipping_duplicates(rows):
    """One savepoint per row: a duplicate ISBN only undoes that row."""
    created, skipped = [], []
    with transaction.atomic():
        for title, isbn in rows:
            try:
                with transaction.atomic():  # inner block: a savepoint
                    created.append(Book.objects.create(title=title, isbn=isbn))
            except IntegrityError:
                skipped.append(isbn)
    return created, skipped

Source: Django: Controlling transactions explicitly

Run with [("A", "100"), ("B", "111"), ("C", "102")] and 111 already present, the test got two created books and ["111"] skipped; the outer transaction kept A and C. Note where the try sits: around the inner atomic, not inside it.

Catching exceptions: the pitfall

When a database error such as IntegrityError is raised, the transaction is broken and Django rolls it back when the atomic block that contains it exits. If you catch the exception inside that block, Django cannot tell that anything went wrong, and the next query raises TransactionManagementError ("You can’t execute queries until the end of the 'atomic' block"). The test below reproduces the mistake on purpose:

from django.db.transaction import TransactionManagementError


def test_catching_inside_atomic_is_a_trap(self):
    Book.objects.create(title="x", isbn="9")
    with self.assertRaises(TransactionManagementError):
        with transaction.atomic():
            try:
                Book.objects.create(title="y", isbn="9")  # IntegrityError
            except IntegrityError:
                pass  # swallowed, the transaction is now broken
            Book.objects.count()  # TransactionManagementError

Source: Django: Avoid catching exceptions inside atomic!

The fix is the pattern of the previous section: the try/except goes outside an inner atomic, which also documents exactly which operations are rolled back. The same rule applies to an exception raised by a signal handler during save(), and the official page adds that catching exceptions from raw SQL leaves Django’s behaviour unspecified and database-dependent.

A related trap is application state. A rollback restores the database, not Python objects: model fields, caches and globals keep the values you assigned. If obj.active = True was set before a failed save() inside atomic, set it back in the except branch, and for caches prefer on_commit (below) to updating the cache inside the transaction.

durable, savepoint=False and the rollback flag

durable=True (Django 3.2) asserts that the block is the outermost one, so its changes are really committed when it exits; nested inside another atomic it raises RuntimeError. It is a guard for functions whose callers must be able to rely on "when this returns, the data is on disk":

def test_durable(self):
    with transaction.atomic():
        with self.assertRaises(RuntimeError):
            with transaction.atomic(durable=True):
                pass

Source: Django: atomic

(Under Django’s TestCase, which wraps each test in a transaction, a durable block is always nested, which is why the test wraps it in assertRaises; use TransactionTestCase to test a durable function for real.)

savepoint=False skips the savepoint for an inner block to save its small overhead. A failure inside then rolls back to the first parent that has a savepoint, or the whole transaction, and the error handling shown above no longer works, so reach for it only when profiling shows savepoint cost.

When you do recover from a database error with your own savepoint, tell Django the transaction is usable again with transaction.set_rollback(False); transaction.get_rollback() reads the flag, and set_rollback(True) forces a rollback on exit without raising, handy for "dry run" code paths:

def test_set_rollback_without_exception(self):
    with transaction.atomic():
        Book.objects.create(title="z", isbn="z")
        transaction.set_rollback(True)
    self.assertEqual(Book.objects.count(), 0)

Source: Django: Savepoints, get_rollback and set_rollback

Low-level savepoints (savepoint_create in 6.1)

Inside an atomic block you can also manage savepoints by hand with savepoint_create(), savepoint_commit(sid) and savepoint_rollback(sid); clean_savepoints() resets the counter that generates ids. Django 6.1 added savepoint_create() and deprecated transaction.savepoint(), which was renamed. Both do the same, but the old name emits a RemovedInDjango70Warning (6.1 also moved to calendar-based deprecation names, so the warning class will be renamed again for 2028). In the scratch project the deprecated call printed savepoint() is deprecated. Use savepoint_create() instead. The 6.1 spelling:

def test_low_level_savepoints(self):
    with transaction.atomic():
        Book.objects.create(title="a", isbn="a")
        sid = transaction.savepoint_create()
        Book.objects.create(title="b", isbn="b")
        transaction.savepoint_rollback(sid)      # b is undone
        sid2 = transaction.savepoint_create()
        Book.objects.create(title="c", isbn="c")
        transaction.savepoint_commit(sid2)       # c becomes part of the transaction
    self.assertEqual(sorted(Book.objects.values_list("isbn", flat=True)), ["a", "c"])

Source: Django: Savepoints

The official page strongly prefers atomic() and says the functions "do nothing" where savepoints are unsupported or in autocommit mode. Savepoints are real on SQLite, PostgreSQL, Oracle and MySQL with InnoDB; on SQLite the low-level calls only work inside an atomic() block because of a flaw in the sqlite3 module, and atomic() cannot be used with autocommit turned off there. If you used transaction.savepoint() on 6.0 or 5.2, switching to savepoint_create() silences the warning; the rest of the API (savepoint_commit, savepoint_rollback, clean_savepoints) is unchanged.

Autocommit and the other low-level calls

transaction.get_autocommit(), set_autocommit(), commit() and rollback() exist for people who write their own transaction management; Django has no "begin" call, you start a transaction by turning autocommit off. They refuse to run inside an atomic block. Setting AUTOCOMMIT: False in a database’s DATABASES entry disables Django’s transaction management altogether for that alias, so you must commit every transaction yourself, including ones Django or a third-party library starts. The official pages recommend atomic in nearly every case; this page does not use the low-level API again.

ATOMIC_REQUESTS: one transaction per request

Setting ATOMIC_REQUESTS = True in a database’s entry of DATABASES makes Django wrap each view in atomic() for that database: it opens the transaction before the view runs, commits if the response is produced, and rolls back if the view raises. In practice it is the atomic decorator applied to every view. Two scope rules explain most surprises: only the view is inside the transaction, so middleware and the rendering of template responses run outside it, and a StreamingHttpResponse body is produced after the view has returned, so code that runs while streaming is outside the transaction (do not write to the database from a streaming generator).

The scratch default alias has "ATOMIC_REQUESTS": True; the archive alias does not, which also shows that the flag is per database. The two views below write a Purchase and then fail, one wrapped by the setting and one opted out:

# config/settings.py (excerpt)
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": BASE_DIR / "default.sqlite3",
        "ATOMIC_REQUESTS": True,
    },
    # ...
}

# bookshelf/views.py
from django.db import transaction
from django.http import HttpResponse

from .models import Book, Purchase


def buy_and_crash(request, book_id):
    """With ATOMIC_REQUESTS=True the Purchase below is rolled back."""
    Purchase.objects.create(book=Book.objects.get(pk=book_id), quantity=1)
    raise RuntimeError("payment gateway down")


def buy_ok(request, book_id):
    Purchase.objects.create(book=Book.objects.get(pk=book_id), quantity=1)
    return HttpResponse("ok")


@transaction.non_atomic_requests
def audit_without_transaction(request, book_id):
    """Opted out: the row survives the crash."""
    Purchase.objects.create(book=Book.objects.get(pk=book_id), quantity=1)
    raise RuntimeError("after the write")

Source: Django: Tying transactions to HTTP requests

A TransactionTestCase with the test client confirmed the behaviour (the client is told not to re-raise the server exceptions): after the first request there were zero purchases, after the opted-out view one, after buy_ok two.

class RealCommitTests(TransactionTestCase):
    def test_atomic_requests(self):
        book = Book.objects.create(title="Dune", isbn="1")
        self.client.raise_request_exception = False
        self.client.get(f"/buy/{book.pk}/")
        self.assertEqual(Purchase.objects.count(), 0)   # rolled back
        self.client.get(f"/audit/{book.pk}/")
        self.assertEqual(Purchase.objects.count(), 1)   # opted out, survived
        self.client.get(f"/buy-ok/{book.pk}/")
        self.assertEqual(Purchase.objects.count(), 2)

Source: Django: non_atomic_requests

non_atomic_requests(using=None) takes an optional alias to opt out for one database only, and it only works when applied to the view itself (not to a helper, a mixin method or dispatch). In a class-based view use method_decorator(transaction.non_atomic_requests, name="dispatch"). Weigh the trade-off: per-request transactions are simple and a good default for small sites, but they hold a transaction (and its locks) open for the whole view, including calls to slow third-party services, which hurts under load. Many teams leave the setting off and use atomic around the specific writes that must be consistent, as the checkout service above does. ATOMIC_REQUESTS and the other database settings are described in Settings & Configuration. Under ASGI the same rule applies, but see Transactions under async.

on_commit: do things after the data is safe

Side effects such as sending a welcome email, enqueuing a background job or invalidating a cache must happen only if the transaction commits. Doing them inside the atomic block has two failure modes: the work runs and the transaction then rolls back (the buyer is told about a purchase that does not exist), or a worker picks the job up before the commit and cannot see the row. transaction.on_commit(func, using=None, robust=False) registers a callable that runs after a successful commit and is discarded on rollback. The completed checkout service, with the notification added:

NOTIFICATIONS = []  # stands in for "send an email" / "enqueue a task"


def notify_buyer(purchase_id):
    NOTIFICATIONS.append(purchase_id)


@transaction.atomic
def buy_book(book_id, quantity):
    book = Book.objects.select_for_update().get(pk=book_id)
    if book.stock < quantity:
        raise OutOfStock(book.title)
    Book.objects.filter(pk=book_id).update(stock=F("stock") - quantity)
    purchase = Purchase.objects.create(book=book, quantity=quantity)
    # Only tell the buyer once the row is really committed.
    transaction.on_commit(lambda: notify_buyer(purchase.pk))
    return purchase

Source: Django: Performing actions after commit

The rules, each checked in the scratch project:

The callback takes no arguments; bind them with functools.partial (the test registered partial(calls.append, i) three times in a loop and got [0, 1, 2]) or a lambda.
Callbacks run in registration order, only after the outermost transaction commits and after autocommit has been restored, so queries inside a callback do not start a new implicit transaction.
A callback registered inside an inner atomic (a savepoint) that is rolled back is discarded; callbacks registered outside it still run. The test registered foo in the outer block and bar in a failed inner block and only foo ran.
Called with no transaction open, the callable runs immediately (a test confirmed "now" was appended on the spot); with autocommit disabled and no atomic, it raises an error.
A failing callback does not roll the transaction back (it is already committed). If a callback raises and robust=False (the default), the callbacks registered after it do not run. robust=True (added in Django 4.2) catches errors derived from Exception, logs them to the django.db.backends.base logger and continues with the next callback:

def test_runs_after_commit_and_robust(self):
    calls = []

    def boom():
        raise RuntimeError("boom")

    with self.assertLogs("django.db.backends.base", level="ERROR") as logs:
        with transaction.atomic():
            transaction.on_commit(boom, robust=True)
            transaction.on_commit(lambda: calls.append("after boom"))
    self.assertEqual(calls, ["after boom"])

Source: Django: on_commit exception handling

The logged line began Error calling RealCommitTests.test_runs_after_commit_and_robust.<locals>.boom in on_commit() during transaction (boom). With robust=False the same test raised RuntimeError out of the with block and the second callback never ran.

Because on_commit runs after the commit, it is not part of the transaction: if the follow-up is so critical that its failure must fail the transaction, on_commit is the wrong tool and the official page points at two-phase commit. There is also deliberately no "rollback hook": a rolled-back or crashed process cannot be relied on to run it, so the documented advice is to delay doing the thing until after the commit. Enqueueing django.tasks or Celery jobs from an on_commit hook is the standard pairing; the task pages of this section build on it.

Testing on_commit hooks

Django’s TestCase wraps each test in a transaction that is never committed, so on_commit callbacks never run. Two remedies exist. TestCase.captureOnCommitCallbacks(using="default", execute=False) (Django 3.2) records the callbacks in a list, so you can assert on them or run them with execute=True, without paying for real commits:

class OnCommitTests(TestCase):
    def test_captured(self):
        services.NOTIFICATIONS.clear()
        book = Book.objects.create(title="Dune", isbn="1", stock=3)
        with self.captureOnCommitCallbacks(execute=True) as callbacks:
            purchase = services.buy_book(book.pk, 1)
        self.assertEqual(len(callbacks), 1)
        self.assertEqual(services.NOTIFICATIONS, [purchase.pk])

Source: Django: captureOnCommitCallbacks

The alternative is TransactionTestCase, where transactions really commit and the callbacks run on their own, at the price of flushing the database between tests (much slower). The robust and ATOMIC_REQUESTS examples above use it for that reason. The pytest-django equivalents are the django_capture_on_commit_callbacks fixture and the transaction=True marker argument.

Transactions under async

Django’s async support covers the ORM’s a-prefixed methods (aget, acreate, asave and so on), but transactions do not yet work in async mode: atomic() cannot be used in an async def. The documented recommendation is to put the transactional code in one synchronous function and call it with sync_to_async(). The scratch test did exactly that:

from asgiref.sync import async_to_sync, sync_to_async


def test_async_transactions(self):
    @sync_to_async
    def make():
        with transaction.atomic():
            return Book.objects.create(title="async", isbn="as").pk

    pk = async_to_sync(make)()
    self.assertTrue(Book.objects.filter(pk=pk).exists())

Source: Django: Asynchronous support, queries and the ORM

The same page recommends disabling persistent connections (CONN_MAX_AGE) under async and using the database backend’s connection pool instead; both are covered in Query Optimization & Fetch Modes.

Raw SQL

Django gives three ways to run raw SQL: embed a fragment in an ORM query with RawSQL, run a whole statement through Manager.raw() and get model instances back, or bypass the model layer with a cursor. The official page opens with a warning worth repeating: explore the ORM first. Between the QuerySet API, Func()/F() expressions, database functions and custom expressions (see Aggregation, Expressions & Functions) most "I need SQL" cases disappear, and each raw statement is code the ORM cannot protect, port to another backend or keep in step with your migrations.

Source: Django: Performing raw SQL queries

Parameters, always

Every raw API takes a params argument, and you must use it for any value that could come from a user: placeholders are %s (a list) or %(key)s (a dict, not supported by the SQLite backend) whatever the database, never ?, and never quoted. String interpolation or quoting the placeholder opens the door to SQL injection. The scratch test passes the classic payload as a parameter and shows it is treated as a literal string, matching no title:

def test_raw_injection_is_literal(self):
    payload = "x' OR '1'='1"
    qs = Book.objects.raw("SELECT * FROM bookshelf_book WHERE title = %s", [payload])
    self.assertEqual(list(qs), [])

Source: Django: SQL injection protection

Manager.raw()

Manager.raw(raw_query, params=(), translations=None) returns a RawQuerySet that you iterate like a QuerySet but which supports only a small part of its API (no filter() or count()). The table name comes from the model (bookshelf_book: app label, underscore, lower-cased class name, unless db_table overrides it). Django does not check the SQL; if the statement does not return rows the error can be cryptic.

def test_raw_params(self):
    qs = Book.objects.raw("SELECT * FROM bookshelf_book WHERE stock > %s", [0])
    self.assertEqual([b.title for b in qs], ["Dune"])

Source: Django: Performing raw queries

Columns map to fields by name, not position, so AS aliases work, and the translations argument maps query column names to field names. Fields you leave out are deferred and loaded on demand (one extra query each, the same N+1 shape that Query Optimization & Fetch Modes teaches you to avoid); the primary key is the one column you cannot omit, otherwise FieldDoesNotExist ("Raw query must include the primary key") is raised. Indexing and slicing a RawQuerySet happens in Python, so put LIMIT in the SQL.

def test_raw_translations_and_deferred(self):
    qs = Book.objects.raw(
        "SELECT id, title AS name FROM bookshelf_book ORDER BY id",
        translations={"name": "title"},
    )
    first = qs[0]
    with self.assertNumQueries(0):  # title came with the raw query
        self.assertEqual(first.title, "Dune")
    with self.assertNumQueries(1):  # stock was left out: loaded on demand
        self.assertEqual(first.stock, 3)

Source: Django: Mapping query fields to model fields

On MySQL the official page also warns about silent type coercion when comparing a string column with an integer, so cast values yourself.

RawSQL fragments in ORM queries

When only one expression needs SQL, keep the rest in the ORM: RawSQL(sql, params) embeds a parameterised fragment in annotate() or filter(). Func() expressions are usually the more portable choice for calling database functions.

from django.db.models.expressions import RawSQL


def test_rawsql_fragment(self):
    qs = Book.objects.annotate(
        in_stock=RawSQL("SELECT stock > %s", (0,)),
    ).order_by("id")
    self.assertEqual([b.in_stock for b in qs], [1, 0])  # SQLite has no boolean type

Source: Django: Raw SQL expressions

Django 6.1 now quotes SELECT aliases from annotate() and table and JOIN aliases systematically; because quoted aliases are case-sensitive, raw SQL that refers to a mixed-case alias (for example in RawSQL) may need to quote it as well. The 6.1 release notes list this under backwards-incompatible changes.

Cursors

For statements that do not map to a model (an UPDATE with a join, DDL, a maintenance command), take a cursor from django.db.connection. cursor.execute(sql, params) followed by fetchone()/fetchall() is the standard DB-API flow, and the cursor is a context manager that closes itself:

from django.db import connection


def test_cursor(self):
    with connection.cursor() as cursor:
        cursor.execute(
            "UPDATE bookshelf_book SET stock = stock + %s WHERE id = %s",
            [10, self.emma.pk],
        )
        cursor.execute("SELECT title, stock FROM bookshelf_book WHERE id = %s", [self.emma.pk])
        row = cursor.fetchone()
    self.assertEqual(row, ("Emma", 10))

Source: Django: Executing custom SQL directly

A raw cursor returns bare tuples. The two helper functions from the docs return dictionaries or named tuples instead, at a small cost in speed and memory, and assume column names are unique:

from collections import namedtuple


def dictfetchall(cursor):
    columns = [col[0] for col in cursor.description]
    return [dict(zip(columns, row)) for row in cursor.fetchall()]


def namedtuplefetchall(cursor):
    desc = cursor.description
    nt_result = namedtuple("Result", [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

Source: Django: dictfetchall and namedtuplefetchall

On the two seeded books the outputs were [{'id': 1, 'title': 'Dune'}, {'id': 2, 'title': 'Emma'}] and [Result(id=1, title='Dune'), Result(id=2, title='Emma')]. Two more rules: a literal percent sign must be doubled (LIKE '%%e') whenever you pass params, and cursors bypass the model layer completely: signals, auto_now fields, validation and deferred on_commit registration do not happen, and a cursor from connection always targets the default alias (see Raw cursors and several databases). CursorWrapper.callproc(procname, params=None, kparams=None) calls a stored procedure; of the built-in backends only Oracle supports keyword parameters, and the example in the docs needs an Oracle database, so it was not run here.

Multiple databases

Most pages assume one database. Django can talk to several: you declare them under aliases in DATABASES, and then either route queries automatically with routers or choose a database by hand. The alias default has special meaning (Django uses it whenever no other database is selected). Typical reasons are an archive or reporting database, a primary with read replicas, or a legacy system you must read.

Source: Django: Multiple databases

Defining and migrating the databases

The scratch project uses three SQLite files as stand-ins for servers: default, archive and legacy (used later). With real servers each entry holds the engine, host, credentials and OPTIONS of its own server; read credentials from the environment as described in Settings & Configuration.

# config/settings.py (excerpt)
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": BASE_DIR / "default.sqlite3",
        "ATOMIC_REQUESTS": True,
    },
    "archive": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": BASE_DIR / "archive.sqlite3",
    },
}
DATABASE_ROUTERS = ["config.routers.ArchiveRouter"]

Source: Django: Defining your databases

If the concept of a default database makes no sense for your project, Django still requires a default entry, but its dictionary may be empty; you must then route every app, including contrib and third-party ones, so no query reaches it. Asking for an alias not in DATABASES raises django.utils.connection.ConnectionDoesNotExist (a test confirmed it for connections["nope"]).

migrate works on one database at a time, default unless you pass --database; most other commands that touch the database behave the same way. makemigrations is the exception: it validates the migration history of the databases (only default unless routers say otherwise) and consults routers' allow_migrate().

python manage.py migrate                      # the default database
python manage.py migrate --database=archive   # the archive database

Source: Django: Synchronizing your databases

Database routers

The default routing is sticky and simple: an object stays on the database it was read from, and everything else goes to default. A router is a plain class with up to four methods, all optional (a router that omits one is skipped for that decision):

db_for_read(model, hints) and db_for_write(model, hints) suggest an alias, or return None for "no opinion".
allow_relation(obj1, obj2, **hints) says whether a relation between two objects is allowed (True, False to forbid, None for no opinion; if nobody has one, only relations within one database are allowed).
allow_migrate(db, app_label, model_name=None, **hints) says whether a migration operation may run on alias db. model_name is None for RunPython and RunSQL unless they supply it through hints, and the model in hints may be a historical model, so rely only on _meta.

The only hint Django currently passes is instance; its database is recorded in instance._state.db, and a good router consults it before falling back to a fixed alias. Routers are listed in DATABASE_ROUTERS and asked in order until one answers; if none does, Django tries instance._state.db and finally default. Order matters, as a catch-all router listed first answers for everything. The router used in the scratch project follows the official example (send an app to its own database and keep it there), and also forbids relations that cross the boundary:

# config/routers.py
class ArchiveRouter:
    """Send the ``archive`` app to the ``archive`` database and keep it there."""

    route_app_labels = {"archive"}

    def db_for_read(self, model, **hints):
        if model._meta.app_label in self.route_app_labels:
            return "archive"
        return None

    def db_for_write(self, model, **hints):
        if model._meta.app_label in self.route_app_labels:
            return "archive"
        return None

    def allow_relation(self, obj1, obj2, **hints):
        labels = {obj1._meta.app_label, obj2._meta.app_label}
        if labels <= self.route_app_labels or not labels & self.route_app_labels:
            return None  # no opinion: fall back to "same database only"
        return False

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if app_label in self.route_app_labels:
            return db == "archive"
        if db == "archive":
            return False
        return None

Source: Django: Multi-db routing example

The matching model stores the id of a book as a plain integer rather than a ForeignKey, for the reason explained under Limitations and the contrib apps:

# archive/models.py
from django.db import models


class ArchivedReview(models.Model):
    book_id = models.PositiveBigIntegerField()  # plain integer, not a ForeignKey (cross-database)
    body = models.TextField()

Source: Django: Cross-database relations

The tests showed the router at work: router.db_for_read(ArchivedReview) and db_for_write return "archive", Book falls through to default, allow_migrate("default", "archive") is False, and after migrate plus migrate --database=archive the archive file held only django_migrations and archive_archivedreview, while the default file held the auth, contenttypes and bookshelf tables but no archive table.

class MultiDbTests(TestCase):
    databases = {"default", "archive"}

    def test_router_decisions(self):
        self.assertEqual(router.db_for_read(ArchivedReview), "archive")
        self.assertEqual(router.db_for_write(ArchivedReview), "archive")
        self.assertEqual(router.db_for_read(Book), "default")
        self.assertTrue(router.allow_migrate("default", "bookshelf"))
        self.assertFalse(router.allow_migrate("default", "archive"))
        self.assertFalse(router.allow_migrate("archive", "bookshelf"))

    def test_models_land_in_their_database(self):
        book = Book.objects.create(title="Dune", isbn="1")
        ArchivedReview.objects.create(book_id=book.pk, body="Classic")
        self.assertEqual(ArchivedReview.objects.all().db, "archive")
        self.assertEqual(Book.objects.all().db, "default")

Source: Django: Multi-database support in tests

One migration subtlety showed up in showmigrations: running migrate on default still records archive.0001_initial as applied there, even though allow_migrate returned False and no table was created, because the operations were skipped, not the migration. Changing allow_migrate() for models that already have migrations can therefore produce missing or extra tables and broken foreign keys, so decide the layout before the first migration. Tests must declare which databases they use with the databases class attribute ({"default", "archive"} above); Django only creates the listed test databases, and a query to an unlisted one fails.

A second router from the official example splits reads and writes (primary/replica). A runnable variant that respects the instance hint, so that objects just written to the primary are read back from it:

import random


class PrimaryReplicaRouter:
    """Reads go to a replica, writes to the primary (no replication-lag handling)."""

    replicas = ("replica1", "replica2")

    def db_for_read(self, model, **hints):
        instance = hints.get("instance")
        if instance is not None and instance._state.db == "primary":
            return "primary"  # read-your-writes for related lookups of a primary object
        return random.choice(self.replicas)

    def db_for_write(self, model, **hints):
        return "primary"

    def allow_relation(self, obj1, obj2, **hints):
        pool = {"primary", *self.replicas}
        return True if obj1._state.db in pool and obj2._state.db in pool else None

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        return db == "primary"

Source: Django: Multi-db routing example

Called directly in the shell (no servers behind the aliases, so only the routing decisions were exercised) it printed primary for writes, {'replica1', 'replica2'} over twenty reads, primary for a read with an instance bound to the primary, True for allow_relation inside the pool and False for allow_migrate("replica1", …). The official page is candid that this design ignores replication lag and the interaction of transactions with routing: a read right after a write may hit a replica that has not caught up, and atomic() blocks must read from the database they write to (which the instance hint helps, but does not guarantee). Treat it as a starting point, not a production policy.

Choosing a database by hand

A manually chosen database always wins over the router. The tools:

QuerySet.using(alias) at any point of a chain; Author.objects.using("other").
save(using=alias) and delete(using=alias); by default delete() runs on the database the instance came from.
Manager.db_manager(alias) gives a copy of a manager bound to an alias, which is how you reach manager-only methods such as create_user() (User.objects.db_manager("new_users").create_user(…)).
In a manager’s get_queryset(), call super() or honour self._db (qs.using(self._db) when not None), otherwise you lose the alias.

def test_db_manager(self):
    mgr = ArchivedReview.objects.db_manager("archive")
    self.assertEqual(mgr.create(book_id=1, body="via db_manager")._state.db, "archive")

Source: Django: Using managers with multiple databases

Moving an object between databases with save(using=…) has a sharp edge: the instance keeps its primary key, so saving it to a second database overwrites any row that already has that key. Clear pk to insert a new row, or pass force_insert=True to guarantee an INSERT (it then raises if the key is taken). The test below saved a review, built a second instance with the same key, saw force_insert=True raise IntegrityError, and then saw a plain save() silently overwrite the first row:

def test_save_using_and_force_insert(self):
    r = ArchivedReview(book_id=1, body="a")
    r.save(using="archive")
    pk = r.pk
    r2 = ArchivedReview(pk=pk, book_id=2, body="clash")
    with self.assertRaises(IntegrityError):
        with transaction.atomic(using="archive"):
            r2.save(using="archive", force_insert=True)
    r2.save(using="archive")  # no force_insert: silently overwrites
    self.assertEqual(ArchivedReview.objects.get(pk=pk).body, "clash")

Source: Django: Moving an object from one database to another

Transactions and raw SQL per database

Each database has its own connection and its own transaction. transaction.atomic(using="archive") and on_commit(func, using="archive") name the alias; a bare atomic() covers default only, and ATOMIC_REQUESTS and non_atomic_requests(using=…) are per alias too. Django offers no distributed transaction: nested blocks on two aliases commit or roll back independently. The test below rolls back the default block while the archive block keeps its row:

def test_transactions_are_per_database(self):
    with transaction.atomic(using="archive"):
        ArchivedReview.objects.create(book_id=1, body="kept in archive txn")
        with transaction.atomic():  # default
            Book.objects.create(title="b", isbn="b")
            transaction.set_rollback(True)  # only default is rolled back
    self.assertEqual(ArchivedReview.objects.count(), 1)
    self.assertEqual(Book.objects.count(), 0)

Source: Django: atomic takes a using argument

If the follow-up work must be consistent across two databases (write one, then write the other), accept eventual consistency: write the primary record, register an on_commit hook that writes the second, and make the second step idempotent, or use an outbox table in the first database and a worker.

Raw cursors and several databases

connection is the default connection; for another database take it from the django.db.connections mapping:

from django.db import connections


def test_connections_alias(self):
    with connections["archive"].cursor() as cursor:
        cursor.execute("SELECT count(*) FROM archive_archivedreview")
        self.assertEqual(cursor.fetchone()[0], 0)

Source: Django: Using raw cursors with multiple databases

Limitations and the contrib apps

Django does not support foreign keys or many-to-many relations that span databases: it could not check that the related primary key is valid, and with PostgreSQL, SQLite, Oracle and MySQL/InnoDB the database itself would refuse a constraint to a table it does not have. Hence ArchivedReview.book_id is a plain integer, and the router’s allow_relation returns False for a relation that crosses the boundary (a test calling router.allow_relation(book, review) got False). The consequences ripple through the contrib apps:

ContentType, Session and Site can each live in any database, given a suitable router.
The auth models (User, Group, Permission) are linked to each other and to ContentType, so they must share a database with ContentType; admin depends on auth; flatpages and redirects depend on sites.
migrate creates a default Site, a ContentType per model and a Permission per model in each database it runs on, whether or not the model is stored there. Content types created in several databases may get different primary keys, which can corrupt data or lose it, so write a router that synchronises these tables to only one database.

The admin has no built-in multi-database support. To show a model that lives elsewhere, subclass ModelAdmin (and InlineModelAdmin) and override save_model, delete_model, get_queryset, formfield_for_foreignkey and formfield_for_manytomany so they pass using=self.using; the official page gives the full listing, and the admin pages of this section show the surrounding site.

Tablespaces

A tablespace is a named storage location used by PostgreSQL and Oracle to organise disk layout (fast disk for hot indexes, a big disk for archive tables). SQLite, MariaDB and MySQL do not support them, and on such backends Django ignores every tablespace option. Django does not create tablespaces: you create them in the database first.

Meta.db_tablespace sets the tablespace of a model’s table, including the tables Django creates for its ManyToManyField`s; `DEFAULT_TABLESPACE is the default, useful for built-in and third-party apps whose code you do not control.
db_tablespace on an Index, or on a field with an index, sets the index tablespace; DEFAULT_INDEX_TABLESPACE is the default. When neither is set, the index lives in the table’s tablespace.

class TablespaceExample(models.Model):
    name = models.CharField(max_length=30, db_index=True, db_tablespace="indexes")
    shortcut = models.CharField(max_length=7)

    class Meta:
        db_tablespace = "tables"
        indexes = [models.Index(fields=["shortcut"], db_tablespace="other_indexes")]

Source: Django: Tablespaces

On PostgreSQL, sqlmigrate would add TABLESPACE "tables" to the CREATE TABLE and TABLESPACE "indexes" and "other_indexes" to the two indexes; the scratch run could not show that, because sqlmigrate needs a live PostgreSQL connection and none was available. On SQLite, manage.py check passed and sqlmigrate bookshelf 0001 printed the same statements as without the options (CREATE TABLE "bookshelf_tablespaceexample" (…) and plain CREATE INDEX statements, with no tablespace), which confirms the documented "ignored on unsupported backends" behaviour. Indexes themselves are the subject of Constraints, Indexes & Composite Keys.

Legacy databases with inspectdb

Django can introspect an existing schema and print the models for it. The process in the how-to is: put the connection parameters in DATABASES, run inspectdb, clean the models up, add the app to INSTALLED_APPS, run migrate to create the core Django tables (auth, content types, sessions), then test and tweak. inspectdb is "a shortcut, not a definitive model generation".

Source: Django: How to integrate Django with a legacy database

The scratch legacy database was created with plain SQL: two tables with upper-case names (one with a column called for, a Python reserved word, and a foreign key) and a view.

python manage.py inspectdb --database=legacy

Source: Django: inspectdb

The output (header comments trimmed) was:

from django.db import models


class CensusAddresses(models.Model):
    person = models.ForeignKey('CensusPersons', models.DO_NOTHING)
    street = models.CharField()

    class Meta:
        managed = False
        db_table = 'CENSUS_ADDRESSES'


class CensusPersons(models.Model):
    first_name = models.CharField()
    for_field = models.TextField(db_column='for', blank=True, null=True)  # Field renamed because it was a Python reserved word.
    birth_date = models.DateField(blank=True, null=True)
    weird = models.BinaryField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'CENSUS_PERSONS'

Source: Django: inspectdb output notes

Read it with the documented limitations in mind:

Models are managed = False: Django will not create, alter or drop those tables. Remove the line (or set True) only if Django should take over the table’s lifecycle.
Column types that cannot be mapped become TextField with a "This field type is a guess." comment, and a column named like a Python keyword gets the suffix _field plus db_column (the for_field above).
Database defaults are not introspected (the table declared DEFAULT 'n/a' on street; it is absent), and on_delete for a foreign key is DO_NOTHING; max_length was not recovered here because SQLite does not keep it, so supply sensible values.
Models that reference each other may be emitted in an order that does not import (a string reference is used when necessary); reorder them.
Pass table names to inspect only those tables. Views are skipped unless you pass --include-views (that run emitted managed = False # Created from a view. Don’t remove. and a model without a primary key); partition tables need --include-partitions (PostgreSQL only); --database chooses the alias.
Every model needs one field with primary_key=True; the generated models relied on the implicit id column that the tables happened to have.

After the clean-up (max_length set, the foreign key changed to PROTECT, Person defined before Address) the models were put in an app called census, and manage.py check and a read and write through using("legacy") both worked:

from census.models import Person, Address

person = Person.objects.using("legacy").create(first_name="Ada", birth_date="1815-12-10")
Address.objects.using("legacy").create(person=person, street="1 Analytical St")
print(Address.objects.using("legacy").select_related("person").get().person.first_name)  # Ada

Source: Django: Test and tweak

Because the models are unmanaged, makemigrations creates a migration with managed: False options only; if you later choose to let Django manage a table, handle the first migration with care, as described in Migrations (faking the initial state against existing tables).

Pitfalls

Swallowing a database error inside an atomic block, then querying, raises TransactionManagementError: catch outside an inner atomic.
A rollback does not restore Python objects or caches: reset the attributes you changed, and use on_commit for cache and notification work.
Running side effects (email, task enqueue) inside the transaction body: use on_commit, and in tests use captureOnCommitCallbacks or TransactionTestCase.
ATOMIC_REQUESTS covers only the view: middleware, template response rendering and streaming bodies are outside, and it holds locks across slow calls; opt out with non_atomic_requests on the view itself.
atomic() does not work in async def: use a synchronous function with sync_to_async().
Building SQL with f-strings, % or quoted placeholders: pass params; remember to double literal % characters.
raw() without the primary key, and deferred fields causing hidden queries.
Cross-database foreign keys, and forgetting that connection is only the default database.
Changing allow_migrate() after migrations exist, or letting contenttypes and auth appear in several databases.
Treating a primary/replica router as a complete solution: replication lag and transactions are your problem.
Expecting inspectdb output to be final: review types, keys, defaults and on_delete.

Where the books are outdated

Older tutorials write checkout and order code with several save() calls and no transaction.atomic(), and enqueue background work inside the request before the data is committed; both patterns lose or duplicate data on failure. Wrap the writes in atomic and register the follow-up with on_commit (available since Django 1.9, with robust since 4.2).
The TestCase guidance for hooks changed: captureOnCommitCallbacks (3.2) replaced hand-rolled workarounds.
Code that predates atomic (commit_on_success, commit_manually) was removed long ago; atomic is the only transaction API to teach.
Examples that call transaction.savepoint() should use savepoint_create() on Django 6.1.
Raw SQL examples that build strings with % are injection holes; the current docs insist on params.

What changed recently

Table 1. Transaction, raw SQL and multi-database changes by version
Version	Change
3.2	`atomic(durable=True)` and `TestCase.captureOnCommitCallbacks()` are added.
4.2	`on_commit()` gains `robust=True`.
6.1	`transaction.savepoint_create()` is added and `transaction.savepoint()` is deprecated in its favour; `annotate()`, table and JOIN aliases are always quoted, so raw SQL that refers to a mixed-case alias (for example inside `RawSQL`) may need quoting too; `inspectdb` introspects `HStoreField` with psycopg 3.2+ and `django.contrib.postgres` installed, and backends can opt out with `DatabaseFeatures.supports_inspectdb`; `check` now supplies all databases when none is specified, so it may connect to each of them.

The 3.2 and 4.2 rows are from those release notes, the 6.1 row from the 6.1 release notes (minor features, deprecations, backwards-incompatible changes and system checks).

Transactions, Raw SQL & Multiple Databases

In a hurry: what goes where

The running example

Transactions

atomic: the single API

Nesting and savepoints

Catching exceptions: the pitfall

durable, savepoint=False and the rollback flag

Low-level savepoints (savepoint_create in 6.1)

Autocommit and the other low-level calls

ATOMIC_REQUESTS: one transaction per request

on_commit: do things after the data is safe

Testing on_commit hooks

Transactions under async

Raw SQL

Parameters, always

Manager.raw()

RawSQL fragments in ORM queries

Cursors

Multiple databases

Defining and migrating the databases

Database routers

Choosing a database by hand

Transactions and raw SQL per database

Raw cursors and several databases

Limitations and the contrib apps

Tablespaces

Legacy databases with inspectdb

Pitfalls

Where the books are outdated

What changed recently

References