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

Sounds like your tests are broken, then. Nothing should depend on absolute ids. — Daniel Roseman, Jun 07 '14 at 15:31
@DanielRoseman Yes, everyone knows it's not best practice. In fact, it's easy to question the use of loading fixtures in tests. That's why things like FactoryGirl exist. However, in this one case, it's a perfectly fine solution. Django docs specify the use of resetting sequences in test cases https://docs.djangoproject.com/en/1.5/topics/testing/overview/#django.test.TransactionTestCase.reset_sequences. Even they mention it's a bad idea. However, my question is: even though it's a bad idea, why do they not support sqlite? — Scott Coates, Jun 07 '14 at 15:41
I don't know that sqlite supports sequence resest in the same way that say Oracle does. For instance I have found that the sqlite Auto Integer Primary Key resets when I empty the relevant table. I know under Oracle the Auto ID sequence exists as a completely separate entity which can be reset independently. — Tony Suffolk 66, Feb 06 '16 at 21:29

mrts · Answer 1 · 2019-07-23T18:43:44.240

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.

Ihor Pomaranskyy · Answer 2 · 2017-03-28T09:27:14.517

0

Maybe this snippet will help:

import os

from django.core.management import call_command
from django.db import connection
from django.utils.six import StringIO


def reset_sequences(app_name):
    os.environ['DJANGO_COLORS'] = 'nocolor'
    buf = StringIO()
    call_command('sqlsequencereset', app_name, stdout=buf)
    buf.seek(0)

    sql = "".join(buf.readlines())

    with connection.cursor() as cursor:
        cursor.execute(sql)

    print("Sequences for app '{}' reset".format(app_name))

edited Mar 28 '17 at 09:27

answered Mar 28 '17 at 09:10

Ihor Pomaranskyy

5,437
34
37

1

Django 3.0 no longer has `django.utils.six`; see https://stackoverflow.com/a/59420098/5139284. We can probably replace the line with `from io import StringIO`. – mic Sep 15 '20 at 16:56
Make sure you don't define `reset_sequences` as a class method of a TestCase, or else you get "assert not self.reset_sequences, 'reset_sequences cannot be used on TestCase instances' AssertionError: reset_sequences cannot be used on TestCase instances" – mic Sep 15 '20 at 17:10
For me, this always prints "No sequences found." – mic Sep 15 '20 at 17:53

Why Doesn't Django Reset Sequences in SQLite3?

2 Answers2