Preventing Duplicates in Databases while using Rails

Gracie McGuire

Follow

Dec 17, 2020 · 3 min read

For anyone creating a database whether through Rails or any other language, ensuring that there are no duplicates is extremely important. Imagine signing up for a service using a username, and learning later on that another user also has the same username. Not only would this be confusing and concerning for the user, but this could (and more than likely would) prevent a myriad of problems for your database.

If you have ever created a database through rails, you have more than likely used the Active Record helper uniqueness to check if an attribute is unique before saving it to the database like so:

class Account < ApplicationRecord
 validates :email, uniqueness: true
end

So what is the uniqueness helper doing for us under the hood? Uniqueness makes a SELECT SQL query to that model’s table to find if the attribute already exists, if it does not exist, it will run INSERT and persist the new instance into the database; if it does exist, however, the instance will not persist.

While the model validations work most of the time, they unfortunately do not work all of the time, and sometimes some validations will slip through the cracks. Say, for example, you create a new application and have a ton of users all sign up at the same time. If two users attempt to use the same username and make the request at the same time, the uniqueness queries could potentially run at the same time and insert both of the users. To prevent this from happening we need to use a unique index in our database. Unique Indexes help us make sure that no two rows of data inside of a table have identical key values.

If you are ahead of the game and have not already created your database, we can stay ahead by adding this validation when we run our initial migration:

rails generate migration CreateUsers username:string:uniq

This will create the following migration:

However, if you’re like me and have already created your DB, we will need to remove any duplicates already existing before adding unique indexes, because otherwise these duplicates will throw an error and make our migrations fail. I found this awesome walkthrough that uses some nifty SQL commands to drop those duplicates, here.

After your duplicates are dropped, we can run a migration that drops the original indexes and adds the new unique ones.

While running into duplicates in a database isn’t common for smaller projects, it is a real issue, and taking these steps as a precaution to prevent this from happening at the get go is your safest bet!

Resources:

• https://dev.to/nodefiend/rails-migration-adding-a-unique-index-and-deleting-duplicates-5cde
• https://medium.com/@igorkhomenko/rails-make-sure-you-have-proper-db-indexes-for-your-models-unique-validations-ffd0364df26f
• https://stackoverflow.com/questions/48007376/rails-add-unique
• https://stackoverflow.com/questions/16735454/correct-way-of-prevent-duplicate-records-in-rails#:~:text=The%20correct%20way%20to%20prevent,model%20which%20you%20did%20correctly.&text=Add%20a%20unique%20index%20in,to%20the%20database%20will%20fail.