如何在Rails中并行地与多个数据库建立_connection ?

Context

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

我正在构建一个SaaS，用户可以在其中创建自己的网站(比如Wix或SquareSpace)。这就是幕后发生的事情:

• 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
• SQL文件在这个外部数据库中运行，以设置默认设置
• 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:

然后我在db中执行sql代码:

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)
   • 用户A正在创建一个网站(建立动态数据库连接)
   • User B tries to access his user area (which requires application's main db data)
   • 用户B试图访问他的用户区域(需要应用程序的主数据库数据)
   • Application throws an error because it tries to retrieve data of app-main-db (which connection is not established at the moment)
   • 应用程序抛出一个错误，因为它试图检索app-main-db的数据(目前尚未建立该连接)
2. 建立与数据库动态连接使得应用程序的主要数据库inacessible一会儿:用户创建一个网站(建立动态数据库连接)用户试图访问他的用户区域(需要应用程序的主要数据库数据)应用程序抛出一个错误,因为它试图检索数据app-main-db(目前没有建立连接)

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.

注:与通过database.yml连接多个数据库不同。这里的目标是同时连接和断开多个用户创建的动态数据库。

3 个解决方案

#1

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

这种宝石可能有帮助。但是，您可能需要重命名一些模型，以使用外部数据库名称空间而不是ApplicationRecord

https://github.com/ankane/multiverse

https://github.com/ankane/multiverse

#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.

我承认这并没有回答您最初问题的核心，但在我看来，这可能需要通过单独的操作来完成，比如通过队列以某种方式触发的纯SQL脚本。

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.

您可以让您的rails应用程序将一个“创建消息”放入队列，并拥有一个单独的服务来监视执行创建操作的队列，然后将一个带有信息的消息传递回队列。rails应用程序监视这些队列，然后对这些信息进行处理。

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.

FWIW是一个很酷的网站，我最近发现它描述了很多流行的排队服务。

#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:

可能不是最好的方法，但是可以通过调用一个外部脚本创建数据库，在一个分开的ruby文件中:

• Create create_database.rb file in lib folder
• 创建create_database。在lib文件夹中的rb文件。

• Put db creation script inside this file

  将db创建脚本放在这个文件中

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

• Execute with Rails Runner

  执行与Rails跑步

  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:

你可以用ARGV把参数传递给你的脚本:

rails runner lib/create_database.rb db_name

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

用ARGV[0]在脚本中捕获:

name = ARGV[0]
puts name
> db_name