Multiple databases in a single query in your Rails apps

Imagine that you are building a separate application for your e-commerce system dedicated to business intelligence. In other words, you want to calculate some stats for the orders. So you are going to create some new model, like OrderStat(s), and have a separate Postgres database for a new app. Sounds trivial so far.However, how are you going to get the data from the actual Orders? One way of doing that would be to do some data liberation and stream all Orders (or events related to orders) into Kafka and let the consumers get the data from Kafka. If that’s ever your plan to have Kafka in your ecosystem, then this is a way better solution than what will be discussed in this article. On the other hand, we went from querying a single table from a different database, which sounds like a straightforward thing, to an event-driven ecosystem backed by Kafka, which is a complex thing, especially if you don’t have much experience with it. This time, for this particular problem, we are going to explore some solution that maybe is not pretty and indicates an architectural smell, but also does the job efficiently - performing queries between two separate PostgreSQL databases (including joins!) using Foreign Data Wrappers.