PRACTICAL PROGRAMMING ADVICE

Three Issues With Using Database Generated IDs

Stop letting the database be in charge of your application

You’ve probably let databases generate IDs for your entities at least once.

But, what if I told you there’s an alternative when developing applications?

I’m sure this stands in sharp contrast to what you learned in the Relational Databases 101 college course and the countless youtube tutorials you’ve watched when learning how toCREATE TABLE UsingTerribleIds ().

Generate your IDs at the application level. Not at the persistence level.

That’s it. You’re free to go on your merry way.

(please note that entity ID is not necessarily a database primary key)

Or, stay put if you want to know more about why database generated entity IDs may be troublesome and want to learn how I approach ID generation at client projects.

So, what’s the issue with having the database generate your entities’ IDs?

1 What’s most problematic is you’re delegating an important aspect of your domain to third party software.

2You’re likely to apply some bad practices while designing your entity classes just to make it easier working with a persistence framework, such as EntityFramework in C# .NET.

One of the worst offenders I see junior programmers do is having public ID setters.

A public setter tells the world it’s okay to change this entity’s ID — which may be a terrible idea.

3You’re making unit testing more difficult than it needs to be. You suddenly rely on a third party providing your entities with IDs.

Say you’ve figured that a public ID setter is essentially a terrible sin, and you don’t want calling code to set the ID either. Your class would instead look something like this below.

Your ORM of choice is possibly still able to set the id field through reflection — you know, nothing is really safe from reflection…

But, how would you unit test this? The id field is set to 0 on instantiation. Instantiating more than one TerribleBook would leave you with identity collision, as now more than one TerribleBook has the same id, even tho your should represent two separate entities.

An example of domain generated IDs

The solution is honestly dead simple. Just have a look at the snippet below.

Let’s walk thru this code, as every change from the TerribleBook to FixedBook might not be obvious to everyone.

We make the constructor private and instantiate new objects using the static factory method. This allows us to abstract the instantiation logic from the caller and even provides us the opportunity to use polymorphism — we might want to return a Null Object instead of throwing.

Notice we’re still taking the id as a constructor parameter. But we are in charge of generating and providing the id (on line 18). Not the database.

The entity id datatype is changed from int to string — simply to allow the possibility to use something that is meaningful to the business. Do however watch out for performance issues and the consequences of using strings as IDs.

The Guid.NewGuid().ToString("D") just ensures we’ll get a hyphen formatted GUID. I like using GUIDs, but you’re free to build your own ids which ever way makes sense to your business and application needs.

UPDATE

Please note that I’m not talking about what to use as primary key…

Also, I make no claim my solution is without trade-offs.. Better entity design traded with potentially worse performance. I know that’s something DBAs will lose their minds over, but so be it.

You’ll have to make your own decisions about what’s best in your specific scenario.

Resources for the curious
--------------------------

When and where to determine the ID of en entity by Matthias Noback

CQS versus server generated IDs by Mark Seemann

Why Auto Increment Is A Terrible Idea by Clément Delafargue

UUID or GUID as Primary Keys? Be Careful! by Tom

SQL Server: Natural Key Versus Surrogate Key by Gregory A. Larsen

Nicklas Millard is a software development engineer in one of the fastest-growing banks, building mission-critical financial services infrastructure.

Previously, he was a Big4 Senior Tech Consultant developing software for commercial clients and government institutions.

New YouTube Channel (@Nicklas Millard)

Connect on LinkedIn