ActiveRecordImporter

This gem helps you insert/update records easily. For now, it only accepts CSV file. This also helps you monitor how many rows are imported, and how many rows failed. This gem also allows you to easily import to any model with few configurations.

Rows are validated one by one but persisted in bulk (one INSERT per chunk, one upsert per chunk) through activerecord-import. See How rows are persisted.

The spec suite runs against PostgreSQL and MySQL (ActiveRecord 7.2); see Development.

• Ruby >= 2.7
• ActiveRecord >= 6.0
• Paperclip >= 5.2.0 and < 7.0

Paperclip's URI attachment adapters are disabled by default to prevent SSRF (CVE-2017-0889). Use local file uploads; do not re-enable URI adapters for untrusted input.

When upgrading from Paperclip 4.x with S3 storage, use aws-sdk-s3, configure s3_region, and change explicit permissions such as :public_read to 'public-read'.

• PostgreSQL (>= 9.5) or MySQL for bulk upserts; other databases fall back to row-by-row saving

I'll release an update to enable this on background job.

Add this line to your application's Gemfile:

gem 'active_record_importer'

And then execute:

$ bundle

Or install it yourself as:

$ gem install active_record_importer

Rows are now validated one by one but persisted in bulk per chunk (see How rows are persisted). If your model relies on its own ActiveRecord save callbacks during import, add bulk_insert: false to acts_as_importable. See the CHANGELOG for the full list of changes.

For the newest version (0.4.0), you don't have to create Import table/model and controller. I already fixed the errors when there's no Import table/model on version 0.3.0. You just need to add the acts_as_importable in your model you want to be importable, and you may now run:

User.import!(file: File.open(PATH_TO_FILE))

insert will be the default insert method for this If you want to use upsert or error_duplicate, define it in your importer options:

class User < ActiveRecord::Base
  acts_as_importable insert_method: 'upsert',
                     find_options: [:email]
end

Or you may use in your console:

User.acts_as_importable insert_method: 'error_duplicate', find_options: ['email']

I'll add a generator on my next release

class ActiveRecordImporterMigration < ActiveRecord::Migration
  def change
    create_table :imports do |t|
      t.attachment  :file
      t.attachment  :failed_file
      t.text        :properties
      t.string      :resource,           null: false
      t.integer     :imported_rows,      default: 0
      t.integer     :failed_rows,        default: 0
      t.timestamps
    end
  end
end

class Import < ActiveRecord::Base
  extend Enumerize
  store :properties, accessors: %i(insert_method find_options batch_size)

  enumerize :insert_method,
            in: %w(insert upsert error_duplicate),
            default: :upsert

  has_attached_file :file
  has_attached_file :failed_file

  attr_accessor :execute_on_create

  validates :resource, presence: true
  validate :check_presence_of_find_options
  validates_attachment :file,
                       content_type: {
                           content_type: %w(text/plain text/csv)
                       }

  validates_attachment :failed_file,
                       content_type: {
                           content_type: %w(text/plain text/csv)
                       }

  # I'll add import options in the next major release
  # accepts_nested_attributes_for :import_options, allow_destroy: true
  ### THIS IS VERSION 0.2.1 and below
  def execute
    resource_class.import!(self, execute_on_create)
  end

  ### THIS IS VERSION 0.2.1 and below
  def execute
    resource_class.import!(object: self, execute: execute_on_create)
  end


  ### THIS IS VERSION 0.2.1 and below
  def execute!
    resource_class.import!(self, true)
  end

  ### THIS IS VERSION 0.3.0
  def execute!
    resource_class.import!(object: self, execute: true)
  end

  def resource_class
    resource.safe_constantize
  end

  def batch_size
    super.to_i
  end

  ##
  # Override this if you prefer have
  # private permissions or you have
  # private methods for reading files
  ##
  def import_file
    local_path?(file) ? file.path : file.url
  end

  ##
  # Override this method if you have
  # private permissions or you have private methods
  # for reading/writing uploaded files
  ##
  def failed_file_path
    local_path?(failed_file) ? failed_file.path : failed_file.url
  end

  private

  def check_presence_of_find_options
    return if insert_method.insert?
    errors.add(:find_options, "can't be blank") if find_options.blank?
  end

  def local_path?(f)
    File.exist? f.path
  end
end

class User < ActiveRecord::Base
  acts_as_importable
end

You may also add import options:

class User < ActiveRecord::Base
  acts_as_importable default_attributes: { first_name: 'Juan',
                                           last_name: 'dela Cruz' },
                     find_options: %i(email),
                     before_save: Proc.new { |user| user.password = 'temporarypassword123' }
                     after_save: Proc.new { |user| puts "THIS IS CALLED AFTER OBJECT IS SAVED" }
end

If you're using ActiveRecord::Store, you may import values to your accessors by including them in the configuration:

class User < ActiveRecord::Base
  store :properties, accessors: [:first_key, :second_key]

  acts_as_importable store_accessors: [:first_key, :second_key]
end

This is a sample import HAML form:

    # resource is your Model name
    = f.input :resource
    # batch_size is useful for large csv file
    = f.input :batch_size
    # insert_methods: [:upsert, :insert, :error_on_duplicate]
    = f.input :insert_method, collection: insert_methods, class: 'form-control insert-method'
    # `find_options` are the list of columns you want to use to update a certain instance or
    # error when a duplicate is found. This is not required when your insert_method is `:insert`
    = f.input :find_options
    = f.input :file, as: :file,
      input_html: { accept: '.csv' }

You may also add some options from the SmarterCSV gem:

| Option                         |  Default
--------------------------------------------------------------
| :convert_values_to_metric      |  nil
| :value_converters              |  nil
| :remove_empty_values           |  false
| :comment_regexp                |  Regexp.new(/^#=>/)
| :force_utf8                    |  true
| :chunk_size                    |  500
| :col_sep                       |  ","

https://github.com/tilo/smarter_csv

class User < ActiveRecord::Base
  acts_as_importable csv_opts: {
                       chunk_size: 2000,
                       col_sep: '|',
                       convert_values_to_numeric: { only: [:age, :salary] }
                     }
end

I'll add more options SOON!

Every CSV chunk (chunk_size / batch_size rows, 500 by default) goes through these steps:

1. The attributes of every row are built (default_attributes, store_accessors, timestamps...).
2. For upsert and error_duplicate, the existing records matching the find_options of the whole chunk are loaded with a single query.
3. An instance is built for every row, the before_save callback runs, and the instance is validated (valid?). Invalid rows are kept aside with their errors (e.g. Validation failed: Email can't be blank).
4. Valid new records are inserted with one bulk INSERT, and valid existing records are updated with one bulk upsert on the primary key, inside one transaction per chunk. If the database rejects a bulk statement (e.g. a unique index without a model validation), that group is retried row by row in savepoints, so only the offending rows fail.
5. Failed rows are written to the failed file with an import_errors column, and imported_rows / failed_rows are updated on the Import record.

Bulk persistence skips the model's own ActiveRecord save callbacks (like activerecord-import does). Rows are saved one by one (still within one transaction per chunk) when:

• the importer has after_save or state_machine_attr options, since they need a persisted instance, or
• bulk insert is disabled for the model:

class User < ActiveRecord::Base
  acts_as_importable bulk_insert: false
end

Duplicates inside the same chunk follow the insert_method: with upsert they are merged into a single record, with error_duplicate the second occurrence fails with Duplicate record found!.

class ImportsController < ApplicationController

  def create
    @import = Import.create!(import_params)
    @import.execute!
  end

  private

  def import_params
    params.require(:import).permit(:file, :resource, :insert_method, :batch_size)
  end
end

File.open(PATH_TO_CSV_FILE) do |file|
  @import = Import.create!(
             resource: 'User',
             file: file,
             insert_method: 'upsert',
             find_options: 'first_name,last_name'
           )
end
@import.execute!

###REMINDER: Headers of your csv file should be formatted/transformed to column names of your IMPORTABLE model

After checking out the repo, run bin/setup to install dependencies.

The specs run against a real database. By default they use PostgreSQL (database active_record_importer_test, created automatically if missing):

$ bundle exec rspec
$ DB=postgresql PG_HOST=localhost PG_USER=postgres PG_PASSWORD=secret bundle exec rspec

To run them against MySQL (uses the trilogy adapter, or MYSQL_ADAPTER=mysql2):

$ docker run -d --name ari_mysql -e MYSQL_ROOT_PASSWORD=root \
    -e MYSQL_DATABASE=active_record_importer_test -p 3307:3306 mysql:8.0
$ DB=mysql MYSQL_PORT=3307 MYSQL_PASSWORD=root bundle exec rspec

Supported variables: DB (postgresql|mysql), DB_NAME, PG_HOST, PG_PORT, PG_USER, PG_PASSWORD, MYSQL_ADAPTER, MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD.

You can also run bin/console for an interactive prompt that will allow you to experiment.

To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and tags, and push the .gem file to rubygems.org.

Bug reports and pull requests are welcome on GitHub at https://github.com/michaelnera/active_record_importer. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the Contributor Covenant code of conduct.

The gem is available as open source under the terms of the MIT License.