0

My application backend consists of a bunch of standalone tasks linked together by messaging queues. They all need to access the same SQL database in order to run and store their results. Right now, all the tasks are accessing the SQL database directly, but by not having a centralized entity to manage access to that database, I am not sure how to maintain the database (apply migrations, ...).

One solution would be to access the database through an API, which would be in charge of maintaining the database. This approach also has the advantage of decoupling the datastore implementation from the application code. On the other hand, the application has to do very expensive queries (insert a 500MB CSV in a table for example), and I'm not sure how performance could be impacted for such a use case.

What's the "recommended" way of architecting such a system?

1

In theory, each of your message handling services is already an api to the database.

The real question is, have you separated out your data correctly?

If you have many services all using the same database its a 'code smell'. Not because your services access the database directly, but because they use the same database.

Now if they are all doing the actions of the database, say updating the order status as events happen. Thats fine, you hide the db behind an order service and off you go.

If they all have their own separate tables and operations though, you should think about separating off the data into a database purely for that service.

0

As you point out a central API that hides the database's implementation details is the simplest solution and likely good enough. I wouldn't worry too much about performance if querying directly already performs sufficiently.

However, it is possible that a central API becomes a bottleneck. In such cases a possible approach is to have the central API broadcast changes as they occur, to a message log, and then replay that message log into another database in every one of the services that consume it. The message log's schema is separate from the source database schema, so you can still do migrations without affecting reads. Every service queries its own copy of the database, so there is no central bottleneck. Write operations still need to go through the source API, and read operations as well in cases where you must be guaranteed to see the latest version of the data (because this system would be eventually consistent). This approach introduces a lot of complexity so I would avoid it and stick with the central API approach as long as reasonably possible.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

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