Louis :emacs: on Nostr: Intermediate report on my "Stored Procedure" project (long post). I've now written 35 ...
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:
https://pastebin.com/DKBzR19i
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 make half of the Fediverse angry... 🙂 )
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:
https://pastebin.com/DKBzR19i
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 make half of the Fediverse angry... 🙂 )