SqlStrings

The main thing provided here is the @sql macro to allow queries to be constructed by normal-looking string interpolation but without danger of SQL injection attacks.

Note that @sql does not parse the text as SQL source as this wouldn't be usable across databases and would be a lot of work. Instead, it keeps any literal SQL text you write as-is and only treats the Julia-level string interpolations specially.

Use runquery to execute queries generated by @sql.

Simple usage

Creating a table and inserting some values

conn = LibPQ.connection(your_connection_string)

runquery(conn, @sql "create table foo (email text, userid integer)")

for (email,id) in [ ("admin@example.com", 1)
                    ("foo@example.com",   2)]
    runquery(conn, @sql "insert into foo values ($email, $id)")
end

Thence:

julia> runquery(conn, @sql "select * from foo") |> DataFrame
2×2 DataFrame
 Row │ email              userid
     │ String?            Int32?
─────┼───────────────────────────
   1 │ admin@example.com       1
   2 │ foo@example.com         2

Howto: Inserting values from a Julia array into a row

In some circumstances it can be useful to use splatting syntax to interpolate a Julia collection into a comma-separated list of values. Generally simple scalar parameters should be preferred for simplicity, but splatting can be useful on occasion:

email_and_id = ("bar@example.com", 3)
runquery(conn, @sql "insert into foo values ($(email_and_id...))")

Howto: Using the in operator with a Julia collection

There's two ways to do this. First, using in and splatting syntax

julia> ids = (1,2)
       runquery(conn, @sql "select * from foo where userid in ($(ids...))") |> DataFrame
       2×2 DataFrame
        Row │ email              userid
            │ String?            Int32?
       ─────┼───────────────────────────
          1 │ admin@example.com       1
          2 │ foo@example.com         2

Second, using the SQL any operator and simply passing a single SQL array parameter:

julia> ids = [1,2]
       runquery(conn, @sql "select * from foo where userid = any($ids)") |> DataFrame
       2×2 DataFrame
        Row │ email              userid
            │ String?            Int32?
       ─────┼───────────────────────────
          1 │ admin@example.com       1
          2 │ foo@example.com         2

Howto: Building up a query from fragments

On occasion you might want to dynamically build up a complicated query from fragments of SQL source text. To do this, the result of @sql can be interpolated into a larger query as follows.

conn = LibPQ.connection(your_connection_string)

some_condition = true

x = 100
x = 20
# Example of an optional clauses - use empty @sql() to disable it.
and_clause = some_condition ? @sql("and y=$y") : @sql()

# Interpolation of values produces SQL parameters; interpolating @sql
# fragments adds them to the query.
q = @sql "select * from table where x=$x $and_clause"
runquery(conn, q)

A word of warning that constructing SQl logic with Julia-level logic can make the code quite hard to understand. It can be worth considering writing one larger SQL query which does more of the logic on the SQL side.