Ruby-on-rails Rails 中的多个数据库

Multiple databases in Rails

提问by luca

Can this be done? In a single application, that manages many projects with SQLite. What I want is to have a different database for each project my app is managing.. so multiple copies of an identically structured database, but with different data in them. I'll be choosing which copy to use base on params on the URI.

这能做到吗？在单个应用程序中，它使用 SQLite 管理许多项目。我想要的是为我的应用程序正在管理的每个项目拥有一个不同的数据库......因此具有相同结构的数据库的多个副本，但其中包含不同的数据。我将根据 URI 上的参数选择要使用的副本。

This is done for 1. security.. I'm a newbe in this kind of programming and I don't want it to happen that for some reason while working on a Project another one gets corrupted.. 2. easy backup and archive of old projects

这样做是为了 1. 安全性.. 我是这种编程的新手，我不希望它发生这种情况，因为某种原因，在一个项目上工作时另一个项目被破坏了.. 2. 轻松备份和存档老项目

采纳答案by Simone Carletti

Rails by default is not designed for a multi-database architecture and, in most cases, it doesn't make sense at all. But yes, you can use different databases and connections.

默认情况下，Rails 不是为多数据库架构设计的，而且在大多数情况下，它根本没有意义。但是是的，您可以使用不同的数据库和连接。

Here's some references:

这里有一些参考：

• ActiveRecord: Connection to multiple databases in different models
• Multiple Database Connections in Ruby on Rails
• Magic Multi-Connections

• ActiveRecord：连接到不同模型中的多个数据库
• Ruby on Rails 中的多个数据库连接
• 神奇的多重连接

回答by adeandrade

If you are able to control and configure each Rails instance, and you can afford wasting resources because of them being on standby, save yourself some trouble and just change the database.yml to modify the database connection used on every instance. If you are concerned about performance this approach won't cut it.

如果您能够控制和配置每个 Rails 实例，并且您可以承受因为它们处于待机状态而浪费资源，那么您可以省去一些麻烦，只需更改 database.yml 以修改每个实例上使用的数据库连接。如果您担心性能，这种方法不会削减它。

For models bound to a single unique table on only one database you can call establish_connection inside the model:

对于仅绑定到一个数据库上的单个唯一表的模型，您可以在模型内调用establish_connection：

establish_connection "database_name_#{RAILS_ENV}"

As described here: http://apidock.com/rails/ActiveRecord/Base/establish_connection/class

如此处所述：http: //apidock.com/rails/ActiveRecord/Base/establish_connection/class

You will have some models using tables from one database and other different models using tables from other databases.

您将有一些模型使用一个数据库中的表，而其他不同的模型使用其他数据库中的表。

If you have identical tables, common on different databases, and shared by a single model, ActiveRecord won't help you. Back in 2009 I required this on a project I was working on, using Rails 2.3.8. I had a database for each customer, and I named the databases with their IDs. So I created a method to change the connection inside ApplicationController:

如果您有相同的表，在不同的数据库上通用，并且由单个模型共享，则 ActiveRecord 将无济于事。早在 2009 年，我就在使用 Rails 2.3.8 的一个项目中要求这样做。我为每个客户都有一个数据库，我用他们的 ID 命名数据库。所以我创建了一个方法来改变 ApplicationController 内部的连接：

def change_database database_id = params[:company_id]
    return if database_id.blank?

    configuration = ActiveRecord::Base.connection.instance_eval { @config }.clone
    configuration[:database] = "database_name_#{database_id}_#{RAILS_ENV}"

    MultipleDatabaseModel.establish_connection configuration
end

And added that method as a before_filterto all controllers:

并将该方法作为before_filter添加到所有控制器：

before_filter :change_database

So for each action of each controller, when params[:company_id] is defined and set, it will change the database to the correct one.

因此，对于每个控制器的每个动作，当定义并设置 params[:company_id] 时，它会将数据库更改为正确的。

To handle migrations I extended ActiveRecord::Migration, with a method that looks for all the customers and iterates a block with each ID:

为了处理迁移，我扩展了 ActiveRecord::Migration，使用一种查找所有客户并使用每个 ID 迭代块的方法：

class ActiveRecord::Migration
    def self.using_databases *args
        configuration = ActiveRecord::Base.connection.instance_eval { @config }
        former_database = configuration[:database]

        companies = args.blank? ? Company.all : Company.find(args)

        companies.each do |company|
            configuration[:database] = "database_name_#{company[:id]}_#{RAILS_ENV}"
            ActiveRecord::Base.establish_connection configuration

            yield self
        end

        configuration[:database] = former_database
        ActiveRecord::Base.establish_connection configuration
    end
end

Note that by doing this, it would be impossible for you to make queries within the same action from two different databases. You can call change_databaseagain but it will get nasty when you try using methods that execute queries, from the objects no longer linked to the correct database. Also, it is obvious you won't be able to join tables that belong to different databases.

请注意，通过这样做，您将无法在来自两个不同数据库的同一操作中进行查询。您可以再次调用change_database，但是当您尝试使用从不再链接到正确数据库的对象执行查询的方法时，它会变得令人讨厌。此外，很明显您将无法连接属于不同数据库的表。

To handle this properly, ActiveRecord should be considerably extended. There should be a plugin by now to help you with this issue. A quick research gave me this one:

为了正确处理这个问题，ActiveRecord 应该被大大扩展。现在应该有一个插件可以帮助您解决这个问题。一项快速研究给了我这个：

DB-Charmer: http://kovyrin.github.com/db-charmer/

DB-Charmer：http: //kovyrin.github.com/db-charmer/

I'm willing to try it. Let me know what works for you.

我愿意尝试一下。让我知道什么对你有用。

回答by rdaniels

I got past this by adding this to the top of my models using the other database

我通过使用其他数据库将它添加到我的模型顶部来解决这个问题

class Customer < ActiveRecord::Base
  ENV["RAILS_ENV"] == "development" ? host = 'devhost' : host = 'prodhost'

  self.establish_connection(
      :adapter  => "mysql",
      :host     => "localhost",
      :username => "myuser",
      :password => "mypass",
      :database => "somedatabase"
    )

回答by SquareCog

You should also check out this project called DB Charmer: http://kovyrin.net/2009/11/03/db-charmer-activerecord-connection-magic-plugin/

您还应该查看这个名为 DB Charmer 的项目：http: //kovyrin.net/2009/11/03/db-charmer-activerecord-connection-magic-plugin/

DbCharmer is a simple yet powerful plugin for ActiveRecord that does a few things:

1. Allows you to easily manage AR models' connections (switch_connection_tomethod)
2. Allows you to switch AR models' default connections to a separate servers/databases
3. Allows you to easily choose where your query should go (on_*methods family)
4. Allows you to automatically send read queries to your slaves while masters would handle all the updates.
5. Adds multiple databases migrations to ActiveRecord

DbCharmer 是一个简单但功能强大的 ActiveRecord 插件，它可以做一些事情：

1. 让您轻松管理 AR 模型的连接（switch_connection_to方法）
2. 允许您将 AR 模型的默认连接切换到单独的服务器/数据库
3. 允许您轻松选择查询的位置（on_*方法系列）
4. 允许您自动向您的从站发送读取查询，而主站将处理所有更新。
5. 将多个数据库迁移添加到 ActiveRecord

回答by Steven Soroka

It's worth noting, in all these solutions you need to remember to close custom database connections. You willrun out of connections and see weird request timeout issues otherwise.

值得注意的是，在所有这些解决方案中，您需要记住关闭自定义数据库连接。否则，您将耗尽连接并看到奇怪的请求超时问题。

An easy solution is to clear_active_connections! in an after_filter in your controller.

一个简单的解决方案是 clear_active_connections！在控制器的 after_filter 中。

after_filter :close_custom_db_connection

def close_custom_db_connection
  MyModelWithACustomDBConnection.clear_active_connections!
end

回答by rdaniels

in your config/database.yml do something like this

在你的 config/database.yml 做这样的事情

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5

development:
  <<: *default
  database: mysite_development

test:
  <<: *default
  database: mysite_test

production:
  <<: *default
  host: 10.0.1.55
  database: mysite_production
  username: postgres_user
  password: <%= ENV['DATABASE_PASSWORD'] %>

db2_development:
  <<: *default
  database: db2_development

db2_test:
  <<: *default
  database: db2_test

db2_production:
  <<: *default
  host: 10.0.1.55
  database: db2_production
  username: postgres_user
  password: <%= ENV['DATABASE_PASSWORD'] %>

then in your models you can reference db2 with

然后在你的模型中你可以引用 db2

class Customers < ActiveRecord::Base
  establish_connection "db2_#{Rails.env}".to_sym
end

回答by Andrew Kane

What you've described in the question is multitenancy (identically structured databases with different data in each). The Apartment gemis great for this.

您在问题中描述的是多租户（结构相同的数据库，每个数据库具有不同的数据）。该公寓的宝石是为这个伟大的。

For the general question of multiple databases in Rails: ActiveRecord supports multiple databases, but Rails doesn't provide a way to manage them. I recently created the Multiversegem to address this.

对于Rails 中多数据库的一般问题：ActiveRecord 支持多数据库，但是Rails 没有提供管理它们的方法。我最近创建了Multiversegem 来解决这个问题。

回答by Dibyajit Goswami

The best solution I have found so far is this:

到目前为止，我发现的最佳解决方案是：

There are 3 database architectures that we can approach.

我们可以使用 3 种数据库架构。

• Single Database for Single Tenant
• Separate Schema for Each Tenant
• Shared Schema for Tenants

• 单一租户的单一数据库
• 每个租户的独立架构
• 租户共享架构

Note: they have certain pros and cons depends on your use case.

注意：它们有一定的优缺点，取决于您的用例。

I got this from this Blog! Stands very helpful for me.

我从这个博客得到了这个！站桩对我很有帮助。

You can use the gem Apartmentfor rails

您可以将 gem公寓用于导轨

Video reference you may follow at Gorails for apartment

您可以在Gorails 上观看公寓视频参考