Keyword parameters for PostgreSQL.

Is your feature request related to a problem? Please describe.

When changing a query with many parameters it is very error prone to make changes that remove an argument. This is because you need to shift all "higher numbered" arguments. If two adjacent arguments (or any two arguments separated by the size of the shift) are of the same type the compiler and even static SQL type checking at compile time won't catch the error. This can lead to logic bugs, often even security critical bugs.

It would be a significant improvement to correctness, reliability and security to support keyword arguments as they aren't prone to this time of error during modification, as well as being easier to read and write in general.

Describe the solution you'd like

PostgreSQL doesn't support keyword parameters natively so this would likely require something specific to sqlx. For example maybe the following syntax could be used.

sqlx::query!(r#"
    DELETE FROM user_emails
    WHERE email = {email}
    AND "user" = {user}
    "#,
    email = request.email,
    user = session.user.id)
    .execute(&mut transaction).await;

This resembles the syntax used by std::fmt::format!. Presumably the "interpolations" would be replaced by $N before the query is sent to PostgreSQL.

This looks like a breaking change since { would now need to be escaped but this could be avoided by not requiring escaping if no assignments are done in the parameter list.

Describe alternatives you've considered

No nothing: This leaves footguns around when editing code and makes the queries much harder to read.

Use a syntax based on other databases support of named parameters: This avoids needing to escape {. However, this would require more complicated query parsing to implement and may cause syntax collisions with future PostgreSQL syntax extensions.

launchbadge / sqlx

Keyword parameters for PostgreSQL. #2235