After working with pure SQL and Ruby for the past few weeks, coming back to Rails and working with Active Record became a little confusing. When creating a database table with pure SQL, you have to think about things like whether each column in the table can be NULL or UNIQUE and which FOREIGN KEYS you need to add. In contrast, Active Record comes with a ton of validations outside of the database.
So when do you add validations and foreign keys at the database level vs at the active record level? Here’s what I learned.
Keep It DRY
If Rails had a tagline, it would be “Don’t Repeat Yourself” (DRY). If you add validations at the database level (when creating the database) and at the active record level (in the application models), you will be repeating yourself.
So which one is better? Considering that good design means it’s really easy to change, the answer is at the Active Record level.
For example, let’s imagine we have a users table with a name, email address, phone number, and password fields. Upon creation of the table, you might decide that a user’s phone number is required, so you make that field “not null” at the database level.
However, maybe a year later, you realize that you actually don’t ever call your customers, so while a phone number is nice to have, you don’t want it to be a mandatory sign up field anymore, especially since some customers end up not signing up because they’re not comfortable giving out their phone number.
To make changes to your program, you now need to make the phone number column not null. That’s a lot harder to do (you now have to do a whole database migration to change the column) than just deleting the “validates_presence_of :phone_number” line in your User model if you had validated at the Active Record level.
The Uniqueness Validation
The one thing you do want to make sure you add at the database level is an index and uniqueness validation for all the unique columns.
Every time you have a “validates_uniqueness_of ” validation at the application level, Active Record first does a lookup to make sure the value is indeed unique and then adds it to the database. An index makes the lookup a lot faster.
There is also a chance that two users add the same exact value at the same time. So both values will not be in the database when Active Record checks for uniqueness, but they’ll both be added at the same time if there isn’t a unique validation at the database level.
So always add an index and uniqueness to your unique columns at the database level!
Let’s say you’re building Freshbooks and a user has many invoices. You’ll have a users and an invoices table. In the invoices table, you’ll need to have a user_id column.
If you were using just SQL, you’d also have a foreign key column that connects that user_id in the invoices table to the id in the users table. With active record, the foreign key connection happens at the Active Record level. Simply type in “has_many :invoices” in the User model and “belongs_to :user” in the Invoice model.
Again, keeping these foreign key connections at the Active Record level makes it easy to change how different tables are related in the long term.
Another key thing to remember is to always add an index to the foreign key id columns. Let’s say you want to look up all invoices for the first user. With rails, you can simply do this with “User.first.invoices”. By indexing the “user_id” field in the “invoices” table, you’re making this type of lookup A LOT faster. Instead of going through each invoice in the invoices table and checking whether the user_id matches, with an indexed user_id, the database instantaneously return the rows that match that user_id in the invoices table.