Replace magic comments with SET

[kyleconroy]

sqlc currently uses a magic comment to configure per-query name and return type:

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

While this has worked well so far, there are some significant downsides. It's very easy to make a typo. If we want to add additional per-query configuration options (custom models, overrides, etc.), we need to change the syntax.

Instead of continuing down the comment path, I think it's better to use the SET command, already part of the SQL standard.

SET query_name = 'GetAuthor';
SET go_command = 'one';
SELECT * FROM authors
WHERE id = $1 LIMIT 1;

Thoughts?

https://www.postgresql.org/docs/current/sql-set.html
https://dev.mysql.com/doc/refman/8.0/en/set-statement.html

[kevinburke1]

I like the conciseness of the magic comment. I'd be nervous about making it more difficult to write a query. I hope you'd continue supporting that and maybe also support other methods if you need more complexity.

[kyleconroy]

I hope you'd continue supporting that

Yes! The magic comment support will not be going away, as that would break a bunch of existing code.

[kevinburke1]

Ok, that's good. Also I guess the idea of assigning a variable and then re-assigning it to different values 20 times in the same file just seems icky to me.

[kyleconroy]

PREPARE might actually be a better fit.

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

PREPARE get_author AS
  SELECT * FROM authors
  WHERE id = $1 LIMIT 1;

PREPARE list_authors AS
  SELECT * FROM authors
  ORDER BY name;

PREPARE create_author AS
  INSERT INTO authors (
    name, bio
  ) VALUES (
    $1, $2
  )
  RETURNING *;

PREPARE delete_authors AS
  DELETE FROM authors
  WHERE id = $1;