9 ms·
SQL + M4 = Composable SQL
- joelschw 4y agoWhilst I hate it - Jinja is so much more accessible for SQL coders.
- deleted 4y ago[deleted]
- scapecast 4y agoThis sounds very similar to what dbt does.
- usgroup 4y agoDo you have a link to an example of DBT doing something similar?
- scapecast 4y agohttps://news.ycombinator.com/item?id=34160408 https://news.ycombinator.com/item?id=34160408
- mritchie712 4y agoCheck out their guide[0] 0 - https://docs.getdbt.com/docs/get-started/getting-started-dbt-core https://docs.getdbt.com/docs/get-started/getting-started-dbt...
- billythemaniam 4y agoDBT has two mechanisms to implement the blog post. The first is macros which closely resembles m4. The second is "ephemeral" models which are separate SQL files which get compiled into a CTE when referenced in the main model.
- kqr 4y agoPOSIX M4 is one of those things I really want to be good for production, but whenever I've tried using it in production it's been insufficient for anything even moderately complicated. (It's not that things are impossible, only that it's more maintainable long term to replace the m4 invocation with a Perl script or a more potent templater like Jinja2 in the cases where the macro expansion needs are large.) 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.
- usgroup 4y agoI think that M4 can be fine in production. Many years ago, I had used it to re-write a massive Squid Proxy configuration file which was mostly a collection of repeating blocks. The file was so big that no-one really understood how it worked, and it evolved by constant monkey patching. Post M4 and distilled to just its non-repeating elements, it was an order of magnitude smaller but also clearer because the macros designated the patterns and what wasn't in a macro was actually unique.
- cbsmith 4y agom4 is fine in production if what you're doing really is just string manipulation. It just tends to be used in cases like the one in this article, where you are manipulating structured text. In that context, you run the risk of errors/complexity/fragility because you are manipulating strings rather the structures.
- cbsmith 4y ago"rather than structures"
- 4y ago
- transfire 4y agoEcto in Elixir/Phoenix world provides much of this.
- hestefisk 4y agoYou might also like https://metacpan.org/dist/bin-sqlpp/view/bin/sqlpp https://metacpan.org/dist/bin-sqlpp/view/bin/sqlpp
- gregn610 4y agoI've found text manipulation of SQL can quickly degenerate into a nightmare of concatenation, nested quoting & escaping. 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-python-&-sqlalchemy#jinja2 https://github.com/gregn610/plpgsql/wiki/meta-sql-with-pytho...) - [2](https://github.com/gregn610/plpgsql/wiki/meta-sql-with-python-&-sqlalchemy#solution-3 https://github.com/gregn610/plpgsql/wiki/meta-sql-with-pytho...)
- larve 4y agoI've been using dbt for achieving the same goals and it's honestly been a bliss. Macros, packages, cross referencing views, unit tests, documentation generation, snapshotting. 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.
- password4321 4y agoIntriguing! I would like to learn more about how well dbt can "take over"/reverse engineer an existing database. > What is dbt? https://docs.getdbt.com/docs/introduction 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 https://docs.getdbt.com/docs/supported-data-platforms -- core: https://github.com/dbt-labs/dbt-core https://github.com/dbt-labs/dbt-core - Python, Apache 2 license cloud: https://www.getdbt.com/pricing/ https://www.getdbt.com/pricing/
- NortySpock 4y agoI've been using a bit of dbt to smooth some of the edges of an existing SQL Server analytics solution. 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.
- smitty1e 4y agoI'm challenged to see the virtue of M4 for string mangling over a scripting tool (e.g. python). 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.
- mhd 4y agoIsn't that the very point of picking m4, as it's always there on a POSIX system, whereas a proper scripting language might not be? It's been a while since I saw someone pick m4 when alternatives were present. 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).
- jjgreen 4y agoFewer deprecation warnings?
- ergonaught 4y agoI used XSLT for somewhat similar purposes twice upon a time. It hurts to realize that that was 20+ years ago, but I was producing many dozens of megabytes of complex SQL and PL/SQL. There was no way to do that "on time" while writing everything by hand, and "refactoring" would have been laughable.
- cfeduke 4y agoI did something similar when I figured out XSLT was for more than just front-end stuff. And I was working with Oracle as the database too; these enterprise code bases acquire huge amounts of often poorly written SQL and it feels like making at least some of the repeated queries DRY would help - but later you realize the people who need to maintain this system when you're gone are the same people who came up with the spaghetti queries in the first place.
- usgroup 4y agoCould you say a bit about how you'd manage a SQL repo using XSLT? It isn't obvious to me how that works.
- cryptonector 4y agoNot OP but I reckon OP would have XML documents from which to generate SQL schema and queries, with one XSL for generating schema, and one or more XSLs for generating queries. You could do the same with jq.
- snthpy 4y agoHave a look at PRQL (www.prql-lang.org)!
- nmstoker 4y agoThanks for the reminder. I knew this was reminding me of something! Here's a previous discussion on PRQL: https://news.ycombinator.com/item?id=31897430 https://news.ycombinator.com/item?id=31897430
- snthpy 4y agoFull disclosure: I'm a PRQL contributor. 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.
- cbsmith 4y agoHey, maybe you can help me with something about PRQL that I've never bothered to investigate (and I'll confess this is laziness on my part). If newlines just extend a pipeline, what separates pipelines? Do semicolons separate pipelines just like they separate queries in SQL?
- snthpy 4y agoI don't recall us discussing that before. You can have named pipelines which basically become CTEs, like table employees_usa = (from employees | filter country=='USA') but ultimately there is usually just one "main" pipeline/query per file/input. 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!
- usgroup 4y ago
- qxmat 4y agoThis appears to be the domain of Dagster + dbt, with which you get data lineage, not just composition.
- cbsmith 4y ago"Note that M4 doesn’t support non-ASCII characters in definitions so my ideal macros had to lose their $ prefixes." $ 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.
- Natfan 4y agoThe M4 Macro Processor by Brian W. Kernighan & Dennis M. Ritchie, 1977-07-01 https://wolfram.schneider.org/bsd/7thEdManVol2/m4/m4.pdf https://wolfram.schneider.org/bsd/7thEdManVol2/m4/m4.pdf
- agentultra 4y agoIf the author was willing to learn Haskell there’s a library, esqueleto, which does exactly this. It’s better than the m4 solution in that the type system will ensure the query fragments are composed correctly with respect to the typing rules of Haskell. 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.
- jdub 4y agoWhile I love SQL, there's something deeply twisted in the mind that combines SQL with M4.
- PaulHoule 4y agoI’ve used M4 when I’ve needed to generate a large number of configuration files for different systems from a single source. 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.)
- edublancas 4y agoReminds me of JupySQL, which allows to compose large SQL queries: https://jupysql.readthedocs.io/en/latest/compose.html https://jupysql.readthedocs.io/en/latest/compose.html Disclaimer: My company develops JupySQL
- yafbum 4y agoThe big problem with macro approaches is that when you get to nontrivial sizes, they get impossible to troubleshoot. You get an error from the SQL engine but that's an on expanded macro. If you've availed yourself of "composability", it's a deep tower of macros, and you typically can't easily tell from the error location and message on the expanded SQL which macro is the cause of the problem. It can become really difficult to maintain. 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.
- rollcat 4y agoYou get this problem with any sufficiently tall sandwich of abstractions. SQLAlchemy has a query builder, the DSL is easily directly translatable to actual SQL statements in your head. But since you have the full power of Python at your disposal, it gets quite convenient to abstract away some of the boilerplate. And then some of the common parameters. And then some more stuff, just for good measure. 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.
- fifilura 4y agoI have also seen this before. The knee-jerk reaction of programmers who are subjected to the imperfection of SQL is to introduce more abstractions. 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.
- habibur 4y agoAbstract INSERT, UPDATE, DELETE and select by id, as these are common idiomatic tasks. And then write sql statements for everything else.
- Communitivity 4y agoM4 is a nightmare in many respects, in my experience at least. I worked with it on a project and it was the biggest source of issues. It is hard to debug, doesn't handle I8N, convoluted rules, everything is a macro.
- gigatexal 4y agoGnarly queries aside what this highlights for me is the verbosity and readability of SQL code. Making me have to lookup queries being used as modules or as libraries makes things more complicated added to that all the idiosyncrasies of the templating language as well
- haolez 4y agoHmm I wonder what's the synergy between this and something like PostgREST or Directus.
- srcreigh 4y agoCTEs considered harmful. This query will run nornally as long as those CTEs stay unmaterialized. But all you need is a single intermediate CTE and due to the “two+ references cause CTE materializations” rule in Postgres, the query will start to load an entire table into an unindexed set and use that for the query. Basically, disables all indexes for that table. 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.”
- Footkerchief 4y ago> You can override that decision by specifying MATERIALIZED to force separate calculation of the WITH query, or by specifying NOT MATERIALIZED to force it to be merged into the parent query. The latter choice risks duplicate computation of the WITH query, but it can still give a net savings if each usage of the WITH query needs only a small part of the WITH query's full output. https://www.postgresql.org/docs/current/queries-with.html#id-1.5.6.12.7 https://www.postgresql.org/docs/current/queries-with.html#id...
- srcreigh 4y agoThanks, it is important for people to know this. In this article 5 tables are pulled into CTEs which aren’t marked as not materialized. 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.
- VincentEvans 4y agoHaving personally encountered a similar need to find ways to manage duplication and complexity in database code and previously considered use of templating as described in the article, I have some thoughts on the subject. 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.
- usgroup 4y agoThe article also abstracts the joins, a field list and an aggregate function too. It also notes the potentially to do so with where clauses, window function and so on.
- VincentEvans 4y agoPoor choice of words on my part - I should’ve said that the main value of the refactoring as presented in the article, as I see it, is extracting an “entity” as often captured in the CTE, or in other times - in a subquery. 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).
- spion 4y agoThings like this is why I've always been a fan of LINQ. Not only do you get excellent first class language support for writing and composing queries, you also get a very good typechecker to ensure that stuff are the right shape.
- pragmatic 4y agoAt my previous job, the founder did everything in Linq. He created a reporting engine in EF6 with Linq. Reports were run against the OLTP db. All queries were in LINQ. 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.
- spion 4y agoThe idea is good, the implementation isn't quite ideal. Its possible to fully model SQL as it is with a query builder in a 1:1 unambigous manner, in a way where absolutely nothing about "plain SQL" is faster. I know SQL. I don't think LINQ is upside-down and backwards.
- loloquwowndueo 4y agoWow who else has PTSD from M4 use for sendmail configuration way back when?
- justin_oaks 4y agoI find it surprising that people still use M4 for anything. I encountered M4 in my first job out of college. The company was using it for email templating. It still leaves a bad taste in my mouth. Velocity, Jinja, Mustache... all are a breath of fresh air compared to M4.
- cryptonector 4y agoOr autoconf. Sendmail is a thing of the past, but autoconf will still be around for quite some time.
- Kuinox 4y ago@work we have a language, called TQL, Transform Query Language, that allow to transform TSQL, which allow us to make reusable stored proc. I made an online demo here: https://kuinox.github.io/TQLBlazorDemo/ https://kuinox.github.io/TQLBlazorDemo/ Sadly we don't have any language docs...
- usgroup 4y agoHas anyone attempted to do anything like this in Racket?
- slt2021 4y agoMacros only looks good at first look, but quickly fall flat when need to debug query or any of the underlying components change. 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
- cryptonector 4y agoYou can always macroexpand and debug that. 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.
- slt2021 4y agoI agree that CTEs can help compose complex query from several ingredient queries. 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