CODEWITHSUNDEEP
mysqlruby-on-railsdatabaseactiverecordconnection-pooling
Artur Haddad
Artur Haddad

Reputation: 1539

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

Context

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

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.

Upvotes: 8

Views: 1489

Answers (3)

engineerDave
engineerDave

Reputation: 3945

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.

Upvotes: 1

Artur Haddad
Artur Haddad

Reputation: 1539

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

Upvotes: 0

Zzz
Zzz

Reputation: 1703

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

Upvotes: 1

Related Questions