The interfaces I have used in my experience were built in-house over Apache Spark and Microsoft SQL, and always felt overengineered and more complicated than it needed to be. For the record I have learnt data retrieval and analysis only on the job and not from any formal training, apologies if I am missing something fundamental.
Generally though, I want developers to justify why SQLite isn't good enough for their app before they build on something else. In a lot of cases SQLite absolutely is good enough, and it removes an insane amount of complexity, especially from things like database backups.
Though I have used Firebird when I needed to scale it support local and remote data before. I don't think Figured gets nearly enough attention.
In the end, there was a complex solution with a all the business logic in stored procedures and functions. With a lot of variability and difficulty supporting the application.
This was for a petition verification system. So each petition could have easily fit into a SQLite database. Been portable and easy to archive, backup, transport etc.
But no,. We had to put all the logic in the database... Because integritai...
> SQLite does not compete with client/server databases. SQLite competes with fopen().
Store stuff in SQLite now, and use a RDBMS later when it makes sense.
In terms of a simple API over SQLite, it will depend on the number of users, the overhead and the cache ability of tall data in practice.
If you have a heavy transactional load, it may not go so well. Similarly, any service may be better implemented in a single connection with seriously requests, which will limit this but reduce complexity.
Realistically, PostgreSQL in Docker is pretty much my baseline for a shared database server. If other systems really need to access it, it's going to be the easier path.
Even Firebird would likely be a better option for many lower end use cases where a shared service is needed.
I wish there was a real simple way to connect to a remote db like you do with postgres.
This tool wont necessarily know about any specifics about how the database relates to your business.