Repository navigation
Customize parameter names #71
Description
Activity
+1 this would be helpful.
I encountered this situation:
CREATE TABLE foo (bar TEXT); -- name: SetBar :exec UPDATE foo SET bar = $1::TEXT;
Casting
$1toTEXTavoids asql.NullStringin the parameter ofSetBar, but the parameter doesn't infer the namebar. It would be convenient if I could set it explicitly.Adding named parameters a la HugSQL could solve this problem too:
-- name: ListBar :many SELECT bar FROM foo WHERE :is_true::bool;
@maxhawkins I've avoided adding support for the HugSQL syntax because it's nonstandard. Passing that query to the PostgreSQL parser returns an error.
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.
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.
psql uses a similar format for its \set command:
\set name 'Max' SELECT * FROM users WHERE name = :name;
I played around with this a bit today. I don't think the
:paramapproach 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,
sqlcis a psuedo-schema,argis a function that takes an identifier as the first argument.sqlc.argis 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.I tested a few of these out on the
mysqlparser to see what works best. It looks like:paramparses great formysql. I realize it's not ideal to have different solutions between engines, but this would be very natural formysqlusers.I was having trouble getting
@paramto parse properly... unless you were thinking of replacing those before parsing.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.
Certain queries end up with generic (read: terrible) names in parameter structs. For example
There is no way to generate a good name for lone parameter in this query. Maybe another special comment?
-- paramcould work.