| 1 |
================== |
| 2 |
Multiple databases |
| 3 |
================== |
| 4 |
|
| 5 |
This topic guide describes Django's support for interacting with |
| 6 |
multiple databases. Most of the rest of Django's documentation assumes |
| 7 |
you are interacting with a single database. If you want to interact |
| 8 |
with multiple databases, you'll need to take some additional steps. |
| 9 |
|
| 10 |
.. seealso:: |
| 11 |
|
| 12 |
See :ref:`testing-multi-db` for information about testing with multiple |
| 13 |
databases. |
| 14 |
|
| 15 |
Defining your databases |
| 16 |
======================= |
| 17 |
|
| 18 |
The first step to using more than one database with Django is to tell |
| 19 |
Django about the database servers you'll be using. This is done using |
| 20 |
the :setting:`DATABASES` setting. This setting maps database aliases, |
| 21 |
which are a way to refer to a specific database throughout Django, to |
| 22 |
a dictionary of settings for that specific connection. The settings in |
| 23 |
the inner dictionaries are described fully in the :setting:`DATABASES` |
| 24 |
documentation. |
| 25 |
|
| 26 |
Databases can have any alias you choose. However, the alias |
| 27 |
``default`` has special significance. Django uses the database with |
| 28 |
the alias of ``default`` when no other database has been selected. |
| 29 |
|
| 30 |
The following is an example ``settings.py`` snippet defining two |
| 31 |
databases -- a default PostgreSQL database and a MySQL database called |
| 32 |
``users``:: |
| 33 |
|
| 34 |
DATABASES = { |
| 35 |
"default": { |
| 36 |
"NAME": "app_data", |
| 37 |
"ENGINE": "django.db.backends.postgresql", |
| 38 |
"USER": "postgres_user", |
| 39 |
"PASSWORD": "s3krit", |
| 40 |
}, |
| 41 |
"users": { |
| 42 |
"NAME": "user_data", |
| 43 |
"ENGINE": "django.db.backends.mysql", |
| 44 |
"USER": "mysql_user", |
| 45 |
"PASSWORD": "priv4te", |
| 46 |
}, |
| 47 |
} |
| 48 |
|
| 49 |
If the concept of a ``default`` database doesn't make sense in the context |
| 50 |
of your project, you need to be careful to always specify the database |
| 51 |
that you want to use. Django requires that a ``default`` database entry |
| 52 |
be defined, but the parameters dictionary can be left blank if it will not be |
| 53 |
used. To do this, you must set up :setting:`DATABASE_ROUTERS` for all of your |
| 54 |
apps' models, including those in any contrib and third-party apps you're using, |
| 55 |
so that no queries are routed to the default database. The following is an |
| 56 |
example ``settings.py`` snippet defining two non-default databases, with the |
| 57 |
``default`` entry intentionally left empty:: |
| 58 |
|
| 59 |
DATABASES = { |
| 60 |
"default": {}, |
| 61 |
"users": { |
| 62 |
"NAME": "user_data", |
| 63 |
"ENGINE": "django.db.backends.mysql", |
| 64 |
"USER": "mysql_user", |
| 65 |
"PASSWORD": "superS3cret", |
| 66 |
}, |
| 67 |
"customers": { |
| 68 |
"NAME": "customer_data", |
| 69 |
"ENGINE": "django.db.backends.mysql", |
| 70 |
"USER": "mysql_cust", |
| 71 |
"PASSWORD": "veryPriv@ate", |
| 72 |
}, |
| 73 |
} |
| 74 |
|
| 75 |
If you attempt to access a database that you haven't defined in your |
| 76 |
:setting:`DATABASES` setting, Django will raise a |
| 77 |
``django.utils.connection.ConnectionDoesNotExist`` exception. |
| 78 |
|
| 79 |
.. _synchronizing_multiple_databases: |
| 80 |
|
| 81 |
Synchronizing your databases |
| 82 |
============================ |
| 83 |
|
| 84 |
The :djadmin:`migrate` management command operates on one database at a |
| 85 |
time. By default, it operates on the ``default`` database, but by |
| 86 |
providing the :option:`--database <migrate --database>` option, you can tell it |
| 87 |
to synchronize a different database. So, to synchronize all models onto |
| 88 |
all databases in the first example above, you would need to call: |
| 89 |
|
| 90 |
.. code-block:: shell |
| 91 |
|
| 92 |
$ ./manage.py migrate |
| 93 |
$ ./manage.py migrate --database=users |
| 94 |
|
| 95 |
If you don't want every application to be synchronized onto a |
| 96 |
particular database, you can define a :ref:`database |
| 97 |
router<topics-db-multi-db-routing>` that implements a policy |
| 98 |
constraining the availability of particular models. |
| 99 |
|
| 100 |
If, as in the second example above, you've left the ``default`` database empty, |
| 101 |
you must provide a database name each time you run :djadmin:`migrate`. Omitting |
| 102 |
the database name would raise an error. For the second example: |
| 103 |
|
| 104 |
.. code-block:: shell |
| 105 |
|
| 106 |
$ ./manage.py migrate --database=users |
| 107 |
$ ./manage.py migrate --database=customers |
| 108 |
|
| 109 |
Using other management commands |
| 110 |
------------------------------- |
| 111 |
|
| 112 |
Most other ``django-admin`` commands that interact with the database operate in |
| 113 |
the same way as :djadmin:`migrate` -- they only ever operate on one database at |
| 114 |
a time, using ``--database`` to control the database used. |
| 115 |
|
| 116 |
An exception to this rule is the :djadmin:`makemigrations` command. It |
| 117 |
validates the migration history in the databases to catch problems with the |
| 118 |
existing migration files (which could be caused by editing them) before |
| 119 |
creating new migrations. By default, it checks only the ``default`` database, |
| 120 |
but it consults the :meth:`allow_migrate` method of :ref:`routers |
| 121 |
<topics-db-multi-db-routing>` if any are installed. |
| 122 |
|
| 123 |
.. _topics-db-multi-db-routing: |
| 124 |
|
| 125 |
Automatic database routing |
| 126 |
========================== |
| 127 |
|
| 128 |
The easiest way to use multiple databases is to set up a database |
| 129 |
routing scheme. The default routing scheme ensures that objects remain |
| 130 |
'sticky' to their original database (i.e., an object retrieved from |
| 131 |
the ``foo`` database will be saved on the same database). The default |
| 132 |
routing scheme ensures that if a database isn't specified, all queries |
| 133 |
fall back to the ``default`` database. |
| 134 |
|
| 135 |
You don't have to do anything to activate the default routing scheme |
| 136 |
-- it is provided 'out of the box' on every Django project. However, |
| 137 |
if you want to implement more interesting database allocation |
| 138 |
behaviors, you can define and install your own database routers. |
| 139 |
|
| 140 |
Database routers |
| 141 |
---------------- |
| 142 |
|
| 143 |
A database Router is a class that provides up to four methods: |
| 144 |
|
| 145 |
.. method:: db_for_read(model, **hints) |
| 146 |
|
| 147 |
Suggest the database that should be used for read operations for |
| 148 |
objects of type ``model``. |
| 149 |
|
| 150 |
If a database operation is able to provide any additional |
| 151 |
information that might assist in selecting a database, it will be |
| 152 |
provided in the ``hints`` dictionary. Details on valid hints are |
| 153 |
provided :ref:`below <topics-db-multi-db-hints>`. |
| 154 |
|
| 155 |
Returns ``None`` if there is no suggestion. |
| 156 |
|
| 157 |
.. method:: db_for_write(model, **hints) |
| 158 |
|
| 159 |
Suggest the database that should be used for writes of objects of |
| 160 |
type Model. |
| 161 |
|
| 162 |
If a database operation is able to provide any additional |
| 163 |
information that might assist in selecting a database, it will be |
| 164 |
provided in the ``hints`` dictionary. Details on valid hints are |
| 165 |
provided :ref:`below <topics-db-multi-db-hints>`. |
| 166 |
|
| 167 |
Returns ``None`` if there is no suggestion. |
| 168 |
|
| 169 |
.. method:: allow_relation(obj1, obj2, **hints) |
| 170 |
|
| 171 |
Return ``True`` if a relation between ``obj1`` and ``obj2`` should be |
| 172 |
allowed, ``False`` if the relation should be prevented, or ``None`` if |
| 173 |
the router has no opinion. This is purely a validation operation, |
| 174 |
used by foreign key and many to many operations to determine if a |
| 175 |
relation should be allowed between two objects. |
| 176 |
|
| 177 |
If no router has an opinion (i.e. all routers return ``None``), only |
| 178 |
relations within the same database are allowed. |
| 179 |
|
| 180 |
.. method:: allow_migrate(db, app_label, model_name=None, **hints) |
| 181 |
|
| 182 |
Determine if the migration operation is allowed to run on the database with |
| 183 |
alias ``db``. Return ``True`` if the operation should run, ``False`` if it |
| 184 |
shouldn't run, or ``None`` if the router has no opinion. |
| 185 |
|
| 186 |
The ``app_label`` positional argument is the label of the application |
| 187 |
being migrated. |
| 188 |
|
| 189 |
``model_name`` is set by most migration operations to the value of |
| 190 |
``model._meta.model_name`` (the lowercased version of the model |
| 191 |
``__name__``) of the model being migrated. Its value is ``None`` for the |
| 192 |
:class:`~django.db.migrations.operations.RunPython` and |
| 193 |
:class:`~django.db.migrations.operations.RunSQL` operations unless they |
| 194 |
provide it using hints. |
| 195 |
|
| 196 |
``hints`` are used by certain operations to communicate additional |
| 197 |
information to the router. |
| 198 |
|
| 199 |
When ``model_name`` is set, ``hints`` normally contains the model class |
| 200 |
under the key ``'model'``. Note that it may be a :ref:`historical model |
| 201 |
<historical-models>`, and thus not have any custom attributes, methods, or |
| 202 |
managers. You should only rely on ``_meta``. |
| 203 |
|
| 204 |
This method can also be used to determine the availability of a model on a |
| 205 |
given database. |
| 206 |
|
| 207 |
:djadmin:`makemigrations` always creates migrations for model changes, but |
| 208 |
if ``allow_migrate()`` returns ``False``, any migration operations for the |
| 209 |
``model_name`` will be silently skipped when running :djadmin:`migrate` on |
| 210 |
the ``db``. Changing the behavior of ``allow_migrate()`` for models that |
| 211 |
already have migrations may result in broken foreign keys, extra tables, |
| 212 |
or missing tables. When :djadmin:`makemigrations` verifies the migration |
| 213 |
history, it skips databases where no app is allowed to migrate. |
| 214 |
|
| 215 |
A router doesn't have to provide *all* these methods -- it may omit one |
| 216 |
or more of them. If one of the methods is omitted, Django will skip |
| 217 |
that router when performing the relevant check. |
| 218 |
|
| 219 |
.. _topics-db-multi-db-hints: |
| 220 |
|
| 221 |
Hints |
| 222 |
~~~~~ |
| 223 |
|
| 224 |
The hints received by the database router can be used to decide which |
| 225 |
database should receive a given request. |
| 226 |
|
| 227 |
At present, the only hint that will be provided is ``instance``, an |
| 228 |
object instance that is related to the read or write operation that is |
| 229 |
underway. This might be the instance that is being saved, or it might |
| 230 |
be an instance that is being added in a many-to-many relation. In some |
| 231 |
cases, no instance hint will be provided at all. The router checks for |
| 232 |
the existence of an instance hint, and determine if that hint should be |
| 233 |
used to alter routing behavior. |
| 234 |
|
| 235 |
Using routers |
| 236 |
------------- |
| 237 |
|
| 238 |
Database routers are installed using the :setting:`DATABASE_ROUTERS` |
| 239 |
setting. This setting defines a list of class names, each specifying a |
| 240 |
router that should be used by the base router |
| 241 |
(``django.db.router``). |
| 242 |
|
| 243 |
The base router is used by Django's database operations to allocate |
| 244 |
database usage. Whenever a query needs to know which database to use, |
| 245 |
it calls the base router, providing a model and a hint (if |
| 246 |
available). The base router tries each router class in turn until one returns |
| 247 |
a database suggestion. If no routers return a suggestion, the base router tries |
| 248 |
the current :attr:`instance._state.db |
| 249 |
<django.db.models.Model._state>` of the hint instance. If no hint instance |
| 250 |
was provided, or :attr:`instance._state.db <django.db.models.Model._state>` is |
| 251 |
``None``, the base router will allocate the ``default`` database. |
| 252 |
|
| 253 |
An example |
| 254 |
---------- |
| 255 |
|
| 256 |
.. admonition:: Example purposes only! |
| 257 |
|
| 258 |
This example is intended as a demonstration of how the router |
| 259 |
infrastructure can be used to alter database usage. It |
| 260 |
intentionally ignores some complex issues in order to |
| 261 |
demonstrate how routers are used. |
| 262 |
|
| 263 |
This example won't work if any of the models in ``myapp`` contain |
| 264 |
relationships to models outside of the ``other`` database. |
| 265 |
:ref:`Cross-database relationships <no_cross_database_relations>` |
| 266 |
introduce referential integrity problems that Django can't |
| 267 |
currently handle. |
| 268 |
|
| 269 |
The primary/replica (referred to as master/slave by some databases) |
| 270 |
configuration described is also flawed -- it |
| 271 |
doesn't provide any solution for handling replication lag (i.e., |
| 272 |
query inconsistencies introduced because of the time taken for a |
| 273 |
write to propagate to the replicas). It also doesn't consider the |
| 274 |
interaction of transactions with the database utilization strategy. |
| 275 |
|
| 276 |
So - what does this mean in practice? Let's consider another sample |
| 277 |
configuration. This one will have several databases: one for the |
| 278 |
``auth`` application, and all other apps using a primary/replica setup |
| 279 |
with two read replicas. Here are the settings specifying these |
| 280 |
databases:: |
| 281 |
|
| 282 |
DATABASES = { |
| 283 |
"default": {}, |
| 284 |
"auth_db": { |
| 285 |
"NAME": "auth_db_name", |
| 286 |
"ENGINE": "django.db.backends.mysql", |
| 287 |
"USER": "mysql_user", |
| 288 |
"PASSWORD": "swordfish", |
| 289 |
}, |
| 290 |
"primary": { |
| 291 |
"NAME": "primary_name", |
| 292 |
"ENGINE": "django.db.backends.mysql", |
| 293 |
"USER": "mysql_user", |
| 294 |
"PASSWORD": "spam", |
| 295 |
}, |
| 296 |
"replica1": { |
| 297 |
"NAME": "replica1_name", |
| 298 |
"ENGINE": "django.db.backends.mysql", |
| 299 |
"USER": "mysql_user", |
| 300 |
"PASSWORD": "eggs", |
| 301 |
}, |
| 302 |
"replica2": { |
| 303 |
"NAME": "replica2_name", |
| 304 |
"ENGINE": "django.db.backends.mysql", |
| 305 |
"USER": "mysql_user", |
| 306 |
"PASSWORD": "bacon", |
| 307 |
}, |
| 308 |
} |
| 309 |
|
| 310 |
Now we'll need to handle routing. First we want a router that knows to |
| 311 |
send queries for the ``auth`` and ``contenttypes`` apps to ``auth_db`` |
| 312 |
(``auth`` models are linked to ``ContentType``, so they must be stored in the |
| 313 |
same database):: |
| 314 |
|
| 315 |
class AuthRouter: |
| 316 |
""" |
| 317 |
A router to control all database operations on models in the |
| 318 |
auth and contenttypes applications. |
| 319 |
""" |
| 320 |
|
| 321 |
route_app_labels = {"auth", "contenttypes"} |
| 322 |
|
| 323 |
def db_for_read(self, model, **hints): |
| 324 |
""" |
| 325 |
Attempts to read auth and contenttypes models go to auth_db. |
| 326 |
""" |
| 327 |
if model._meta.app_label in self.route_app_labels: |
| 328 |
return "auth_db" |
| 329 |
return None |
| 330 |
|
| 331 |
def db_for_write(self, model, **hints): |
| 332 |
""" |
| 333 |
Attempts to write auth and contenttypes models go to auth_db. |
| 334 |
""" |
| 335 |
if model._meta.app_label in self.route_app_labels: |
| 336 |
return "auth_db" |
| 337 |
return None |
| 338 |
|
| 339 |
def allow_relation(self, obj1, obj2, **hints): |
| 340 |
""" |
| 341 |
Allow relations if a model in the auth or contenttypes apps is |
| 342 |
involved. |
| 343 |
""" |
| 344 |
if ( |
| 345 |
obj1._meta.app_label in self.route_app_labels |
| 346 |
or obj2._meta.app_label in self.route_app_labels |
| 347 |
): |
| 348 |
return True |
| 349 |
return None |
| 350 |
|
| 351 |
def allow_migrate(self, db, app_label, model_name=None, **hints): |
| 352 |
""" |
| 353 |
Make sure the auth and contenttypes apps only appear in the |
| 354 |
'auth_db' database. |
| 355 |
""" |
| 356 |
if app_label in self.route_app_labels: |
| 357 |
return db == "auth_db" |
| 358 |
return None |
| 359 |
|
| 360 |
And we also want a router that sends all other apps to the |
| 361 |
primary/replica configuration, and randomly chooses a replica to read |
| 362 |
from:: |
| 363 |
|
| 364 |
import random |
| 365 |
|
| 366 |
|
| 367 |
class PrimaryReplicaRouter: |
| 368 |
def db_for_read(self, model, **hints): |
| 369 |
""" |
| 370 |
Reads go to a randomly-chosen replica. |
| 371 |
""" |
| 372 |
return random.choice(["replica1", "replica2"]) |
| 373 |
|
| 374 |
def db_for_write(self, model, **hints): |
| 375 |
""" |
| 376 |
Writes always go to primary. |
| 377 |
""" |
| 378 |
return "primary" |
| 379 |
|
| 380 |
def allow_relation(self, obj1, obj2, **hints): |
| 381 |
""" |
| 382 |
Relations between objects are allowed if both objects are |
| 383 |
in the primary/replica pool. |
| 384 |
""" |
| 385 |
db_set = {"primary", "replica1", "replica2"} |
| 386 |
if obj1._state.db in db_set and obj2._state.db in db_set: |
| 387 |
return True |
| 388 |
return None |
| 389 |
|
| 390 |
def allow_migrate(self, db, app_label, model_name=None, **hints): |
| 391 |
""" |
| 392 |
All non-auth models end up in this pool. |
| 393 |
""" |
| 394 |
return True |
| 395 |
|
| 396 |
Finally, in the settings file, we add the following (substituting |
| 397 |
``path.to.`` with the actual Python path to the module(s) where the |
| 398 |
routers are defined):: |
| 399 |
|
| 400 |
DATABASE_ROUTERS = ["path.to.AuthRouter", "path.to.PrimaryReplicaRouter"] |
| 401 |
|
| 402 |
The order in which routers are processed is significant. Routers will |
| 403 |
be queried in the order they are listed in the |
| 404 |
:setting:`DATABASE_ROUTERS` setting. In this example, the |
| 405 |
``AuthRouter`` is processed before the ``PrimaryReplicaRouter``, and as a |
| 406 |
result, decisions concerning the models in ``auth`` are processed |
| 407 |
before any other decision is made. If the :setting:`DATABASE_ROUTERS` |
| 408 |
setting listed the two routers in the other order, |
| 409 |
``PrimaryReplicaRouter.allow_migrate()`` would be processed first. The |
| 410 |
catch-all nature of the PrimaryReplicaRouter implementation would mean |
| 411 |
that all models would be available on all databases. |
| 412 |
|
| 413 |
With this setup installed, and all databases migrated as per |
| 414 |
:ref:`synchronizing_multiple_databases`, lets run some Django code: |
| 415 |
|
| 416 |
.. code-block:: pycon |
| 417 |
|
| 418 |
>>> # This retrieval will be performed on the 'auth_db' database |
| 419 |
>>> fred = User.objects.get(username="fred") |
| 420 |
>>> fred.first_name = "Frederick" |
| 421 |
|
| 422 |
>>> # This save will also be directed to 'auth_db' |
| 423 |
>>> fred.save() |
| 424 |
|
| 425 |
>>> # These retrieval will be randomly allocated to a replica database |
| 426 |
>>> dna = Person.objects.get(name="Douglas Adams") |
| 427 |
|
| 428 |
>>> # A new object has no database allocation when created |
| 429 |
>>> mh = Book(title="Mostly Harmless") |
| 430 |
|
| 431 |
>>> # This assignment will consult the router, and set mh onto |
| 432 |
>>> # the same database as the author object |
| 433 |
>>> mh.author = dna |
| 434 |
|
| 435 |
>>> # This save will force the 'mh' instance onto the primary database... |
| 436 |
>>> mh.save() |
| 437 |
|
| 438 |
>>> # ... but if we re-retrieve the object, it will come back on a replica |
| 439 |
>>> mh = Book.objects.get(title="Mostly Harmless") |
| 440 |
|
| 441 |
This example defined a router to handle interaction with models from the |
| 442 |
``auth`` app, and other routers to handle interaction with all other apps. If |
| 443 |
you left your ``default`` database empty and don't want to define a catch-all |
| 444 |
database router to handle all apps not otherwise specified, your routers must |
| 445 |
handle the names of all apps in :setting:`INSTALLED_APPS` before you migrate. |
| 446 |
See :ref:`contrib_app_multiple_databases` for information about contrib apps |
| 447 |
that must be together in one database. |
| 448 |
|
| 449 |
Manually selecting a database |
| 450 |
============================= |
| 451 |
|
| 452 |
Django also provides an API that allows you to maintain complete control |
| 453 |
over database usage in your code. A manually specified database allocation |
| 454 |
will take priority over a database allocated by a router. |
| 455 |
|
| 456 |
Manually selecting a database for a ``QuerySet`` |
| 457 |
------------------------------------------------ |
| 458 |
|
| 459 |
You can select the database for a ``QuerySet`` at any point in the |
| 460 |
``QuerySet`` "chain." Call ``using()`` on the ``QuerySet`` to get another |
| 461 |
``QuerySet`` that uses the specified database. |
| 462 |
|
| 463 |
``using()`` takes a single argument: the alias of the database on |
| 464 |
which you want to run the query. For example: |
| 465 |
|
| 466 |
.. code-block:: pycon |
| 467 |
|
| 468 |
>>> # This will run on the 'default' database. |
| 469 |
>>> Author.objects.all() |
| 470 |
|
| 471 |
>>> # So will this. |
| 472 |
>>> Author.objects.using("default") |
| 473 |
|
| 474 |
>>> # This will run on the 'other' database. |
| 475 |
>>> Author.objects.using("other") |
| 476 |
|
| 477 |
Selecting a database for ``save()`` |
| 478 |
----------------------------------- |
| 479 |
|
| 480 |
Use the ``using`` keyword to ``Model.save()`` to specify to which |
| 481 |
database the data should be saved. |
| 482 |
|
| 483 |
For example, to save an object to the ``legacy_users`` database, you'd |
| 484 |
use this: |
| 485 |
|
| 486 |
.. code-block:: pycon |
| 487 |
|
| 488 |
>>> my_object.save(using="legacy_users") |
| 489 |
|
| 490 |
If you don't specify ``using``, the ``save()`` method will save into |
| 491 |
the default database allocated by the routers. |
| 492 |
|
| 493 |
Moving an object from one database to another |
| 494 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| 495 |
|
| 496 |
If you've saved an instance to one database, it might be tempting to |
| 497 |
use ``save(using=...)`` as a way to migrate the instance to a new |
| 498 |
database. However, if you don't take appropriate steps, this could |
| 499 |
have some unexpected consequences. |
| 500 |
|
| 501 |
Consider the following example: |
| 502 |
|
| 503 |
.. code-block:: pycon |
| 504 |
|
| 505 |
>>> p = Person(name="Fred") |
| 506 |
>>> p.save(using="first") # (statement 1) |
| 507 |
>>> p.save(using="second") # (statement 2) |
| 508 |
|
| 509 |
In statement 1, a new ``Person`` object is saved to the ``first`` |
| 510 |
database. At this time, ``p`` doesn't have a primary key, so Django |
| 511 |
issues an SQL ``INSERT`` statement. This creates a primary key, and |
| 512 |
Django assigns that primary key to ``p``. |
| 513 |
|
| 514 |
When the save occurs in statement 2, ``p`` already has a primary key |
| 515 |
value, and Django will attempt to use that primary key on the new |
| 516 |
database. If the primary key value isn't in use in the ``second`` |
| 517 |
database, then you won't have any problems -- the object will be |
| 518 |
copied to the new database. |
| 519 |
|
| 520 |
However, if the primary key of ``p`` is already in use on the |
| 521 |
``second`` database, the existing object in the ``second`` database |
| 522 |
will be overridden when ``p`` is saved. |
| 523 |
|
| 524 |
You can avoid this in two ways. First, you can clear the primary key |
| 525 |
of the instance. If an object has no primary key, Django will treat it |
| 526 |
as a new object, avoiding any loss of data on the ``second`` |
| 527 |
database: |
| 528 |
|
| 529 |
.. code-block:: pycon |
| 530 |
|
| 531 |
>>> p = Person(name="Fred") |
| 532 |
>>> p.save(using="first") |
| 533 |
>>> p.pk = None # Clear the primary key. |
| 534 |
>>> p.save(using="second") # Write a completely new object. |
| 535 |
|
| 536 |
The second option is to use the ``force_insert`` option to ``save()`` |
| 537 |
to ensure that Django does an SQL ``INSERT``: |
| 538 |
|
| 539 |
.. code-block:: pycon |
| 540 |
|
| 541 |
>>> p = Person(name="Fred") |
| 542 |
>>> p.save(using="first") |
| 543 |
>>> p.save(using="second", force_insert=True) |
| 544 |
|
| 545 |
This will ensure that the person named ``Fred`` will have the same |
| 546 |
primary key on both databases. If that primary key is already in use |
| 547 |
when you try to save onto the ``second`` database, an error will be |
| 548 |
raised. |
| 549 |
|
| 550 |
Selecting a database to delete from |
| 551 |
----------------------------------- |
| 552 |
|
| 553 |
By default, a call to delete an existing object will be executed on |
| 554 |
the same database that was used to retrieve the object in the first |
| 555 |
place: |
| 556 |
|
| 557 |
.. code-block:: pycon |
| 558 |
|
| 559 |
>>> u = User.objects.using("legacy_users").get(username="fred") |
| 560 |
>>> u.delete() # will delete from the `legacy_users` database |
| 561 |
|
| 562 |
To specify the database from which a model will be deleted, pass a |
| 563 |
``using`` keyword argument to the ``Model.delete()`` method. This |
| 564 |
argument works just like the ``using`` keyword argument to ``save()``. |
| 565 |
|
| 566 |
For example, if you're migrating a user from the ``legacy_users`` |
| 567 |
database to the ``new_users`` database, you might use these commands: |
| 568 |
|
| 569 |
.. code-block:: pycon |
| 570 |
|
| 571 |
>>> user_obj.save(using="new_users") |
| 572 |
>>> user_obj.delete(using="legacy_users") |
| 573 |
|
| 574 |
Using managers with multiple databases |
| 575 |
-------------------------------------- |
| 576 |
|
| 577 |
Use the ``db_manager()`` method on managers to give managers access to |
| 578 |
a non-default database. |
| 579 |
|
| 580 |
For example, say you have a custom manager method that touches the |
| 581 |
database -- ``User.objects.create_user()``. Because ``create_user()`` |
| 582 |
is a manager method, not a ``QuerySet`` method, you can't do |
| 583 |
``User.objects.using('new_users').create_user()``. (The |
| 584 |
``create_user()`` method is only available on ``User.objects``, the |
| 585 |
manager, not on ``QuerySet`` objects derived from the manager.) The |
| 586 |
solution is to use ``db_manager()``, like this:: |
| 587 |
|
| 588 |
User.objects.db_manager("new_users").create_user(...) |
| 589 |
|
| 590 |
``db_manager()`` returns a copy of the manager bound to the database you specify. |
| 591 |
|
| 592 |
Using ``get_queryset()`` with multiple databases |
| 593 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| 594 |
|
| 595 |
If you're overriding ``get_queryset()`` on your manager, be sure to |
| 596 |
either call the method on the parent (using ``super()``) or do the |
| 597 |
appropriate handling of the ``_db`` attribute on the manager (a string |
| 598 |
containing the name of the database to use). |
| 599 |
|
| 600 |
For example, if you want to return a custom ``QuerySet`` class from |
| 601 |
the ``get_queryset`` method, you could do this:: |
| 602 |
|
| 603 |
class MyManager(models.Manager): |
| 604 |
def get_queryset(self): |
| 605 |
qs = CustomQuerySet(self.model) |
| 606 |
if self._db is not None: |
| 607 |
qs = qs.using(self._db) |
| 608 |
return qs |
| 609 |
|
| 610 |
Exposing multiple databases in Django's admin interface |
| 611 |
======================================================= |
| 612 |
|
| 613 |
Django's admin doesn't have any explicit support for multiple |
| 614 |
databases. If you want to provide an admin interface for a model on a |
| 615 |
database other than that specified by your router chain, you'll |
| 616 |
need to write custom :class:`~django.contrib.admin.ModelAdmin` classes |
| 617 |
that will direct the admin to use a specific database for content. |
| 618 |
|
| 619 |
``ModelAdmin`` objects have the following methods that require customization |
| 620 |
for multiple-database support:: |
| 621 |
|
| 622 |
class MultiDBModelAdmin(admin.ModelAdmin): |
| 623 |
# A handy constant for the name of the alternate database. |
| 624 |
using = "other" |
| 625 |
|
| 626 |
def save_model(self, request, obj, form, change): |
| 627 |
# Tell Django to save objects to the 'other' database. |
| 628 |
obj.save(using=self.using) |
| 629 |
|
| 630 |
def delete_model(self, request, obj): |
| 631 |
# Tell Django to delete objects from the 'other' database |
| 632 |
obj.delete(using=self.using) |
| 633 |
|
| 634 |
def get_queryset(self, request): |
| 635 |
# Tell Django to look for objects on the 'other' database. |
| 636 |
return super().get_queryset(request).using(self.using) |
| 637 |
|
| 638 |
def formfield_for_foreignkey(self, db_field, request, **kwargs): |
| 639 |
# Tell Django to populate ForeignKey widgets using a query |
| 640 |
# on the 'other' database. |
| 641 |
return super().formfield_for_foreignkey( |
| 642 |
db_field, request, using=self.using, **kwargs |
| 643 |
) |
| 644 |
|
| 645 |
def formfield_for_manytomany(self, db_field, request, **kwargs): |
| 646 |
# Tell Django to populate ManyToMany widgets using a query |
| 647 |
# on the 'other' database. |
| 648 |
return super().formfield_for_manytomany( |
| 649 |
db_field, request, using=self.using, **kwargs |
| 650 |
) |
| 651 |
|
| 652 |
The implementation provided here implements a multi-database strategy |
| 653 |
where all objects of a given type are stored on a specific database |
| 654 |
(e.g., all ``User`` objects are in the ``other`` database). If your |
| 655 |
usage of multiple databases is more complex, your ``ModelAdmin`` will |
| 656 |
need to reflect that strategy. |
| 657 |
|
| 658 |
:class:`~django.contrib.admin.InlineModelAdmin` objects can be handled in a |
| 659 |
similar fashion. They require three customized methods:: |
| 660 |
|
| 661 |
class MultiDBTabularInline(admin.TabularInline): |
| 662 |
using = "other" |
| 663 |
|
| 664 |
def get_queryset(self, request): |
| 665 |
# Tell Django to look for inline objects on the 'other' database. |
| 666 |
return super().get_queryset(request).using(self.using) |
| 667 |
|
| 668 |
def formfield_for_foreignkey(self, db_field, request, **kwargs): |
| 669 |
# Tell Django to populate ForeignKey widgets using a query |
| 670 |
# on the 'other' database. |
| 671 |
return super().formfield_for_foreignkey( |
| 672 |
db_field, request, using=self.using, **kwargs |
| 673 |
) |
| 674 |
|
| 675 |
def formfield_for_manytomany(self, db_field, request, **kwargs): |
| 676 |
# Tell Django to populate ManyToMany widgets using a query |
| 677 |
# on the 'other' database. |
| 678 |
return super().formfield_for_manytomany( |
| 679 |
db_field, request, using=self.using, **kwargs |
| 680 |
) |
| 681 |
|
| 682 |
Once you've written your model admin definitions, they can be |
| 683 |
registered with any ``Admin`` instance:: |
| 684 |
|
| 685 |
from django.contrib import admin |
| 686 |
from myapp.models import Author, Book, Publisher |
| 687 |
|
| 688 |
# Import our custom ModelAdmin and TabularInline from where they're defined. |
| 689 |
from myproject.admin import MultiDBModelAdmin, MultiDBTabularInline |
| 690 |
|
| 691 |
|
| 692 |
# Specialize the multi-db admin objects for use with specific models. |
| 693 |
class BookInline(MultiDBTabularInline): |
| 694 |
model = Book |
| 695 |
|
| 696 |
|
| 697 |
class PublisherAdmin(MultiDBModelAdmin): |
| 698 |
inlines = [BookInline] |
| 699 |
|
| 700 |
|
| 701 |
admin.site.register(Author, MultiDBModelAdmin) |
| 702 |
admin.site.register(Publisher, PublisherAdmin) |
| 703 |
|
| 704 |
othersite = admin.AdminSite("othersite") |
| 705 |
othersite.register(Publisher, MultiDBModelAdmin) |
| 706 |
|
| 707 |
This example sets up two admin sites. On the first site, the |
| 708 |
``Author`` and ``Publisher`` objects are exposed; ``Publisher`` |
| 709 |
objects have a tabular inline showing books published by that |
| 710 |
publisher. The second site exposes just publishers, without the |
| 711 |
inlines. |
| 712 |
|
| 713 |
Using raw cursors with multiple databases |
| 714 |
========================================= |
| 715 |
|
| 716 |
If you are using more than one database you can use |
| 717 |
``django.db.connections`` to obtain the connection (and cursor) for a |
| 718 |
specific database. ``django.db.connections`` is a dictionary-like |
| 719 |
object that allows you to retrieve a specific connection using its |
| 720 |
alias:: |
| 721 |
|
| 722 |
from django.db import connections |
| 723 |
|
| 724 |
with connections["my_db_alias"].cursor() as cursor: |
| 725 |
... |
| 726 |
|
| 727 |
Limitations of multiple databases |
| 728 |
================================= |
| 729 |
|
| 730 |
.. _no_cross_database_relations: |
| 731 |
|
| 732 |
Cross-database relations |
| 733 |
------------------------ |
| 734 |
|
| 735 |
Django doesn't currently provide any support for foreign key or |
| 736 |
many-to-many relationships spanning multiple databases. If you |
| 737 |
have used a router to partition models to different databases, |
| 738 |
any foreign key and many-to-many relationships defined by those |
| 739 |
models must be internal to a single database. |
| 740 |
|
| 741 |
This is because of referential integrity. In order to maintain a |
| 742 |
relationship between two objects, Django needs to know that the |
| 743 |
primary key of the related object is valid. If the primary key is |
| 744 |
stored on a separate database, it's not possible to easily evaluate |
| 745 |
the validity of a primary key. |
| 746 |
|
| 747 |
If you're using Postgres, SQLite, Oracle, or MySQL with InnoDB, this is |
| 748 |
enforced at the database integrity level -- database level key |
| 749 |
constraints prevent the creation of relations that can't be validated. |
| 750 |
|
| 751 |
However, if you're using MySQL with MyISAM tables, there is no enforced |
| 752 |
referential integrity; as a result, you may be able to 'fake' cross database |
| 753 |
foreign keys. However, this configuration is not officially supported by |
| 754 |
Django. |
| 755 |
|
| 756 |
.. _contrib_app_multiple_databases: |
| 757 |
|
| 758 |
Behavior of contrib apps |
| 759 |
------------------------ |
| 760 |
|
| 761 |
Several contrib apps include models, and some apps depend on others. Since |
| 762 |
cross-database relationships are impossible, this creates some restrictions on |
| 763 |
how you can split these models across databases: |
| 764 |
|
| 765 |
- each one of ``contenttypes.ContentType``, ``sessions.Session`` and |
| 766 |
``sites.Site`` can be stored in any database, given a suitable router. |
| 767 |
- ``auth`` models — ``User``, ``Group`` and ``Permission`` — are linked |
| 768 |
together and linked to ``ContentType``, so they must be stored in the same |
| 769 |
database as ``ContentType``. |
| 770 |
- ``admin`` depends on ``auth``, so its models must be in the same database |
| 771 |
as ``auth``. |
| 772 |
- ``flatpages`` and ``redirects`` depend on ``sites``, so their models must be |
| 773 |
in the same database as ``sites``. |
| 774 |
|
| 775 |
In addition, some objects are automatically created just after |
| 776 |
:djadmin:`migrate` creates a table to hold them in a database: |
| 777 |
|
| 778 |
- a default ``Site``, |
| 779 |
- a ``ContentType`` for each model (including those not stored in that |
| 780 |
database), |
| 781 |
- the ``Permission``\s for each model (including those not stored in that |
| 782 |
database). |
| 783 |
|
| 784 |
For common setups with multiple databases, it isn't useful to have these |
| 785 |
objects in more than one database. Common setups include primary/replica and |
| 786 |
connecting to external databases. Therefore, it's recommended to write a |
| 787 |
:ref:`database router<topics-db-multi-db-routing>` that allows synchronizing |
| 788 |
these three models to only one database. Use the same approach for contrib |
| 789 |
and third-party apps that don't need their tables in multiple databases. |
| 790 |
|
| 791 |
.. warning:: |
| 792 |
|
| 793 |
If you're synchronizing content types to more than one database, be aware |
| 794 |
that their primary keys may not match across databases. This may result in |
| 795 |
data corruption or data loss. |