Custom primary key format for Django models

When developing customer facing web applications usually you want to have some unique id for each object. By default django uses integer ID that is autoincremented by the database. However sometimes you want to expose this ID to your customer but you don’t want to expose how many objects of this type are there in your DB. Imagine case of e-commerce that want to show order number to customer but doesn’t want to reveal amount of orders to everyone in the world(especially competition). There are a few ways to accomplish that:

Use UUID

Valid option if you don’t need to spell ID over the phone for example. You can add UUIDField to the model as separate field or you can replace default id with it- however it’s pretty lenghty and using it with verbal communication is rather painful.

Use separate fields for internal ID and for external ID

Another valid option- however if you want to have second AutoField in Django model, you need to do some workaround about it(http://schinckel.net/2015/05/17/django-second-autofield/). Also, second(a.k.a external) ID needs to have some structure, different from just autoincremented field because otherwise you’ll expose information about number of orders this way or another.

Use dynamically generated external ID

Do you keep internal ID as regular AutoField but you generate something else dynamically- perhaps date-based or/and using a hashing function. Problem with this approach is that it may be difficult to spot all edge cases of your custom external ID function.

User Postgres features to have custom ID format

This way can be applied to internal or external ID- in this example I’ll use internal ID that will be exposed to end customer but it can be also separate field, this doesn’t matter a lot. Format that I want to achieve is:
YYYYMMDD[4 digits number]- so for 2nd order placed on Feb 15th it should be 201802150002 – this way only information exposed is amount of orders placed within given day. Of course someone may place an order each day just before midnight but this is enough in most cases to hide information about amount of orders in general.

First you need to replace old id field(AutoField) with a BigAutoField:
id = models.BigAutoField(primary_key=True)

 

this will make sure that Django will get value of this field from Postgres and will not to try to update it with Python code.

And now fun begins- you need to add an empty migration to make this field to be autoupdated with our custom format. So first run

./manage.py makemigrations yourapp --empty

and later make sure that it’s content looks like that:

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import migrations


class Migration(migrations.Migration):

   dependencies = [
       (yourapp, '0002_auto_123123_2127'),
   ]

   operations = [
       migrations.RunSQL("""
           CREATE FUNCTION before_insert_autoid() RETURNS TRIGGER AS $$
           DECLARE
             base_val  BIGINT;
             last_value        BIGINT;
           BEGIN
             -- base_val is the minimal value of the sequence for the current day: YYYYMMDD0001
             base_val := (EXTRACT(YEAR FROM now())::BIGINT * 100000000) +
                         (EXTRACT(MONTH FROM now())::BIGINT * 1000000) +
                         (EXTRACT(DAY FROM now())::BIGINT * 10000) + 1;

             BEGIN
                 -- So if the sequence is less, then update it
                
                 last_value := currval('yourapp_yourmodel_id_seq');
                
                 IF (last_value < base_val) THEN
                   PERFORM setval('yourapp_yourmodel_id_seq', base_val);
                 END IF;
             EXCEPTION
                 -- If you can’t get currval(ie. another session is asking for currval)
                 WHEN SQLSTATE '55000' THEN
                 SELECT last_value INTO last_value FROM yourapp_yourmodel_id_seq LIMIT 1;
                 IF (last_value < base_val) THEN
                   PERFORM setval('yourapp_yourmodel_id_seq', base_val);
                 END IF;
             END;

             -- Now assign the order id and continue with the insert
             NEW.id := currval('yourapp_yourmodel_id_seq');
             RETURN NEW;
           END; $$ LANGUAGE plpgsql;

           CREATE TRIGGER tr_bi_order
             BEFORE INSERT ON yourapp_yourmodel
             FOR EACH ROW EXECUTE PROCEDURE before_insert_autoid();
           """)
   ]

This will make sure that before each save of your model, ID is generated with desired format and that it’s unique by day.

Of course it has it’s own cons- it’s not as performant as just having autoincremented ID and needs Postgres to be used as database(I didn’t check other solutions to be honest) also in testing. Also, in case of problems, it’s not a basic SQL that you need to know to fix- however this doesn’t seem to be a real con for me.