2 ms·

Having personally encountered a similar need to find ways to manage duplication and complexity in database code and previously considered use of templating as d

Having 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 ago
The 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 ago
  Poor 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).

usgroup 4y ago

The 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 ago
Poor 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).