Connecting multiple applications to the same database is often 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 its data somewhere, right? You need a database so 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 are 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 some problems:
- a database is an implementation detail. You now exposed it 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 whatever data they want - you might have applications connecting to your DB without even knowing they exist).
- if your database structure 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 the help of 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 your behavior? Maybe create a crap load of stored procedures to share the behavior. And now you get more and more entrenched into using that database vendor implementation. Want to change from OracleDB 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, a gateway, a facade, 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 its 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 to multiple DBs (as your title asks), things are simpler and less problematic concerning what I said above. But this is also a trade-off. Transactions might suck across multiple databases, and so will performing joins. Keeping things separated makes sense but will cause performance degradation as now you need to open connections to more databases. You might also cause problems some place else, if other applications connect to the same databases (i.e. not just "your" application to "your" own multiple databases), etc.
At the end of the day, it's not about patterns or anti-patterns, but about thinking carefully about one design decision or another (i.e. do you need one DB or more and how you will access them), trade-offs, and above all, a good dose of common sense.
