Image of 8 black old world flycatcher birds flying towards the left, and 1 red bird facing right.
Image of 8 black old world flycatcher birds flying towards the left, and 1 red bird facing right.
Header Photo Found Here

Preventing Duplicates in Databases while using Rails

Gracie McGuire
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.

Image created in Figma

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!

Weekly Webtips

Explore the world of web technologies through a series of tutorials

Sign up for 💌 Weekly Newsletter

By Weekly Webtips

Get the latest news on the world of web technologies with a series of tutorial Take a look.

By signing up, you will create a Medium account if you don’t already have one. Review our Privacy Policy for more information about our privacy practices.

Check your inbox
Medium sent you an email at to complete your subscription.

Gracie McGuire

Written by

NYC based software engineer and designer. she/her/hers </racism> </patriarchy> graciemcguire.com

Weekly Webtips

Explore the world of web technologies through a series of tutorials

Gracie McGuire

Written by

NYC based software engineer and designer. she/her/hers </racism> </patriarchy> graciemcguire.com

Weekly Webtips

Explore the world of web technologies through a series of tutorials

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store