In the last few days I’m experimenting with substituting CRUD API code with Stored Procedures which directly produce the endpoints JSON as a single-row scalar value. API is then just a wrapper that authenticates, validates input and streams the DB’s JSON directly to the client.
No ORMs, no SQL generators etc.
All SQL is where it should belong: in the database
API does only single „CALL myfunc(…)“ db calls
A simple centralised error handler can accurately report errors from the database
No weird mixed row/json columns scanning into structs and re-marshalling everything to JSON
Codebase is collapsing to 20% (by LOCs)
Stored Procedures can use wonderfully declarative SQL code
Response times in the microseconds, even for multiple queries, all happens inside the DB
More side effects:
the data model can change and evolve without touching the API at all
Zero deploys mean zero downtime
the API application is so tiny, I could easily switch it to any programming language I want (yes, even Common Lisp) without worrying about available databases libraries, type mapping and rewriting tens of thousands of lines of intermixed language/SQL-code.
The general direction of the dev industry is heading in the opposite direction. More ORMs, more layers, more database abstraction. More weird proprietary cloud databases with each their own limited capabilities and query language.
So you tell me: Is it crazy? Is it wrong? Why do I have doubts despite everything working out beautifully?
Intermediate report on my "Stored Procedure" project (long post).
I've now written 35 stored procedures and functions ( in MySQL ) replacing a massive amount of application code.
MySQL's stored procedure syntax is very capable, I had to write only a few functions to add some JSON conversion comfort. I can use multi-result sets and had to modify the MySQL driver to Go to support OUT parameters, not a big issue.
What I can say is that a really good SQL editor that "understands" your code and provides some intellisense while writing helps a lot. So far I stick with #DataGrip, which also supports syncing the DB schema with Git, so every change can be properly tracked. It has a price tag though.
I've tried MySQL Workbench, DBWeaver, TablePlus, Querious. None of those help with writing stored procedures like DataGrip does.
#DevArt's MySQL Studio could be another candidate but is Windows-only and painfully slow running with #Crossover on macOS.
I've replaced really super-ugly dynamically generated SQL code with beautifully pure SQL queries, discovering a lot of bugs during the process. Now, looking at the code, I can see what it does, not just hoping that it will work out. Reminder: I have to work with a complex data model spanning many tables, so dynamically adding JOINs and ORDER BY clauses is not easy and amount to potentially hundreds of test cases. Not with Stored Procedures.
If you are really interested in a big-boy query involving spatial queries (distance from point, bounding box etc.), LATERAL CROSS JOINs and parameterised ordering, check this out:
What surprised me most is that MySQL is fast and I mean really fast. In most cases involving complex JOINs over many tables much faster than PostgreSQL (which is everybody's darling, I understand). Having this performance at hand I could replace all hourly-updating MATERIALIZED VIEWs with real-time queries.
Also, MySQL has excellent Spatial functions and a decent FULLTEXT search, I don't miss anything from PostgreSQL right now. Also its native Event scheduler (a cron for SQL), makes table maintenance a breeze. No more separate SQL scripts running in a crontab anymore.
PostgreSQL pg/plsql syntax is bulky, ugly, and hard to read (with::all::those::automatically added::type::casts). MySQL doesn't need this.
What MySQL is not good at: error messages are super-crappy and don't help most of the time ("you have a syntax error, check that your code is correct ..."). So a high level of SQL confidence helps, but documentation is very accessible and will structured.
There is still a long way ahead, I think I've migrated about 50% of the application right now - there are still some complex use cases to solve but I feel confident about this approach.
If you still think about MySQL capabilities in terms of versions 5 or earlier, you will be surprised how far it came with the most recent version 8.
I know Oracle is universally hated in the Fediverse, but: don't forget that most of PostgreSQL recent "let's add hundreds of features every 6 months" party is also mostly driven (funded, developed) by commercial parties looking to lure away Oracle and SQL Server clients.
I can't wait to finish this project and will keep you all posted.
#mysql#sql
(#)PostgreSQL (let's not make half of the Fediverse angry... 🙂 )