Rails Application with multiple Databases

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!

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