Top 9 features a database IDE should have for serious development of procedures/functions:
Knows about the SQL dialect I'm currently developing with (i.e. Postgres, MySQL etc.)
Introspects the schema including views, procedures etc.
Can do suggestions while I type, not only on tables and table columns, but also on parameters, variables and temporary tables defined inside a procedure
Can spot unused variables and references to non-existing tables/columns
Shows table and column definitions in the suggest box and on hover
Does not drop the procedure before it is re-created, until it can prove that it will compile. That could be accomplished by creating the procedure first under a temporary name
Shows dependencies between tables/columns and procedures, even if that is not natively supported by the database engine (again, by introspection)
Lets me quickly jump between different database objects without filling out a "search form"
Provides context-sensitive documentation of built-in database functions
While all of this is fairly standard for any programming language, the only DB IDE that can do all of this right now (at least for MySQL and Postgres) with exception of (6) is JetBrains DataGrip.
Other rather expensive tools only provide a small subset of these features. Tools I evaluated:
Navicat (most expensive and highly unstable)
DBeaver
TablePlus
Querious (macOS only)
DevArt's dbForge (Windows only, but the only tool that features a step debugger (!) for Stored Procedures)
MySQL Workbench
Pgadmin
DBVisualizer
Database Client extension in VSCode from Weijan Chen
Azure Data Studio
It seems that Jetbrains has literally zero competitors in this area - so they deserve a bear hug from me.
I'm now almost through migrating PG to MySQL with Stored Procedures only. Ended up with 140 Stored Procedures. The insights I gained into the business domain are incredible.
Now there are some bigger challenges:
How to test an API that literally has hundreds of different endpoints + parameter combinations against the new version
How to transfer data of a 100GB+ sized PG to MySQL in a timely manner so that downtime is reduced to < 15 minutes.
Or even more challenging: how to transfer 60 PG tables to MySQL with a "slightly" optimised schema and a buggy pg_dump exporter, that wrongly decodes JSON values into unreadable data (bug filed 2015, maintainers not interested)? Or a buggy PG_MySQL Foreign Data Wrapper that fails with Boolean and JSON columns (bug filed in 2020, maintainers not interested)?
I've tried 10 different tools that advertise themself as a solution to this and not a single one was able to overcome these challenges (issues with JSON, Timestamp and Boolean columns). Any hints?
So if "interoperability" is a goal of the SQL standard, it clearly failed. If "interoperability" is a benchmark for open source databases, Postgres doesn't shine at all. All the features that make Postgres "so good" (like ARRAYs which are unknown to every other SQL database, BOOLs and Custom Types) are in fact locking your project in like forever.
However, I'm not the one who gives up easily. I'll likely end up with a hand-rolled migration tool and then sell it to make a fortune off it, for all those non-existing devs who want to migrate away from Postgres. :neofox_evil:
I did it! I put my M.2 SSD into the USB enclosure, I then plugged it in my linux laptop, got the file system mounted, was able to copy its mysql data dir to my laptops mysql data dir, start the mysql server and poof! was able to export my DB. Now it's up and running on AWS and all is good! Yee Haw!!!! #Linux#AWS#Sendy#MySQL
Speaking as someone that's primarily used #MySQL, are any of the other #SQL dialects REALLY so different that job ads need to specify which one? I've never had any actual problems the few times I've worked with #PostgreSQL or #MSSQL.
Have an odd #MySQL issue. Two MySQL dumps: schema and data. Schema has drop if table doesn't exist, imports fine. Data gives several Duplicate entry '1' for key 'PRIMARY' errors and some tables are missing lots of data. Using -f on import doesn't help.
Upon inspection, the data file has no duplicate primary keys. Quadruple checked this. So... why the errors? There is a mysql server version difference, and the data is 16G. Perhaps a config issue?
I want to write a review document about a relational database. Are there any tool / language / format to help with not only describing tables and relationships, but also commenting and adding suggestions?
So far the only things I found only renders something about the current state of the database (like a UML diagram), but I can't add manual or programmatic comments.
This weekend was the one when a moderately important 1.2TB #MySQL table gets locked up because its primary key reached its max unsigned int value, and no more inserts could be performed 🥶
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?
No, you're not crazy. You're using a domain specific language to do what it's good at, and you're moving compute closer to the data which always improves performance.
It just means you need people experienced with DB/SQL, which isn't as common, which is why people aren't doing it as much.
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... 🙂 )
Intermediate report 2 on my "Stored Procedure" project (long post).
I think it's time to talk about some of the downsides of Stored Procedures.
I think I've now accumulated enough knowledge to do so. The following applies mostly to MySQL.
Drivers
Support for Stored Procedures in Go is terrible. It is clear that maintainers of the drivers do not care to implement full functionality for Stored Procedures, just because "nobody uses them".
One notable exception is the Go driver for SQL Server, which is now maintained by Microsoft itself and is excellent.
I patched the MySQL driver for Go to support OUT parameters, after studying the MySQL Client/Server protocol and writing a small prototype driver myself. The MySQL driver[1] project is plagued by lack of interest and rudeness of the maintainers ("you are wasting my time" is a common response). What makes the situation even more complex is that there is also MariaDB and both act slightly different and start to diverge more and more.
I think it would be wise for #Oracle to step in and produce an official Go driver for MySQL exclusively.
However, after some hours I figured out the issue and I was able to implement full support for OUT parameters and multi-resultsets.
Tooling
There are many UI tools for macOS but the only one that is capable of serious database development is DataGrip from #JetBrains. Other UI tools either have no support for Stored Procedure development at all or are too rudimentary in that they provide no language support. I've tried them all.
Dev experience
I love writing SQL for Stored Procedures but there are some unique downsides I want to highlight.
– Passing table data between Stored Procedures is only possible either by creating temporary tables and "by convention" use them in the other SP or by creating JSON Arrays. Since tools do not know about these temporary tables, they will regard their usage as a potential error in your code.
TEMPORARY TABLEs are also the only way to collect multi-row SELECT results of a Stored Procedure.
– MySQL does not support the INSERT INTO/UPDATE ... RETURNING clause [unlike PostgreSQL], meaning that if you do multiple inserts/updates and want to know the auto-created IDs of these rows, this is only possible by using a CURSOR with a LOOP and accumulate the results in a TEMPORARY TABLE.
– CURSORs are ugly beasts. You have to DECLARE them (which is only allowed at a specific position in your code), OPEN them, FETCH row by row into user variables (which you are have to declare, too) and CLOSE them.
– Exception handling in Stored Procedures is somewhat convoluted. There is no Try/Catch construct. You can "SIGNAL" errors or warnings and can optionally write HANDLERs for specific exceptions but these do not transfer execution, you need to create and modify variables that you have to check in your code, since the SP just continues to run.
– MySQL gladly accepts SPs with buggy code. It is imperative to write "Test-SPs" to make sure they run as intended.
– Since SPs are defined in the same namespace (database) with your tables, views, functions etc. it is important to find a clear naming scheme, otherwise it will get very messy. There are no "packages" or "schemas" in MySQL.
– MySQL does not have user-defined types. If you declare variables for a specific column over and over, you have to rephrase theirs data types. PostgreSQL has a way to say "this variable is of type [table.column_xyz]". Not so in MySQL.
Having said all that, writing pure SQL with all these limitations provides a sense of clarity to focus on the problem at hand. Instead of thinking about "how" to solve, you focus on "what" to solve. Because you can solve anything with SQL [2] :-). Also, your app will be damn fast with Stored Procedures.
I hope I can cross the finish line next week and have actual results to share.
On a #Ubuntu 20.04 (Focal) #Linux server, #MySQL should be upgraded from 5.7 to 8.0, using the MySQL community packages from the official MySQL repositories.
But a "simple task" turned out to be a weird head-scratcher. But read yourself. 👇