Why Doesn't Django Reset Sequences in SQLite3?

Question

Why does Django allow you to reset the sequences (AutoID) fields on postgres and other DBMS's but not SQLite3?

Looking at the source code for the sql_flush method in django/db/backends/sqlite3/base.py, there is a comment that says:

Note: No requirement for reset of auto-incremented indices (cf. other sql_flush() implementations). Just return SQL at this point

I have a few tests where I load in fixture files that depend on absolute primary key ids. Because Django doesn't reset the auto id field for SQLite, these fixtures do not load correctly.

It appears that it is somewhat trivial to reset the auto id columns in sqlite: How can I reset a autoincrement sequence number in sqlite

Answer 1

You can monkey-patch sql_flush as follows to reset SQLite sequences:

from django.db.backends.sqlite3.operations import DatabaseOperations
from django.db import connection


def _monkey_patch_sqlite_sql_flush_with_sequence_reset():
    original_sql_flush = DatabaseOperations.sql_flush

    def sql_flush_with_sequence_reset(self, style, tables, sequences, allow_cascade=False):
        sql_statement_list = original_sql_flush(self, style, tables, sequences, allow_cascade)
        if tables:
            # DELETE FROM sqlite_sequence WHERE name IN ($tables)
            sql = '%s %s %s %s %s %s (%s);' % (
                style.SQL_KEYWORD('DELETE'),
                style.SQL_KEYWORD('FROM'),
                style.SQL_TABLE(self.quote_name('sqlite_sequence')),
                style.SQL_KEYWORD('WHERE'),
                style.SQL_FIELD(self.quote_name('name')),
                style.SQL_KEYWORD('IN'),
                ', '.join(style.SQL_FIELD(f"'{table}'") for table in tables)
            )
            sql_statement_list.append(sql)
        return sql_statement_list

    DatabaseOperations.sql_flush = sql_flush_with_sequence_reset

You would use it as follows for example in a TransactionTestCase:

from django.test import TransactionTestCase


class TransactionTestCaseWithSQLiteSequenceReset(TransactionTestCase):

    reset_sequences = True

    @classmethod
    def setUpClass(cls):
        super().setUpClass()
        if connection.vendor == 'sqlite':
            _monkey_patch_sqlite_sql_flush_with_sequence_reset()

This assures that tests that depend on fixed primary keys work with both SQLite and other database backends like PostgreSQL. However, see Django documentation for caveats regarding reset_sequences. For one thing, it makes tests slow.