Don't use default values in your database

This is a bad practice in my opinion if your database is owned by one application (or service). It is the usual scenario when using microservices: your service is the only owner of that database. Of course, it might not be your use case.

If several services are writing to the same database, then I can see some good examples where you would want default values. For example, if you don’t have control on the queries that are already writing to it.

Why shouldn’t you use default values in your schema?

• Default values are usually defined in your code too. Now you would have these defaults spread in your code and your database.
• Default values probably depend on your application flow. When the default is in the scheme, you are probably thinking of one single scenario.
• It won’t help you to maintain your code.

When schemas evolve, it is super common to add a not null column. The common practice is to add the column with a default value, which will fill that new column with the default value. But the unexpected behaviour is that don’t have to write that value in your INSERTs anymore. And it is not probably what you wanted.

For example, let’s say we have a table for users and we are writing a functionality for a new type of user. We would probably do something like this in our database:

ALTER TABLE user ADD user_type ENUM('PLAYER', 'COACH') NOT NULL DEFAULT 'PLAYER';

It adds the user_type column and set all the old values to 'PLAYER'.

The robust way to do that is:

ALTER TABLE user ADD user_type ENUM('PLAYER', 'COACH') NOT NULL DEFAULT 'PLAYER';
UPDATE user SET user_type='PLAYER' where user_type is null;
ALTER TABLE user MODIFY user_type ENUM('PLAYER', 'COACH') NOT NULL;

That is, add the column as nullable, set the value, set the not null.

In case that you are altering your database while your code is running (for example, in a high availability environment), your code have to support writing the new column but probably reading null values until all the SQL updates are completed.

That forces you to do two deployments:

1. First deploy to add the nullable column. New code starts writing the default value but supports to read a null.
2. Second deploy to update the value in database. After that, the code can assume the value is not null anymore.

More work, I know, but this is the cost of making your database more manageable in the future!

PS: sorry for the typos in advance, I wrote this in 10 minutes!