ActiveRecord create_or_update based on natural-key

Sharad Jain, Thursday, January 8, 2009 @ 3:46 pm

Have you often run into a situation where you have a hash full of properties and you want to either create a new ActiveRecord object (if it doesn't exist) or update one (if it exists)?

For example,

class User
  # has properties: ssn, first_name, :last_name, :age, :email, :password
  def create_or_update_by_ssn(params)
    user = Disease.find_by_ssn(params[:ssn])
    if user.nil?
      user = User.create(params)
    else
      user.update_attributes!(params)
    end
  end
end

Here, the business rule states that a user's SSN is unique, so called natural-key.

If we can identify what constitutes a natural-key for each model object in our domain-model, when we could DRY out this functionality.

With such a solution, the above code becomes:

class User
  natural_key :ssn
end

And then the client call becomes:

User.create_or_update_by_natural_key(params)

If you think, such a thing could be useful, you can download the gem here.

Related posts:

1. How to use will_paginate with non-ActiveRecord collection/array
2. acts_without_database: Leverage ActiveRecord with Non-Database Backed Objects
3. Using Mocha for ActiveRecord Partial Mocks with Finders
4. activerecord tests: modify activerecord logging without tripping rollback convenience
5. Using ActiveRecord and Metaprogramming to Define Constants for Enumerated Types

Comments (3)

Software Development

Comments: 3 so far

• Leave a comment
• Comment feed for this post
• TrackBack URL

1. 

   Why not use AR-Extensions?

   In my case I’m working with over 20,000 rows for my ‘import’ so running 40k queries (as your method would require) rather than 20k would be too costly.

   Comment by Andrew Herron, Thursday, January 8, 2009 @ 4:29 pm

2. 

   Andrew, Thanks for pointing me to ar-extensions. I was unaware of it. It seems Model.import with n_duplicate_key_update is what could be used. I wonder if it is database agnostic. Thanks.

   Comment by Sharad Jain, Thursday, January 8, 2009 @ 5:08 pm

3. 

   I dug into the code for the plugin and it appears to take advantage of the MySQL syntax for insert.

   What other database systems were you looking to use?

   A separate version for SQLite could be written using the ON CONFLICT clause:
   http://www.sqlite.org/lang_conflict.html

   It doesn’t look so easy with Postgres and you might need a trigger for that. While needing a second query, it would at least be run in the DB and not require a new call through the Rails DB layer.

   There are ways of creating a single query that would include conditionals to also do this though I’m not sure how complex or taxing it would be on some systems. Here’s some discussion on how to do it with Oracle:
   http://www.sqlrecipes.com/sql_questions_answers/insert_update_record_if_already_exists-6/

   Comment by Andrew Herron, Friday, January 9, 2009 @ 10:16 am

Leave a comment