We often use multiple databases in our Rails application.
Rails 6 made it easier to connect to
Let’s consider a use case where:
User can create multiple posts.
Anyone looking at the post can add a comment - crowd-sourced comments.
In this case, we can store the crowd-sourced comments in a separate database,
as comments can grow quickly and may need a different kind of data management approach.
The database.yml can be like below:
The models would look like below:
In Rails 6, to fetch comments on all posts of a user, we can add a custom method,
as associations can’t join across databases.
Here, we are fetching the user-created post ids first, and then all the comments for those post ids.
For all such associations across different databases we will have to add custom methods as above.
Rails 7 has
an option in has many through association to find the elegant way to solve the problem of multiple database joining.
We can use the has_many :through association with the newly added disable_joins: true option like below:
If we try to load the associations now, it fires 2 separate queries to load the post ids,
and the comments with those post ids.
This allows us to eager load the comments. But if we iterate through the users, and try to load the comments there’s an additional query fired to select the post ids of the user.
Though, there’s a workaround to solve this N+1 query issue, with the cost of loading associated posts in the memory like below: