Transcript

Check out this users table:

\d users;

We have two columns: a full name and an admin flag.

The Rails app that touches this database expects that these two values will never be null. If they are, computers around the world with burst into flames.

Our Rails devs have accomplished this feat by adding ActiveRecord presence validations on the user model.

So, the values can never be null, right?

Wrong.

While we've been talking, a rogue developer has logged into psql, and hacking is underway.

table users;

In this psql session, my nulls are skulls.

What does it mean if an admin flag is null? Does that mean they aren't an admin?

Here we can see why not null database constraints are important. Even if we could prevent people from inserting null data directly from SQL, the database still allows it. Someday other applications will interact with this database too. And we'll have to tell each of these apps about the constraint, or they won't honor it.

The database should be a single source of truth about what kind of data it will and won't allow.

Unfortunately, we have existing data, and if we try to just add the constraint, we'll get this error:

alter table users alter column admin set not null;
-- ERROR: column "admin" contains null values

Right, those null values.

What we need to do first is an update statement. I've chosen a default of false. Usually you can come up with something that's better than null.

update users set admin = false where admin is null;

Now, the nulls are gone:

table users;

Time to add our migration. We're also going to add a default that matches our update statement. This will protect us from bad data.

alter table users alter column admin set not null;
alter table users alter column admin set default false;

Now things are looking a lot better:

\d users;

Rails devs may be wondering how to implement this type of constraint on existing data using ActiveRecord. There are several ways to achieve this. At Hashrocket, we write a lot of ActiveRecord migrations in plain old SQL. That's something we plan to cover in a future episode.

To sum up, protect your database from nulls early, and you will have better data.

Thanks for watching.

Setup

-- Set nulls to skulls
\pset null 💀

-- Create users table
create table users (
  full_name varchar not null,
  admin boolean
);

-- Create users with null values
insert into users (full_name, admin) values ('Kenneth Parcell', true);
insert into users (full_name) values ('Liz Lemon');
insert into users (full_name) values ('Tracy Jordan');