Someone on Reddit suggested stored procedures, which seems like a good alternative.
Alas, SQLite doesn't have them, so query building it is.
Alas, SQLite doesn't have them, so query building it is.
Simply stated the problem is: Viewing code as data (in a lisp sense) and transforming arbitrary data into a query that can be executed.
Stored procedures are the slipperiest slope I've seen as a developer.
If you have good knowledge and experience in the language your preferred version of SQL implements, that's good. If you just have people that understand how to optimize schemas and queries, you might find that you encounter some of the same problems as if you shelled out to somewhat large and complex bash scripts. The value of doing so over using your core app language is debatable.
That said, I wasn't making a case about replacing your DBMS. I specifically avoided that because yes, most people stay with what they know and used, and even if they switch, they switch for a different project, not within the same project. There are some cases where multiple DBMS back-end support is useful, but I think that's a fairly small subset (software aimed towards enterprises which wants to ease into your existing system and note add new requirements, and open source software meant to use one of the many DBMS back-ends you might have).
My actual point is more along the lines of:
- Most DBMS hosted languages I've seen are pretty shitty in comparison to what you're already using.
- The tooling for it is likely much worse or possibly non-existent.
- You are probably less familiar with it and likely to fall into the pitfalls of the language. All languages have them, shitty languages have more. See first point.
- If you accept those points and the degree to which you accept them should definitely play a role in deciding to use stored procedure you've written in the language your DBMS provides.
- I think trade offs are actually similar to what you would see writing chunks of your program in bash and calling out to that bash script. People can write well designed and safe bash programs. It's not easy, and there are a lot of pitfalls, and you can do it in the main language you're writing probably. Thus the reasons against calling out to bash for chunks of core are likely similar to the reasons against calling a stored procedure.
To me, the shitty procedural languages you mention are just for gluing queries together. The important stuff happens in SQL and the simplicity of keeping it all in the database is worth it.
So, the question is, does your team know SQL/PSM, or PL/SQL, or PL/pgSQL, or some other variant, and how well.
There are a lot of data retrieval where a stores procedure will will save you incredibly amounts of resources because it gives you the exact dataset you need exactly when you need it.
With SQLite, your entire application IS the process, and your SQLite data moves from disk to your app processes RAM.
I think the SQLite model is much better as you get to use your modern language and tooling (which is better than the language used for stored procedures which has not changed in 20 years, and is generally a bear to observe, test, develop).