Persistent connections¶

Persistent connections avoid the overhead of reestablishing a connection tothe database in each HTTP request. They’re controlled by theCONN_MAX_AGE parameter which defines the maximum lifetime of aconnection. It can be set independently for each database.

The default value is0, preserving the historical behavior of closing thedatabase connection at the end of each request. To enable persistentconnections, setCONN_MAX_AGE to a positive integer of seconds. Forunlimited persistent connections, set it toNone.

When using ASGI, persistent connections should be disabled. Instead, use yourdatabase backend’s built-in connection pooling if available, or investigate athird-party connection pooling option if required.

Encoding¶

Django assumes that all databases use UTF-8 encoding. Using other encodings mayresult in unexpected behavior such as “value too long” errors from yourdatabase for data that is valid in Django. See the database specific notesbelow for information on how to set up your database correctly.

PostgreSQL connection settings¶

SeeHOST for details.

To connect using a service name from theconnection service file and apassword from thepassword file, you must specify them in theOPTIONS part of your database configuration inDATABASES:

DATABASES={"default":{"ENGINE":"django.db.backends.postgresql","OPTIONS":{"service":"my_service","passfile":".my_pgpass",},}}

[my_service]host=localhostuser=USERdbname=NAMEport=5432

localhost:5432:NAME:USER:PASSWORD

The PostgreSQL backend passes the content ofOPTIONS as keywordarguments to the connection constructor, allowing for more advanced controlof driver behavior. All availableparameters are described in detail in thePostgreSQL documentation.

Optimizing PostgreSQL’s configuration¶

Django needs the following parameters for its database connections:

• client_encoding:'UTF8',

• default_transaction_isolation:'readcommitted' by default,or the value set in the connection options (see below),

• timezone:

  • whenUSE_TZ isTrue,'UTC' by default, or theTIME_ZONE value set for the connection,

  • whenUSE_TZ isFalse, the value of the globalTIME_ZONE setting.

If these parameters already have the correct values, Django won’t set them forevery new connection, which improves performance slightly. You can configurethem directly inpostgresql.conf or more conveniently per databaseuser withALTER ROLE.

Django will work just fine without this optimization, but each new connectionwill do some additional queries to set these parameters.

Isolation level¶

Like PostgreSQL itself, Django defaults to theREADCOMMITTEDisolationlevel. If you need a higher isolation level such asREPEATABLEREAD orSERIALIZABLE, set it in theOPTIONS part of your databaseconfiguration inDATABASES:

fromdjango.db.backends.postgresql.psycopg_anyimportIsolationLevelDATABASES={# ..."OPTIONS":{"isolation_level":IsolationLevel.SERIALIZABLE,},}

Role¶

If you need to use a different role for database connections than the role usedto establish the connection, set it in theOPTIONS part of yourdatabase configuration inDATABASES:

DATABASES={"default":{"ENGINE":"django.db.backends.postgresql",# ..."OPTIONS":{"assume_role":"my_application_role",},},}

Connection pool¶

To use a connection pool withpsycopg, you can either set"pool" in theOPTIONS part of your database configuration inDATABASESto be a dict to be passed toConnectionPool, ortoTrue to use theConnectionPool defaults:

DATABASES={"default":{"ENGINE":"django.db.backends.postgresql",# ..."OPTIONS":{"pool":True,},},}

This option requirespsycopg[pool] orpsycopg-pool to be installedand is ignored withpsycopg2.

Server-side parameters binding¶

Withpsycopg 3.1.8+, Django defaults to theclient-side bindingcursors. If you want to use theserver-side binding set it in theOPTIONS part of your database configuration inDATABASES:

DATABASES={"default":{"ENGINE":"django.db.backends.postgresql",# ..."OPTIONS":{"server_side_binding":True,},},}

This option is ignored withpsycopg2.

Indexes forvarchar andtext columns¶

When specifyingdb_index=True on your model fields, Django typicallyoutputs a singleCREATEINDEX statement. However, if the database typefor the field is eithervarchar ortext (e.g., used byCharField,FileField, andTextField), then Django will createan additional index that uses an appropriatePostgreSQL operator classfor the column. The extra index is necessary to correctly performlookups that use theLIKE operator in their SQL, as is done with thecontains andstartswith lookup types.

Migration operation for adding extensions¶

If you need to add a PostgreSQL extension (likehstore,postgis, etc.)using a migration, use theCreateExtension operation.

Server-side cursors¶

When usingQuerySet.iterator(), Django opens aserver-sidecursor. By default, PostgreSQL assumes thatonly the first 10% of the results of cursor queries will be fetched. The queryplanner spends less time planning the query and starts returning resultsfaster, but this could diminish performance if more than 10% of the results areretrieved. PostgreSQL’s assumptions on the number of rows retrieved for acursor query is controlled with thecursor_tuple_fraction option.

Manually-specifying values of auto-incrementing primary keys¶

Django uses PostgreSQL’s identity columns to store auto-incrementing primarykeys. An identity column is populated with values from asequence that keepstrack of the next available value. Manually assigning a value to anauto-incrementing field doesn’t update the field’s sequence, which might latercause a conflict. For example:

>>>fromdjango.contrib.auth.modelsimportUser>>>User.objects.create(username="alice",pk=1)<User: alice>>>># The sequence hasn't been updated; its next value is 1.>>>User.objects.create(username="bob")IntegrityError: duplicate key value violates unique constraint"auth_user_pkey" DETAIL:  Key (id)=(1) already exists.

If you need to specify such values, reset the sequence afterward to avoidreusing a value that’s already in the table. Thesqlsequenceresetmanagement command generates the SQL statements to do that.

Test database templates¶

You can use theTEST['TEMPLATE'] setting to specifyatemplate (e.g.'template0') from which to create a test database.

Speeding up test execution with non-durable settings¶

You can speed up test execution times byconfiguring PostgreSQL to benon-durable.

Version support¶

Django supports MySQL 8.0.11 and higher.

Django’sinspectdb feature uses theinformation_schema database, whichcontains detailed data on all database schemas.

Django expects the database to support Unicode (UTF-8 encoding) and delegates toit the task of enforcing transactions and referential integrity. It is importantto be aware of the fact that the two latter ones aren’t actually enforced byMySQL when using the MyISAM storage engine, see the next section.

Storage engines¶

MySQL has severalstorage engines. You can change the default storage enginein the server configuration.

MySQL’s default storage engine isInnoDB. This engine is fully transactionaland supports foreign key references. It’s the recommended choice. However, theInnoDB autoincrement counter is lost on a MySQL restart because it does notremember theAUTO_INCREMENT value, instead recreating it as “max(id)+1”.This may result in an inadvertent reuse ofAutoFieldvalues.

The main drawbacks ofMyISAM are that it doesn’t support transactions orenforce foreign-key constraints.

MySQL DB API Drivers¶

MySQL has a couple drivers that implement the Python Database API described inPEP 249:

• mysqlclient is a native driver. It’sthe recommended choice.

• MySQL Connector/Python is a pure Python driver from Oracle that does notrequire the MySQL client library or any Python modules outside the standardlibrary.

These drivers are thread-safe and provide connection pooling.

In addition to a DB API driver, Django needs an adapter to access the databasedrivers from its ORM. Django provides an adapter for mysqlclient while MySQLConnector/Python includesits own.

Time zone definitions¶

If you plan on using Django’stimezone support,usemysql_tzinfo_to_sql to load time zone tables into the MySQL database.This needs to be done just once for your MySQL server, not per database.

Creating your database¶

You cancreate your database using the command-line tools and this SQL:

CREATEDATABASE<dbname>CHARACTERSETutf8;

This ensures all tables and columns will use UTF-8 by default.

Connecting to the database¶

Refer to thesettings documentation.

Connection settings are used in this order:

1. OPTIONS.

2. NAME,USER,PASSWORD,HOST,PORT

3. MySQL option files.

In other words, if you set the name of the database inOPTIONS,this will take precedence overNAME, which would overrideanything in aMySQL option file.

Here’s a sample configuration which uses a MySQL option file:

# settings.pyDATABASES={"default":{"ENGINE":"django.db.backends.mysql","OPTIONS":{"read_default_file":"/path/to/my.cnf",},}}

# my.cnf[client]database=NAMEuser=USERpassword=PASSWORDdefault-character-set=utf8

Several otherMySQLdb connection options may be useful, such asssl,init_command, andsql_mode.

Creating your tables¶

When Django generates the schema, it doesn’t specify a storage engine, sotables will be created with whatever default storage engine your databaseserver is configured for. The easiest solution is to set your database server’sdefault storage engine to the desired engine.

If you’re using a hosting service and can’t change your server’s defaultstorage engine, you have a couple of options.

• After the tables are created, execute anALTERTABLE statement toconvert a table to a new storage engine (such as InnoDB):

  ALTERTABLE<tablename>ENGINE=INNODB;

  This can be tedious if you have a lot of tables.

• Another option is to use theinit_command option for MySQLdb prior tocreating your tables:

  "OPTIONS":{"init_command":"SET default_storage_engine=INNODB",}

  This sets the default storage engine upon connecting to the database.After your tables have been created, you should remove this option as itadds a query that is only needed during table creation to each databaseconnection.

Table names¶

There areknown issues in even the latest versions of MySQL that can cause thecase of a table name to be altered when certain SQL statements are executedunder certain conditions. It is recommended that you use lowercase tablenames, if possible, to avoid any problems that might arise from this behavior.Django uses lowercase table names when it auto-generates table names frommodels, so this is mainly a consideration if you are overriding the table namevia thedb_table parameter.

Savepoints¶

Both the Django ORM and MySQL (when using the InnoDBstorage engine) support databasesavepoints.

If you use the MyISAM storage engine please be aware of the fact that you willreceive database-generated errors if you try to use thesavepoint-relatedmethods of the transactions API. The reasonfor this is that detecting the storage engine of a MySQL database/table is anexpensive operation so it was decided it isn’t worth to dynamically convertthese methods in no-op’s based in the results of such detection.

Notes on specific fields¶

ALTERTABLE`your_table`MODIFY`your_datetime_column`DATETIME(6)

Row locking withQuerySet.select_for_update()¶

MySQL and MariaDB do not support some options to theSELECT...FORUPDATEstatement. Ifselect_for_update() is used with an unsupported option, thenaNotSupportedError is raised.

When usingselect_for_update() on MySQL, make sure you filter a querysetagainst at least a set of fields contained in unique constraints or onlyagainst fields covered by indexes. Otherwise, an exclusive write lock will beacquired over the full table for the duration of the transaction.

Automatic typecasting can cause unexpected results¶

When performing a query on a string type, but with an integer value, MySQL willcoerce the types of all values in the table to an integer before performing thecomparison. If your table contains the values'abc','def' and youquery forWHEREmycolumn=0, both rows will match. Similarly,WHEREmycolumn=1will match the value'abc1'. Therefore, string type fields included in Djangowill always cast the value to a string before using it in a query.

If you implement custom model fields that inherit fromField directly, are overridingget_prep_value(), or useRawSQL,extra(), orraw(), you should ensure that you performappropriate typecasting.

Substring matching and case sensitivity¶

For all SQLite versions, there is some slightly counterintuitive behavior whenattempting to match some types of strings. These are triggered when using theiexact orcontains filters in Querysets. The behaviorsplits into two cases:

1. For substring matching, all matches are done case-insensitively. That is afilter such asfilter(name__contains="aa") will match a name of"Aabb".

2. For strings containing characters outside the ASCII range, all exact stringmatches are performed case-sensitively, even when the case-insensitive optionsare passed into the query. So theiexact filter will behave exactlythe same as theexact filter in these cases.

Some possible workarounds for this aredocumented at sqlite.org, but theyaren’t utilized by the default SQLite backend in Django, as incorporating themwould be fairly difficult to do robustly. Thus, Django exposes the defaultSQLite behavior and you should be aware of this when doing case-insensitive orsubstring filtering.

Decimal handling¶

SQLite has no real decimal internal type. Decimal values are internallyconverted to theREAL data type (8-byte IEEE floating point number), asexplained in theSQLite datatypes documentation, so they don’t supportcorrectly-rounded decimal floating point arithmetic.

“Database is locked” errors¶

SQLite is meant to be a lightweight database, and thus can’t support a highlevel of concurrency.OperationalError:databaseislocked errors indicatethat your application is experiencing more concurrency thansqlite canhandle in default configuration. This error means that one thread or process hasan exclusive lock on the database connection and another thread timed outwaiting for the lock the be released.

Python’s SQLite wrapper hasa default timeout value that determines how long the second thread is allowed towait on the lock before it times out and raises theOperationalError:databaseislocked error.

If you’re getting this error, you can solve it by:

• Switching to another database backend. At a certain point SQLite becomestoo “lite” for real-world applications, and these sorts of concurrencyerrors indicate you’ve reached that point.

• Rewriting your code to reduce concurrency and ensure that databasetransactions are short-lived.

• Increase the default timeout value by setting thetimeout databaseoption:

  "OPTIONS":{# ..."timeout":20,# ...}

  This will make SQLite wait a bit longer before throwing “database is locked”errors; it won’t really do anything to solve them.

"OPTIONS":{# ..."transaction_mode":"IMMEDIATE",# ...}

QuerySet.select_for_update() not supported¶

SQLite does not support theSELECT...FORUPDATE syntax. Calling it willhave no effect.

Isolation when usingQuerySet.iterator()¶

There are special considerations described inIsolation In SQLite whenmodifying a table while iterating over it usingQuerySet.iterator(). Ifa row is added, changed, or deleted within the loop, then that row may or maynot appear, or may appear twice, in subsequent results fetched from theiterator. Your code must handle this.

Enabling JSON1 extension on SQLite¶

To useJSONField on SQLite, you need to enable theJSON1 extension on Python’ssqlite3 library. If the extension isnot enabled on your installation, a system error (fields.E180) will beraised.

To enable the JSON1 extension you can follow the instruction onthe wiki page.

Setting pragma options¶

Pragma options can be set upon connection by using theinit_command intheOPTIONS part of your database configuration inDATABASES. The example below shows how to enable extra durability ofsynchronous writes and change thecache_size:

DATABASES={"default":{"ENGINE":"django.db.backends.sqlite3",# ..."OPTIONS":{"init_command":"PRAGMA synchronous=3; PRAGMA cache_size=2000;",},}}

Connecting to the database¶

To connect using the service name of your Oracle database, yoursettings.pyfile should look something like this:

DATABASES={"default":{"ENGINE":"django.db.backends.oracle","NAME":"xe","USER":"a_user","PASSWORD":"a_password","HOST":"","PORT":"",}}

In this case, you should leave bothHOST andPORT empty.However, if you don’t use atnsnames.ora file or a similar naming methodand want to connect using the SID (“xe” in this example), then fill in bothHOST andPORT like so:

DATABASES={"default":{"ENGINE":"django.db.backends.oracle","NAME":"xe","USER":"a_user","PASSWORD":"a_password","HOST":"dbprod01ned.mycompany.com","PORT":"1540",}}

You should either supply bothHOST andPORT, or leaveboth as empty strings. Django will use a different connect descriptor dependingon that choice.

"NAME":"localhost:1521/orclpdb1"

"NAME":("(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))""(CONNECT_DATA=(SERVICE_NAME=orclpdb1)))")

Threaded option¶

If you plan to run Django in a multithreaded environment (e.g. Apache using thedefault MPM module on any modern operating system), then youmust setthethreaded option of your Oracle database configuration toTrue:

"OPTIONS":{"threaded":True,}

Failure to do this may result in crashes and other odd behavior.

INSERT … RETURNING INTO¶

By default, the Oracle backend uses aRETURNINGINTO clause to efficientlyretrieve the value of anAutoField when inserting new rows. This behaviormay result in aDatabaseError in certain unusual setups, such as wheninserting into a remote table, or into a view with anINSTEADOF trigger.TheRETURNINGINTO clause can be disabled by setting theuse_returning_into option of the database configuration toFalse:

"OPTIONS":{"use_returning_into":False,}

In this case, the Oracle backend will use a separateSELECT query toretrieveAutoField values.

Naming issues¶

Oracle imposes a name length limit of 30 characters. To accommodate this, thebackend truncates database identifiers to fit, replacing the final fourcharacters of the truncated name with a repeatable MD5 hash value.Additionally, the backend turns database identifiers to all-uppercase.

To prevent these transformations (this is usually required only when dealingwith legacy databases or accessing tables which belong to other users), usea quoted name as the value fordb_table:

classLegacyModel(models.Model):classMeta:db_table='"name_left_in_lowercase"'classForeignModel(models.Model):classMeta:db_table='"OTHER_USER"."NAME_ONLY_SEEMS_OVER_30"'

Quoted names can also be used with Django’s other supported databasebackends; except for Oracle, however, the quotes have no effect.

When runningmigrate, anORA-06552 error may be encountered ifcertain Oracle keywords are used as the name of a model field or thevalue of adb_column option. Django quotes all identifiers usedin queries to prevent most such problems, but this error can stilloccur when an Oracle datatype is used as a column name. Inparticular, take care to avoid using the namesdate,timestamp,number orfloat as a field name.

NULL and empty strings¶

Django generally prefers to use the empty string ('') rather thanNULL, but Oracle treats both identically. To get around this, theOracle backend ignores an explicitnull option on fields thathave the empty string as a possible value and generates DDL as ifnull=True. When fetching from the database, it is assumed thataNULL value in one of these fields really means the emptystring, and the data is silently converted to reflect this assumption.

TextField limitations¶

The Oracle backend storesTextFields asNCLOB columns. Oracle imposessome limitations on the usage of such LOB columns in general:

• LOB columns may not be used as primary keys.

• LOB columns may not be used in indexes.

• LOB columns may not be used in aSELECTDISTINCT list. This means thatattempting to use theQuerySet.distinct method on a model thatincludesTextField columns will result in anORA-00932 error whenrun against Oracle. As a workaround, use theQuerySet.defer method inconjunction withdistinct() to preventTextField columns from beingincluded in theSELECTDISTINCT list.