| 1 |
========= |
| 2 |
Databases |
| 3 |
========= |
| 4 |
|
| 5 |
Django officially supports the following databases: |
| 6 |
|
| 7 |
* :ref:`PostgreSQL <postgresql-notes>` |
| 8 |
* :ref:`MariaDB <mariadb-notes>` |
| 9 |
* :ref:`MySQL <mysql-notes>` |
| 10 |
* :ref:`Oracle <oracle-notes>` |
| 11 |
* :ref:`SQLite <sqlite-notes>` |
| 12 |
|
| 13 |
There are also a number of :ref:`database backends provided by third parties |
| 14 |
<third-party-notes>`. |
| 15 |
|
| 16 |
Django attempts to support as many features as possible on all database |
| 17 |
backends. However, not all database backends are alike, and we've had to make |
| 18 |
design decisions on which features to support and which assumptions we can make |
| 19 |
safely. |
| 20 |
|
| 21 |
This file describes some of the features that might be relevant to Django |
| 22 |
usage. It is not intended as a replacement for server-specific documentation or |
| 23 |
reference manuals. |
| 24 |
|
| 25 |
General notes |
| 26 |
============= |
| 27 |
|
| 28 |
.. _persistent-database-connections: |
| 29 |
|
| 30 |
Persistent connections |
| 31 |
---------------------- |
| 32 |
|
| 33 |
Persistent connections avoid the overhead of reestablishing a connection to |
| 34 |
the database in each HTTP request. They're controlled by the |
| 35 |
:setting:`CONN_MAX_AGE` parameter which defines the maximum lifetime of a |
| 36 |
connection. It can be set independently for each database. |
| 37 |
|
| 38 |
The default value is ``0``, preserving the historical behavior of closing the |
| 39 |
database connection at the end of each request. To enable persistent |
| 40 |
connections, set :setting:`CONN_MAX_AGE` to a positive integer of seconds. For |
| 41 |
unlimited persistent connections, set it to ``None``. |
| 42 |
|
| 43 |
When using ASGI, persistent connections should be disabled. Instead, use your |
| 44 |
database backend's built-in connection pooling if available, or investigate a |
| 45 |
third-party connection pooling option if required. |
| 46 |
|
| 47 |
Connection management |
| 48 |
~~~~~~~~~~~~~~~~~~~~~ |
| 49 |
|
| 50 |
Django opens a connection to the database when it first makes a database |
| 51 |
query. It keeps this connection open and reuses it in subsequent requests. |
| 52 |
Django closes the connection once it exceeds the maximum age defined by |
| 53 |
:setting:`CONN_MAX_AGE` or when it isn't usable any longer. |
| 54 |
|
| 55 |
In detail, Django automatically opens a connection to the database whenever it |
| 56 |
needs one and doesn't have one already — either because this is the first |
| 57 |
connection, or because the previous connection was closed. |
| 58 |
|
| 59 |
At the beginning of each request, Django closes the connection if it has |
| 60 |
reached its maximum age. If your database terminates idle connections after |
| 61 |
some time, you should set :setting:`CONN_MAX_AGE` to a lower value, so that |
| 62 |
Django doesn't attempt to use a connection that has been terminated by the |
| 63 |
database server. (This problem may only affect very low traffic sites.) |
| 64 |
|
| 65 |
At the end of each request, Django closes the connection if it has reached its |
| 66 |
maximum age or if it is in an unrecoverable error state. If any database |
| 67 |
errors have occurred while processing the requests, Django checks whether the |
| 68 |
connection still works, and closes it if it doesn't. Thus, database errors |
| 69 |
affect at most one request per each application's worker thread; if the |
| 70 |
connection becomes unusable, the next request gets a fresh connection. |
| 71 |
|
| 72 |
Setting :setting:`CONN_HEALTH_CHECKS` to ``True`` can be used to improve the |
| 73 |
robustness of connection reuse and prevent errors when a connection has been |
| 74 |
closed by the database server which is now ready to accept and serve new |
| 75 |
connections, e.g. after database server restart. The health check is performed |
| 76 |
only once per request and only if the database is being accessed during the |
| 77 |
handling of the request. |
| 78 |
|
| 79 |
Caveats |
| 80 |
~~~~~~~ |
| 81 |
|
| 82 |
Since each thread maintains its own connection, your database must support at |
| 83 |
least as many simultaneous connections as you have worker threads. |
| 84 |
|
| 85 |
Sometimes a database won't be accessed by the majority of your views, for |
| 86 |
example because it's the database of an external system, or thanks to caching. |
| 87 |
In such cases, you should set :setting:`CONN_MAX_AGE` to a low value or even |
| 88 |
``0``, because it doesn't make sense to maintain a connection that's unlikely |
| 89 |
to be reused. This will help keep the number of simultaneous connections to |
| 90 |
this database small. |
| 91 |
|
| 92 |
The development server creates a new thread for each request it handles, |
| 93 |
negating the effect of persistent connections. Don't enable them during |
| 94 |
development. |
| 95 |
|
| 96 |
When Django establishes a connection to the database, it sets up appropriate |
| 97 |
parameters, depending on the backend being used. If you enable persistent |
| 98 |
connections, this setup is no longer repeated every request. If you modify |
| 99 |
parameters such as the connection's isolation level or time zone, you should |
| 100 |
either restore Django's defaults at the end of each request, force an |
| 101 |
appropriate value at the beginning of each request, or disable persistent |
| 102 |
connections. |
| 103 |
|
| 104 |
If a connection is created in a long-running process, outside of Django’s |
| 105 |
request-response cycle, the connection will remain open until explicitly |
| 106 |
closed, or timeout occurs. You can use ``django.db.close_old_connections()`` to |
| 107 |
close all old or unusable connections. |
| 108 |
|
| 109 |
Encoding |
| 110 |
-------- |
| 111 |
|
| 112 |
Django assumes that all databases use UTF-8 encoding. Using other encodings may |
| 113 |
result in unexpected behavior such as "value too long" errors from your |
| 114 |
database for data that is valid in Django. See the database specific notes |
| 115 |
below for information on how to set up your database correctly. |
| 116 |
|
| 117 |
.. _postgresql-notes: |
| 118 |
|
| 119 |
PostgreSQL notes |
| 120 |
================ |
| 121 |
|
| 122 |
Django supports PostgreSQL 14 and higher. `psycopg`_ 3.1.8+ or `psycopg2`_ |
| 123 |
2.8.4+ is required, though the latest `psycopg`_ 3.1.8+ is recommended. |
| 124 |
|
| 125 |
.. note:: |
| 126 |
|
| 127 |
Support for ``psycopg2`` is likely to be deprecated and removed at some |
| 128 |
point in the future. |
| 129 |
|
| 130 |
.. _postgresql-connection-settings: |
| 131 |
|
| 132 |
PostgreSQL connection settings |
| 133 |
------------------------------- |
| 134 |
|
| 135 |
See :setting:`HOST` for details. |
| 136 |
|
| 137 |
To connect using a service name from the `connection service file`_ and a |
| 138 |
password from the `password file`_, you must specify them in the |
| 139 |
:setting:`OPTIONS` part of your database configuration in :setting:`DATABASES`: |
| 140 |
|
| 141 |
.. code-block:: python |
| 142 |
:caption: ``settings.py`` |
| 143 |
|
| 144 |
DATABASES = { |
| 145 |
"default": { |
| 146 |
"ENGINE": "django.db.backends.postgresql", |
| 147 |
"OPTIONS": { |
| 148 |
"service": "my_service", |
| 149 |
"passfile": ".my_pgpass", |
| 150 |
}, |
| 151 |
} |
| 152 |
} |
| 153 |
|
| 154 |
.. code-block:: text |
| 155 |
:caption: ``.pg_service.conf`` |
| 156 |
|
| 157 |
[my_service] |
| 158 |
host=localhost |
| 159 |
user=USER |
| 160 |
dbname=NAME |
| 161 |
port=5432 |
| 162 |
|
| 163 |
.. code-block:: text |
| 164 |
:caption: ``.my_pgpass`` |
| 165 |
|
| 166 |
localhost:5432:NAME:USER:PASSWORD |
| 167 |
|
| 168 |
The PostgreSQL backend passes the content of :setting:`OPTIONS` as keyword |
| 169 |
arguments to the connection constructor, allowing for more advanced control |
| 170 |
of driver behavior. All available `parameters`_ are described in detail in the |
| 171 |
PostgreSQL documentation. |
| 172 |
|
| 173 |
.. _connection service file: https://www.postgresql.org/docs/current/libpq-pgservice.html |
| 174 |
.. _password file: https://www.postgresql.org/docs/current/libpq-pgpass.html |
| 175 |
.. _parameters: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS |
| 176 |
|
| 177 |
.. warning:: |
| 178 |
|
| 179 |
Using a service name for testing purposes is not supported. This |
| 180 |
:ticket:`may be implemented later <33685>`. |
| 181 |
|
| 182 |
Optimizing PostgreSQL's configuration |
| 183 |
------------------------------------- |
| 184 |
|
| 185 |
Django needs the following parameters for its database connections: |
| 186 |
|
| 187 |
- ``client_encoding``: ``'UTF8'``, |
| 188 |
- ``default_transaction_isolation``: ``'read committed'`` by default, |
| 189 |
or the value set in the connection options (see below), |
| 190 |
- ``timezone``: |
| 191 |
- when :setting:`USE_TZ` is ``True``, ``'UTC'`` by default, or the |
| 192 |
:setting:`TIME_ZONE <DATABASE-TIME_ZONE>` value set for the connection, |
| 193 |
- when :setting:`USE_TZ` is ``False``, the value of the global |
| 194 |
:setting:`TIME_ZONE` setting. |
| 195 |
|
| 196 |
If these parameters already have the correct values, Django won't set them for |
| 197 |
every new connection, which improves performance slightly. You can configure |
| 198 |
them directly in :file:`postgresql.conf` or more conveniently per database |
| 199 |
user with `ALTER ROLE`_. |
| 200 |
|
| 201 |
Django will work just fine without this optimization, but each new connection |
| 202 |
will do some additional queries to set these parameters. |
| 203 |
|
| 204 |
.. _ALTER ROLE: https://www.postgresql.org/docs/current/sql-alterrole.html |
| 205 |
|
| 206 |
.. _database-isolation-level: |
| 207 |
|
| 208 |
Isolation level |
| 209 |
--------------- |
| 210 |
|
| 211 |
Like PostgreSQL itself, Django defaults to the ``READ COMMITTED`` `isolation |
| 212 |
level`_. If you need a higher isolation level such as ``REPEATABLE READ`` or |
| 213 |
``SERIALIZABLE``, set it in the :setting:`OPTIONS` part of your database |
| 214 |
configuration in :setting:`DATABASES`:: |
| 215 |
|
| 216 |
from django.db.backends.postgresql.psycopg_any import IsolationLevel |
| 217 |
|
| 218 |
DATABASES = { |
| 219 |
# ... |
| 220 |
"OPTIONS": { |
| 221 |
"isolation_level": IsolationLevel.SERIALIZABLE, |
| 222 |
}, |
| 223 |
} |
| 224 |
|
| 225 |
.. note:: |
| 226 |
|
| 227 |
Under higher isolation levels, your application should be prepared to |
| 228 |
handle exceptions raised on serialization failures. This option is |
| 229 |
designed for advanced uses. |
| 230 |
|
| 231 |
.. _isolation level: https://www.postgresql.org/docs/current/transaction-iso.html |
| 232 |
|
| 233 |
.. _database-role: |
| 234 |
|
| 235 |
Role |
| 236 |
---- |
| 237 |
|
| 238 |
If you need to use a different role for database connections than the role used |
| 239 |
to establish the connection, set it in the :setting:`OPTIONS` part of your |
| 240 |
database configuration in :setting:`DATABASES`:: |
| 241 |
|
| 242 |
DATABASES = { |
| 243 |
"default": { |
| 244 |
"ENGINE": "django.db.backends.postgresql", |
| 245 |
# ... |
| 246 |
"OPTIONS": { |
| 247 |
"assume_role": "my_application_role", |
| 248 |
}, |
| 249 |
}, |
| 250 |
} |
| 251 |
|
| 252 |
.. _postgresql-pool: |
| 253 |
|
| 254 |
Connection pool |
| 255 |
--------------- |
| 256 |
|
| 257 |
.. versionadded:: 5.1 |
| 258 |
|
| 259 |
To use a connection pool with `psycopg`_, you can either set ``"pool"`` in the |
| 260 |
:setting:`OPTIONS` part of your database configuration in :setting:`DATABASES` |
| 261 |
to be a dict to be passed to :class:`~psycopg:psycopg_pool.ConnectionPool`, or |
| 262 |
to ``True`` to use the ``ConnectionPool`` defaults:: |
| 263 |
|
| 264 |
DATABASES = { |
| 265 |
"default": { |
| 266 |
"ENGINE": "django.db.backends.postgresql", |
| 267 |
# ... |
| 268 |
"OPTIONS": { |
| 269 |
"pool": True, |
| 270 |
}, |
| 271 |
}, |
| 272 |
} |
| 273 |
|
| 274 |
This option requires ``psycopg[pool]`` or :pypi:`psycopg-pool` to be installed |
| 275 |
and is ignored with ``psycopg2``. |
| 276 |
|
| 277 |
.. _database-server-side-parameters-binding: |
| 278 |
|
| 279 |
Server-side parameters binding |
| 280 |
------------------------------ |
| 281 |
|
| 282 |
With `psycopg`_ 3.1.8+, Django defaults to the :ref:`client-side binding |
| 283 |
cursors <psycopg:client-side-binding-cursors>`. If you want to use the |
| 284 |
:ref:`server-side binding <psycopg:server-side-binding>` set it in the |
| 285 |
:setting:`OPTIONS` part of your database configuration in |
| 286 |
:setting:`DATABASES`:: |
| 287 |
|
| 288 |
DATABASES = { |
| 289 |
"default": { |
| 290 |
"ENGINE": "django.db.backends.postgresql", |
| 291 |
# ... |
| 292 |
"OPTIONS": { |
| 293 |
"server_side_binding": True, |
| 294 |
}, |
| 295 |
}, |
| 296 |
} |
| 297 |
|
| 298 |
This option is ignored with ``psycopg2``. |
| 299 |
|
| 300 |
Indexes for ``varchar`` and ``text`` columns |
| 301 |
-------------------------------------------- |
| 302 |
|
| 303 |
When specifying ``db_index=True`` on your model fields, Django typically |
| 304 |
outputs a single ``CREATE INDEX`` statement. However, if the database type |
| 305 |
for the field is either ``varchar`` or ``text`` (e.g., used by ``CharField``, |
| 306 |
``FileField``, and ``TextField``), then Django will create |
| 307 |
an additional index that uses an appropriate `PostgreSQL operator class`_ |
| 308 |
for the column. The extra index is necessary to correctly perform |
| 309 |
lookups that use the ``LIKE`` operator in their SQL, as is done with the |
| 310 |
``contains`` and ``startswith`` lookup types. |
| 311 |
|
| 312 |
.. _PostgreSQL operator class: https://www.postgresql.org/docs/current/indexes-opclass.html |
| 313 |
|
| 314 |
Migration operation for adding extensions |
| 315 |
----------------------------------------- |
| 316 |
|
| 317 |
If you need to add a PostgreSQL extension (like ``hstore``, ``postgis``, etc.) |
| 318 |
using a migration, use the |
| 319 |
:class:`~django.contrib.postgres.operations.CreateExtension` operation. |
| 320 |
|
| 321 |
.. _postgresql-server-side-cursors: |
| 322 |
|
| 323 |
Server-side cursors |
| 324 |
------------------- |
| 325 |
|
| 326 |
When using :meth:`QuerySet.iterator() |
| 327 |
<django.db.models.query.QuerySet.iterator>`, Django opens a :ref:`server-side |
| 328 |
cursor <psycopg:server-side-cursors>`. By default, PostgreSQL assumes that |
| 329 |
only the first 10% of the results of cursor queries will be fetched. The query |
| 330 |
planner spends less time planning the query and starts returning results |
| 331 |
faster, but this could diminish performance if more than 10% of the results are |
| 332 |
retrieved. PostgreSQL's assumptions on the number of rows retrieved for a |
| 333 |
cursor query is controlled with the `cursor_tuple_fraction`_ option. |
| 334 |
|
| 335 |
.. _cursor_tuple_fraction: https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-CURSOR-TUPLE-FRACTION |
| 336 |
|
| 337 |
.. _transaction-pooling-server-side-cursors: |
| 338 |
|
| 339 |
Transaction pooling and server-side cursors |
| 340 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| 341 |
|
| 342 |
Using a connection pooler in transaction pooling mode (e.g. `PgBouncer`_) |
| 343 |
requires disabling server-side cursors for that connection. |
| 344 |
|
| 345 |
Server-side cursors are local to a connection and remain open at the end of a |
| 346 |
transaction when :setting:`AUTOCOMMIT <DATABASE-AUTOCOMMIT>` is ``True``. A |
| 347 |
subsequent transaction may attempt to fetch more results from a server-side |
| 348 |
cursor. In transaction pooling mode, there's no guarantee that subsequent |
| 349 |
transactions will use the same connection. If a different connection is used, |
| 350 |
an error is raised when the transaction references the server-side cursor, |
| 351 |
because server-side cursors are only accessible in the connection in which they |
| 352 |
were created. |
| 353 |
|
| 354 |
One solution is to disable server-side cursors for a connection in |
| 355 |
:setting:`DATABASES` by setting :setting:`DISABLE_SERVER_SIDE_CURSORS |
| 356 |
<DATABASE-DISABLE_SERVER_SIDE_CURSORS>` to ``True``. |
| 357 |
|
| 358 |
To benefit from server-side cursors in transaction pooling mode, you could set |
| 359 |
up :doc:`another connection to the database </topics/db/multi-db>` in order to |
| 360 |
perform queries that use server-side cursors. This connection needs to either |
| 361 |
be directly to the database or to a connection pooler in session pooling mode. |
| 362 |
|
| 363 |
Another option is to wrap each ``QuerySet`` using server-side cursors in an |
| 364 |
:func:`~django.db.transaction.atomic` block, because it disables ``autocommit`` |
| 365 |
for the duration of the transaction. This way, the server-side cursor will only |
| 366 |
live for the duration of the transaction. |
| 367 |
|
| 368 |
.. _PgBouncer: https://www.pgbouncer.org/ |
| 369 |
|
| 370 |
.. _manually-specified-autoincrement-pk: |
| 371 |
|
| 372 |
Manually-specifying values of auto-incrementing primary keys |
| 373 |
------------------------------------------------------------ |
| 374 |
|
| 375 |
Django uses PostgreSQL's identity columns to store auto-incrementing primary |
| 376 |
keys. An identity column is populated with values from a `sequence`_ that keeps |
| 377 |
track of the next available value. Manually assigning a value to an |
| 378 |
auto-incrementing field doesn't update the field's sequence, which might later |
| 379 |
cause a conflict. For example: |
| 380 |
|
| 381 |
.. code-block:: pycon |
| 382 |
|
| 383 |
>>> from django.contrib.auth.models import User |
| 384 |
>>> User.objects.create(username="alice", pk=1) |
| 385 |
<User: alice> |
| 386 |
>>> # The sequence hasn't been updated; its next value is 1. |
| 387 |
>>> User.objects.create(username="bob") |
| 388 |
IntegrityError: duplicate key value violates unique constraint |
| 389 |
"auth_user_pkey" DETAIL: Key (id)=(1) already exists. |
| 390 |
|
| 391 |
If you need to specify such values, reset the sequence afterward to avoid |
| 392 |
reusing a value that's already in the table. The :djadmin:`sqlsequencereset` |
| 393 |
management command generates the SQL statements to do that. |
| 394 |
|
| 395 |
.. _sequence: https://www.postgresql.org/docs/current/sql-createsequence.html |
| 396 |
|
| 397 |
Test database templates |
| 398 |
----------------------- |
| 399 |
|
| 400 |
You can use the :setting:`TEST['TEMPLATE'] <TEST_TEMPLATE>` setting to specify |
| 401 |
a `template`_ (e.g. ``'template0'``) from which to create a test database. |
| 402 |
|
| 403 |
.. _template: https://www.postgresql.org/docs/current/sql-createdatabase.html |
| 404 |
|
| 405 |
Speeding up test execution with non-durable settings |
| 406 |
---------------------------------------------------- |
| 407 |
|
| 408 |
You can speed up test execution times by `configuring PostgreSQL to be |
| 409 |
non-durable <https://www.postgresql.org/docs/current/non-durability.html>`_. |
| 410 |
|
| 411 |
.. warning:: |
| 412 |
|
| 413 |
This is dangerous: it will make your database more susceptible to data loss |
| 414 |
or corruption in the case of a server crash or power loss. Only use this on |
| 415 |
a development machine where you can easily restore the entire contents of |
| 416 |
all databases in the cluster. |
| 417 |
|
| 418 |
.. _psycopg: https://www.psycopg.org/psycopg3/ |
| 419 |
.. _psycopg2: https://www.psycopg.org/ |
| 420 |
|
| 421 |
.. _mariadb-notes: |
| 422 |
|
| 423 |
MariaDB notes |
| 424 |
============= |
| 425 |
|
| 426 |
Django supports MariaDB 10.5 and higher. |
| 427 |
|
| 428 |
To use MariaDB, use the MySQL backend, which is shared between the two. See the |
| 429 |
:ref:`MySQL notes <mysql-notes>` for more details. |
| 430 |
|
| 431 |
.. _mysql-notes: |
| 432 |
|
| 433 |
MySQL notes |
| 434 |
=========== |
| 435 |
|
| 436 |
Version support |
| 437 |
--------------- |
| 438 |
|
| 439 |
Django supports MySQL 8.0.11 and higher. |
| 440 |
|
| 441 |
Django's ``inspectdb`` feature uses the ``information_schema`` database, which |
| 442 |
contains detailed data on all database schemas. |
| 443 |
|
| 444 |
Django expects the database to support Unicode (UTF-8 encoding) and delegates to |
| 445 |
it the task of enforcing transactions and referential integrity. It is important |
| 446 |
to be aware of the fact that the two latter ones aren't actually enforced by |
| 447 |
MySQL when using the MyISAM storage engine, see the next section. |
| 448 |
|
| 449 |
.. _mysql-storage-engines: |
| 450 |
|
| 451 |
Storage engines |
| 452 |
--------------- |
| 453 |
|
| 454 |
MySQL has several `storage engines`_. You can change the default storage engine |
| 455 |
in the server configuration. |
| 456 |
|
| 457 |
MySQL's default storage engine is InnoDB_. This engine is fully transactional |
| 458 |
and supports foreign key references. It's the recommended choice. However, the |
| 459 |
InnoDB autoincrement counter is lost on a MySQL restart because it does not |
| 460 |
remember the ``AUTO_INCREMENT`` value, instead recreating it as "max(id)+1". |
| 461 |
This may result in an inadvertent reuse of :class:`~django.db.models.AutoField` |
| 462 |
values. |
| 463 |
|
| 464 |
The main drawbacks of MyISAM_ are that it doesn't support transactions or |
| 465 |
enforce foreign-key constraints. |
| 466 |
|
| 467 |
.. _storage engines: https://dev.mysql.com/doc/refman/en/storage-engines.html |
| 468 |
.. _MyISAM: https://dev.mysql.com/doc/refman/en/myisam-storage-engine.html |
| 469 |
.. _InnoDB: https://dev.mysql.com/doc/refman/en/innodb-storage-engine.html |
| 470 |
|
| 471 |
.. _mysql-db-api-drivers: |
| 472 |
|
| 473 |
MySQL DB API Drivers |
| 474 |
-------------------- |
| 475 |
|
| 476 |
MySQL has a couple drivers that implement the Python Database API described in |
| 477 |
:pep:`249`: |
| 478 |
|
| 479 |
- :pypi:`mysqlclient` is a native driver. It's **the recommended choice**. |
| 480 |
- `MySQL Connector/Python`_ is a pure Python driver from Oracle that does not |
| 481 |
require the MySQL client library or any Python modules outside the standard |
| 482 |
library. |
| 483 |
|
| 484 |
.. _MySQL Connector/Python: https://dev.mysql.com/downloads/connector/python/ |
| 485 |
|
| 486 |
In addition to a DB API driver, Django needs an adapter to access the database |
| 487 |
drivers from its ORM. Django provides an adapter for mysqlclient while MySQL |
| 488 |
Connector/Python includes `its own`_. |
| 489 |
|
| 490 |
.. _its own: https://dev.mysql.com/doc/connector-python/en/connector-python-django-backend.html |
| 491 |
|
| 492 |
mysqlclient |
| 493 |
~~~~~~~~~~~ |
| 494 |
|
| 495 |
Django requires `mysqlclient`_ 1.4.3 or later. |
| 496 |
|
| 497 |
MySQL Connector/Python |
| 498 |
~~~~~~~~~~~~~~~~~~~~~~ |
| 499 |
|
| 500 |
MySQL Connector/Python is available from the `download page`_. |
| 501 |
The Django adapter is available in versions 1.1.X and later. It may not |
| 502 |
support the most recent releases of Django. |
| 503 |
|
| 504 |
.. _download page: https://dev.mysql.com/downloads/connector/python/ |
| 505 |
|
| 506 |
.. _mysql-time-zone-definitions: |
| 507 |
|
| 508 |
Time zone definitions |
| 509 |
--------------------- |
| 510 |
|
| 511 |
If you plan on using Django's :doc:`timezone support </topics/i18n/timezones>`, |
| 512 |
use `mysql_tzinfo_to_sql`_ to load time zone tables into the MySQL database. |
| 513 |
This needs to be done just once for your MySQL server, not per database. |
| 514 |
|
| 515 |
.. _mysql_tzinfo_to_sql: https://dev.mysql.com/doc/refman/en/mysql-tzinfo-to-sql.html |
| 516 |
|
| 517 |
Creating your database |
| 518 |
---------------------- |
| 519 |
|
| 520 |
You can `create your database`_ using the command-line tools and this SQL: |
| 521 |
|
| 522 |
.. code-block:: sql |
| 523 |
|
| 524 |
CREATE DATABASE <dbname> CHARACTER SET utf8mb4; |
| 525 |
|
| 526 |
This ensures all tables and columns will use UTF-8 by default. |
| 527 |
|
| 528 |
.. _create your database: https://dev.mysql.com/doc/refman/en/create-database.html |
| 529 |
|
| 530 |
.. _mysql-collation: |
| 531 |
|
| 532 |
Collation settings |
| 533 |
~~~~~~~~~~~~~~~~~~ |
| 534 |
|
| 535 |
The collation setting for a column controls the order in which data is sorted |
| 536 |
as well as what strings compare as equal. You can specify the ``db_collation`` |
| 537 |
parameter to set the collation name of the column for |
| 538 |
:attr:`CharField <django.db.models.CharField.db_collation>` and |
| 539 |
:attr:`TextField <django.db.models.TextField.db_collation>`. |
| 540 |
|
| 541 |
The collation can also be set on a database-wide level and per-table. This is |
| 542 |
`documented thoroughly`_ in the MySQL documentation. In such cases, you must |
| 543 |
set the collation by directly manipulating the database settings or tables. |
| 544 |
Django doesn't provide an API to change them. |
| 545 |
|
| 546 |
.. _documented thoroughly: https://dev.mysql.com/doc/refman/en/charset.html |
| 547 |
|
| 548 |
By default, with a UTF-8 database, MySQL will use the |
| 549 |
``utf8mb4_0900_ai_ci`` collation. This results in all string equality |
| 550 |
comparisons being done in a *case-insensitive* manner. That is, ``"Fred"`` and |
| 551 |
``"freD"`` are considered equal at the database level. If you have a unique |
| 552 |
constraint on a field, it would be illegal to try to insert both ``"aa"`` and |
| 553 |
``"AA"`` into the same column, since they compare as equal (and, hence, |
| 554 |
non-unique) with the default collation. If you want case-sensitive comparisons |
| 555 |
on a particular column or table, change the column or table to use the |
| 556 |
``utf8mb4_0900_as_cs`` collation. |
| 557 |
|
| 558 |
Please note that according to `MySQL Unicode Character Sets`_, comparisons for |
| 559 |
the ``utf8mb4_general_ci`` collation are faster, but slightly less correct, |
| 560 |
than comparisons for ``utf8mb4_unicode_ci``. If this is acceptable for your |
| 561 |
application, you should use ``utf8mb4_general_ci`` because it is faster. If |
| 562 |
this is not acceptable (for example, if you require German dictionary order), |
| 563 |
use ``utf8mb4_unicode_ci`` because it is more accurate. |
| 564 |
|
| 565 |
.. _MySQL Unicode Character Sets: https://dev.mysql.com/doc/refman/en/charset-unicode-sets.html |
| 566 |
|
| 567 |
.. warning:: |
| 568 |
|
| 569 |
Model formsets validate unique fields in a case-sensitive manner. Thus when |
| 570 |
using a case-insensitive collation, a formset with unique field values that |
| 571 |
differ only by case will pass validation, but upon calling ``save()``, an |
| 572 |
``IntegrityError`` will be raised. |
| 573 |
|
| 574 |
Connecting to the database |
| 575 |
-------------------------- |
| 576 |
|
| 577 |
Refer to the :doc:`settings documentation </ref/settings>`. |
| 578 |
|
| 579 |
Connection settings are used in this order: |
| 580 |
|
| 581 |
#. :setting:`OPTIONS`. |
| 582 |
#. :setting:`NAME`, :setting:`USER`, :setting:`PASSWORD`, :setting:`HOST`, |
| 583 |
:setting:`PORT` |
| 584 |
#. MySQL option files. |
| 585 |
|
| 586 |
In other words, if you set the name of the database in :setting:`OPTIONS`, |
| 587 |
this will take precedence over :setting:`NAME`, which would override |
| 588 |
anything in a `MySQL option file`_. |
| 589 |
|
| 590 |
Here's a sample configuration which uses a MySQL option file:: |
| 591 |
|
| 592 |
# settings.py |
| 593 |
DATABASES = { |
| 594 |
"default": { |
| 595 |
"ENGINE": "django.db.backends.mysql", |
| 596 |
"OPTIONS": { |
| 597 |
"read_default_file": "/path/to/my.cnf", |
| 598 |
}, |
| 599 |
} |
| 600 |
} |
| 601 |
|
| 602 |
.. code-block:: ini |
| 603 |
|
| 604 |
# my.cnf |
| 605 |
[client] |
| 606 |
database = NAME |
| 607 |
user = USER |
| 608 |
password = PASSWORD |
| 609 |
default-character-set = utf8mb4 |
| 610 |
|
| 611 |
Several other `MySQLdb connection options`_ may be useful, such as ``ssl``, |
| 612 |
``init_command``, and ``sql_mode``. |
| 613 |
|
| 614 |
.. _MySQL option file: https://dev.mysql.com/doc/refman/en/option-files.html |
| 615 |
.. _MySQLdb connection options: https://mysqlclient.readthedocs.io/user_guide.html#functions-and-attributes |
| 616 |
|
| 617 |
.. _mysql-sql-mode: |
| 618 |
|
| 619 |
Setting ``sql_mode`` |
| 620 |
~~~~~~~~~~~~~~~~~~~~ |
| 621 |
|
| 622 |
The default value of the ``sql_mode`` option contains ``STRICT_TRANS_TABLES``. |
| 623 |
That option escalates warnings into errors when data are truncated upon |
| 624 |
insertion, so Django highly recommends activating a `strict mode`_ for MySQL to |
| 625 |
prevent data loss (either ``STRICT_TRANS_TABLES`` or ``STRICT_ALL_TABLES``). |
| 626 |
|
| 627 |
.. _strict mode: https://dev.mysql.com/doc/refman/en/sql-mode.html#sql-mode-strict |
| 628 |
|
| 629 |
If you need to customize the SQL mode, you can set the ``sql_mode`` variable |
| 630 |
like other MySQL options: either in a config file or with the entry |
| 631 |
``'init_command': "SET sql_mode='STRICT_TRANS_TABLES'"`` in the |
| 632 |
:setting:`OPTIONS` part of your database configuration in :setting:`DATABASES`. |
| 633 |
|
| 634 |
.. _mysql-isolation-level: |
| 635 |
|
| 636 |
Isolation level |
| 637 |
~~~~~~~~~~~~~~~ |
| 638 |
|
| 639 |
When running concurrent loads, database transactions from different sessions |
| 640 |
(say, separate threads handling different requests) may interact with each |
| 641 |
other. These interactions are affected by each session's `transaction isolation |
| 642 |
level`_. You can set a connection's isolation level with an |
| 643 |
``'isolation_level'`` entry in the :setting:`OPTIONS` part of your database |
| 644 |
configuration in :setting:`DATABASES`. Valid values for |
| 645 |
this entry are the four standard isolation levels: |
| 646 |
|
| 647 |
* ``'read uncommitted'`` |
| 648 |
* ``'read committed'`` |
| 649 |
* ``'repeatable read'`` |
| 650 |
* ``'serializable'`` |
| 651 |
|
| 652 |
or ``None`` to use the server's configured isolation level. However, Django |
| 653 |
works best with and defaults to read committed rather than MySQL's default, |
| 654 |
repeatable read. Data loss is possible with repeatable read. In particular, |
| 655 |
you may see cases where :meth:`~django.db.models.query.QuerySet.get_or_create` |
| 656 |
will raise an :exc:`~django.db.IntegrityError` but the object won't appear in |
| 657 |
a subsequent :meth:`~django.db.models.query.QuerySet.get` call. |
| 658 |
|
| 659 |
.. _transaction isolation level: https://dev.mysql.com/doc/refman/en/innodb-transaction-isolation-levels.html |
| 660 |
|
| 661 |
Creating your tables |
| 662 |
-------------------- |
| 663 |
|
| 664 |
When Django generates the schema, it doesn't specify a storage engine, so |
| 665 |
tables will be created with whatever default storage engine your database |
| 666 |
server is configured for. The easiest solution is to set your database server's |
| 667 |
default storage engine to the desired engine. |
| 668 |
|
| 669 |
If you're using a hosting service and can't change your server's default |
| 670 |
storage engine, you have a couple of options. |
| 671 |
|
| 672 |
* After the tables are created, execute an ``ALTER TABLE`` statement to |
| 673 |
convert a table to a new storage engine (such as InnoDB): |
| 674 |
|
| 675 |
.. code-block:: sql |
| 676 |
|
| 677 |
ALTER TABLE <tablename> ENGINE=INNODB; |
| 678 |
|
| 679 |
This can be tedious if you have a lot of tables. |
| 680 |
|
| 681 |
* Another option is to use the ``init_command`` option for MySQLdb prior to |
| 682 |
creating your tables:: |
| 683 |
|
| 684 |
"OPTIONS": { |
| 685 |
"init_command": "SET default_storage_engine=INNODB", |
| 686 |
} |
| 687 |
|
| 688 |
This sets the default storage engine upon connecting to the database. |
| 689 |
After your tables have been created, you should remove this option as it |
| 690 |
adds a query that is only needed during table creation to each database |
| 691 |
connection. |
| 692 |
|
| 693 |
Table names |
| 694 |
----------- |
| 695 |
|
| 696 |
There are `known issues`_ in even the latest versions of MySQL that can cause the |
| 697 |
case of a table name to be altered when certain SQL statements are executed |
| 698 |
under certain conditions. It is recommended that you use lowercase table |
| 699 |
names, if possible, to avoid any problems that might arise from this behavior. |
| 700 |
Django uses lowercase table names when it auto-generates table names from |
| 701 |
models, so this is mainly a consideration if you are overriding the table name |
| 702 |
via the :class:`~django.db.models.Options.db_table` parameter. |
| 703 |
|
| 704 |
.. _known issues: https://bugs.mysql.com/bug.php?id=48875 |
| 705 |
|
| 706 |
Savepoints |
| 707 |
---------- |
| 708 |
|
| 709 |
Both the Django ORM and MySQL (when using the InnoDB :ref:`storage engine |
| 710 |
<mysql-storage-engines>`) support database :ref:`savepoints |
| 711 |
<topics-db-transactions-savepoints>`. |
| 712 |
|
| 713 |
If you use the MyISAM storage engine please be aware of the fact that you will |
| 714 |
receive database-generated errors if you try to use the :ref:`savepoint-related |
| 715 |
methods of the transactions API <topics-db-transactions-savepoints>`. The reason |
| 716 |
for this is that detecting the storage engine of a MySQL database/table is an |
| 717 |
expensive operation so it was decided it isn't worth to dynamically convert |
| 718 |
these methods in no-op's based in the results of such detection. |
| 719 |
|
| 720 |
Notes on specific fields |
| 721 |
------------------------ |
| 722 |
|
| 723 |
.. _mysql-character-fields: |
| 724 |
|
| 725 |
Character fields |
| 726 |
~~~~~~~~~~~~~~~~ |
| 727 |
|
| 728 |
Any fields that are stored with ``VARCHAR`` column types may have their |
| 729 |
``max_length`` restricted to 255 characters if you are using ``unique=True`` |
| 730 |
for the field. This affects :class:`~django.db.models.CharField`, |
| 731 |
:class:`~django.db.models.SlugField`. See `the MySQL documentation`_ for more |
| 732 |
details. |
| 733 |
|
| 734 |
.. _the MySQL documentation: https://dev.mysql.com/doc/refman/en/create-index.html#create-index-column-prefixes |
| 735 |
|
| 736 |
``TextField`` limitations |
| 737 |
~~~~~~~~~~~~~~~~~~~~~~~~~ |
| 738 |
|
| 739 |
MySQL can index only the first N chars of a ``BLOB`` or ``TEXT`` column. Since |
| 740 |
``TextField`` doesn't have a defined length, you can't mark it as |
| 741 |
``unique=True``. MySQL will report: "BLOB/TEXT column '<db_column>' used in key |
| 742 |
specification without a key length". |
| 743 |
|
| 744 |
.. _mysql-fractional-seconds: |
| 745 |
|
| 746 |
Fractional seconds support for Time and DateTime fields |
| 747 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
| 748 |
|
| 749 |
MySQL can store fractional seconds, provided that the column definition |
| 750 |
includes a fractional indication (e.g. ``DATETIME(6)``). |
| 751 |
|
| 752 |
Django will not upgrade existing columns to include fractional seconds if the |
| 753 |
database server supports it. If you want to enable them on an existing database, |
| 754 |
it's up to you to either manually update the column on the target database, by |
| 755 |
executing a command like: |
| 756 |
|
| 757 |
.. code-block:: sql |
| 758 |
|
| 759 |
ALTER TABLE `your_table` MODIFY `your_datetime_column` DATETIME(6) |
| 760 |
|
| 761 |
or using a :class:`~django.db.migrations.operations.RunSQL` operation in a |
| 762 |
:ref:`data migration <data-migrations>`. |
| 763 |
|
| 764 |
``TIMESTAMP`` columns |
| 765 |
~~~~~~~~~~~~~~~~~~~~~ |
| 766 |
|
| 767 |
If you are using a legacy database that contains ``TIMESTAMP`` columns, you must |
| 768 |
set :setting:`USE_TZ = False <USE_TZ>` to avoid data corruption. |
| 769 |
:djadmin:`inspectdb` maps these columns to |
| 770 |
:class:`~django.db.models.DateTimeField` and if you enable timezone support, |
| 771 |
both MySQL and Django will attempt to convert the values from UTC to local time. |
| 772 |
|
| 773 |
Row locking with ``QuerySet.select_for_update()`` |
| 774 |
------------------------------------------------- |
| 775 |
|
| 776 |
MySQL and MariaDB do not support some options to the ``SELECT ... FOR UPDATE`` |
| 777 |
statement. If ``select_for_update()`` is used with an unsupported option, then |
| 778 |
a :exc:`~django.db.NotSupportedError` is raised. |
| 779 |
|
| 780 |
=============== ========= ===== |
| 781 |
Option MariaDB MySQL |
| 782 |
=============== ========= ===== |
| 783 |
``SKIP LOCKED`` X (≥10.6) X |
| 784 |
``NOWAIT`` X X |
| 785 |
``OF`` X |
| 786 |
``NO KEY`` |
| 787 |
=============== ========= ===== |
| 788 |
|
| 789 |
When using ``select_for_update()`` on MySQL, make sure you filter a queryset |
| 790 |
against at least a set of fields contained in unique constraints or only |
| 791 |
against fields covered by indexes. Otherwise, an exclusive write lock will be |
| 792 |
acquired over the full table for the duration of the transaction. |
| 793 |
|
| 794 |
Automatic typecasting can cause unexpected results |
| 795 |
-------------------------------------------------- |
| 796 |
|
| 797 |
When performing a query on a string type, but with an integer value, MySQL will |
| 798 |
coerce the types of all values in the table to an integer before performing the |
| 799 |
comparison. If your table contains the values ``'abc'``, ``'def'`` and you |
| 800 |
query for ``WHERE mycolumn=0``, both rows will match. Similarly, ``WHERE mycolumn=1`` |
| 801 |
will match the value ``'abc1'``. Therefore, string type fields included in Django |
| 802 |
will always cast the value to a string before using it in a query. |
| 803 |
|
| 804 |
If you implement custom model fields that inherit from |
| 805 |
:class:`~django.db.models.Field` directly, are overriding |
| 806 |
:meth:`~django.db.models.Field.get_prep_value`, or use |
| 807 |
:class:`~django.db.models.expressions.RawSQL`, |
| 808 |
:meth:`~django.db.models.query.QuerySet.extra`, or |
| 809 |
:meth:`~django.db.models.Manager.raw`, you should ensure that you perform |
| 810 |
appropriate typecasting. |
| 811 |
|
| 812 |
.. _sqlite-notes: |
| 813 |
|
| 814 |
SQLite notes |
| 815 |
============ |
| 816 |
|
| 817 |
Django supports SQLite 3.31.0 and later. |
| 818 |
|
| 819 |
SQLite_ provides an excellent development alternative for applications that |
| 820 |
are predominantly read-only or require a smaller installation footprint. As |
| 821 |
with all database servers, though, there are some differences that are |
| 822 |
specific to SQLite that you should be aware of. |
| 823 |
|
| 824 |
.. _SQLite: https://www.sqlite.org/ |
| 825 |
|
| 826 |
.. _sqlite-string-matching: |
| 827 |
|
| 828 |
Substring matching and case sensitivity |
| 829 |
--------------------------------------- |
| 830 |
|
| 831 |
For all SQLite versions, there is some slightly counterintuitive behavior when |
| 832 |
attempting to match some types of strings. These are triggered when using the |
| 833 |
:lookup:`iexact` or :lookup:`contains` filters in querysets. The behavior |
| 834 |
splits into two cases: |
| 835 |
|
| 836 |
1. For substring matching, all matches are done case-insensitively. That is a |
| 837 |
filter such as ``filter(name__contains="aa")`` will match a name of ``"Aabb"``. |
| 838 |
|
| 839 |
2. For strings containing characters outside the ASCII range, all exact string |
| 840 |
matches are performed case-sensitively, even when the case-insensitive options |
| 841 |
are passed into the query. So the :lookup:`iexact` filter will behave exactly |
| 842 |
the same as the :lookup:`exact` filter in these cases. |
| 843 |
|
| 844 |
Some possible workarounds for this are `documented at sqlite.org`_, but they |
| 845 |
aren't utilized by the default SQLite backend in Django, as incorporating them |
| 846 |
would be fairly difficult to do robustly. Thus, Django exposes the default |
| 847 |
SQLite behavior and you should be aware of this when doing case-insensitive or |
| 848 |
substring filtering. |
| 849 |
|
| 850 |
.. _documented at sqlite.org: https://www.sqlite.org/faq.html#q18 |
| 851 |
|
| 852 |
.. _sqlite-decimal-handling: |
| 853 |
|
| 854 |
Decimal handling |
| 855 |
---------------- |
| 856 |
|
| 857 |
SQLite has no real decimal internal type. Decimal values are internally |
| 858 |
converted to the ``REAL`` data type (8-byte IEEE floating point number), as |
| 859 |
explained in the `SQLite datatypes documentation`__, so they don't support |
| 860 |
correctly-rounded decimal floating point arithmetic. |
| 861 |
|
| 862 |
__ https://www.sqlite.org/datatype3.html#storage_classes_and_datatypes |
| 863 |
|
| 864 |
"Database is locked" errors |
| 865 |
--------------------------- |
| 866 |
|
| 867 |
SQLite is meant to be a lightweight database, and thus can't support a high |
| 868 |
level of concurrency. ``OperationalError: database is locked`` errors indicate |
| 869 |
that your application is experiencing more concurrency than ``sqlite`` can |
| 870 |
handle in default configuration. This error means that one thread or process has |
| 871 |
an exclusive lock on the database connection and another thread timed out |
| 872 |
waiting for the lock the be released. |
| 873 |
|
| 874 |
Python's SQLite wrapper has |
| 875 |
a default timeout value that determines how long the second thread is allowed to |
| 876 |
wait on the lock before it times out and raises the ``OperationalError: database |
| 877 |
is locked`` error. |
| 878 |
|
| 879 |
If you're getting this error, you can solve it by: |
| 880 |
|
| 881 |
* Switching to another database backend. At a certain point SQLite becomes |
| 882 |
too "lite" for real-world applications, and these sorts of concurrency |
| 883 |
errors indicate you've reached that point. |
| 884 |
|
| 885 |
* Rewriting your code to reduce concurrency and ensure that database |
| 886 |
transactions are short-lived. |
| 887 |
|
| 888 |
* Increase the default timeout value by setting the ``timeout`` database |
| 889 |
option:: |
| 890 |
|
| 891 |
"OPTIONS": { |
| 892 |
# ... |
| 893 |
"timeout": 20, |
| 894 |
# ... |
| 895 |
} |
| 896 |
|
| 897 |
This will make SQLite wait a bit longer before throwing "database is locked" |
| 898 |
errors; it won't really do anything to solve them. |
| 899 |
|
| 900 |
.. _sqlite-transaction-behavior: |
| 901 |
|
| 902 |
Transactions behavior |
| 903 |
~~~~~~~~~~~~~~~~~~~~~ |
| 904 |
|
| 905 |
.. versionadded:: 5.1 |
| 906 |
|
| 907 |
SQLite supports three transaction modes: ``DEFERRED``, ``IMMEDIATE``, and |
| 908 |
``EXCLUSIVE``. |
| 909 |
|
| 910 |
The default is ``DEFERRED``. If you need to use a different mode, set it in the |
| 911 |
:setting:`OPTIONS` part of your database configuration in |
| 912 |
:setting:`DATABASES`, for example:: |
| 913 |
|
| 914 |
"OPTIONS": { |
| 915 |
# ... |
| 916 |
"transaction_mode": "IMMEDIATE", |
| 917 |
# ... |
| 918 |
} |
| 919 |
|
| 920 |
To make sure your transactions wait until ``timeout`` before raising "Database |
| 921 |
is Locked", change the transaction mode to ``IMMEDIATE``. |
| 922 |
|
| 923 |
For the best performance with ``IMMEDIATE`` and ``EXCLUSIVE``, transactions |
| 924 |
should be as short as possible. This might be hard to guarantee for all of your |
| 925 |
views so the usage of :setting:`ATOMIC_REQUESTS <DATABASE-ATOMIC_REQUESTS>` is |
| 926 |
discouraged in this case. |
| 927 |
|
| 928 |
For more information see `Transactions in SQLite`_. |
| 929 |
|
| 930 |
.. _`Transactions in SQLite`: https://www.sqlite.org/lang_transaction.html#deferred_immediate_and_exclusive_transactions |
| 931 |
|
| 932 |
``QuerySet.select_for_update()`` not supported |
| 933 |
---------------------------------------------- |
| 934 |
|
| 935 |
SQLite does not support the ``SELECT ... FOR UPDATE`` syntax. Calling it will |
| 936 |
have no effect. |
| 937 |
|
| 938 |
.. _sqlite-isolation: |
| 939 |
|
| 940 |
Isolation when using ``QuerySet.iterator()`` |
| 941 |
-------------------------------------------- |
| 942 |
|
| 943 |
There are special considerations described in `Isolation In SQLite`_ when |
| 944 |
modifying a table while iterating over it using :meth:`.QuerySet.iterator`. If |
| 945 |
a row is added, changed, or deleted within the loop, then that row may or may |
| 946 |
not appear, or may appear twice, in subsequent results fetched from the |
| 947 |
iterator. Your code must handle this. |
| 948 |
|
| 949 |
.. _`Isolation in SQLite`: https://www.sqlite.org/isolation.html |
| 950 |
|
| 951 |
.. _sqlite-json1: |
| 952 |
|
| 953 |
Enabling JSON1 extension on SQLite |
| 954 |
---------------------------------- |
| 955 |
|
| 956 |
To use :class:`~django.db.models.JSONField` on SQLite, you need to enable the |
| 957 |
`JSON1 extension`_ on Python's :py:mod:`sqlite3` library. If the extension is |
| 958 |
not enabled on your installation, a system error (``fields.E180``) will be |
| 959 |
raised. |
| 960 |
|
| 961 |
To enable the JSON1 extension you can follow the instruction on |
| 962 |
`the wiki page`_. |
| 963 |
|
| 964 |
.. note:: |
| 965 |
|
| 966 |
The JSON1 extension is enabled by default on SQLite 3.38+. |
| 967 |
|
| 968 |
.. _JSON1 extension: https://www.sqlite.org/json1.html |
| 969 |
.. _the wiki page: https://code.djangoproject.com/wiki/JSON1Extension |
| 970 |
|
| 971 |
.. _sqlite-init-command: |
| 972 |
|
| 973 |
Setting pragma options |
| 974 |
---------------------- |
| 975 |
|
| 976 |
.. versionadded:: 5.1 |
| 977 |
|
| 978 |
`Pragma options`_ can be set upon connection by using the ``init_command`` in |
| 979 |
the :setting:`OPTIONS` part of your database configuration in |
| 980 |
:setting:`DATABASES`. The example below shows how to enable extra durability of |
| 981 |
synchronous writes and change the ``cache_size``:: |
| 982 |
|
| 983 |
DATABASES = { |
| 984 |
"default": { |
| 985 |
"ENGINE": "django.db.backends.sqlite3", |
| 986 |
# ... |
| 987 |
"OPTIONS": { |
| 988 |
"init_command": "PRAGMA synchronous=3; PRAGMA cache_size=2000;", |
| 989 |
}, |
| 990 |
} |
| 991 |
} |
| 992 |
|
| 993 |
.. _Pragma options: https://www.sqlite.org/pragma.html |
| 994 |
|
| 995 |
.. _oracle-notes: |
| 996 |
|
| 997 |
Oracle notes |
| 998 |
============ |
| 999 |
|
| 1000 |
Django supports `Oracle Database Server`_ versions 19c and higher. Version |
| 1001 |
2.3.0 or higher of the `oracledb`_ Python driver is required. |
| 1002 |
|
| 1003 |
.. deprecated:: 5.0 |
| 1004 |
|
| 1005 |
Support for ``cx_Oracle`` is deprecated. |
| 1006 |
|
| 1007 |
.. _`Oracle Database Server`: https://www.oracle.com/ |
| 1008 |
.. _`oracledb`: https://oracle.github.io/python-oracledb/ |
| 1009 |
|
| 1010 |
In order for the ``python manage.py migrate`` command to work, your Oracle |
| 1011 |
database user must have privileges to run the following commands: |
| 1012 |
|
| 1013 |
* CREATE TABLE |
| 1014 |
* CREATE SEQUENCE |
| 1015 |
* CREATE PROCEDURE |
| 1016 |
* CREATE TRIGGER |
| 1017 |
|
| 1018 |
To run a project's test suite, the user usually needs these *additional* |
| 1019 |
privileges: |
| 1020 |
|
| 1021 |
* CREATE USER |
| 1022 |
* ALTER USER |
| 1023 |
* DROP USER |
| 1024 |
* CREATE TABLESPACE |
| 1025 |
* DROP TABLESPACE |
| 1026 |
* CREATE SESSION WITH ADMIN OPTION |
| 1027 |
* CREATE TABLE WITH ADMIN OPTION |
| 1028 |
* CREATE SEQUENCE WITH ADMIN OPTION |
| 1029 |
* CREATE PROCEDURE WITH ADMIN OPTION |
| 1030 |
* CREATE TRIGGER WITH ADMIN OPTION |
| 1031 |
|
| 1032 |
While the ``RESOURCE`` role has the required ``CREATE TABLE``, |
| 1033 |
``CREATE SEQUENCE``, ``CREATE PROCEDURE``, and ``CREATE TRIGGER`` privileges, |
| 1034 |
and a user granted ``RESOURCE WITH ADMIN OPTION`` can grant ``RESOURCE``, such |
| 1035 |
a user cannot grant the individual privileges (e.g. ``CREATE TABLE``), and thus |
| 1036 |
``RESOURCE WITH ADMIN OPTION`` is not usually sufficient for running tests. |
| 1037 |
|
| 1038 |
Some test suites also create views or materialized views; to run these, the |
| 1039 |
user also needs ``CREATE VIEW WITH ADMIN OPTION`` and |
| 1040 |
``CREATE MATERIALIZED VIEW WITH ADMIN OPTION`` privileges. In particular, this |
| 1041 |
is needed for Django's own test suite. |
| 1042 |
|
| 1043 |
All of these privileges are included in the DBA role, which is appropriate |
| 1044 |
for use on a private developer's database. |
| 1045 |
|
| 1046 |
The Oracle database backend uses the ``SYS.DBMS_LOB`` and ``SYS.DBMS_RANDOM`` |
| 1047 |
packages, so your user will require execute permissions on it. It's normally |
| 1048 |
accessible to all users by default, but in case it is not, you'll need to grant |
| 1049 |
permissions like so: |
| 1050 |
|
| 1051 |
.. code-block:: sql |
| 1052 |
|
| 1053 |
GRANT EXECUTE ON SYS.DBMS_LOB TO user; |
| 1054 |
GRANT EXECUTE ON SYS.DBMS_RANDOM TO user; |
| 1055 |
|
| 1056 |
Connecting to the database |
| 1057 |
-------------------------- |
| 1058 |
|
| 1059 |
To connect using the service name of your Oracle database, your ``settings.py`` |
| 1060 |
file should look something like this:: |
| 1061 |
|
| 1062 |
DATABASES = { |
| 1063 |
"default": { |
| 1064 |
"ENGINE": "django.db.backends.oracle", |
| 1065 |
"NAME": "xe", |
| 1066 |
"USER": "a_user", |
| 1067 |
"PASSWORD": "a_password", |
| 1068 |
"HOST": "", |
| 1069 |
"PORT": "", |
| 1070 |
} |
| 1071 |
} |
| 1072 |
|
| 1073 |
|
| 1074 |
In this case, you should leave both :setting:`HOST` and :setting:`PORT` empty. |
| 1075 |
However, if you don't use a ``tnsnames.ora`` file or a similar naming method |
| 1076 |
and want to connect using the SID ("xe" in this example), then fill in both |
| 1077 |
:setting:`HOST` and :setting:`PORT` like so:: |
| 1078 |
|
| 1079 |
DATABASES = { |
| 1080 |
"default": { |
| 1081 |
"ENGINE": "django.db.backends.oracle", |
| 1082 |
"NAME": "xe", |
| 1083 |
"USER": "a_user", |
| 1084 |
"PASSWORD": "a_password", |
| 1085 |
"HOST": "dbprod01ned.mycompany.com", |
| 1086 |
"PORT": "1540", |
| 1087 |
} |
| 1088 |
} |
| 1089 |
|
| 1090 |
You should either supply both :setting:`HOST` and :setting:`PORT`, or leave |
| 1091 |
both as empty strings. Django will use a different connect descriptor depending |
| 1092 |
on that choice. |
| 1093 |
|
| 1094 |
Full DSN and Easy Connect |
| 1095 |
~~~~~~~~~~~~~~~~~~~~~~~~~ |
| 1096 |
|
| 1097 |
A Full DSN or Easy Connect string can be used in :setting:`NAME` if both |
| 1098 |
:setting:`HOST` and :setting:`PORT` are empty. This format is required when |
| 1099 |
using RAC or pluggable databases without ``tnsnames.ora``, for example. |
| 1100 |
|
| 1101 |
Example of an Easy Connect string:: |
| 1102 |
|
| 1103 |
"NAME": "localhost:1521/orclpdb1" |
| 1104 |
|
| 1105 |
Example of a full DSN string:: |
| 1106 |
|
| 1107 |
"NAME": ( |
| 1108 |
"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))" |
| 1109 |
"(CONNECT_DATA=(SERVICE_NAME=orclpdb1)))" |
| 1110 |
) |
| 1111 |
|
| 1112 |
.. _oracle-pool: |
| 1113 |
|
| 1114 |
Connection pool |
| 1115 |
--------------- |
| 1116 |
|
| 1117 |
.. versionadded:: 5.2 |
| 1118 |
|
| 1119 |
To use a connection pool with `oracledb`_, set ``"pool"`` to ``True`` in the |
| 1120 |
:setting:`OPTIONS` part of your database configuration. This uses the driver's |
| 1121 |
`create_pool()`_ default values:: |
| 1122 |
|
| 1123 |
DATABASES = { |
| 1124 |
"default": { |
| 1125 |
"ENGINE": "django.db.backends.oracle", |
| 1126 |
# ... |
| 1127 |
"OPTIONS": { |
| 1128 |
"pool": True, |
| 1129 |
}, |
| 1130 |
}, |
| 1131 |
} |
| 1132 |
|
| 1133 |
To pass custom parameters to the driver's `create_pool()`_ function, you can |
| 1134 |
alternatively set ``"pool"`` to be a dict:: |
| 1135 |
|
| 1136 |
DATABASES = { |
| 1137 |
"default": { |
| 1138 |
"ENGINE": "django.db.backends.oracle", |
| 1139 |
# ... |
| 1140 |
"OPTIONS": { |
| 1141 |
"pool": { |
| 1142 |
"min": 1, |
| 1143 |
"max": 10, |
| 1144 |
# ... |
| 1145 |
} |
| 1146 |
}, |
| 1147 |
}, |
| 1148 |
} |
| 1149 |
|
| 1150 |
.. _`create_pool()`: https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#connection-pooling |
| 1151 |
|
| 1152 |
Threaded option |
| 1153 |
--------------- |
| 1154 |
|
| 1155 |
If you plan to run Django in a multithreaded environment (e.g. Apache using the |
| 1156 |
default MPM module on any modern operating system), then you **must** set |
| 1157 |
the ``threaded`` option of your Oracle database configuration to ``True``:: |
| 1158 |
|
| 1159 |
"OPTIONS": { |
| 1160 |
"threaded": True, |
| 1161 |
} |
| 1162 |
|
| 1163 |
Failure to do this may result in crashes and other odd behavior. |
| 1164 |
|
| 1165 |
INSERT ... RETURNING INTO |
| 1166 |
------------------------- |
| 1167 |
|
| 1168 |
By default, the Oracle backend uses a ``RETURNING INTO`` clause to efficiently |
| 1169 |
retrieve the value of an ``AutoField`` when inserting new rows. This behavior |
| 1170 |
may result in a ``DatabaseError`` in certain unusual setups, such as when |
| 1171 |
inserting into a remote table, or into a view with an ``INSTEAD OF`` trigger. |
| 1172 |
The ``RETURNING INTO`` clause can be disabled by setting the |
| 1173 |
``use_returning_into`` option of the database configuration to ``False``:: |
| 1174 |
|
| 1175 |
"OPTIONS": { |
| 1176 |
"use_returning_into": False, |
| 1177 |
} |
| 1178 |
|
| 1179 |
In this case, the Oracle backend will use a separate ``SELECT`` query to |
| 1180 |
retrieve ``AutoField`` values. |
| 1181 |
|
| 1182 |
Naming issues |
| 1183 |
------------- |
| 1184 |
|
| 1185 |
Oracle imposes a name length limit of 30 characters. To accommodate this, the |
| 1186 |
backend truncates database identifiers to fit, replacing the final four |
| 1187 |
characters of the truncated name with a repeatable MD5 hash value. |
| 1188 |
Additionally, the backend turns database identifiers to all-uppercase. |
| 1189 |
|
| 1190 |
To prevent these transformations (this is usually required only when dealing |
| 1191 |
with legacy databases or accessing tables which belong to other users), use |
| 1192 |
a quoted name as the value for ``db_table``:: |
| 1193 |
|
| 1194 |
class LegacyModel(models.Model): |
| 1195 |
class Meta: |
| 1196 |
db_table = '"name_left_in_lowercase"' |
| 1197 |
|
| 1198 |
|
| 1199 |
class ForeignModel(models.Model): |
| 1200 |
class Meta: |
| 1201 |
db_table = '"OTHER_USER"."NAME_ONLY_SEEMS_OVER_30"' |
| 1202 |
|
| 1203 |
Quoted names can also be used with Django's other supported database |
| 1204 |
backends; except for Oracle, however, the quotes have no effect. |
| 1205 |
|
| 1206 |
When running ``migrate``, an ``ORA-06552`` error may be encountered if |
| 1207 |
certain Oracle keywords are used as the name of a model field or the |
| 1208 |
value of a ``db_column`` option. Django quotes all identifiers used |
| 1209 |
in queries to prevent most such problems, but this error can still |
| 1210 |
occur when an Oracle datatype is used as a column name. In |
| 1211 |
particular, take care to avoid using the names ``date``, |
| 1212 |
``timestamp``, ``number`` or ``float`` as a field name. |
| 1213 |
|
| 1214 |
.. _oracle-null-empty-strings: |
| 1215 |
|
| 1216 |
NULL and empty strings |
| 1217 |
---------------------- |
| 1218 |
|
| 1219 |
Django generally prefers to use the empty string (``''``) rather than |
| 1220 |
``NULL``, but Oracle treats both identically. To get around this, the |
| 1221 |
Oracle backend ignores an explicit ``null`` option on fields that |
| 1222 |
have the empty string as a possible value and generates DDL as if |
| 1223 |
``null=True``. When fetching from the database, it is assumed that |
| 1224 |
a ``NULL`` value in one of these fields really means the empty |
| 1225 |
string, and the data is silently converted to reflect this assumption. |
| 1226 |
|
| 1227 |
``TextField`` limitations |
| 1228 |
------------------------- |
| 1229 |
|
| 1230 |
The Oracle backend stores each ``TextField`` as an ``NCLOB`` column. Oracle |
| 1231 |
imposes some limitations on the usage of such LOB columns in general: |
| 1232 |
|
| 1233 |
* LOB columns may not be used as primary keys. |
| 1234 |
|
| 1235 |
* LOB columns may not be used in indexes. |
| 1236 |
|
| 1237 |
* LOB columns may not be used in a ``SELECT DISTINCT`` list. This means that |
| 1238 |
attempting to use the ``QuerySet.distinct`` method on a model that |
| 1239 |
includes ``TextField`` columns will result in an ``ORA-00932`` error when |
| 1240 |
run against Oracle. As a workaround, use the ``QuerySet.defer`` method in |
| 1241 |
conjunction with ``distinct()`` to prevent ``TextField`` columns from being |
| 1242 |
included in the ``SELECT DISTINCT`` list. |
| 1243 |
|
| 1244 |
.. _subclassing-database-backends: |
| 1245 |
|
| 1246 |
Subclassing the built-in database backends |
| 1247 |
========================================== |
| 1248 |
|
| 1249 |
Django comes with built-in database backends. You may subclass an existing |
| 1250 |
database backends to modify its behavior, features, or configuration. |
| 1251 |
|
| 1252 |
Consider, for example, that you need to change a single database feature. |
| 1253 |
First, you have to create a new directory with a ``base`` module in it. For |
| 1254 |
example: |
| 1255 |
|
| 1256 |
.. code-block:: text |
| 1257 |
|
| 1258 |
mysite/ |
| 1259 |
... |
| 1260 |
mydbengine/ |
| 1261 |
__init__.py |
| 1262 |
base.py |
| 1263 |
|
| 1264 |
The ``base.py`` module must contain a class named ``DatabaseWrapper`` that |
| 1265 |
subclasses an existing engine from the ``django.db.backends`` module. Here's an |
| 1266 |
example of subclassing the PostgreSQL engine to change a feature class |
| 1267 |
``allows_group_by_selected_pks_on_model``: |
| 1268 |
|
| 1269 |
.. code-block:: python |
| 1270 |
:caption: ``mysite/mydbengine/base.py`` |
| 1271 |
|
| 1272 |
from django.db.backends.postgresql import base, features |
| 1273 |
|
| 1274 |
|
| 1275 |
class DatabaseFeatures(features.DatabaseFeatures): |
| 1276 |
def allows_group_by_selected_pks_on_model(self, model): |
| 1277 |
return True |
| 1278 |
|
| 1279 |
|
| 1280 |
class DatabaseWrapper(base.DatabaseWrapper): |
| 1281 |
features_class = DatabaseFeatures |
| 1282 |
|
| 1283 |
Finally, you must specify a :setting:`DATABASE-ENGINE` in your ``settings.py`` |
| 1284 |
file:: |
| 1285 |
|
| 1286 |
DATABASES = { |
| 1287 |
"default": { |
| 1288 |
"ENGINE": "mydbengine", |
| 1289 |
# ... |
| 1290 |
}, |
| 1291 |
} |
| 1292 |
|
| 1293 |
You can see the current list of database engines by looking in |
| 1294 |
:source:`django/db/backends`. |
| 1295 |
|
| 1296 |
.. _third-party-notes: |
| 1297 |
|
| 1298 |
Using a 3rd-party database backend |
| 1299 |
================================== |
| 1300 |
|
| 1301 |
In addition to the officially supported databases, there are backends provided |
| 1302 |
by 3rd parties that allow you to use other databases with Django: |
| 1303 |
|
| 1304 |
* :pypi:`CockroachDB <django-cockroachdb>` |
| 1305 |
* :pypi:`Firebird <django-firebird>` |
| 1306 |
* :pypi:`Google Cloud Spanner <django-google-spanner>` |
| 1307 |
* :pypi:`Microsoft SQL Server <mssql-django>` |
| 1308 |
* :pypi:`MongoDB <django-mongodb-backend>` |
| 1309 |
* :pypi:`Snowflake <django-snowflake>` |
| 1310 |
* :pypi:`TiDB <django-tidb>` |
| 1311 |
* :pypi:`YugabyteDB <django-yugabytedb>` |
| 1312 |
|
| 1313 |
The Django versions and ORM features supported by these unofficial backends |
| 1314 |
vary considerably. Queries regarding the specific capabilities of these |
| 1315 |
unofficial backends, along with any support queries, should be directed to |
| 1316 |
the support channels provided by each 3rd party project. |