Syntax for prepared queries

[swt30]

I came across this little package and it's great! Exactly what I was looking for when working with LibPQ, which doesn't provide named parameters.

I would find it more useful to be able to pass parameters as a Dict{Symbol,Any}, rather than pulling them from the environment with a macro - seems cleaner and safer, and works well if your workflow looks like load SQL query template from file > interpolate variables > run query. I don't think this package supports that because of the macro approach (correct me if I'm wrong, not a macro wizard here).

So I put that together for myself. Concretely, it's a non-macro equivalent to @sql_str called something like render(sql::AbstractString, parameters::AbstractDict{Symbol,Any}, that renders a template given a Dict of parameters. While doing that, I also hacked in a way to reuse parameters if the same parameter appears more than once in the template.

Does that sound like something you'd like me to contribute to this package?

[c42f]

Hey, very sorry I missed your comment here. I'm glad you like this library :-)

I think what you're looking for is SQL prepared statements, but with named arguments rather than positional arguments?

With positional arguments, things are already simple enough. Let's use the following basic example to discuss.

Some setup:

julia> c = LibPQ.Connection("...")
PostgreSQL connection (CONNECTION_OK) with parameters:                
  user = postgres                                                                                                                      
  password = ********************                                                                                                     
...

julia> LibPQ.execute(c, "create table test (x bigint, y text)")                                                                                 

julia> using SQLStrings

julia> function runquery(conn, sql::SQLStrings.Sql)
           query, args = SQLStrings.prepare(sql)
           LibPQ.execute(conn, query, args)
       end

julia> for i=1:10
           runquery(c, sql`insert into test values ($i, $("str_$i"))`)
       end

julia> using DataFrames

julia> runquery(c, sql`select * from test`) |> DataFrame
10×2 DataFrame
 Row │ x       y       
     │ Int64?  String? 
─────┼─────────────────
   1 │      1  str_1
   2 │      2  str_2
   3 │      3  str_3
   4 │      4  str_4
   5 │      5  str_5
   6 │      6  str_6
   7 │      7  str_7
   8 │      8  str_8
   9 │      9  str_9
  10 │     10  str_10

Now to create and use a prepared statement with bare LibPQ:

julia> statement = LibPQ.prepare(c, raw"select * from test where x > $1")
PostgreSQL prepared statement named __libpq_stmt_0__ with query select * from test where x > $1

julia> LibPQ.execute(statement, (7,)) |> DataFrame
3×2 DataFrame
 Row │ x       y       
     │ Int64?  String? 
─────┼─────────────────
   1 │      8  str_8
   2 │      9  str_9
   3 │     10  str_10

Possible design

So, what I think you want is basically prepared statements, but with more convenience and with named arguments? Here's a possible design I think could work:

# The following would be a representation of a prepared statement with named arguments,
# but without taking them from the macro environment, and without referring to any particular DB
# connection.
q = lazysql`select * from test where x > $x`

# Maybe the resulting type would be called `ParameterizedSql` or something?
q::ParameterizedSql

# This can be prepared with respect to a particular DB connection, resulting in a prepared statement
# which can be executed.
statement = prepare(c, q)

# The statement would need to be a custom type to allow us to manage the name=>positional argument mapping
statement::SqlStrings.PreparedStatement

# We should be able to execute this with a dictionary, as you've suggested:
execute(statement, Dict("x"=>7))

# Or with a named tuple
execute(statement, (x=7,))

# Symbol dictionaries should probably also work
execute(statement, Dict(:x=>7))

We could also support positional arguments in ParameterizedSql so that we can offer a standard notation for positional arguments (IIUC different DBs have different syntax for this.)

[swt30]

Thanks! Yes, I think you have partially understood what I'm getting at. Given that the underlying library doesn't support named arguments to an execute command, at least in the case of LibPQ, I think SQLStrings might be a good place to provide a convenience layer that does named=>positional argument mapping.

However I don't think prepared queries were what I had in mind. The recommended way of using SQLStrings right now seems to be your runquery example, where you: a) run SQLStrings.prepare to generate a parameterised query + a list of positional arguments, taking the arguments from the environment; b) execute the parameterised query with those arguments, by supplying the parameterised query and argument list to your database connection of choice.

I wondered if it would be possible to just have an alternate route in a) where you provide a Dict or similar, rather than taking from the environment.

So my proposed adaptation to your design might look something like this:

# A representation of a parameterised statement with named arguments, but without taking anything from the environment
q = lazysql`select * from test where x > $x`
# It would also be helpful to have the ability to parse a string into a LazySQL object, so
# that we can read a parameterised query from a file
q2 = LazySQL(read("query.sql", String))

# This can be prepared in the same way as the current `@sql_cmd` object,
# but explicitly providing the named parameters - maybe even have a kwarg route 
query, args = SQLStrings.prepare(q, Dict(:x => 7))
# Then it's up to you to use your DB connector to execute the query with parameters, as normal.
# At this point query looks like `select * from test where x > $1` and args looks like `(7,)`
LibPQ.execute(conn, query, args)

Does that make sense? Maybe a prepared query is the way to go about this, but it seems to me that the above approach sidesteps needing to care about how different backends might construct a prepared query, and keeps all the action in SQLStrings.prepare instead.

[c42f]

Maybe a prepared query is the way to go about this, but it seems to me that the above approach sidesteps needing to care about how different backends might construct a prepared query, and keeps all the action in SQLStrings.prepare instead.

The downside here is that you loose some efficiency of prepared statements — normally those would involve transmitting the SQL to the database a single time, letting the database's SQL parser (and maybe query planner) process the prepared statement and return you a handle. Then you can execute the same prepared statement many times with different input data, but without resending the query.

Having said all that, I think it'd probably be fine to have a utility function to turn a LazySQL/ParameterizedSql into an SQLStrings.Sql object. Perhaps it'd be called prepare or perhaps something else.

BTW prepare is currently only a semi-public API; needed for use when connecting SQLStrings to database backends, but shouldn't be needed when just using queries. I think it needs to be slightly database-specific because the placeholder syntax isn't standardized — see the to_placeholder argument to prepare().