[Next] [Feature] Generalized query placeholders (including support for expanding arrays into multiple placeholders)

[mehcode]

@abonander and I have been discussing the benefits of providing a more Python style of SQL placeholders for parameters

In Python database connectors, it looks like this:

conn.execute("SELECT %(name)s", name="Hello")

The connector takes care of:

• Using ?, $1, or @p1 depending on the database driver
• Expanding arrays into ?, ?, ?, ...
• Allowing named parameters where none are supported such as PostgreSQL

The really interesting part is Intellij understands that %(name)s is a SQL parameter and correctly parses that query and still provides proper code completion ( as a side note, Intellij Rust recently gained support for SQL highlighting and code completion inside of sqlx::query! ).

---

We are proposing that we add support for the following ( both in query! and query ):

Positional Parameter

// "SELECT ?" in MySQL
// "SELECT $1" in PostgreSQL

sqlx::query!("SELECT {0}", 10);

Named Parameter

// "SELECT @id" in MSSQL
// "SELECT $id" in SQLite
// "SELECT $1" in PostgreSQL

sqlx::query!("SELECT {id}", id=10);

Inline Parameter (only macros)

// "SELECT @id" in MSSQL
// "SELECT $id" in SQLite
// "SELECT $1" in PostgreSQL

let id = 10;
sqlx::query!("SELECT {id}");

Array Parameter

// MySQL = "SELECT * FROM foo WHERE id IN (?, ?, ?, ...)"
// PostgreSQL = "SELECT * FROM foo WHERE id IN ($1, $2, $3, ...)"

sqlx::query!("SELECT * FROM foo WHERE id IN ({ids+})", ids=[1, 2, 3]);

• {ids+} or {ids*} becomes a comma-delimited list of parameters
• {ids+} for a 0 length array becomes NULL
• {ids*} for a 0 length array becomes `` (nothing)

---

I was on the fence about doing something like this but we can teach Intellij to recognize {...} as a SQL parameter which would let Intellij continue to parse and provide completion in our SQL queries.

Thoughts?

[jplatte]

Big thumbs up from me 👍

If the inline form could also permit {foo.bar}, that would be amazing!

[mehcode]

If the inline form could also permit {foo.bar}, that would be amazing!

We should probably follow the syntax recommendation here if we want to allow expressions: https://rust-lang.github.io/rfcs/2795-format-args-implicit-identifiers.html#alternative-solution---interpolation

let foo = Foo { bar: 50 };

// SELECT $1
sqlx::query!("SELECT {(foo.bar)}");

This also lets our Kleene operator work with these complex expressions:

let foo = Foo { bar: [1, 2, 3] };

// SELECT $1, $2, $3
sqlx::query!("SELECT {(foo.bar)*}");

[jplatte]

I think that 90% of my uses of this are going to be {(foo.bar)} or {(foo.bar.baz)}. But since it's backwards-compatible to allow this without the extra parentheses later on, it seems fine to start without that.

[D1plo1d]

The IN ({ids*}) syntax would be a god send for Sqlite in particular.

I wonder if it would be possible to add a minimal extensions of this syntax for INSERT as well?

I'd like to insert all the fields of a struct succinctly - as a nice ergonomic for developers who want to prototyping quickly and leave choosing specific fields as a later optimization.

Perhaps if there was a way to generate 'static str or const column names via a macro they could be expanded into the query at compile time?

#[derive(sqlx::Columns)]
struct Foo { a: i32, b: i32, c: i32 }
let foo = Foo { a: 1, b: 2, c: 3 };

let values = foo.column_values();
// INSERT INTO foos (a, b, c) VALUES (?, ? ,?)
sqlx::query!("INSERT INTO foos ({Foo::COLUMNS*}) VALUES (values*)");

[abonander]

@D1plo1d I would probably prefer something a little less implicit like the following: #591

That would work okay with this proposal, although if you use the ...foo syntax we'll have to disable the ability to implicitly bind variables from the scope for that query (since we can't know whether to generate code to get a given bind parameter from foo or from the surrounding scope).

However, we can't implicitly expand the column list from the fields of foo while maintaining the guarantees of query!() as there's no way to get the fields of Foo in a proc macro without some hacky side-channel communication between #[derive(sqlx::Columns)] and sqlx::query!() (which I realize is the pot calling the kettle black in this situation but I'd prefer to limit the hackiness where possible).

[cbondurant]

Is there any update to when this might be implemented, or alternatively what blockers currently exist for implementation?

I have a use-case of passing a vec of arguments for a WHERE x IN (?) clause with SQLite and cannot use the listed postgres workaround due to the database type, this feature would be incredibly helpful towards that goal.

[blagowtf]

I would love this as I'm currently writing a lot of code to manually trick indices for postgres. Having this done by name instead would be amazing

[marvin-j97]

Not being able to use WHERE ... IN (?) in SQLite makes it really hard to batch WHERE operations (for example in a data loader). With Postgres it works fine because of ANY($1).