There are number of options to use for ID fields in PostgreSQL each one of them have valid use cases of when to use or not use them. I won’t go into detail of the pro’s and cons of using which approach, because ultimately that is a debate for another time and place.
In my particular case, I needed to create a table that made use of UUID ( Universally Unique Identifiers), which is an identifier with a 128-bit quantity generated by an algorithm chosen to make it very unlikely that the same identifier will be generated by anyone else in the known universe using the same algorithm.
In distributed systems, these identifiers provide a better uniqueness guarantee than sequence generators, which are only unique within a single database.
What is a UUID?
A UUID is written as a sequence of lower-case hexadecimal digits, in several groups separated by hyphens, specifically a group of 8 digits followed by three groups of 4 digits followed by a group of 12 digits, for a total of 32 digits representing the 128 bits.
UUIDs are 36 character strings containing numbers, letters and dashes, designed to be globally unique.
The chances of a duplicate UUID are so low it is safe to assume each ID will be unique. Separate computers can generate UUIDs at the same time with no communication and still be confident the UUIDs are unique. Independent systems that use UUIDs can be safely merged at any time without worrying about collisions.
PostgreSQL provides storage and comparison functions for UUIDs, but the core database does not include any function for generating UUIDs, because no single algorithm is well suited for every application.
However, there are two extension plugins available to help generate the UUIDs
- uuid-ossp – Several functions to implement standard algorithms
- pgcrypto – A generation function for random UUIDS
How to configure EF Core to generate UUID
In following on from Using EF Core in a Separate Class Library project I will extend upon the details of how to use EF Core using the Model-First approach to build a database. The post details the approach used to create a PostgreSQL database.
Npgsql.EntityFrameworkCore.PostgreSQL is an Entity Framework Core provider built on top of Npgsql. It allows you to confgiure EF Core with PostreSQL.
In our database, we want to use the Database to generate a new UUID, when we insert new records.
As mentioned previously PostgreSQL doesn’t include any UUID generation functions, therefore we have to add one of the extension such as
pgcrypto. This can be done by placing the following code in your model’s
Once we have added the extension we can now go edit the
ThreatConfiguration file and edit our Identifier property to make use of the autogeneration.
Postgres has support for universally unique identifiers as a column data type via
uuid. If you have a UUID column, you may need to generate a UUID.
The uuid-ossp module provides a number of functions for generating UUIDs including the
uuid_generate_v4() function which bases the UUID entirely off random numbers. We are going to make use of this algorithm to generate our UUID.
Checkout the F.44. uuid-ossp PostgreSql documentation for more detail
Configure the column type to make use of the uuid column type – 8.12. UUID Type
We can now run the migration and apply this to our database. When we inspect our updated schema we will see our changes applied.
Adding new records to our database will result in new Identifier being generated