Hi!
I have a simple question regarding the use of DATABASE VIEW API instead of a REST API. Now, I have been told that according to governing documents in Equinor we are required to use REST APIs where each table is exposed via separate endpoints instead of database views. I cannot understand this being the intention with the statement being made in TR1621 section 2.3. I think it is a misunderstanding to say that a database view is not an API. A database view can be supported as an API independently of the underlying database, just as a REST API. The difference is that a database view is way more powerful.
Let me illustrate with an example:
Say I have two tables in a remote database: Cars A and Persons B, with millions of rows each, too much data to download. Now, say I want to have all rows from table A with cars, where the owners work at Equinor. BUT, one cannot see from Table A that a person works in Equinor, so first a query in table B must be made to search by employer and find say 8000 people. These are the owners. Now, find the cars. I have to filter the cars table one by one for each of those 8000 people. If this has to be done one by one through a REST API endpoint that takes 151ms to respond. This will take quite some time, actually 20 minutes.
Database joins on a view would solve this. A database join would allow me to join the tables together and return the result in one query. It would perform, less error prone and way easier to maintain.
In my specific case, I am asked to work with the operations and maintenance data REST APIs at api.equinor.com. This interface has endpoints one-to-one with table views in an Omnia COPY of the on premise SAP database with millions of rows in each table. Since it is only a copy of the original database – all operations are limited to read only. However, as I am not working ON the omnia team, I am told that I am not allowed to make database connections from my backend app directly to the database, but must use the API. (I have a backend app that is serving web-clients) In my case I need to join, not just two tables as in the example above, but four, and I have not one, but several such queries.
One solution is to ask a remote team in a separate location to create custom endpoints. However this is going to demand a lot in meetings and coordination. If instead, the existing database view could be supported as an API we would have a flexible, yet powerful integration point. A view is decoupled from database tables and can be versioned just like a REST API. The general structure of SAP does not change either. I talked to colleagues with experience in similar integration patterns and everyone I have asked so far recommends using database views. I have searched the net, and everywhere I have seen a rest API being recommended, it is for a simpler problems with small amounts of data, or different problems altogether.
So, my question is if we can include database views as APIs in TR1621. I really cannot see any reason why not.
regards, K