ActiveRecord upsert implementation

upsert.rb

  # Perform an upsert.
  #
  # Insert a new document using the supplied data hash
  # If the insert fails due to a duplicate record then the `update` values
  # are applied.
  #
  # Example:
  #
  #  # Values to insert
  #  insert = {
  #    updated_at:  Time.now,
  #    date:        Date.today,
  #    location_id: 301,
  #    supplier:    'SupplierA',
  #    count:       1
  #  }
  #
  #
  #  # If record exists also increment the existing count by 1
  #  update = { '$inc': { count: 1 } }
  #
  #  # Or, use raw sql to increment:
  #  update = { sql: 'count = count + 1' }
  #
  #  DailySupplier.upsert(insert, update)
  #
  #
  # Note:
  # - Validations are _not_ performed.
  # - To increment the existing value is must contain the string `VALUES(` anything else will result in the value being escaped
  def self.upsert(insert, update = insert)
    # Iterate over supplied hash building the insert, values and the update clause
    conn    = connection
    columns = []
    values  = []
    updates = []

    time   = Time.now
    insert = insert.merge('created_at' => time) if columns_hash.has_key?('created_at') && !insert.has_key?('created_at') && !insert.has_key?(:created_at)
    insert = insert.merge('updated_at' => time) if columns_hash.has_key?('updated_at') && !insert.has_key?('updated_at') && !insert.has_key?(:updated_at)
    update = update.merge('updated_at' => time) if columns_hash.has_key?('updated_at') && !update.has_key?('updated_at') && !update.has_key?(:updated_at)

    # Insert:
    insert.each_pair do |name, value|
      columns << conn.quote_column_name(name)
      values << conn.quote(value)
    end

    # Update:
    update.each_pair do |name, value|
      case name
      when '$inc'
        value.each_pair do |inc_name, inc_value|
          quoted_column_name = conn.quote_column_name(inc_name)
          updates << "#{quoted_column_name} = #{quoted_column_name} + #{conn.quote(inc_value)}"
        end
      when :sql
        updates << value
      else
        updates << "#{conn.quote_column_name(name)}=#{conn.quote(value)}"
      end
    end

    conn.execute "INSERT INTO #{quoted_table_name} (#{columns.join(',')}) VALUES(#{values.join(',')}) ON DUPLICATE KEY UPDATE #{updates.join(',')}"
  end

We use this upsert method to atomically increment daily counts of several key metrics that are stored in MySQL. Should work with other relational databases too.