query argument names

[schurhammer]

At the moment query arguments are just called arg_1, arg_2 etc. It would be nice to have a way of renaming them, to improve usability of the generated gleam code.
Perhaps we could provide names in a doc comment in the sql?

[giacomocavalieri]

Yeah I agree it could be a nice improvement, I'm adding a couple of considerations:

1. The ideal solution would be for Postgres to start supporting named query parameters like sqlite does, something like this:

   select wibble
   from wobble
   where col = $named_param

   So we could use the names used in the query without any non standard extension introduced by Squirrel
2. The only other way I could see this being supported (since I want Squirrel to stick to the Postgres dialect of SQL) is with a special comment added above the query. But there's a lot of open questions, what will the comment look like? How could this play with other proposed magic comments, for example for nullability?

[binajmen]

What about :

-- type AccountRow
-- $1 account_id
-- $2 is_verified
select
  account.id,
  account.name,
  account.avatar
from
  account
where
  account.id = $1 and account.verified = $2

-- type AccountRow
-- $1 avatar_url, nullable
-- $2 account_id
update
  account
set
  avatar = $1
where
  id = $2
returning
  id,
  name,
  avatar

Leading to something like :

pub type AccountRow {
  AccountRow(
    id: Int,
    name: String,
    avatar: Option(String),
  )
}

pub fn find_account(db, account_id, is_verified) {
  let decoder = {
    use id <- decode.field(0, decode.int)
    use name <- decode.field(1, decode.string)
    use avatar <- decode.field(3, decode.optional(decode.string))
    decode.success(AccountRow(id:, name:, avatar:))
  }

  let query = "..."

  pog.query(query)
  |> pog.parameter(pog.text(account_id))
  |> pog.parameter(pog.bool(is_verified))
  |> pog.returning(account_decoder())
  |> pog.execute(db)
}

pub fn update_account(db, avatar_url, account_id) {
  let decoder = {
    use id <- decode.field(0, decode.int)
    use name <- decode.field(1, decode.string)
    use avatar <- decode.field(3, decode.optional(decode.string))
    decode.success(AccountRow(id:, name:, avatar:))
  }

  let query = "..."

  pog.query(query)
  |> pog.parameter(pog.nullable(avatar_url))
  |> pog.parameter(pog.string(account_id))
  |> pog.returning(decoder)
  |> pog.execute(db)
}

This is just food for thoughts, I'd love to help if you believe a Gleam beginner will not screw your code 😅

[giacomocavalieri]

Could be an option! Still I won't accept any contributions regarding this until I'm sold on a specific design and am convinced we can't solve this any other way. So far this problem hasn't come up that much, I'd rather wait and get more feedback before rushing any specific implementation

[ninanomenon]

Is there any chance we could get this for insert too?

[vpribish]

I'm keen on this too, it's setting me up for very tricky bugs in the current implementation. How about putting names as an inline comment immediately ahead of the positional parameter like :

select
  account.id,
  account.name,
  account.avatar
from
  account
where
  account.id = /* account_id */ $1 and account.verified = /* is_verified */ $2

this very strict and minimal format keeps the name right next to the number, is valid sql, and should be easy to parse

[giacomocavalieri]

Mhm of all the possible alternatives I've seen so far, this is the one I like the best

[sclee15]

For now, I’d like to propose a simple approach for generating comments from SQL statements.
This method simply copies lines containing $ placeholders into a documentation comment.
For example, a doc comment would look like this:
/// name: select_account
///
/// > 🐿️ This function was generated automatically using v4.6.0 of
/// > the squirrel package.
/// From SQL: account.id = $1 AND account.verified = $2
This approach is much easier to implement,
and it allows developers to order the arguments in a way that makes much more sense!

[DisguisedPigeon]

This is already possible! If you want to add some information on the docs, you can add a sql comment above the query, like this:

-- This query selects an account based on its id and whether it's verified
--
-- arg_1: account.id
-- arg_2: account.verified
select -- ... 
from account
where account.id = $1 and account.verified = $2

This sql generates the following docs

/// This query selects an account based on its id and whether it's verified
///
/// arg_1: account.id
/// arg_2: account.verified
///
/// > 🐿️ This function was generated automatically using v4.6.0 of
/// > the squirrel package.
pub fn select_account(arg_1: Int, arg_2: Bool) {
  //...
}

(I didn't double check the generation, but it's something along those lines)

[jimutt]

I think a comment-based convention would be great from a developer experience perspective. But I understand it would be a controversial addition. 🙂

Especially when you have several parameters of the same type and similar meanings, for things like i.e. limits & offsets limit $1 offset $2; the added clarity would help reduce the risk of mixups further.

[andresgutgon]

Also there is https://github.com/daniellionel01/parrot which is similar to Squirrel but use https://sqlc.dev/. I haven't tried but Claude tells me that this is possible:

INSERT INTO contacts (
  email,
  first_name,
  last_name,
  phone,
  company,
  title,
  stage,
  profile_picture_url,
  notes,
  created_at,
  updated_at
)
VALUES (
  @email,
  @first_name,
  @last_name,
  @phone,
  @company,
  @title,
  @stage,
  @profile_picture_url,
  @notes,
  NOW(),
  NOW()
)
RETURNING
  id,
  first_name,
  last_name,
  email,
  phone,
  company,
  title,
  stage,
  profile_picture_url,
  notes,
  created_at::timestamp,
  updated_at::timestamp;

pub type CreateContactRow {
  CreateContactRow(
    id: Int,
    first_name: String,
    last_name: String,
    email: String,
    phone: Option(String),
    company: Option(String),
    title: Option(String),
    stage: String,
    profile_picture_url: Option(String),
    notes: Option(String),
    created_at: Timestamp,
    updated_at: Timestamp,
  )
}

pub fn create_contact(
  db: pog.Connection,
  email email: String,
  first_name first_name: String,
  last_name last_name: String,
  phone phone: Option(String),
  company company: Option(String),
  title title: Option(String),
  stage stage: String,
  profile_picture_url profile_picture_url: Option(String),
  notes notes: Option(String),
) -> Result(pog.Returned(CreateContactRow), pog.QueryError) {
  ...
}

Maybe Squirrel could take a similar approach.

[giacomocavalieri]

Squirrel's goal is to not introduce ad hoc extensions to SQL but accept plain Postgres SQL queries that can work without Squirrel too. So this is not a viable solution.