Right, but it lives with your application code and has the same syntax as the application code. That's probably preferable to SQL stored procedures (which often live outside source control).
It’s not hard to get SQL DDL and stored price in source control with Liquibase or Flyway. I’ve even done TDD sproc unit (integration) tests in them. But I’m a webapp turned data engineer guy…
That's probably preferable to SQL stored
procedures (which often live outside source control).
Stored procs definitely have some big pros and big cons, but I don't think this is one of them -- any ORM with a decent set of tools to manage migrations (ActiveRecord is one) makes this objection a non issue IMO.
I explicitly do not want to manage stored procedures in the same way as typical migrations - if I did, I would wind up with many, many versions of the procedure in my code base as it evolved over time. This would make grepping or locating the latest version pretty annoying.
Flyway (migration tool in Java) has a notion of “repeatable” migrations, though, which would do the trick.
This would make grepping or locating the latest version pretty annoying
Wouldn't this be an issue with any database object managed via migrations? Do any of them make this easy for any database object?
In ActiveRecord, you have your migrations folder(s) and then you have your `structure.sql` (essentially the raw output of mysqldump or pgdump) or the equivalent.
If I need to see the literal database definition of any database object I look it up in there. Not the slickest solution but works well enough - really just a few keystrokes in my editor.
I'd be curious how other migration tools handle (or fail to handle) this.
I’ve found that all this does is make the query less readable. SQL is purpose made for writing queries, and avoids unnecessary syntax noise you get when trying to fit the query into a host language based dsl.
That really depends on the language - specifically, on whether it already has constructs that can map nicely (e.g. LINQ in C#), or macros to define them, or syntax that is generally amenable to DSLs even without macros in the picture (e.g. Lisps).
SQL itself is also not a particularly well-designed query language. E.g. the order of the query doesn't reflect the natural data flow (SELECT .. FROM .. is reversed - compare to XQuery's FLWOR, for example), there are warts like WHERE vs HAVING etc. A good DSL can do much better.
SQL is powerful. A DSL that "fixes" things in this area getting all the other language feature interactions right isn't trivial, all the while users have to learn yet another language. Take PRQL for example: https://prql-lang.org. It looks nice, but the examples are very basic. What about window functions, grouping sets, lateral, DML, recursive SQL, pattern matching, pivot/unpivot etc. Might be doable, but perhaps, they've already made a decision that won't enable one of those features without adding new kludges.
Besides, every single "fix" will be a proprietary solution, while SQL is an ISO/IEC standard that's here to stay and universally adopted.
> A good DSL can do much better.
Stonebraker's QUEL was "better", before SQL, and yet, where is QUEL today?
> Take PRQL for example: https://prql-lang.org. It looks nice, but the examples are very basic. What about window functions, grouping sets, lateral, DML, recursive SQL, pattern matching, pivot/unpivot etc.
Window functions are very much supported! Check out the examples on the home page & in the docs.
The others aren't yet, but not because of a policy — we've started with the most frequently used features and adding features as they're needed.
> Besides, every single "fix" will be a proprietary solution, while SQL is an ISO/IEC standard that's here to stay and universally adopted.
And yet in practice the fixes end up more portable. How many of the things on your list of non-basic SQL have consistent syntax across databases, yet alone consistent behaviour?
But the point here isn't just that it can be more regular than SQL. Integrating with the syntax of the host language is also a considerable advantage, ideally with static type checking.
In a statically typed language, what you get from a good query builder is that "malformed SQL statements" blow up at compile-time instead of at run-time.