The future of template engine (e.g. jinja) support? (pass {{...}} as is)

[eitsupi]

I was wondering if this feature, which is included for dbt, is planned to be maintained even if dbt side supports PRQL and the replacement by jinja is done before PRQL is converted to SQL.

prql/book/src/integrations/dbt.md

Lines 115 to 117 in 5280161

	- The PRQL compiler passes text that's containing `{{` & `}}` through to dbt
	without modification, which allows us to embed jinja expressions in PRQL.
	(This was added to PRQL specifically for this use-case.)

Because I remembered that the R package glue also had the ability to process SQL templates and realized that it could be combined with this.
https://glue.tidyverse.org/reference/glue_sql.html

(Once after dbt can work well without relying on it) I am not sure this needs to exist as a feature of the PRQL language, but I also find it an interesting feature to be able to combine with existing template engines.
Might be worth mentioning on pages other than dbt integration.

[max-sixty]

This is a good discussion topic!

There are a few things we can use templates for:

• Language augmentation. For example, dbt macros & s-strings — if we want to take LEFT of 8 columns, we can write a loop which generates SQL rather than copying & pasting 8 lines.
  • Ideally we need these less & less, because we can express these in PRQL, and we get the benefits of a structure language rather than opaque string replacements.
• Runtime variables. For example, we want to generate a report for an artist with all their albums, so we write a query and have a placeholder for the artist id.
  • An advantage of PRQL's pipelines is that these can generally be expressed by appending additional transforms. For example, appending | filter [artist_id == 1234], avoiding the need for templating. There are some potential injection issues, but I think we can work around these.
  • One wrinkle here is that DBs won't always push predicates down sufficiently, such that having this filter towards the top of a query will be more performant than having it lower. This can either be a deficiency in the query optimizer or, in some cases, the location of a filter or join can cause a different result.
  • SQL traditionally handled these with variables like $1, and a value is then passed in a structured form $1=1234, separately to the query. This also protects against injection issues. We could consider using this in PRQL. It wouldn't be trivial:
    • DB implementations are heterogeneous such that it would need to be dialect-specific.
    • Currently PRQL compiles to a flat SQL query. If we implemented this, we'd produce a flat SQL query and a map of variables, which the DB engine would then need to submit to the DB. So we're not being that helpful here...

So my initial view is that we should try and get as far as we can with:

• Allow for customization with appending transforms
• No Language Augmentation — instead we use functions and, as a stop-gap, s-strings

Are there cases which won't work with that? How important is protecting against injection issues?