Here you go. I was in the car when I responded earlier.

“It’s not right for every situation, but several years ago I worked on a project with two separate code bases with recurring subscription systems. Due to a dispute with one of them and some limitations in services, we had to quickly request for every single user to manually update their subscriptions from one platform to the other. In order to make it as easy as possible, the new system needed to be able to know everything about the subscription on the old system so that users would only need to update their payment information and click a button. Our choices were to build an entire API on the old system with a legacy code base that nobody understood or to let the new database connect to the legacy database directly to perform joins. We ended up going with the second option and had a working version in production under two weeks with no issues. Everyone migrated successfully and the crisis was averted. Sometimes SQL is the best API.”

This seems really good. The only comment I have on reading is that when you are able to move more toward security, it might be worth looking at what Hashicorp Vault does regarding temporary credentials for access so that the stakeholder bootstrapping process might be able to not have setup configurations that are wholly manual.

Unless I misunderstood you want to give the user and password to a client in the web to access the database directly?

Yup, I think it’s a slight misunderstanding in that the user and password are meant to be given to an application, not to a client. I.e.: think a Phoenix app which you would typically provide a DATABASE_URL to, not a React client that might typically talk to an API endpoint.

Thanks for flagging up as I should make that clear. You definitely shouldn’t expose the db user credentials publicly

Also, the cURL request can be done from a client in the internet?

IntegrateDB is an application you would deploy on your infrastructure (server / cloud / cluster) fairly close to / alongside your database. So you can lock down access as you prefer – network firewall, RBAC, reverse proxy, etc.

The API is also envisaged as something more for an operator to talk to (a CLI is on the roadmap) or to use as part of an provisioning workflow. It would be unusual for your application to talk to it.

Hope that makes sense

It’s an often overlooked and under appreciated need. I once had a project where I needed to connect two systems and my options were to either build an API so one application could read from the other…or connect the databases so I could just query both at the same time.

Went with door #2 and the entire urgent project was done in 2 weeks. Not a single hiccup was experienced.

Exactly

There are some nuances. Particularly the replication consumer and notifications system as envisaged need to be a running service. However, it may well be possible to refactor out so these are different tools in the box – a library / local ops client and a notification service. The latter could run with just replication permission and wouldn’t expose the db in the same way.

Thanks for the suggestion, much to ruminate on!