Ruby on Rails Connect to Multiple Databases and Migrations

Ruby on Rails connect to Multiple Databases and using ActiveRecord with multiple databases, it’s really simple take it easy. Let’s run through this.

Rake Tasks
Well, I want to handle migrations for two databases, so I need two separate Rake tasks to handle that:

<code class="ruby"><span class="line"><span class="n">desc</span> <span class="s2">"Migrate the database through scripts in db/migrate directory."</span>
</span>
<span class="line"><span class="n">namespace</span> <span class="ss">:db</span> <span class="k">do</span>
</span><span class="line">  <span class="n">task</span> <span class="ss">:migrate</span> <span class="k">do</span>
</span><span class="line">    <span class="ss">Rake</span><span class="p">:</span><span class="ss">:Task</span><span class="o">[</span><span class="s2">"db:migrate_db1"</span><span class="o">].</span><span class="n">invoke</span>
</span><span class="line">    <span class="ss">Rake</span><span class="p">:</span><span class="ss">:Task</span><span class="o">[</span><span class="s2">"db:migrate_db2"</span><span class="o">].</span><span class="n">invoke</span>
</span><span class="line">  <span class="k">end</span>
</span>
<span class="line">  <span class="n">task</span> <span class="ss">:migrate_db1</span> <span class="k">do</span>
</span><span class="line">    <span class="ss">ActiveRecord</span><span class="p">:</span><span class="ss">:Base</span><span class="o">.</span><span class="n">establish_connection</span> <span class="no">DB1_CONF</span>
</span><span class="line">    <span class="ss">ActiveRecord</span><span class="p">:</span><span class="ss">:Migrator</span><span class="o">.</span><span class="n">migrate</span><span class="p">(</span><span class="s2">"db/migrate/db1/"</span><span class="p">)</span>
</span><span class="line">  <span class="k">end</span>
</span>
<span class="line">  <span class="n">task</span> <span class="ss">:migrate_db2</span> <span class="k">do</span>
</span><span class="line">    <span class="ss">ActiveRecord</span><span class="p">:</span><span class="ss">:Base</span><span class="o">.</span><span class="n">establish_connection</span> <span class="no">DB2_CONF</span>
</span><span class="line">    <span class="ss">ActiveRecord</span><span class="p">:</span><span class="ss">:Migrator</span><span class="o">.</span><span class="n">migrate</span><span class="p">(</span><span class="s2">"db/migrate/db2/"</span><span class="p">)</span>
</span><span class="line">  <span class="k">end</span>
</span><span class="line"><span class="k">end</span>
</span></code>

My first task is db:migrate that delegates out to db:migrate_db1 & db:migrate_db2.

Each of those establish a connection to the database and then runs the migrations from their own separate folders. This allows you to store migrations in separate folders so you can easily manage them.

The migrations are exactly the same as normal.

Database Connections
In order to get those migrations to work, I need to configure the database connections. I’m going to define everything in the database.yml just like normal, but with a different naming convention:

database.yml
<code class="ruby"><span class="line"><span class="ss">defaults</span><span class="p">:</span> <span class="o">&amp;</span><span class="n">defaults</span>
</span><span class="line">  <span class="ss">username</span><span class="p">:</span> <span class="n">root</span>
</span><span class="line">  <span class="ss">password</span><span class="p">:</span> <span class="mi">1234567</span>
</span><span class="line">  <span class="ss">adapter</span><span class="p">:</span> <span class="n">mysql2</span>
</span><span class="line">  <span class="ss">encoding</span><span class="p">:</span> <span class="n">utf8</span>
</span><span class="line">  <span class="ss">collation</span><span class="p">:</span> <span class="n">utf8_unicode_ci</span>
</span>
<span class="line"><span class="ss">db1</span><span class="p">:</span>
</span><span class="line">  <span class="ss">development</span><span class="p">:</span>
</span><span class="line">    <span class="ss">database</span><span class="p">:</span> <span class="n">db1_development</span>
</span><span class="line">    <span class="ss">host</span><span class="p">:</span> <span class="n">localhost</span>
</span><span class="line">    <span class="o">&lt;&lt;</span><span class="p">:</span> <span class="o">*</span><span class="n">defaults</span>
</span>
<span class="line">  <span class="nb">test</span><span class="p">:</span>
</span><span class="line">    <span class="ss">database</span><span class="p">:</span> <span class="n">db1_test</span>
</span><span class="line">    <span class="ss">host</span><span class="p">:</span> <span class="n">localhost</span>
</span><span class="line">    <span class="o">&lt;&lt;</span><span class="p">:</span> <span class="o">*</span><span class="n">defaults</span>
</span>
<span class="line">  <span class="ss">staging</span><span class="p">:</span>
</span><span class="line">    <span class="ss">database</span><span class="p">:</span> <span class="n">db1_staging</span>
</span><span class="line">    <span class="ss">host</span><span class="p">:</span> <span class="n">localhost</span>
</span><span class="line">    <span class="o">&lt;&lt;</span><span class="p">:</span> <span class="o">*</span><span class="n">defaults</span>
</span>
<span class="line">  <span class="ss">production</span><span class="p">:</span>
</span><span class="line">    <span class="ss">database</span><span class="p">:</span> <span class="n">db1_production</span>
</span><span class="line">    <span class="ss">host</span><span class="p">:</span> <span class="n">localhost</span>
</span><span class="line">    <span class="o">&lt;&lt;</span><span class="p">:</span> <span class="o">*</span><span class="n">defaults</span>
</span>
<span class="line"><span class="ss">db2</span><span class="p">:</span>
</span><span class="line">  <span class="ss">development</span><span class="p">:</span>
</span><span class="line">    <span class="ss">database</span><span class="p">:</span> <span class="n">db2_development</span>
</span><span class="line">    <span class="ss">host</span><span class="p">:</span> <span class="n">localhost</span>
</span><span class="line">    <span class="o">&lt;&lt;</span><span class="p">:</span> <span class="o">*</span><span class="n">defaults</span>
</span>
<span class="line">  <span class="nb">test</span><span class="p">:</span>
</span><span class="line">    <span class="ss">database</span><span class="p">:</span> <span class="n">db2_test</span>
</span><span class="line">    <span class="ss">host</span><span class="p">:</span> <span class="n">localhost</span>
</span><span class="line">    <span class="o">&lt;&lt;</span><span class="p">:</span> <span class="o">*</span><span class="n">defaults</span>
</span>
<span class="line">  <span class="ss">staging</span><span class="p">:</span>
</span><span class="line">    <span class="ss">database</span><span class="p">:</span> <span class="n">db2_staging</span>
</span><span class="line">    <span class="ss">host</span><span class="p">:</span> <span class="n">localhost</span>
</span><span class="line">    <span class="o">&lt;&lt;</span><span class="p">:</span> <span class="o">*</span><span class="n">defaults</span>
</span>
<span class="line">  <span class="ss">production</span><span class="p">:</span>
</span><span class="line">    <span class="ss">database</span><span class="p">:</span> <span class="n">db2_production</span>
</span><span class="line">    <span class="ss">host</span><span class="p">:</span> <span class="n">localhost</span>
</span><span class="line">    <span class="o">&lt;&lt;</span><span class="p">:</span> <span class="o">*</span><span class="n">defaults</span>
</span></code>

I configure two separate databases db1 & db2.

Then I need to configure the app to load these now. I open application.rb or environment file(s):

application.rb
<code class="ruby"><span class="line"><span class="no">ENV</span><span class="o">[</span><span class="s1">'ENV'</span><span class="o">]</span> <span class="o">||=</span> <span class="s1">'development'</span>
</span>
<span class="line"><span class="n">db_conf</span> <span class="o">=</span> <span class="ss">YAML</span><span class="p">:</span><span class="ss">:load</span><span class="p">(</span><span class="no">File</span><span class="o">.</span><span class="n">open</span><span class="p">(</span><span class="no">File</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="no">APP_PATH</span><span class="p">,</span><span class="s1">'config'</span><span class="p">,</span><span class="s1">'database.yml'</span><span class="p">)))</span>
</span>
<span class="line"><span class="no">DB1_CONF</span> <span class="o">=</span> <span class="n">db_conf</span><span class="o">[</span><span class="s2">"db1"</span><span class="o">][</span><span class="no">ENV</span><span class="o">[</span><span class="s1">'ENV'</span><span class="o">]]</span>
</span><span class="line"><span class="no">DB2_CONF</span> <span class="o">=</span> <span class="n">db_conf</span><span class="o">[</span><span class="s2">"db2"</span><span class="o">][</span><span class="no">ENV</span><span class="o">[</span><span class="s1">'ENV'</span><span class="o">]]</span>
</span></code>

Take a look at what’s going on:
– I set the database configuration to use. You can just use Rails.env here instead of ENV[‘ENV’].
– I load up the database.yml config and parse it with YAML.
– I grab the configuration from the file for each db and the correct environment that I’m running in.

Connecting Models
When I’m working with multiple databases, I like to explicitly setup the connections inside the models themselves instead of inheriting from ActiveRecord::Base and using subclasses.

user.rb
<code class="ruby"><span class="line"><span class="k">class</span> <span class="nc">User</span> <span class="o">&lt;</span> <span class="ss">ActiveRecord</span><span class="p">:</span><span class="ss">:Base</span>
</span><span class="line">  <span class="n">establish_connection</span> <span class="no">DB1_CONF</span>
</span><span class="line"><span class="k">end</span>
</span></code>
product.rb
<code class="ruby"><span class="line"><span class="k">class</span> <span class="nc">Product</span> <span class="o">&lt;</span> <span class="ss">ActiveRecord</span><span class="p">:</span><span class="ss">:Base</span>
</span><span class="line">  <span class="n">establish_connection</span> <span class="no">DB2_CONF</span>
</span><span class="line"><span class="k">end</span>
</span></code>

Well, All you really need to do is load the configurations, establish the database connections, and setup the migrations to load from a specific directory for each database.

Don’t know how to build task…

Try renaming the file to sample_data.rake.

I was able to get your example working (replacing the internals of the task with a p statement) by putting your code in a file called testomatic.rake in lib/tasks.

//geekhmer.github.io/blog/2015/02/07/ruby-on-rails-connect-to-multiple-databases-and-migrations