--- title: Importing data and handling conflicts in Ruby on Rails applications description: If you've got a bunch of data to import, existing data to import it on top of, and you want to either make updates to existing records or create new records – and be fast – this post is for you. --- [Home](https://mutuallyhuman-pr-605.herokuapp.com/) - [Team](https://mutuallyhuman-pr-605.herokuapp.com/team) - [Work](https://mutuallyhuman-pr-605.herokuapp.com/work) - [Blog](https://mutuallyhuman-pr-605.herokuapp.com/blog) - [Contact](https://mutuallyhuman-pr-605.herokuapp.com/contacts) # Importing data and handling conflicts in Ruby on Rails applications By [Zach Dennis](https://resources.mutuallyhuman.com/test/author/zach-dennis) on 18 08 2016 The other week we took a look at [importing data quickly into Ruby on Rails applications](https://resources.mutuallyhuman.com/blog/2016/06/28/importing-data-quickly-in-ruby-on-rails-applications) and saw how [activerecord-import](https://www.github.com/zdennis/activerecord-import) can speed up importing large sets of data, by 13x to nearly 40x, with just a few lines of code. One thing that post didn't cover was how to handle conflicts with our data. Should we ignore the duplicates, update the duplicates, or let it fail noisily? In this post we'll take a look at updating the duplicates. ## A Simple Example Let's say that we have an authors database with the following schema: We want to pull in an updated authors feed that will update the author names in our system. We may have misspellings, use initials where the author prefers their name expanded or vise versa, or perhaps the author has changed their name. Whatever it is we don't own the authors data so we want an upstream dataset to be used to make sure we've got up-to-date authors information. In the above schema we are using the `key` field as the globally unique identifier for each author. For the simplicity of this example our authors data already has a `key` that matches up with a corresponding `key` in the upstream data feed that we're going to import. ### Running the import For the time being let's assume that the only piece of author information we're interested in is the name. We can utilize the `:on_duplicate_key_update` option of activerecord-import's `import` method to specify that we want to update `name` when a duplicate is found: The above code will efficiently `INSERT` new records into the database or update the `name` column when a duplicate is found. ### How the above import works (MySQL) If the data being inserted would cause a duplicate value in a `UNIQUE` index or `PRIMARY KEY` then MySQL will perform an `UPDATE` on the existing row. It will update the columns based on the list of columns you provide to the `:on_duplicate_key_update` option. This relies on the underlying [INSERT ... ON DUPLICATE KEY UPDATE](http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html) functionality provided by MySQL. ### How the above import works (PostgreSQL) If the data being inserted would cause a duplicate value on the `PRIMARY KEY` field then PostgreSQL will perform an `UPDATE` on the existing row. It will update the columns based on the list of columns you provide to the `:on_duplicate_key_update` option. The above example would fail in PostgreSQL if `key` weren't the primary key on the authors table, even if `key` had a `UNIQUE` index or a `UNIQUE` constraint. This relies on the underlying [INSERT ... ON CONFLICT](https://www.postgresql.org/docs/9.5/static/sql-insert.html) functionality provided by PostgreSQL (only available in 9.5 and higher). ## Specifying how to detect duplicates with PostgreSQL With PostgreSQL activerecord-import supports passing a hash to `:on_duplicate_key_update`. The available options are: - **columns** — the array of columns to be updated when there is a duplicate - **conflict\_target** — the column(s) or index expression that PostgreSQL can use to infer an index from for detecting duplicates. Don't pass the name of the index here, just the name of the columns in the index. Use this or `constraint_name`, but not both. - **constraint\_name** — the name of the `CONSTRAINT` to use to detect duplicates. Unlike `conflict_target`, do not pass in the column name(s) that make(s) up the constraint; instead pass in the name of the constraint. Use this *or* `conflict_target`, but not both. In the earlier example we added a `UNIQUE` index on `authors.key`. If the primary key on the table was the `id` field we would use the following `import` call to ensure our author names got updated: To see how `constraint_name` is used let's remove the unique index on the `authors.key` and add an actual `CONSTRAINT`: Here's the updated call to `import`: In case you're wondering, ActiveRecord doesn't provide any methods for creating actual PostgreSQL database constraints, so that is why the above schema change executed raw SQL. ## How about SQLite3? SQLite3 doesn't provide an equivalent upsert implementation. The closest thing it currently supports is `INSERT OR REPlACE`. Rather than updating existing columns it can be used to replace an entire row with a new row. activerecord-import currently doesn't provide any support for this SQLite3 feature. ## Related bits ### Why `validate: false` in the above examples? Let's say that the author model looked like this: The above uniqueness validation will run a query every time `valid?` is called on an author instance. Unfortunately, activerecord-import is not able to batch validate. Instead, it will try to validate each author instance individually. That will cause 10,000 `SELECT ... FROM authors WHERE key = ...` queries to hit the database before the import. In the context of the above example we didn't need to run this validation since we're relying on a database-level `UNIQUE` index/constraint in order to trigger an update. Because of this we were able to the import without validations. If we would have kept validations turned on nothing bad would have happened except the import would have gone a wee bit slower. ### Use database level indexes/constraints for uniqueness The validation helpers provided by Rails are useful, but they're not enough for fighting the war against duplicate data. On its own a `validates :column, uniqueness: true` line in an ActiveRecord model won't actually ensure that you have unique data, nor will it be enough to utilize the `:on_duplicate_key_update` option in activerecord-import. ## Summary We saw previously how activerecord-import's `on_duplicate_key_update` option can be used to tell the database what columns to update when it finds a duplicate. Since MySQL and PostgreSQL support different call semantics this resulted in a few variations on how `on_duplicate_key_update` can be used. For MySQL, it's a simple collection of columns to update, whereas with PostgreSQL it could be that or it could be a Hash of `:columns` and either `:conflict_target` or `:constraint_name`. Now that we know how to leverage the speed and efficiency of activerecord-import with new data as well as updating existing data, we'll take a look next at ignoring unique key and constraint violations. If there are any particular topics you'd like to see covered go ahead and post to Github or send us a [tweet](https://twitter.com/intent/tweet?button_hashtag=activerecord-import&text=How%20do%20I%20...%20%40mutuallyhuman%20). Happy coding! Image credit: [Thomas Quine](https://www.flickr.com/photos/quinet/11976098215/in/photolist-jfhCKZ-nhfUnU-6jhot-hvvoNw-69fDHg-e25RL8-nDMva2-dBpvSH-iiQRub-duCRJQ-47xY9-nimsdL-4MFji7-h31iVx-6v57TT-Jz1Dgb-2Zopvv-9PFFyn-nbWnte-m3Jrft-fGxSLS-9fFrf4-uynTe-fK8DL8-8E1FY7-fPsrYd-fkwmU5-futdrS-efzxeS-gEit7Y-fjDr1T-fHUZRt-fWbMXj-fDbjmu-f1dgxP-ftAooR-9PvKBT-f1uSth-fJagkm-fJRoNE-fjVxZh-fCRKGv-dk4baS-fb2xN9-5vghAu-r5nyWS-ftCqqD-ffgz2B-ptacGr-s5ThhR) ### Related Topics - [Uncategorized (106)](https://resources.mutuallyhuman.com/test/topic/uncategorized) - [User Experience (20)](https://resources.mutuallyhuman.com/test/topic/user-experience) - [Ruby (11)](https://resources.mutuallyhuman.com/test/topic/ruby) - [Interaction Design (10)](https://resources.mutuallyhuman.com/test/topic/interaction-design) - [JavaScript (9)](https://resources.mutuallyhuman.com/test/topic/javascript) ### Related Posts ### About Mutually Human Mutually Human is a custom software design and development consultancy specializing in mobile and web-based products and services. We help our clients design, develop and bring to market innovative products and services based on insightful research and strategy aligned with business objectives. We’ve helped Fortune 500 companies, state governments, and startups. - [Home](https://www.mutuallyhuman.com) - [Team](https://www.mutuallyhuman.com/team) - [Work](https://www.mutuallyhuman.com/work) - [Blog](https://www.mutuallyhuman.com/blog) - [Contact](https://www.mutuallyhuman.com/contacts) logo [Mutually Human Grand Rapids: 401 Hall St. SW Suite 430 Grand Rapids, MI 49503 USA](https://www.mutuallyhuman.com/contacts#grand-rapids) [Mutually Human Columbus: 243 N. Fifth Street Suite 300 Columbus, OH 43215 USA](https://www.mutuallyhuman.com/contacts#columbus) +1 616 475-4225 [hello@mutuallyhuman.com](mailto:hello@mutuallyhuman.com) © 2017 Mutually Human