Allow dynamic databases based on url params

madav 0
Viewed 28

I have a Rails app (currently 6.1 but I can easily upgrade it if necessary) that connects to external MSSQL databases and provides an API (read-only) with the data.

I use these gems to help me access the databases:

gem 'activerecord-sqlserver-adapter', '6.1.2'
gem 'composite_primary_keys', '13.0.0'
gem 'tiny_tds', '2.1.5'

I have many of these MSSQL databases that have identical tables/views, but are connected to different data sources, and I use this one app to be able to create an API from these databases.

Right now, a quick setup hack I was able to do was to:

  1. create a subdomain (ex db1.domain.com, db2.domain.com)

  2. create multiple databases in my database.yml:

db1:
  <<: *default
  database: db2

db1:
  <<: *default
  database: db1

  1. create environment configs for each db

  2. spin up an app on the server catching the subdomain and setting RAILS_ENV to be db1 and db2

This works fine, but it's not dynamic! It was good for the first 2-3 times, but we are now over 20 databases, and I don't want each new database to have to be done manually continuously.

I would love for domain.com/db1 and domain.com/db2 to dynamically know that the database is db1 and db2 even though no code has been changed in the app and no new apps were spun up in the server.

I know Rails now supports multiple databases, but I could not figure it out with the MSSQL the last time I tried (right when it was released).

I'm going to take another deep dive into this, but wondering if anyone here has had any similar experiences or advice for me.

Any tips, tricks, or advice are super appreciated!

Thanks

tiny-tds multiple-databases ruby-on-rails sql-server
1 Answers

In case anyone has a similar issue, I was able to resolve this pretty easily with some inspiration from this SO answer.

Firstly, I wrapped all my routes in a scope with scope '/:db_key' do. (In routes.rb)

Then in app/controllers/application_controller.rb I added a connection to the database based on the key (since in my case all db info except key was the same I only had to modify the key, but you can easily edit any field):

class ApplicationController < ActionController::API
  before_action :setup_db_connection

  ...

  private

  def setup_db_connection
    # connect to the database with the name of the database key
    @setup_db_connection ||= ActiveRecord::Base.establish_connection(
      Rails.application.credentials.db.to_h.merge(database: params[:db_key])
    )
  end
end

In rails credentials, I listed all the db creds:

db:
  host: xxxxx
  localhost: xxxxxx
  port: xxxx
  username: user
  password: reallyStrongPassword
  database: have_the_default_db_here_but_its_not_needed_since_i_replace_it
  adapter: sqlserver
  encoding: utf8
  mode: dblib
  reconnect: true
  timeout: 350000

Since there are no migrations, I am just reading from external databases, I did not need any special databases in database.yml, nor did I need any more rails env per database.

I know this is a really unique situation since I connect to other databases that have different database names per account but have exact same schema, and there is no need for write. But posting this in case it helps someone else, or gives another dev some inspiration.

madav0
Related