Database Constraints, But Why?
Many people know why we should use model validations, but why also use database constraints? All this and more (not really more actually) can be yours if you read on! OK fine, short story: to protect the integrity of our data!
The Problem(s)
Our backend currently relies on model validations to ensure the correct format of any data that reaches the database. This has worked great so far, but we’re at a point where we need to start using SQL to add and update data in the database in order to speed up performance. Or we need to interact with the database directly to perform bulk operations that are otherwise difficult to perform. Any modifications made in this manner will not pass through validations :(
The Solution: Database Constraints!
Database constraints are the perfect answer to the problem stated above. They also help protect our data in the event that we switch out part of our stack, specifically the backend application that interacts with the database (So many things can go wrong when we make big changes!).
Like model validations, database constraints allow us to declare what the data in a column should look like: does it follow a pattern? Should it be a certain length? Can it be empty? Is there a range of values it should fall within? What about uniqueness? And on and on. In the event that someone bypasses the model validations to add or modify data, that new data won’t be added unless it conforms to the database constraints, thus protecting the integrity of our data.
As a general rule, I like to ensure that my database constraints mirror my model validations, or vice versa. At times, this means I need to break out my old SQL friend when I’m writing migrations, since the out-of-the-box constraints (aka helpers) are limited regardless of what we’re using for the backend application.
A Rails Example
Here’s a quick example of a migration that includes database constraints to ensure that certain columns always contain data, are unique, and/or require a minimum character length:
create_table :bands do |t|
t.string :name, null: false, unique: true
t.integer :members, null: false t.timestamps
endexecute <<-SQL
ALTER TABLE bands
ADD CONSTRAINT check_minimum_length
CHECK (LENGTH(name) > 2)
SQL
And this also has the added benefit of automatically indexing the name column, which allows for faster lookups! This would not be true with a model validation alone. Ooooh, benefits…
Quick note: please don’t index every column in every table: this is a bad idea. Please look into why if you don’t believe me.
Conclusion / PSA
If there’s even the slightest chance that you or someone else will directly modify data in the database, please, please, whip out those constraints! I know there are people out there who don’t, so you do you. Personally, I always use database constraints, we’re tight like that.
