Should I use a database as gateway to other services? [closed]

Question

The context

I have an IoT project where the sensors are sending data to my Postgres database. The sensors are manages by a stand-alone service which provides a REST API to query various information about the sensors (e.g. are they online).

I would like to have a unified interface for querying all the related data regardless of which actual application stores the data.

The idea

I would like to use Postgres as a gateway which could query data from the external services and provide a unified interface.

Something along the lines of:

SELECT sensor_id, client_email
FROM sensors
WHERE is_online=false;

That would be a view integrating together information from the database (sensor_id, client_email) and from the external service (is_online). In the future the number of external services holding some parts of the information might increase.

The technical implementation I am considering would use functions written in the Python procedural language which could query the REST API.

I am treating the database as a first-class citizen: it defines a clear public API for the other applications just as any other service would do.

Alternation solutions

An alternative would be to create separate service which would provide some sort of API – e.g. GraphQL – based on the data from the Postgres database and the other services.

Advantages of the Postgres based solution

In my opinion the Postgres based solution would have the following advantages:

• SQL queries are more flexible than GraphQL queries, especially for GROUP BY and HAVING style usecases.
• SQL (and Postgres specifically) has better support for managing users and their access rights.
• Most other applications are interacting with the database directly. It would be nice if they could continue using the database for all the data they need without needing to know where it is actually coming from.
• Managing another application and keeping it up to date with the database is a lot of extra work.

What would be the advantages and disadvantages of that approach? How would you solve that type of a problem?

Answer 1

In short

This is acceptable on a small scale, if the applications using the database are mostly analytic systems on a trusted network. But consider an event driven architecture or a CQRS design for larger scale or more teal-time applications.

More details

Your narrative explains that:

• a python program (external to the database) would query the IoT API and feed the database.
• the database would aggregate the data obtained by the your python “gateway”
• other applications may access this data.

Two things are unclear here:

• do other programs/services access the database directly? Or do they access it via a python program that exposes your own API?
• ok for the select, but can other programs/services command IoT devices by updating the data in the database?

Whatever the answer, the database will not be the “gateway”. The gateway is your python program that makes the glue between the IoT API and your world. The database acts here as “aggregator” or “data warehouse”.

Exposing the database directly, especially in a read-only context has the advantages of hiding the technical details and unifying access using a well known SQL interface with a large set of possible connectors.

But exposing the database directly has the inconvenience of security issues (e.g. select vs update), and the risk of inconsistency if you allow updates (e.g. what if the update succeeds but the corresponding command sent to the Iot device fails?).

Moreover you are then stuck by the structure of the database. You can no longer modify it as you want, since a lot of other applications will depend on it. This is contrary to the microservice architecture which seeks to have independently deployable services that do not rely on a central database.

Last but not least, the applications are bound to poll the database. This is ok for analytics that would read it anyway. But this is not efficient for programms that need to monitor the situation in real time: they need to constantly query the db to see if there’s a change, instead of getting notified when something interesting happens. So the load of your database might be artificially inflated while at the same time your database server becomes the limit to scalability.