Customize parameter names #71

Activity

1. kyleconroy

   added

   enhancementNew feature or requestNew feature or request

   on Oct 25, 2019
2. maxhawkins commented on Dec 14, 2019

   

   maxhawkins

   on Dec 14, 2019

   Contributor

   More actions

   +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.
3. maxhawkins commented on Dec 16, 2019

   

   maxhawkins

   on Dec 16, 2019

   Contributor

   More actions

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

   -- name: ListBar :many
   SELECT bar FROM foo WHERE :is_true::bool;
4. kyleconroy commented on Dec 17, 2019

   

   kyleconroy

   on Dec 17, 2019

   CollaboratorAuthor

   More actions

   @maxhawkins I've avoided adding support for the HugSQL syntax because it's nonstandard. Passing that query to the PostgreSQL parser returns an error.
5. maxhawkins commented on Dec 17, 2019

   

   maxhawkins

   on Dec 17, 2019

   Contributor

   More actions

   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.
6. kyleconroy commented on Jan 8, 2020

   

   kyleconroy

   on Jan 8, 2020

   CollaboratorAuthor

   More actions

   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.
7. maxhawkins commented on Jan 8, 2020

   

   maxhawkins

   on Jan 8, 2020

   Contributor

   More actions

   psql uses a similar format for its \set command:

   \set name 'Max'
   SELECT * FROM users WHERE name = :name;
8. kyleconroy commented on Jan 15, 2020

   

   kyleconroy

   on Jan 15, 2020

   CollaboratorAuthor

   More actions

   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.
9. cmoog commented on Jan 16, 2020

   

   cmoog

   on Jan 16, 2020

   Last edited by cmoog

   Contributor

   More actions

   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.
10. kyleconroy commented on Jan 16, 2020

    

    kyleconroy

    on Jan 16, 2020

    CollaboratorAuthor

    More actions

    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.
11. kyleconroy

    closed this as completedin #328on Feb 10, 2020