Stay Ahead in Ruby!
From development processes to the most useful gems, get it all straight in your inbox. Join our Ruby blog today!

Skip to main content

Rails Application with multiple Databases

Attention! This article might be outdated, refer to latest documentation if solution does not work.
Rails Application with multiple Databases - cover image
How to connect a second database to your Rails application?

Since our application already uses PostgreSQL we will use MySQL as a second database. First of all, let’s configure our docker-compose.yml file by adding a new service and Dockerfile by adding default-libmysqlclient-dev to it.

# docker-compose.yml

  services:
    db_mysql:
      image: mysql
      environment:
        MYSQL_ROOT_PASSWORD: root
        MYSQL_USERNAME: root
        MYSQL_PASSWORD: password
        MYSQL_DATABASE: mysql_development
      ports:
        - "3307:3306"
      volumes:
        - mysql_data:/var/run/mysqld

  volumes:
    mysql_data:
    # ...

When they are configured, we should add mysql2 gem to our Gemfile and run bundle install.

# Gemfile

  gem "mysql2"

In the config/database.yml we will add information about our new database called “additional”. However, you can call it on your own.

# config/database.yml

  development:
    primary:
      adapter: postgresql
      database: postgresql_development
      port: 5432
    additional:
      adapter: mysql2
      username: root
      password: password
      database: mysql_development
      host: db_mysql # name of the docker-compose service
      port: 3306
      migrations_paths: db/additional_migrate # path where our migrations for this DB will be located

Once it’s ready, it’s time to create our new “additional” database. To do so, you can use the built-in Rails function:

  rails db:create:additional # or your database name

And now, it’s time to create our first migration. When you want to create an “additional” migration, you should add --database additional option. Be careful with it! Without specifying a database, Rails will add migrations to the primary database.

  rails generate migration CreateUser name:string age:integer --database additional

It will create just a regular Rails migration file, under the path we specified in the config/database.yml file.

# db/additional_migrate/20230117165229_create_additional_users.rb

class CreateAdditionalUsers < ActiveRecord::Migration[7.0]
  def change
    create_table :additional_users do |t|
      t.string :name
      t.integer :age

      t.timestamps
    end
  end
end

To run new migrations only for the new database, you should use the following command:

  rails db:migrate:additional

Now we should prepare our BaseRecord. We will tweak this model to connect to the additional database properly.

# app/models/additional/base_record.rb

  # frozen_string_literal: true

  class Additional::BaseRecord < ActiveRecord::Base
    # Connects our application directly to the database
    connects_to database: { reading: :additional, writing: :additional }

    # `connects_to` can only be called on ActiveRecord::Base or abstract classes
    self.abstract_class = true

    def self.table_name_prefix
      "additional_"
    end

  end

When you create a new model, inherit it from our recently created Additional::BaseRecord model to apply all this magic.

# app/models/additional/user.rb

  # frozen_string_literal: true

  class Additional::User < Additional::BaseRecord
  end

It’s time to test it. Let’s create a simple user record!

Creation of a new user.
Creation of a new user.
Verification of recently created user.
Verification of recently created user.

Now, you can enjoy your Rails application connected to two databases simultaneously!

We are ready to provide expert's help with your product
or build a new one from scratch for you!

Contact MobiDev’s tech experts!