Just read this article: https://microservices.io/patterns/data/database-per-service.html We are designing 2 microservices - both have tables in a shared db. What I'm wondering is if I build an API that provides access to these sets of tables... where should this (HTTP REST) API be installed? on a separate application server? or right on the database server itself? I'm leaning towards a separate APP server so that I can spin up as many app servers as I may need in future; and that the only place state is maintained is on the database server itself... but just want to be 100% sure.
The microservices provide an API to their data. The services themselves can access their own data directly, there is no need to add an extra abstraction in between. In this sense, the database is an implementation detail of a microservice.
If you want to expose a different API than your microservices offer, that would be a separate service or a kind of API gateway, but this API shouldn't know about the databases directly.
In any case, only build what you need. You can refactor the internal structure later if it has proven itself unsuitable. For example, if you deploy your microservices on-premises for clients, you may want to interface with whatever database system that client already uses. Then, some kind of persistence service to abstract over any differences could make sense.
Note that “server” can mean both “a piece of software that responds to requests” or “a piece of hardware that sits in a rack”. Every microservice should be their own software-server, though they may share hardware. However, larger databases have unique hardware requirements, in which case you should not run other services on your database hardware. In a cloud setting, all of this would be abstracted away anyways, especially when using some container orchestration mechanism.