I actually have been using a combination of a numeric integer ID as the PK and a UUID as a lookup field to do routing lookups etc. for this purpose in Postgres backed app I'm working on.
I found this approach to be more trouble than it's worth and plan on switching to a UUID PK key and doing away with the integer sequence.
Here are the complications I ran into:
The libraries I'm using for the ORM and API are designed to work with a primary key for single record get access. For example they want you to do `resource.get(ID)` where ID is the primary key, however, I now have to do `resource.find({ where: { uuid: 'myuuid' }})`. This is for all resources on all pages.
In Postgres, the integer PK sequence has a state that keeps track of what number it's at. In certain circumstances it can get out of sequence and this can trip up migrations.
We already have created_at and updated_at fields and these are probably better for ordering than the sequencing.
Had I to do it again, I would just use UUIDv4 until it runs into issues and either date fields or a sequence where necessary. If anyone has better ideas I would be most grateful as this is something I go back and forth on.
I found this approach to be more trouble than it's worth and plan on switching to a UUID PK key and doing away with the integer sequence.
Here are the complications I ran into:
The libraries I'm using for the ORM and API are designed to work with a primary key for single record get access. For example they want you to do `resource.get(ID)` where ID is the primary key, however, I now have to do `resource.find({ where: { uuid: 'myuuid' }})`. This is for all resources on all pages.
In Postgres, the integer PK sequence has a state that keeps track of what number it's at. In certain circumstances it can get out of sequence and this can trip up migrations.
We already have created_at and updated_at fields and these are probably better for ordering than the sequencing.
Had I to do it again, I would just use UUIDv4 until it runs into issues and either date fields or a sequence where necessary. If anyone has better ideas I would be most grateful as this is something I go back and forth on.