Aggregating data and searching across multiple databases

Aggregating data and searching across multiple databases

  

We are using Outsystems v10 on-premises and are trying to use Outsystems to display information that is stored in our existing proprietary application (database is SQL server). The existing application will continue to exist and grow in parallel with our Outsystems-built applications.

We have a somewhat unique database architecture with several (10+) databases per environment with identical schemas but different data that we need to be able to search "across" in our Outsystems applications (some of you may know this as creating database "shards"). For all intents and purposes, all relational data for each of the databases is self-contained (e.g. data on database A never refers to data on database B). The number of databases is not fixed and will increase over time, meaning any solution must be robust enough to handle additional databases being added without having to update hundreds of server actions.

We have successfully been able to use the DatabaseConnection_SetConnectionStringForSession server action to be able to switch between databases when the data source is known, but we have been unable to figure out a way to search across all databases to find the result we are looking for. 

For example, let's say that each database has a [Movie] table with identical schema, and I want to search all of the databases to find all matches that contains "Star Wars" in the title and return them in one table.

How do I accomplish this with Outsystems in a manner that is both maintainable for the long-term and doesn't require the management of dozens of different connection strings in Service Center?

I'm not sure of the practicality of this, and I'm positive it would not be efficient, but what if you stored your database connections in a table managed by OutSystems?  You would then use a for loop combined with DatabaseConnection_SetConnectionStringForSession.

That said, it seems to me that it would be better to have an external search service (e.g. Solr), and consume that service from OutSystems.  If you need to be able to update data from that datasource, then you could use the server action at that point maybe.

Craig St. Jean wrote:

I'm not sure of the practicality of this, and I'm positive it would not be efficient, but what if you stored your database connections in a table managed by OutSystems?  You would then use a for loop combined with DatabaseConnection_SetConnectionStringForSession.

That said, it seems to me that it would be better to have an external search service (e.g. Solr), and consume that service from OutSystems.  If you need to be able to update data from that datasource, then you could use the server action at that point maybe.

Hey Craig, thanks for the reply. 

I've tried this, but it does not work and it appears to be by design. One of the noted drawbacks of using DatabaseConnection_SetConnectionStringForSession is that once an aggregate or query uses a connection string, it can't be changed again within the same "flow" or pass of the foreach loop:

From the documentation of PlatformRuntime_API: If there is a single query that uses the Database Connection with the old database before this action is executed, the switch to the new database will not happen until the end of the current request. Only in a next request the database switch becomes effective.

PDT, you could spawn one REST request to each database connection. Each REST request would consume its own transaction and return the results. You would have to combine the results back into a single list, but that would be the easier part.

You will need to be careful to not mix the IDs of each schema. A movie with Id=1 means nothing when you have 10 identical movie tables - you need to know from which table it came from.

I would still look into Solr, as it really seem more appropriate to your enterprise scenario.

leonardo.fernandes wrote:

PDT, you could spawn one REST request to each database connection. Each REST request would consume its own transaction and return the results. You would have to combine the results back into a single list, but that would be the easier part.

You will need to be careful to not mix the IDs of each schema. A movie with Id=1 means nothing when you have 10 identical movie tables - you need to know from which table it came from.

I would still look into Solr, as it really seem more appropriate to your enterprise scenario.

Regarding Solr, our current application handles searching across the database shards seamlessly. The problem is integrating this architecture to work with Outsystems as we start to build auxiliary applications to support our "core" enterprise application.

If I'm going to spend the time to spin up individual REST requests for each database shard (and consume countless AOs), then we might as well just create web services in our enterprise application that aggregates all of the information and have Outsystems consume this, but both kind of defeat the purpose.

You don't need to consume REST services for each shard. You can have one REST service and change its endpoint URL at runtime, relying that the swagger definition will be the same. You can do that inside the OnBeforeRequest action. This way you can grow the number of shards without increasing the number of AOs.