I’ve built a way to manage database connection logic and how I process SQL statements in a centralised place (the API is containerised and hosted as a microservice in GCP Cloud Run) and I think this project is pretty cool and useful

Maintaining database logic in a central place makes a lot of sense seeing I’m working on multiple apps that all make use of the relational database and I don’t want to have to make the same improvements multiple times

Shipping the database server logic as a composer package wouldn’t solve this problem. I’d still have to run the composer update command, build the containers, push them into the registry and invoke the deployment pipelines for multiple apps

My relational database API and client looks like this:

With this approach, I’ll have a consistent relationship between the relational database client and API/server. I can make changes to the database connection objects and even switch between relational database servers, all without affecting the client. The API supports both MySQL and PostgreSQL as a database server type (both include support for UNIX domain sockets as well as TCP/IP as the connection types)

JSON is the data interchange format and the database API client ships as a composer package. An interface always ensures that the query methods exist and take in the expected parameters. The database API client hardly ever needs to change. When interacting with the relational database, the client will always be working with the database name and SQL statement. The database API/server, on the other hand, may change a lot. If I can’t afford to use Google Cloud SQL anymore, I’d like to easily be able to move to something cheaper or if I don’t want to use persistent database connections anymore, I’d like to simply be able to not use them

If the SQL statement returns a result table, it will be cast into an associative array and this structure will always be provided to the client (validation occurs on output on the API/server and validation occurs on input on the client). The client can optionally instruct the server to store the result table (the associate array is serialised) in memcached (the in-memory key/value store that’s shared between all my apps) for quicker retrieval in future (the key expires after 15 minutes)

I built a return type that I use on both static methods in the database API client class that makes it easy to search through the result table and return specific rows or simply return the entire result table

databaseAPIClient::query(string $database, 
                         string $sql, 
                         bool $cache = false): jsonResponse

databaseAPIClient::queryV2(string $database,
                           string $sql,
                           bool $cache = false): jsonResponse

I built version 2 of my relational database API in less than 3 nights’ and the functionality I get from this work is well worth the effort