Django Auto Sequence Number with Alpha Numeric key & Uniuqe with ORM

Using Django Using the ORM
1

Am working on an invoice app building, which has a requirement that invoice numbers should be unique & alphanumeric with sequence should be generated in the table.
I Am using PostgreSQL. eg: INV-0001 , INV-0002 etc…

In the older way without ORM or other tech-stack, I used to create a table sequence number and trigger in PostgreSQL, something similar, which will be handled at the database level, which is high concurrent no use will get repeated number and good performance.

CREATE SEQUENCE invoice_number_seq;
CREATE TABLE invoices (
    id SERIAL PRIMARY KEY,
    invoice_number VARCHAR(20) UNIQUE
);
CREATE OR REPLACE FUNCTION generate_invoice_number()
RETURNS TRIGGER AS $$
BEGIN
    NEW.invoice_number := 'INV-' || LPAD(nextval('invoice_number_seq')::TEXT, 3, '0');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_invoice_number
BEFORE INSERT ON invoices
FOR EACH ROW
EXECUTE FUNCTION generate_invoice_number();

But in Django way ORM I believe we can not create a trigger but we can implement this in a different way

To avoid race conditions and add atomic transaction

from django.db import models, transaction

class ControlSequence(models.Model):
    name = models.CharField(max_length=100, unique=True)
    sequence_number = models.IntegerField(default=0)
    prefix = models.CharField(max_length=10)

    @classmethod
    def get_next_number(cls, sequence_name):
        with transaction.atomic():
            sequence = cls.objects.select_for_update().get(name=sequence_name)
            sequence.sequence_number += 1
            sequence.save()
            return sequence.sequence_number

This will be called in all other model managers as below before saving

# Get the next invoice number
next_invoice_number = ControlSequence.get_next_number('invoice')
print(f'Next invoice number: {next_invoice_number}')

using above way , I do not need to create a trigger in db,
I can stick to Django and also I can control prefixes based on client requirements, which is common in most ERP systems like JDE, SAP, etc…

My question is

  1. SInce am new to Django, I need to know, if is it scalable meaning it can be good enough like a trigger in DB, and make sure no ID repeats & works concurrently.
  2. And how do we do this for bulk_create or bulk_udpate the same concept? if it is a db trigger it will take care while bulk_save. how do we handle efficiency in bulk_save without performance bottleneck?
  3. How do we handle in Admin portal
2

Your concept does not avoid race conditions. An atomic transaction only means it’s an “all or nothing” update, it does not prevent two different rows from being updated concurrently. You would need to lock the table for that.

My recommendations in this type of situation is to not store the invoice number with the alphabetic prefix. Keep the invoice number as just the number, and use model methods to create the “full” invoice number when needed.

Otherwise, yes - create the trigger in the database. (If you’re using PostgreSQL, you can use django-pgtrigger to help with that. Or you can just create the trigger yourself. There’s no reason to get the ORM involved. If you want, you could even add the trigger-creation as a custom Data Migration using the RunSQL command.

1 Like
3

Ok got it, I can use the trigger for better performance

but one quick question in my code!
since I use select_for_update it locks the db row, so this will ensure preventing concurrent users from getting the same ID for the invoice right? or am I wrong?

sequence = cls.objects.select_for_update().get(name=sequence_name)
4

Ok, yes, I misread the code originally.

You are not wrong, I was.

6

Thanks for this post.

Please on this line

@classmethod
    def get_next_number(cls, sequence_name):

What does cls represent? I tried to run this code but it is telling me MyModel.models.ControlSequence.DoesNotExist: ControlSequence matching query does not exist.

Where am I getting it wrong. Or do I need to Initiatialise the ControlSequence Table?
Thanks.

7

I think I have been able to solve it. I initialised the table and it worked. Thanks.