1

I'm currently with with a Progress ERP database, which has two connection brokers: 4GL (aka ABL-used by the "desktop" apps) and SQL-92 (for any other language to connect, using ODBC or JDBC).

The number of connections is a little unbalanced, being 80% for 4GL (used by Progress GUI Apps - the legacy) against 20% for SQL-92. That's something that we can't change.

A few web apps written in PHP and Java needs to connect to the SQL-92 broker, and this number will increase in the next year. If each one of those applications simply connects or keep a connection pool for themselves, the number of connections will reach it's limit soon.

In order to solve this problem, I was thinking about how to maximize the use of those limited connections. My first idea is to centralize access to the database into a Rest Web Service using a connection pool (JDBC or ODBC).

The clients could send SQL queries (alongside some metadata) through an HTTP request. The Web Service then sends the query to the proper database using a connection pool.

Is it a viable solution or a problematic one?

PS.: One of my concerns with normal web services (which takes a request and then queries the database) is that every team would need to change this web service app when they need something new, creating a bottleneck for the teams.

Improve this question

2 Answers 2

Reset to default
1

The simplest solution in your case would be for each application to directly connect to the broker and then release the connection immediately after executing its query. If you have more concurrent requests to your applications than the number of available connections, you are going to incur latency no matter what, and putting another web service in between the app and the database isn't going to help.

You can selectively optimize the latency for some applications by giving them a small number of dedicated connections while the rest of the applications acquire and release connections as required. Another option to improve latency is to cache some data in the application layer and fall back to the cache if you cannot immediately obtain a database connection.

Improve this answer
1

Fronting a database with an API is an expensive (but not wrong) solution because of the impedance mismatch of database and REST APIs. You'd have to create adapters for all the tables into classes and also account for database operation having no result or a scalar result. A REST API is very loosely coupled, descriptive request/response pattern. For transferring larger amounts of data, I'd use a protocol like gRPC that supports streaming and doesn't repeat field names with every record.

A couple patterns potentially apply depending on the data usage pattern.

For read access, a database replica would be a staightforward solution because most database products have a replication protocol, plus there are third party CDC (change data capture) products.

Going further, a data warehouse approach may also be useful. The difference between a database replica and a data warehouse is that the replica has the same database schema. A data warehouse is optimized for reading and often holds denormalized data. For example, if the original data has customer and order tables, a data warehouse might just have an order table with the customer and order tables joined.

I'd also look for a proxy for database connections. Not sure if such products exist - such a proxy would basically just bundle requests and look to the consumer like a database provider.

Improve this answer
1
  • If a REST API has a representation schema that uses human readable format and repeats field names with ever records, that's because the API designer designed the representation schema to repeat field names. That design choice has nothing to do with REST.
    – Lie Ryan
    Commented Dec 16, 2019 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.