Back to Technical
Technical
RubyRuby on RailsProductionDatabaseData ImportsData Integrity

Before touching production Data: How I use Rails console to make data scripts safer

•Zaneta Gebka

Some time ago I had to run a pretty ordinary data import in a Rails application. Nothing fancy, just the source data had changed. Some existing records needed updating, some needed moving to different categories, and some new ones needed to be created together with tags and categories. I bet you know that case.

The importer itself was not particularly exotic. Take data from Excel, use it in a script and save it into the database. Tadam!

However, the difficult part was everything around it. Because you know, you cannot just run a script on production data. I mean, no one will stop you probably, but you know you should not, right?

Production already had records from an older process, with views, likes and other history attached. Some shared a title despite being different things. Some still belonged to old categories. So I spent quite a lot of time in rails console, trying to understand what the importer would actually do with all of that.

First, test it somewhere else

I first run the script against a staging or local database, before touching production. For this import, I restored a copy of production locally. My clean development data did not have all those old records and their history, which turned out to be quite important.

Even there, I check where I am connected:

Rails.env
ActiveRecord::Base.connection.current_database

A terminal on my laptop can still connect to a remote database through DATABASE_URL or a tunnel. I prefer to check that before making my local experiment everybody else's problem.

The ticket and the database disagreed

One record was supposedly in the wrong category. Before fixing it, I checked:

record = Post.find_by!(title: some_title)
record.topic.slug

It was already in the right category. Well, that was one less thing to fix.

It also made me slow down a bit. The task described what someone thought was happening, but the database had a slightly different story. Starting with a few queries saved me from writing code for a problem that was no longer there.

Finding the record was the actual problem

The first version looked for existing records inside the destination category:

Post
  .where(topic_id: target_topic_id)
  .where('LOWER(title) = ?', title.downcase)

Sounds reasonable, until you remember that part of the import was moving records between categories. If a record was still in its old category, this query could not find it. So the importer happily created a new one.

No exception. Just duplicates. Fun. That showed up when I tested with the restored production data. The development fixtures had nothing sitting in an old category, so they had given me very little reason to worry. The new copies also had none of the views, likes or comments attached to the original records. Deleting the old ones would mean losing that history and breaking references to their IDs. Once the matching was fixed, I could update the existing records instead:

existing_record.update!(
  content: new_content,
  topic: target_topic,
  tags: tags
)

Same record, same ID, new content and category. That was what I needed from the beginning.

Same title, different story

To look for duplicates, I used:

Post
  .group('LOWER(title)')
  .having('COUNT(*) > 1')
  .count

But a repeated title did not always mean a duplicate. Some records had the same title and different source data. I had to look at their categories, timestamps and related data to understand which ones were actually copies. An earlier version of the importer could delete records it considered duplicates. I removed that part. If the script could not confidently match an incoming row to an existing record, I preferred it to raise and let me investigate. Automatically deleting the wrong record would give me a much less enjoyable investigation afterwards.

Run it again

Before the import, I saved some counts in the console:

before = {
  posts: Post.count,
  tags: Tag.count,
  post_tags: PostTag.count
}

Afterwards, I compared them and checked the duplicate query again. The script reaching its last line was nice, but I also wanted to see whether it had created the records and associations I expected.

Then I ran it a second time against the same test database. The counts stayed stable and no new duplicates appeared. Much better than discovering on production that every rerun creates another copy of everything.

Rails already knew the rule

Despite the local testing, a few records still failed during the first production run because of a uniqueness validation. I had assumed that tags were unique within a user. To see what the model actually enforced, I checked:

Tag.validators_on(:name).map do |validator|
  [validator.class.name, validator.options]
end

The name was globally unique. My importer was searching by both name and user, then trying to create a tag whose name already existed for someone else. I changed it to find by name and only assign the user when creating a new tag. The rule was already there in the model. I had just written a different one in my head.

Failed does not mean nothing happened

Each imported record had its own transaction:

rows.each do |row|
  Post.transaction do
    import(row)
  end
end

So when a few records failed, the earlier successful ones stayed committed. The error at the end did not undo the whole import.

Wrapping the entire loop in one transaction would behave differently. In this case, I already had a partially imported database. Before running the corrected script again, I needed to check what was already saved, which rows had failed and what the rerun would do with the successful ones.

Before pressing enter

So before I decide a script is ready for production, I test it on staging or a local copy with data that includes those older records. I check how it finds existing records, whether it keeps their IDs and what happens if I run it twice or it fails halfway through.

Then I check the production connection, make sure I have a backup I can restore, and save some counts to compare afterwards. By that point, I want to be able to explain what the script will change and what I will do if it stops halfway. That makes pressing enter a bit less exciting. Which, in this case, is good.