Alternative query parameters formats

[linux-china]

Now PRQL uses $1, $2 as parameters, and it's good for parameters with index. For some cases,
named parameters are required when transpiled to SQL, For example, in Java, Spring JDBC and Hibernate use :age style for named parameter, and MyBatis uses #{email} style. How about named parameter ${name} and ${person.age} support in PRQL and to make SQL transpile easy.

prql target:sql.mysql
prql framework:mybatis

from employees
filter age > ${age}
filter status == ${status}

[max-sixty]

Thanks for the suggestion @linux-china .

I think we could quite easily support both $foo and ${foo} and ${foo.bar}, if that's what you're thinking.

To confirm, is the bracketed approach better because then you can have multiple words? Are there SQL dialects which support multiple words though?

What would the SQL look like for filter age > ${person.age} for the dialect you'd like to use?

[linux-china]

@max-sixty yes, it's my thinking. $foo and ${foo} are all good. If just choose one, and I prefer ${foo}.
${person.age}(expression language style) still important for some Java libraries, for example MyBatis.

filter age > ${person.age} will be transpiled to where age > #{person.age} if target framework is MyBatis, then MyBatis will use generated SQL to talk to database.

[max-sixty]

OK, I'm fine with that. I adjusted the title, hope that's OK.

To set expectations: my guess is that this would need to come from a contribution. I'm not sure how confident you are in Rust, but it could be a good early PR! (I know you've done lots on the JetBrains extension, so no pressure...)

FWIW I don't have it clear in my mind how the MyBatis approach would work. I'm looking at https://mybatis.org/mybatis-3/dynamic-sql.html to understand it. To the extent it's "Postgres but with #s for parameters", then possibly we could add it as target:sql.mybatis.

But to the extent it's a templating language which we would compile to, that would be more complex. I'm not ruling anything out at all — but it's probably worth thinking about how you'd envision that working / whether there's an approach that would be compatible with as much of the existing PRQL codebase as possible.

Thank you @linux-china !

[snth]

I like the ${foo} style and think it would be a great addition.

[aljazerzen]

Wait, how does that compile to SQL?

SELECT * FROM employees WHERE age > ${age} AND status == ${status}

Many dialects don't support this, only numbered params.

[linux-china]

@aljazerzen yes, this feature is useful for some database/sql frameworks, and not for database directly.

[aljazerzen]

I see. From our standpoint, this is an SQL dialect (or flavor of a dialect?). And plan of action is:

• Add support for named parameters in PRQL (just parsing and pass trough). For all target dialects this will be same syntax in PRQL.
• If the target SQL dialect supports it, emit appropriate syntax (${param} or #{param}).
• If it does not, throw an error.

[snth]

This might come in handy for dbt integrations. I have to look at the dbt side again before commenting more but putting a pin here to circle back to.

[aljazerzen]

Update: I've looked into current state and my plan of action above.

At the moment, we allow parameters of format $hello.world and $3, which are transpiled as-is.

If we want different parameter formats (:my_param, #{my_param} or ${my_param}), there are a few options:

• extend PRQL syntax to support things like: $:my_param, which would transpile to :my_param. This is a bit hacky.
• add a compilation flag, similar to dialect, but orthogonal. I.e. sql.mysql.jdbc or sql.postgres.my_batis. I don't like that because it does not seem justified, if it will affect only parameter format.
• use s-strings for different param styles (see example below). Named parameters in SQL seem hacky to me anyway, because they are plain string manipulations. Which means that falling back to hacky s-string does not feel like huge compromise.

This works right now:

from employees
filter id == s"#{{hello.world}}"

[max-sixty]

Agree that s-strings are a good temporary way forward, and then if alternatives become popular we could look at something more integrated...