In Django with several DATABASES aliases, how do you choose which database a QuerySet, a save() or a delete() runs against?
answer
- one alias per DATABASES key
- a method anywhere in the QuerySet chain
- a keyword on save and delete
- manual choice outranks the router
- manager-only methods need a bound copy
basics
~10 sCall using('alias') on a QuerySet, or pass using='alias' to Model.save() and Model.delete(). A manual choice overrides any router; without one, Django asks DATABASE_ROUTERS and falls back to the 'default' alias.
solid answer
~30 sEvery key in `DATABASES` is an alias, and `'default'` must exist. `Listing.objects.using('reporting')` returns a clone of the QuerySet bound to that alias, and it applies to everything the QuerySet sends, including `update()` or `delete()` called on it. For one instance, `obj.save(using='reporting')` and `obj.delete(using='reporting')` do the same. A manually chosen alias always wins over `DATABASE_ROUTERS`; with no manual choice and no router answer, Django uses the database the instance was loaded from, else `'default'`. Manager-only methods such as `create_user()` are not on the QuerySet, so bind the manager instead: `User.objects.db_manager('reporting').create_user(...)`.
code
python · 16 linesfrom django.contrib.auth import get_user_model
from shop.models import Listing
User = get_user_model()
# QuerySet: reads and writes go to 'reporting'
stale = Listing.objects.using('reporting').filter(active=False)
stale.update(archived=True)
# One instance
listing = Listing(title='Lamp')
listing.save(using='reporting')
listing.delete(using='reporting')
# Manager-only method on another alias
User.objects.db_manager('reporting').create_user('ops', password='s3cret-pass')go deeper
Recall the three entry points: using() on a QuerySet, the using keyword on save() and delete(), and that 'default' is where everything goes unless told otherwise.
Explain the precedence: manual alias, then the first router answer, then the instance's recorded database, then 'default'. Show why manager-only methods need db_manager().
Point out the traps you have met: using() carrying writes to a replica, pk collisions when copying rows between aliases, and custom managers that drop the bound alias in get_queryset().
Argue when explicit using() calls should be replaced by a router so the placement rule lives in one audited place, and when an explicit alias is the clearer contract.
## Aliases: what the DATABASES keys mean Every key in the `DATABASES` setting is an **alias**: a name Django uses to look up one connection in `django.db.connections`. The `'default'` alias is mandatory. If other aliases are defined and `'default'` is missing, Django raises `ImproperlyConfigured` with *You must define a 'default' database*; it may, however, be an empty dict when routers send every model elsewhere. Adding a second alias changes nothing by itself. Until your code or a router asks for it, every query still runs on `'default'`. ```python DATABASES = { 'default': {'ENGINE': 'django.db.backends.postgresql', 'NAME': 'shop'}, 'reporting': {'ENGINE': 'django.db.backends.postgresql', 'NAME': 'shop_reporting'}, } ``` ## Choosing the database for a QuerySet: using() `QuerySet.using(alias)` returns a **clone** of the QuerySet bound to that alias. It can appear anywhere in the chain, and because QuerySets are lazy nothing touches the alias until the QuerySet is evaluated. - The alias applies to **every** statement the QuerySet sends: the `SELECT` when it is iterated, and the `UPDATE` or `DELETE` of `.update()` or `.delete()` called on it. `using()` is not a read-only hint. - Each object loaded through it records where it came from in `instance._state.db`. Later related lookups and saves on that object use this as their fallback. - A QuerySet built without `using()` asks the router when it runs: `db_for_read` for reads and `db_for_write` for writes such as `update()`, `get_or_create()` or `select_for_update()`. ## Choosing it for one object: save(using=) and delete(using=) `Model.save(using='alias')` writes the instance to that alias, and `Model.delete(using='alias')` deletes it there. Without the keyword, both ask the routers' `db_for_write` with the instance as a hint; if no router answers, they fall back to `instance._state.db`, so an object loaded from `'reporting'` is saved back to `'reporting'`. Copying an object between databases is where this bites: 1. `p.save(using='first')` inserts the row and gives `p` a primary key. 2. `p.save(using='second')` keeps that primary key. If a row with the same key already exists on `'second'`, Django **overwrites** it instead of inserting a copy. 3. To copy safely, either set `p.pk = None` first (a new row with a new key) or pass `force_insert=True` (same key, and an error if it is taken). ## Manager-only methods: db_manager() Some methods live on the **manager**, not on the QuerySet: `UserManager.create_user()` is the classic one, and custom manager methods are others. `User.objects.using('reporting')` returns a QuerySet, which has no `create_user`, so the call raises `AttributeError`. `Manager.db_manager(alias)` returns a copy of the manager bound to the alias, so its methods run there: ```python User.objects.db_manager('reporting').create_user('ops', password='...') ``` If you override `get_queryset()` on a custom manager, call `super()` or pass the manager's `_db` on with `.using(self._db)`, otherwise `db_manager()` silently stops working for that manager. ## Who wins: the precedence order | Source of the alias | Example | Beats | |---|---|---| | **Manual choice** | `using()`, `save(using=)`, `delete(using=)`, `db_manager()` | routers and fallbacks | | **Router answer** | first router in `DATABASE_ROUTERS` returning an alias | the fallbacks | | **Instance hint** | `instance._state.db` of the object involved | `'default'` | | **Default** | `'default'` | nothing | The router layer is consulted only when no manual alias was given, which is why `using()` is the escape hatch when a router sends a query somewhere you do not want it. ## How the choice travels with objects The alias is not only a property of one query. Once an object is loaded, its `_state.db` goes with it: - Following a relation, such as `listing.seller` or `listing.photos.all()`, passes the listing to the routers as the `instance` hint. With no router answering, the related query runs on the listing's database. - Saving or deleting the object without `using` goes back to that database under the same rule. - A brand-new object that has never been saved has `_state.db` set to `None`, so it follows the routers or `'default'`. To see where an unevaluated QuerySet will go, read its `db` property: `Listing.objects.using('reporting').db` returns `'reporting'`, and without `using()` it returns the router's read answer. It is a cheap way to check routing in a shell or a unit test without sending SQL. ## Practical notes - For raw SQL on a specific alias, take the connection by name: `connections['reporting'].cursor()`. - A QuerySet bound with `using('replica')` and then `.update()`d sends the `UPDATE` to the replica; on a read-only replica that fails at the database, not in Django. - Manual `using()` calls scattered through views are hard to audit. When the rule is really 'this app's models live on that database', put it in a router and keep `using()` for the exceptions.
- What happens when an instance saved to one alias is saved again with save(using=) to another alias?The instance keeps its primary key, so Django tries to write that key on the second database. If no row has it, the object is copied; if one does, that existing row is overwritten. To copy safely, clear `pk` before saving to get a fresh row, or pass `force_insert=True` to keep the key and get an error when it is already taken.
- Why does User.objects.using('reporting').create_user(...) raise AttributeError?`using()` returns a QuerySet, and `create_user()` is defined on `UserManager`, not on the QuerySet class. `User.objects.db_manager('reporting')` returns a copy of the manager with its database set, so `create_user()` and any other manager method run on that alias.
saying these in an interview costs you the question
- Adding a second DATABASES alias makes Django split reads and writes automatically.
- using() only affects reads; update() on that QuerySet still writes to default.
- A router's answer overrides an explicit using() call.
- save(using=...) on an existing instance always inserts a new copy.
- The 'default' key can be left out of DATABASES once other aliases exist.