11.11 Inserts: returned keys, conflicts and batch sizes

How many rows to a statement: bulk_batch_size

QuerySet._batched_insert asks BaseDatabaseOperations.bulk_batch_size, before it writes any of its batches, how many instances one statement may carry, given the fields and the instances, and takes the smaller of the answer and the caller’s batch size, and never less than one. No backend of Django’s overrides bulk_batch_size. Where the features give no BaseDatabaseFeatures.max_query_params, or there are no fields, it answers with the number of instances; otherwise it divides the limit by the number of columns, a CompositePrimaryKey counting as its columns, and rounds down, one parameter to each value of each row.

SQLite’s limit is 32766 in this build, asked of the driver’s connection at each read (What a database can do: BaseDatabaseFeatures), and Oracle’s 65535; PostgreSQL’s is None, or 65535 where parameters are bound on the server under psycopg 3 (PostgreSQL), and MySQL’s None. Without a limit, and without a batch size from the caller, each of the two groups bulk_create sorts its instances into, those with a key and those without, goes in one statement. In the recording below, made from a running Django on SQLite, a line at the left margin is Python as it was run, with -> before its value; the lines under it are the calls it set going, nested, with -> before what each returned; and SQL begins a statement as Django handed it to its cursor. Five ports, with batch_size=2:

made = Port.objects.bulk_create([Port(name=f'Skerry {n}', country='Faroe') for n in range(1, 6)], batch_size=2)
  QuerySet.bulk_create([5 of Port], batch_size=2)
    QuerySet._batched_insert([5 of Port], batch_size=2)
      BaseDatabaseOperations.bulk_batch_size(fields=[name, country], objs=[5 of Port])
        DatabaseFeatures.max_query_params  ->  32766
        DatabaseFeatures.max_query_params  ->  32766
        -> 16383
      Atomic.__enter__  (an Atomic made with savepoint=False)
        SQL BEGIN
      QuerySet._insert([2 of Port])
        SQLInsertCompiler.execute_sql
          SQL INSERT INTO "fleet_port" ("name", "country") VALUES (%s, %s), (%s, %s) RETURNING "fleet_port"."id" with params ('Skerry 1', 'Faroe', 'Skerry 2', 'Faroe')
...
[port.pk for port in made]  ->  [13, 14, 15, 16, 17]

bulk_batch_size read the limit twice, to see that there is one and to divide it, and each read asked the driver’s connection again: two columns to a row make 16383 rows to a statement, and the caller’s two was the smaller. The same method sizes the batches of QuerySet.bulk_update (bulk_create and bulk_update), of QuerySet.in_bulk and of the collector of a delete. The five ports made three batches, and _batched_insert writes more than one batch inside an atomic block, so that they commit together.

The statement’s words and rows: insert_statement and bulk_insert_sql

SQLInsertCompiler.as_sql asks first for the opening words, BaseDatabaseOperations.insert_statement, given the conflict mode: "INSERT INTO" in the base class, which SQLite’s and MySQL’s change only where a conflict is to be ignored. Then, by whether columns are to come back and by the returning features and BaseDatabaseFeatures.has_bulk_insert, it writes one statement for all the rows or one for each row; that choice is the compiler’s, and INSERT, UPDATE and DELETE: the insert, update and delete compilers has it.

Where the rows go in one statement, BaseDatabaseOperations.bulk_insert_sql writes them, given the fields and the rows of placeholders: "VALUES" and each row in parentheses, in the base class. PostgreSQL’s DatabaseOperations.bulk_insert_sql writes "SELECT * FROM" and an array for each column where its own compiler has sent an InsertUnnest, and the base class’s rows otherwise. Oracle’s writes each row as a "SELECT" of its placeholders (followed by " FROM DUAL" before Oracle 23), wrapped where the field’s type calls for it in a conversion from BulkInsertMapper, and selects from the rows joined by "UNION ALL"; its comments say that the union stands in a subquery because Oracle adds a sequence’s next value to an insert into a table with an identity column, and a sequence’s next value cannot be used with "UNION" (Oracle).

Here two ports are compiled by each backend’s own compiler through insert_sql, a helper of the recording’s own, with the fields to return set as QuerySet._batched_insert sets them, where the backend can return the rows of a bulk insert. PostgreSQL 17, MySQL 8.4 and Oracle 23 were assumed, with the drivers and no server, and nothing was sent:

ports = [Port(name='Hull', country='England'), Port(name='Whitby', country='England')]
insert_sql(sqlite, ports)  ->  [('INSERT INTO "fleet_port" ("name", "country") VALUES (%s, %s), (%s, %s) RETURNING "fleet_port"."id"', ('Hull', 'England', 'Whitby', 'England'))]
insert_sql(postgresql, ports)  ->  [('INSERT INTO "fleet_port" ("name", "country") SELECT * FROM UNNEST((%s)::varchar[], (%s)::varchar[]) RETURNING "fleet_port"."id"', (['Hull', 'Whitby'], ['England', 'England']))]
insert_sql(mysql, ports)  ->  [('INSERT INTO `fleet_port` (`name`, `country`) VALUES (%s, %s), (%s, %s)', ('Hull', 'England', 'Whitby', 'England'))]
insert_sql(oracle, ports)  ->  [('INSERT INTO "FLEET_PORT" ("NAME", "COUNTRY") SELECT * FROM (SELECT %s col_0, %s col_1 UNION ALL SELECT %s, %s)', ('Hull', 'England', 'Whitby', 'England'))]

SQLite’s is the base class’s rows with "RETURNING" after them. PostgreSQL’s carries four values in two parameters, each a list of one column’s values. MySQL’s asks for nothing back, and the instances get no keys. Oracle’s is its union, and asks for nothing back either: its features let it return the columns of one row, not the rows of a bulk insert. Two voyages due to sail, compiled for Oracle 19 with no server by two more of the recording’s helpers, show both the conversions and " FROM DUAL":

full(inserts_for(oracle19, due, ['ship', 'sailed']))  ->  [('INSERT INTO "FLEET_VOYAGE" ("SHIP_ID", "SAILED") SELECT * FROM (SELECT TO_NUMBER(%s) col_0, TO_DATE(%s) col_1 FROM DUAL UNION ALL SELECT TO_NUMBER(%s), TO_DATE(%s) FROM DUAL)', (1, datetime.date(2026, 5, 1), 2, datetime.date(2026, 5, 9)))]

Conflicts: insert_statement and on_conflict_suffix_sql

Three features say what a backend can do with a new row that collides with an old one: BaseDatabaseFeatures.supports_ignore_conflicts, pass over the new row; BaseDatabaseFeatures.supports_update_conflicts, update the old row from the new; and BaseDatabaseFeatures.supports_update_conflicts_with_target, name in that update the columns whose collision sets it off, the conflict’s target. The base class has the first true and the others false. QuerySet._check_bulk_create_options reads them before anything is written, refuses what the backend cannot do, and answers with the mode, OnConflict.IGNORE or OnConflict.UPDATE (bulk_create and bulk_update). The compiler gives the mode to insert_statement and to BaseDatabaseOperations.on_conflict_suffix_sql, the second with the columns to update and the target’s; the base class’s suffix is empty.

On SQLite, over the ships, whose name and home port are unique together:

Ship.objects.bulk_create([Ship(name='Petrel', tonnage=480, home_id=1), Ship(name='Fulmar', tonnage=650, home_id=1)], ignore_conflicts=True)
  DatabaseOperations.insert_statement(on_conflict=OnConflict.IGNORE)  ->  'INSERT OR IGNORE INTO'
  DatabaseOperations.on_conflict_suffix_sql(on_conflict=OnConflict.IGNORE)  ->  ''
  SQL INSERT OR IGNORE INTO "fleet_ship" ("name", "tonnage", "home_id", "captain_id") VALUES (%s, %s, %s, %s), (%s, %s, %s, %s) with params ('Petrel', 480, 1, None, 'Fulmar', 650, 1, None)
Ship.objects.bulk_create([Ship(name='Petrel', tonnage=480, home_id=1)], update_conflicts=True, unique_fields=['name', 'home'], update_fields=['tonnage'])
  DatabaseOperations.insert_statement(on_conflict=OnConflict.UPDATE)  ->  'INSERT INTO'
  DatabaseOperations.on_conflict_suffix_sql(on_conflict=OnConflict.UPDATE)  ->  'ON CONFLICT("name", "home_id") DO UPDATE SET "tonnage" = EXCLUDED."tonnage"'
  SQL INSERT INTO "fleet_ship" ("name", "tonnage", "home_id", "captain_id") VALUES (%s, %s, %s, %s) ON CONFLICT("name", "home_id") DO UPDATE SET "tonnage" = EXCLUDED."tonnage" RETURNING "fleet_ship"."id" with params ('Petrel', 480, 1, None)
list(Ship.objects.order_by('pk').values_list('name', 'tonnage'))  ->  [('Petrel', 480), ('Gannet', 1200), ('Fulmar', 650)]
  SQL SELECT "fleet_ship"."name" AS "name", "fleet_ship"."tonnage" AS "tonnage" FROM "fleet_ship" ORDER BY "fleet_ship"."id" ASC

The first statement passed over the Petrel, a ship of 480 tons with home port 1 already in the table, and added the Fulmar. It asked for nothing back, since QuerySet._batched_insert asks for the returning fields only where conflicts are not being ignored. The second, which wrote the Petrel’s own tonnage back, named its target and asked for the key, as an insert with no conflict does.

MySQL’s DatabaseOperations.on_conflict_suffix_sql gives the new row an alias on MySQL, the comment saying that MySQL does not support "VALUES()" there, and uses "VALUE()" on MariaDB. The Petrel’s update, pair[:1] being the Petrel alone of the two ships above, compiled for MySQL 8.4 and for MariaDB 11.4 with no server; only MariaDB’s asks for the key back:

full(inserts_for(mysql, pair[:1], ['name', 'tonnage', 'home', 'captain'], on_conflict=OnConflict.UPDATE, update_fields=['tonnage'], unique_fields=['name', 'home']))  ->  [('INSERT INTO `fleet_ship` (`name`, `tonnage`, `home_id`, `captain_id`) VALUES (%s, %s, %s, %s) AS new ON DUPLICATE KEY UPDATE `tonnage` = new.`tonnage`', ('Petrel', 480, 1, None))]
full(inserts_for(mariadb, pair[:1], ['name', 'tonnage', 'home', 'captain'], on_conflict=OnConflict.UPDATE, update_fields=['tonnage'], unique_fields=['name', 'home']))  ->  [('INSERT INTO `fleet_ship` (`name`, `tonnage`, `home_id`, `captain_id`) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE `tonnage` = VALUE(`tonnage`) RETURNING `fleet_ship`.`id`', ('Petrel', 480, 1, None))]

What comes back: returning_columns, fetch_returned_rows and last_insert_id

On SQLite, PostgreSQL and MariaDB a save, and a bulk_create that does not ignore conflicts, can bring back the keys and the columns the database filled. On MySQL and Oracle a bulk_create brings back nothing. A save on MySQL gets its key from BaseDatabaseOperations.last_insert_id where the key is an AutoField; a save on Oracle brings its columns back unless the alias’s "OPTIONS" turn "use_returning_into" off. Model._save_table and QuerySet._batched_insert choose what to ask for by the returning features (Saving an instance). Once the statements have run, SQLInsertCompiler.execute_sql has BaseDatabaseOperations.fetch_returned_rows read the rows from the cursor where the backend can return them, by BaseDatabaseFeatures.can_return_rows_from_bulk_insert for several rows and BaseDatabaseFeatures.can_return_columns_from_insert for one; otherwise, where the first field to return is an AutoField, it makes one row of what last_insert_id returns, and where it is not, returns nothing. What comes back goes through the returned columns’ converters (The compiler: as_sql, execute_sql and results_iter).

BaseDatabaseOperations.returning_columns writes "RETURNING" and each column qualified by its table, with no parameters, and fetch_returned_rows asks the cursor for all its rows. Oracle’s DatabaseOperations.returning_columns adds "INTO" and a variable for each column, for the database to fill, and its DatabaseOperations.fetch_returned_rows reads them back (Oracle).

The base class’s last_insert_id is the driver’s cursor’s last row id, which every backend but Oracle’s keeps; Oracle’s asks the database for the sequence behind the key’s column and then for its current value. Here SQLite’s feature was switched off on the connection for one save, so that SQLite took MySQL’s road:

connection.features.can_return_columns_from_insert = False
stromness = Port.objects.create(name='Stromness', country='Scotland')
  SQLInsertCompiler.execute_sql
    SQLInsertCompiler.as_sql
    SQL INSERT INTO "fleet_port" ("name", "country") VALUES (%s, %s) with params ['Stromness', 'Scotland']
    BaseDatabaseOperations.last_insert_id(cursor, table_name='fleet_port', pk_name='id')
      CursorWrapper.__getattr__('lastrowid')
      -> 18
    CursorWrapper.__getattr__('close')
stromness.pk  ->  18
del connection.features.can_return_columns_from_insert
connection.features.can_return_columns_from_insert  ->  True

The statement has no "RETURNING", and the last row id was read through Django’s cursor, which hands an attribute it does not have to the driver’s (Cursors: executing a statement, the query log and execute wrappers). Where a key comes back this way and other fields were asked for too, they cannot come with it: Model._assign_returned_values sets the key and takes the others out of the instance’s dictionary, which its comment calls stale, so that each is fetched when next read.

BaseDatabaseOperations.returning_columns and BaseDatabaseOperations.fetch_returned_rows serve an update’s "RETURNING" too, which SQLUpdateCompiler.as_sql writes and SQLUpdateCompiler.execute_returning_sql reads back for Model.save where the backend has BaseDatabaseFeatures.can_return_rows_from_update: SQLite and PostgreSQL do, Oracle does unless "use_returning_into" is off, and MySQL’s features class, for MariaDB too, does not (INSERT, UPDATE and DELETE: the insert, update and delete compilers).

A value’s placeholder: binary_placeholder_sql and modify_insert_params

SQLInsertCompiler.field_as_sql writes each value’s placeholder: a field with a placeholder method writes its own, an expression is compiled, and anything else is "%s" with the value as its parameter. BinaryField.get_placeholder_sql asks BaseDatabaseOperations.binary_placeholder_sql, given the value and the compiler, which compiles an expression and writes "%s" for anything else; MySQL’s writes "_binary %s", its syntax for binary content, for a value that is neither None nor an expression. The update compiler asks a field for its placeholder the same way.

BaseDatabaseOperations.modify_insert_params is given each placeholder and its parameters last, and returns the parameters as they are; only Oracle’s spatial backend overrides it (Spatial backends: PostGIS, SpatiaLite, MySQL and Oracle).

A key the database makes: pk_default_value and autoinc_sql

BaseDatabaseOperations.pk_default_value is the value of an insert with no field to write, which the compiler’s comment says can only be a model whose primary key is a single auto field: the statement names the key’s column, and each instance’s row holds this value, "DEFAULT" in the base class and so on PostgreSQL, "NULL" on SQLite, MySQL and Oracle. BaseDatabaseOperations.autoinc_sql is a hook for a backend that needs statements after a table is made to give an auto field its values; no backend of Django’s overrides it, their auto fields’ column types being identity columns on PostgreSQL and Oracle, "AUTO_INCREMENT" on MySQL and "AUTOINCREMENT" on SQLite (Column types: from a field to a column).

The backends’ own compilers

A query names its compiler’s class, and the operations object takes it by that name from the module BaseDatabaseOperations.compiler_module names, django.db.models.sql.compiler in the base class (The SQL a backend writes: BaseDatabaseOperations). PostgreSQL’s and MySQL’s name modules of their own, and each module imports the base’s compilers that it does not change and lists them beside its own, so that every name a query may ask for is there.

PostgreSQL: InsertUnnest and the insert compiler

PostgreSQL’s SQLInsertCompiler overrides assemble_as_sql alone, to send the rows of a bulk insert as arrays through "UNNEST", one parameter to a column, which the comment says reduces the time spent planning the query. In place of the rows of placeholders it returns an InsertUnnest, a list of one placeholder for each column, each cast to an array of the column’s type, which writes itself as "UNNEST" of them; and in place of the rows of parameters a single row, of a list of values for each column. The type is the field’s db_type cut at its first parenthesis, so that, the comment says, no value is truncated: an array of "varchar", not of "varchar(60)". BaseDatabaseOperations.bulk_batch_size knows nothing of this, and counts a parameter for each value all the same.

The comments name the cases that keep the base class’s rows: a single row, which needs as many placeholders either way; a field of None, the mark of a row inserted with the key’s default alone; a field with a placeholder method, whose placeholder may depend on the value where an array needs one for all; a field whose internal type, or its target’s for a relation, has no entry in the connection’s data_types, arrays and geometries being known trouble; and any value that is an expression, which cannot stand in an array of literal values.

MySQL: the delete and update compilers

MySQL’s SQLDeleteCompiler.as_sql writes a delete over joined tables in MySQL’s own syntax, which its comment prefers to the base class’s subquery on the key since MySQL and MariaDB plan it better. It hands the delete to the base class where the query has one alias, or where the condition compares an aggregate or a window function, which the syntax cannot carry, the comment says, having no "GROUP BY", no "HAVING" and no room for the subquery that stands in for "QUALIFY".

MySQL’s SQLUpdateCompiler.as_sql adds "ORDER BY" to the base class’s statement where the queryset was given an ordering with order_by. The documentation gives the use, updating a unique field in an order that avoids a conflict on the way, and says the ordering is ignored on other databases. Where a column of the ordering belongs to a joined table, or compiling it raises FieldError, the ordering is dropped and the statement sent without it. A delete of the Petrel’s crew across a join, and an update of every ship heaviest first, compiled for MySQL 8.4 with no server:

delete_sql(petrels_crew, mysql)  ->  ('DELETE `crew_sailor` FROM `crew_sailor` INNER JOIN `fleet_ship` ON (`crew_sailor`.`ship_id` = `fleet_ship`.`id`) WHERE `fleet_ship`.`name` = %s', ('Petrel',))
...
update_sql(heaviest_first, mysql, tonnage=models.F('tonnage') + 1)  ->  ('UPDATE `fleet_ship` SET `tonnage` = (`fleet_ship`.`tonnage` + %s) ORDER BY `fleet_ship`.`tonnage` DESC', (1,))