How to establish_connection with more than one database in parallel in Rails?

Question

Context

I'm building a SaaS where users can create their own websites (like Wix or SquareSpace). That's what happens behind scenes:

• My app has its main database which stores users
• When a user creates his website, an external database is created to store its data
• SQL file runs in this external database to set default settings
• Other users shall create their websites simultaneously

---

Approach

To create a new database and establish connection I do the following:

ActiveRecord::Base.connection.execute("CREATE DATABASE #{name}")
ActiveRecord::Base.establish_connection(<dynamic db data>)

Then I execute sql code in the db by doing:

sql = File.read(sql_file.sql)
statements = sql.split(/;$/)
statements.pop
ActiveRecord::Base.transaction do
  statements.each do |statement|
    connection.execute(statement)
  end
end

Then I reestablish connection with main db:

ActiveRecord::Base.establish_connection :production

---

Problem

1. Establishing connection to dynamic database makes application's main database inacessible for a while:
   • User A is creating a website (establishes dynamic database connection)
   • User B tries to access his user area (which requires application's main db data)
   • Application throws an error because it tries to retrieve data of app-main-db (which connection is not established at the moment)

How can I handle many users creating their websites simultaneously without databases conflict?

In other words, how can I establish_connection with more than one database in parallel?

---

NOTE: It is not the same as connecting to multiple databases through database.yml. The goal here is to connect and disconnect to dynamic created databases by multiple users simultaneously.

Nonoccurrence

Perhaps it would be easier using the same database and adding a "website_id" fields to each table where you store data that's specific to an user or website. – Jankell

Answer 1

This gem may help. However,you may need to rename some of your models to use the external database namespace instead of ApplicationRecord

https://github.com/ankane/multiverse

Zermatt

Answer 2

I admit that this doesn't answer the core of your initial question but IMO this probably needs to be done via a separate operation, say a pure SQL script triggered somehow via a queue.

You could have your rails app drop a "create message" onto a queue and have a separate service that monitors the queue that does the create operations, and then pass a message with info back to the queue. The rails application monitors the queue for these and then does something with the information.

The larger issue is decoupling your operations. This will help you down the road with things like maintenance, scaling, etc.

FWIW here is a really cool website I found recently describing a lot of popular queuing services.

Faddist

Answer 3

Probably not the best approach but it can be achieved by calling an external script that creates the database, in a separated ruby file:

• Create create_database.rb file in lib folder

• Put db creation script inside this file

  ActiveRecord::Base.connection.execute("CREATE DATABASE #{name}")
  ActiveRecord::Base.establish_connection(<dynamic db data>)
  

• Execute with Rails Runner

  rails runner lib/create_database.rb
  

• or with system, if you want to call it from controller

  system("rails runner lib/create_database.rb")
  

This way you can create and access multiple databases without stopping your main database.

---

Passing arguments

You can pass arguments to your script with ARGV:

rails runner lib/create_database.rb db_name

And catch it inside the script with ARGV[0]:

name = ARGV[0]
puts name
> db_name

Nonoccurrence