enterprise architecture – Is there any recognized pattern supporting or discouraging the access to multiple database from a single application?

Connecting multiple applications to the same database is generally a bad idea. It’s an integration anti-pattern you might call The Shared Database and it’s unfortunately a very easy anti-pattern to fall into.

It’s common for a database to be created as part of building an application. That application needs to store data somewhere, right? So you need a database and you create one. But then, after some time, other applications get created that might need access to some data that already exists (the usual suspects: users, customers, products, orders, etc). How do they get the data? Well, nothing simpler: just connect to the same database, right?

And now you have problems:

  • a database is an implementation detail. Now you exposed this to other applications;
  • an implementation detail can be changed because it’s just that, an implementation detail. Want to change the implementation of your database, change tables structures, schemas, etc? You can’t do it anymore without affecting other applications (anyone with the database connection credentials can basically reach out into your database and fetch data – you might have applications connecting to your DB without even knowing they exist).
  • if your database isn’t exactly how others need it, you might need to make changes to your DB for someone else’s use case. Or they will do so themselves 🙂 and break your application with your own database.
  • hello tight coupling, bye bye loose coupling. That’s bad however you put it.
  • you only store data, you don’t store behavior. The behavior is in your application. How can others reuse behavior? Maybe create a crap load of stored procedures to share the behavior. You get more and more entrenched into using that database vendor implementation. Want to change from Oracte to MySQL? You now need approval from others.
  • etc.

A shared database is like having neighbors paying you a visit and then refusing to leave. You now have to live with them.

So exposing “your” database to others by using and API, or a gateway, or a facade, or a service, or whatever, is a way to share the data and behavior without having to live with your neighbors. It’s a good practice that hides the implementation details. Of course it’s not without it’s disadvantages: it’s more complex than “select * from”, more verbose, it still is an integration point (which needs to evolve, maybe not at the same pace for everyone using it, so you now need API versioning), etc.

When it comes to one application connecting yo multiple DBs (as your title asks), things are simpler and less problematic concerning what I said above. But again, it’s a trade-off. Transactions might suck across multiple databases, so will joins. Keeping things separated makes sense but will cause performance degradation as now you need to open connections to more databases, etc.

At the end of the day, it’s not about patterns or anti-patterns, but trade-offs, thinking carefully about one design decision or another, and basically, a good dose of common sense.