[Proposal] Support named bind parameters

[mehcode]

struct Query {
  fn bind_as<T>(name: &'static str, value: T) -> Self;
}

query!("SELECT ?name", name = 320);

[abonander]

With call-site hygiene stabilized in 1.45, the explicit binding could actually be omitted:

let name = 320;
query!("select ?name");

[jplatte]

Should this be closed in favor of #875? That one has a bunch more comments.

[abonander]

This is a related issue of supporting named parameters in general. Implementing #875 would close both issues.

[CMeldgaard]

Is there any update on named bindings?

[CommanderStorm]

Not really, otherwise this issue would be closed, or that progress would be linked to this issue. You can however help make this happen.. In rust, most people are volunteers ^^ The contribution guide is here: https://github.com/launchbadge/sqlx/blob/main/CONTRIBUTING.md

In case your company needs this, you could also consider sponsoring @abonander (I don't speak for launchbadge or Austin => have no inside intel if that is an option, but that should likely be taken offline anyway..)

[Flamenco]

I have come up with a solution to this issue. I wrote a procedural macro to wrap the sqlx command, the SQL string, and the parameters.

        let aString = "hello";
        let aNumber = 12;
        let aBoolean = true;

        let res = bind_by_name!(
            sqlx::query
            "select :aString as aString, :aNumber as aNumber, :aBoolean as aBoolean"
            [aString aNumber aBoolean]
        ).fetch_one(&db.pool).await?

This expands to:

sqlx::query("select ? as aString, ? as aNumber, ? as aBoolean")
  .bind(aString).bind(aNumber).bind(aBoolean)
  .fetch_one(&db.pool).await?

You can also map values directly:

        let res = bind_by_name!(
            sqlx::query
            "select :aString as aString, :aNumber as aNumber, :aBoolean as aBoolean"
            [aString:"hello" aNumber:12 aBoolean:true]
        ).fetch_one(&db.pool).await?

Whitespace and brackets are flexable

        let res = bind_by_name!(
            sqlx::query "select :aString as foo" {aString:"hello"}
       ).fetch_one(&db.pool).await?

This will work on any sqlx command that accepts a string and has a bind function.

There is also a postgres version that uses a$ prefix and has numbered placeholders.

        let res = bind_by_name_pg!(
            sqlx::query
            "select $aString as aString, $aNumber as aNumber, $aBoolean as aBoolean"
            [aString:"hello" aNumber:12 aBoolean:true]
        )

That expands to:

sqlx::query("select $1 as aString, $2 as aNumber, $3 as aBoolean")
  .bind("hello").bind(12).bind(true)

The macro shows edit-time errors if you use a parameter name in your SQL that is not in the map.

As you can see, I am not a big fan of "noisy" syntax. 😎

I will post this crate after some feedback and if there is interest.

Working Screenshots

[CMeldgaard]

@Flamenco, i would love to see this as a crate :D

[Flamenco]

@CMeldgaard This is still on the TODO list. It's going to take a bit more encouragement from the public before we publish. :D

[tyt2y3]

(I posted this in another issue already), in sea-query 1.0-rc you can do:

let values = vec![(Uuid::new_v4(), 12, "A"), (Uuid::new_v4(), 14, "B")];

let res = sea_query::sqlx::sqlite::query!(
    sql = r#"INSERT INTO "character" ("uuid", "font_size", "character") VALUES {..(values.0:2),}"#
).execute(pool).await?;

let character = Character { id: 1, font_size: 14 };

let res = sea_query::sqlx::sqlite::query!(
    sql = r#"UPDATE "character" SET "font_size" = {character.font_size} WHERE "id" = {character.id}"#
).execute(pool).await?;

let ids = [2, 3, 4];

let res = sea_query::sqlx::sqlite::query!(
    sql = r#"DELETE FROM "character" WHERE "id" IN ({..ids})"#
).execute(pool).await?;

full example can be found here: https://github.com/SeaQL/sea-query/blob/master/examples/sqlx_sqlite/src/main.rs