SQL + M4 = Composable SQL
emiruz.com
emiruz.com
The only place where I tried using M4 in production and I know it's still used there, the final system ended up generating the M4 templates and the script to invoke it from another script, and then yet another script fixes up some loose ends left by M4. Effectively the m4 step is entirely redundant and was only left in there because the people picking up the project after me didn't want to remove Chesterton's fence. (I learned later.)
Maybe there's a more useful non-POSIX M4, but I really like the idea of a POSIX standard templating/macro-expansion language. Only M4 isn't it, in my experience.
https://www.gnu.org/software/autoconf/manual/autoconf-2.67/h...
The whole thing works with having a "template file" which looks mostly like the config with a couple M4 macros to source envars. Envar sourcing logic came from a base M4 file we included in each template at the top. There were a couple sharp edges with escaping but the obviousness of the whole setup made M4 fine. If M4 was a bit easier to work with I'd love to use it for something more complicated, but for our purposes it was fine.
$ is ASCII character 36. I believe the intended restriction is that M4 restricts macro names to be made up of letters, digits, and the '_' character (and cannot start with a digit), which means you can't have a $ in the macro name.
And I don't even swear as much at jinja as I thought I would.
dbt allows macros to resolve arguments at "evaluation" time by nesting queries (for example, get a list of DISTINCT values so that they can be then used as column names), which is really useful. I have a particularly nasty database schema (thanks, wordpress) to deal with, and I now have plenty of macros that do the dirty stuff for me.
> What is dbt?
https://docs.getdbt.com/docs/introduction
Collaborate on data models, version them, and test and document your queries before safely deploying them to production, with monitoring and visibility.
https://docs.getdbt.com/docs/supported-data-platforms
--
core: https://github.com/dbt-labs/dbt-core - Python, Apache 2 license
dbt's biggest strength is that you can incrementally expand it to fill the gaps in your existing solution: some source data quality tests here, a few automatic history-of-changes tables powered by dbt snapshot there, and with enough context you can then create push-button documentation and data-lineage graphs, which tends to be a lot more documentation than most companies' BI teams maintain natively.
The downsides that I have found so far (subjectively) have been basically that it's wholely geared for batch operations, it seems to expect to export to only one data warehouse and thus lacks a "many-to-many" data-mesh-like mode, and it wasn't easy to hack the documentation page to describe things like "the ETL process that populates this table". Also, it does not seem to have a way to define or create source tables if they do not exist (so it will not black-start a project for you, it's really only for existing data), and if your code is already checked in to SSDT you might have to move some code around.
...But I have complex problems due to $CURRENT_JOB's existing code base, organizational structure and business needs. (And yes, I am really liking my job.)
My needs aside, dbt is refreshingly easy to get started with, it provides value very fast relative to time invested, and you could do far worse than to spend a day or a week seeing what small annoyances dbt could help you with.
I do agree that the iterative workflow could be a bit smoother, and the doc side of things is a bit rough. I expected the project to gain a higher velocity than it seems to have right now.
This uncovered a ton of hidden assumptions and dirty data which I either cleaned up, or manually flagged in the views (as in, skip the orders listed in this CSV file because they stem from the period of time where shipstation blablabla).
Since then I'm continuously adding to it, refactoring the macros as if they were code, and it really feels in line with the normal workflow of a developer.
Good approaches here have some form of higher level validation and error messages, such that by the time the SQL engine gets your query, it runs well almost all the time.
Then you try to bite the sandwich, and half the ingredients go sideways: change one parameter, and the generated SQL can look wildly different, etc.
I still think DSLs are superior to macros though; a smart DSL transformer will catch a whole class of errors, while a macro engine like M4 could happily spit out garbage with syntax errors.
But it is perfectly possible to write correct SQL code. Just like it is possible to solve mathematical equations with pen and paper.
I try to accept SQL as it is and avoid any abstractions because they make the query impossible to read.
I write my queries top down using CTEs and test manually along the way.
And it turns out that 90% of the problems are not in the query logic but with the input data and the two villains of tabular data.
Duplicate rows and NULL values.
And then write sql statements for everything else.
In the example given in the article - composability mainly centers around extracting and reusing the contents of the CTE. Rather than resorting to an external template preprocessing tool and introducing “compile” stage that brings its own set of challenges (e.g you can no longer directly review or run queries, template syntax is not supported by IDEs) - one could consider extracting the body of the CTE into a sql view or sql function, which could then be reused as needed. You can even nest such reusable primitives. It does require some discipline, but it is much simpler to keep straight than relying on text templates.
Depending the database engine and subject to some constraints - many (postgres, sql server, for example) can already inline and expand such sql constructs during planning and execution time, for instance pushing down predicates as an optimization technique.
It also notes the potentially to do so with where clauses, window function and so on.
Extracting and templating other structural components of the query in the list you provided - I personally interpret as needless complexity that is likely to make things harder rather that easier, especially considering that there’s no database-native alternative mechanism that I can point to, and I already explained that I consider text templating an anti-pattern (though perhaps it does have value in those instances where the database engine is not as full featured as postgres - sqlite as an example).
I wrote up Using plain Jinja[1] or SQLAlchemy+Jinja[2] as complementary techniques. The post is a bit dated but the principles should still work.
- [1](https://github.com/gregn610/plpgsql/wiki/meta-sql-with-pytho...)
- [2](https://github.com/gregn610/plpgsql/wiki/meta-sql-with-pytho...)
Squeezing the repetition out of strings make sense, but Yet Another Mouth To Feed (YAMTF) has to bring something substantial to the project to justify inclusion.
These days it might be a bit different, as you might either be operating on a very stripped OS (container), and thus might need to provide all your tools anyways, or you might have a standard image with a more wider range of scripting opportunities (even Perl might be a good candidate here, as it's available on any system where you've got a non-restricted git installed).
The drawback is that query fragments written in esqueleto’s experimental DSL are a bit funny looking compared to the plain SQL you’re trying to write. And you’d have to learn a non-trivial amount of Haskell which is a tall order.
Using m4 to this purpose is an interesting, out of the box idea. I’m curious what challenges it has in practice.
You could do the same with jq.
Here's a previous discussion on PRQL:
There will be a big PRQL release in January which makes that previous discussion look quite dated. Best source for current state is the prql-lang.org website and the github repo (github.com/PRQL/prql).
Also try the playground - there you can live queries on real data in your browser.
You can have named pipelines which basically become CTEs, like
table employees_usa = (from employees | filter country=='USA')
Because PRQL only targets queries / DQL at the moment, I'm not sure what the sense multiple pipelines per file would be at this point.
You're welcome to open an issue or start a discussion on the Github repo about your use case though, we'd love to hear about it!
With the next release you will be able to create functions that are reusable pipeline segments (e.g. something like "top 5 items per group"). This works at the moment but is currently undocumented. Do expect there to be breaking changes on this still though as there are some issues around resolving names and namespaces etc... that we still need to work out.
There is a (currently rather small) stdlib of functions but beyond that we still need to think about a good way to share code and create reusable libraries.
(I am not affiliated with dplyr/R/RStudio in any way, just a huge fan.)
I always thought just building a pure .Net DB server using its types and language model would be very productive rather than translating to SQL, like PRQL or Entity Framework.
It's a nosql/multimodal document store written in .NET and supports LINQ-like syntax.
Written in .Net but not really using the .Net data type/model, more of a json db, used to use Windows only ESENT data engine but I think they got around to building their own K/V in .Net at some point.
I am thinking something more like https://velocitydb.com
> ".Net but not really using the .Net data type/model"
What's that mean? The .NET driver has seamless object persistence.
If I am designing a .Net database from scratch with Linq as its target query language then I would want the storage engine to understand and take advantage of types to optimize storage with minimal translation rather than having to serialize/deserialize from .Net to BSON/JSON with key overhead included.
RavenDB has collection-level compression across multiple documents which minimizes this: https://ravendb.net/articles/ravendb-5-0-features-smart-docu...
Yes that's exactly what I said.
.Net is a strongly typed language with a strongly typed data model, hence my point of RavenDB not being a .Net data model even though it's written in .Net. I want a .Net DB that takes advantage of the type system rather than a JSON DB I can access with a .Net client, there are plenty of those.
Document compression with dictionary training in a schemaless databases is a bandaid over their fact there is no schema. A Relational DB saves a ton of space with increased performance and lower CPU (rather than decreased performance and higher CPU with compression) because it has a strongly typed schema and therefore does not need to read/write the column names and types for every row and it can serialize column data to their most efficient form based on the type. This sort of efficiency flows through the entire system including better cache utilization, smaller indexes, and less data sent over the wire to the client.
You can still do compression on top of a schema for even more savings like most DB's but you can do even more interesting storage techniques like column storage vs row storage if you have a schema that can really save space and increase performance depending on usage. A .Net DB could similar things again because if a strong type system.
RavenDB looks like it has come a long way and Ayende definitely knows databases and .Net, however I am not a big fan of the JSON/document database philosophy, just my option based on experience. I prefer a strongly typed system that has an untyped escape hatch when needed, like PG with JSONB columns. That way you can stick to strong types and loose documents sparingly when needed. In a .Net DB that would be dynamic types being transparently stored with a loss of efficiency.
That's how they all work, with various trade-offs. If you want a ".NET database", the only example would be to simply dump the in-memory bytes to disk, which you can through the various serializers in the framework.
0 - https://docs.getdbt.com/docs/get-started/getting-started-dbt...
Velocity, Jinja, Mustache... all are a breath of fresh air compared to M4.
Query languages are a great target for metaprogramming but something like JooQ that works at the AST level is going to be a lot better than M4. (Recent versions of JooQ allow you to write transformations that work on that tree.)
I made an online demo here:
https://kuinox.github.io/TQLBlazorDemo/
Sadly we don't have any language docs...
Disclaimer: My company develops JupySQL
LINQ is a nightmare in these scenerios. Imagine 10s to 100s to 1000s of lines of indecipherable LINQ.
We spent so much time debugging these things and trying to make them performant.
After he sold the company I began replacing the LINQ garbage with inline queries with dapper when appropriate. So satisfying to see queries go from minutes to seconds to unmeasurable in SSMS.
The problem with LINQ is it's upside down and backwards to people who know SQL. You can easily create horrible queries and unless you're an expert in both LINQ and SQL you'll have no idea how to fix it. There problem was it was difficult in many cases to get LINQ to create performant SQL, when writing a simple(r) query was both shorter and faster.
We regularly hit the recursion limit in SQL Server and other errors I had honestly never seen in all my time using SQL Server.
I know SQL. I don't think LINQ is upside-down and backwards.
The entire approach of CTEs is flawed, because you are declaring your schema in query - by saying CTE1 is my $orders_query, instead of referring to a view formalized in schema: select * from views_tenders join views_sales join views_items - instead of a sandwich of CTEs
using macros with SQL is like using python script to autogenerate classes and interfaces in Java for $my_class and $my_interface, instead of having a formally defined hierarchy of classes and interfaces with methods and signatures
CTEs exist for very good reasons. Chief among those is that they make recursive queries easy to express and understand. But also because it lets you have one command that implements what might look like temp table/view creation, population of those tables, run various queries, and delete said temp schema, but the optimizer gets to see what all you are doing and optimize accordingly (there might be no temp tables in the optimized query). As well CTEs help with readability by letting you abstract sub-queries and outdent them.
CTEs do have some downsides, mainly that if you want to reuse them across statements, well, you can't.
> using macros with SQL is like using python script to autogenerate classes and interfaces in Java for $my_class and $my_interface, instead of having a formally defined hierarchy of classes and interfaces with methods and signatures
We live in a world full of generated code. The alternative is much worse, so we have to be able to debug through generated code.
But once several different queries start sharing common CTE ingredients - this is an indicator that you are relying on implicit(informal) schema, which should be formalized and formalized.
Just take your CTE ingredient queries and declare them as views, isolated to your namespace. And let everyone reuse your views, instead of copy-pasting CTE ingredients, or using code-generation to achieve the same.
The benefit is single source of truth - there will be only one definition of CTE subquery, and it can evolve/extend independently while letting everyone reuse your parts.
I assure you - if you take your CTE with 4 subqueries, and instead create 4 views, the query plan will be the same regardless of using CTEs with code-generation or using views.
The benefit is you dont have to use codegeneration, each view will be testable/verifiable
Idk why Postgres doesn’t disable materialization by default. IME it’s a niche use case compared to using CTEs as intermediate views (where you want to expand all joins and use indexes). It’d be better to have an explicit opt in “Yes, store this result on disk, disable all indexes, and process it O(N) fashion.”
https://www.postgresql.org/docs/current/queries-with.html#id...
It wasn’t the point of the example, but the default is scary, leading to disabling all indexes for the query once it gets more layers.