Customize parameter names

[kyleconroy]

Certain queries end up with generic (read: terrible) names in parameter structs. For example

CREATE TABLE foo (bar text not null);

-- name: ListBar :many
SELECT bar FROM foo WHERE $1:bool;

There is no way to generate a good name for lone parameter in this query. Maybe another special comment? -- param could work.

-- name: ListBar :many
-- param: 1 IsTrue
SELECT bar FROM foo WHERE $1:bool;

[maxhawkins]

+1 this would be helpful.

I encountered this situation:

CREATE TABLE foo (bar TEXT);

-- name: SetBar :exec
UPDATE foo
SET bar = $1::TEXT;

Casting $1 to TEXT avoids a sql.NullString in the parameter of SetBar, but the parameter doesn't infer the name bar. It would be convenient if I could set it explicitly.

[maxhawkins]

Adding named parameters a la HugSQL could solve this problem too:

-- name: ListBar :many
SELECT bar FROM foo WHERE :is_true::bool;

[kyleconroy]

@maxhawkins I've avoided adding support for the HugSQL syntax because it's nonstandard. Passing that query to the PostgreSQL parser returns an error.

[maxhawkins]

OK, agreed. Avoiding nonstandard syntax makes sense to me.

I want to add automatic SQL formatting to my query files and nonstandard syntax would break it.

[kyleconroy]

SQLAlchemy supports named parameters in the form :param:

stmt = text("SELECT * FROM users WHERE users.name BETWEEN :x AND :y")
stmt = stmt.bindparams(x="m", y="z")

Sequel, a Ruby database toolkit, supports the same format:

DB.fetch("SELECT * FROM albums WHERE name LIKE :pattern", pattern: 'A%') do |row|
  puts row[:name]
end

While this format isn't part of the SQL standard, I think it solves the problem nicely.

[maxhawkins]

psql uses a similar format for its \set command:

\set name 'Max'
SELECT * FROM users WHERE name = :name;

[kyleconroy]

I played around with this a bit today. I don't think the :param approach is going to work. The PostgreSQL parser barfs on those queries. I attempted to use the sqlx named parameter code, but it operates on single queries, not an entire file. It also failed to handle comments.

Instead, I think it's better if we create a psuedo-function and map it to an operator. Here's what it would look like:

-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = sqlc.arg(id) LIMIT 1;

-- name: CreateAuthor :one
INSERT INTO authors (
  name, bio
) VALUES (
  sqlc.arg(name), sqlc.arg(bio)
)
RETURNING *;

-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = sqlc.arg(id);

In this case, sqlc is a psuedo-schema, arg is a function that takes an identifier as the first argument. sqlc.arg is a bit cumbersome to write, so we could map it to the @ operator, which surprisingly works for both PostgreSQL and MySQL.

-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = @id LIMIT 1;

-- name: CreateAuthor :one
INSERT INTO authors (
  name, bio
) VALUES (
  @name, @bio
)
RETURNING *;

-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = @id;

I think this approach is much better than relying on a syntax that doesn't parse. There are also a bunch of different ways to approach the arg function (sqlc.arg.name, sqlc_arg(name), _$.arg), etc. We just want to pick an approach that won't likely cause issues for existing queries.

[cmoog]

I tested a few of these out on the mysql parser to see what works best. It looks like :param parses great for mysql. I realize it's not ideal to have different solutions between engines, but this would be very natural for mysql users.

I was having trouble getting @param to parse properly... unless you were thinking of replacing those before parsing.

[kyleconroy]

I realize it's not ideal to have different solutions between engines, but this would be very natural for mysql users.

In an ideal world we'd use the same operator for all engines. We should make sure the long-form solution (e.g sqlc.arg(name)) works the same across all engines. This should be much easier, since it's a SQL function.