How can I migrate data from an old SQLite database without affecting ActiveRecord model connections?

You can’t simply “switch” connections inside the same block without extra work, because calling establish_connection on ActiveRecord::Base (or using its connection block) changes the default connection for all models. The recommended approach is to separate your old and current database logic into different model classes so that each one is tied to its proper connection. Here’s one way to do it:

Define an Abstract Base for the Old Database

Create an abstract class that establishes a separate connection to your old database:

RUBYCopied
class OldRecord < ActiveRecord::Base
  self.abstract_class = true
  establish_connection(
    adapter: 'sqlite3',
    database: 'storage/old.sqlite3'
  )
end

Create a Model for the Old Data

Now, define a model for the table you want to read (e.g., batches) inheriting from the abstract class:

RUBYCopied
class OldBatch < OldRecord
  self.table_name = 'batches'
end

Use the Separate Models in Your Code

Now you can iterate over your old records and write to your new database using your normal models. For example:

RUBYCopied
OldBatch.find_each do |old_batch|
  # Process and insert into your new database using Batch (which is still
  # connected to your default/new database).
  Batch.create!(
    attribute1: old_batch.attribute1,
    attribute2: old_batch.attribute2
    # ... add mapping as needed
  )
end

Explanation

• Separation of Concerns:
  By isolating the connection for the legacy database in its own abstract class, you ensure that models like Batch remain connected to your current Rails environment (new database) while you can still read data from OldBatch.

• No Interference:
  Since ActiveRecord’s connection handling is global per model, defining separate subclasses keeps the connections isolated. This avoids the pitfalls you encountered with Batch.count returning the wrong value.

• Efficient Data Processing:
  Using find_each (which batches the query results) is also a good practice to process data in chunks if you have a large number of records.

Using this pattern, you can work in a single loop where each model call is executed on its intended connection without manually switching connections mid-loop.