Django's default, implicit primary keys aren't guaranteed to be sequential.
If a transaction inserts a row and then is rolled back, the sequence counter isn't rolled back for performance reasons, creating a gap in primary keys.
This can cause compliance issues for some use cases such as accounting.
This risk isn't well known. Since most transactions succeed, values look sequential. Gaps will only be revealed by audits.
django-sequences provides a get_next_value
function which is designed to
be used as follows:
from django.db import transaction from sequences import get_next_value from invoices.models import Invoice with transaction.atomic(): Invoice.objects.create(number=get_next_value('invoice_numbers'))
The guarantees of django-sequences only apply if you call get_next_value
and save its return value to the database within the same transaction!
django-sequences is compatible with Django 1.11 (LTS), 2.1 and 2.2.
Install django-sequences:
$ pip install django-sequences
Add it to the list of applications in your project's settings:
INSTALLED_APPS += ['sequences.apps.SequencesConfig']
Run migrations:
$ django-admin migrate
>>> from sequences import get_next_value
This function generates a gapless sequence of integer values:
>>> get_next_value() 1 >>> get_next_value() 2 >>> get_next_value() 3
It supports multiple independent sequences:
>>> get_next_value('cases') 1 >>> get_next_value('cases') 2 >>> get_next_value('invoices') 1 >>> get_next_value('invoices') 2
The first value defaults to 1. It can be customized:
>>> get_next_value('customers', initial_value=1000) # pro growth hacking
The initial_value
parameter only matters when get_next_value
is called
for the first time for a given sequence — assuming the corresponding database
transaction gets committed; as discussed above, if the transaction is rolled
back, the generated value isn't consumed. It's also possible to initialize a
sequence in a data migration and not use initial_value
in actual code.
Sequences can loop:
>>> get_next_value('seconds', initial_value=0, reset_value=60)
When the sequence reaches reset_value
, it restarts at initial_value
.
In other works, it generates reset_value - 2
, reset_value - 1
,
initial_value
, initial_value + 1
, etc. In that case, each call to
get_next_value
must provide initial_value
when it isn't the default
and reset_value
.
Database transactions that call get_next_value
for a given sequence
are serialized. In other words, when you call get_next_value
in a
database transaction, other callers trying to get a value from the same
sequence block until the transaction completes, either with a commit or a
rollback. You should keep such transactions short to minimize the impact on
performance.
This is why databases default to a faster behavior that may create gaps.
Passing nowait=True
makes get_next_value
raise an exception instead of
blocking in this scenario. This is rarely useful. Also it doesn't work for the
first call. (Arguably this is a bug. Patches welcome.)
Calls to get_next_value
for distinct sequences don't interact with one
another.
Finally, passing using='...'
allows selecting the database on which the
current sequence value is stored. When this parameter isn't provided, it
defaults to the default database for writing models of the sequences
application. See "Multiple databases" below for details.
To sum up, the complete signature of get_next_value
is:
get_next_value( sequence_name='default', initial_value=1, reset_value=None, *, nowait=False, using=None, )
Under the hood, get_next_value
relies on the database's transactional
integrity to guarantee that each value is returned exactly once.
>>> from sequences import get_last_value
This function returns the last value generated by a sequence:
>>> get_last_value() None >>> get_next_value() 1 >>> get_last_value() 1 >>> get_next_value() 2 >>> get_last_value() 2
If the sequence hasn't generated a value yet, get_last_value
returns
None
.
It supports independent sequences like get_next_value
:
>>> get_next_value('cases') 1 >>> get_last_value('cases') 1 >>> get_next_value('invoices') 1 >>> get_last_value('invoices') 1
It accepts using='...'
for selecting the database on which the current
sequence value is stored, defaulting to the default database for reading
models of the sequences
application.
The complete signature of get_last_value
is:
get_last_value( sequence_name='default', *, using=None, )
get_last_value
is a convenient and fast way to tell how many values a
sequence generated but it makes no guarantees. Concurrent calls to
get_next_value
may produce unexpected results of get_last_value
.
>>> from sequences import Sequence
(not to be confused with sequences.models.Sequence
, a private API)
This class stores parameters for a sequence and provides get_next_value
and get_last_value
methods:
>>> claim_ids = Sequence('claims') >>> claim_ids.get_next_value() 1 >>> claim_ids.get_next_value() 2 >>> claim_ids.get_last_value() 2
This reduces the risk of errors when the same sequence is used in multiple places.
Instances of Sequence
are also infinite iterators:
>>> next(claim_ids) 3 >>> next(claim_ids) 4
The complete API is:
Sequence( sequence_name='default', initial_value=1, reset_value=None, *, using=None, ) Sequence.get_next_value( self, *, nowait=False ) Sequence.get_last_value( self, )
All parameters have the same meaning as in the get_next_value
and
get_last_value
functions.
You can run tests with:
$ make test
If you'd like to contribute, please open an issue or a pull request on GitHub!
django-sequences is tested on PostgreSQL, MySQL, Oracle, and SQLite.
MySQL only supports the nowait
parameter when it's MariaDB ≥ 8.0.1.
Applications that will only ever be deployed with an SQLite database don't
need django-sequences because SQLite's INTEGER PRIMARY KEY AUTOINCREMENT
fields are guaranteed to be sequential.
Since django-sequences relies on the database to guarantee transactional integrity, the current value for a given sequence must be stored in the same database as models containing generated values.
In a project that uses multiple databases, you must write a suitable database
router to create tables for the sequences
application on all databases
storing models containing sequential numbers.
Each database has its own namespace: a sequence with the same name stored in two databases will have independent counters in each database.
- Add the
get_last_value
function. - Add the
Sequence
class.
- Optimize performance on MySQL.
- Test on MySQL, SQLite and Oracle.
- Optimize performance on PostgreSQL ≥ 9.5.
- Provide looping sequences with
reset_value
.
- Add support for multiple databases.
- Add translations.
nowait
becomes keyword-only argument.- Drop support for Python 2.
- Initial stable release.