Naming the return type

[hfjallemark]

Just like you can name the generated function with the -- name: comment it would be nice if you could name the return type as well as this type tends to get spread out in the code base as you pass it around in functions.

So instead of:

-- name: get_job_listings
select * from job_listing

generating:

GetJobListingsRow

you could provide a name:

-- name: get_job_listings
-- returns: JobListing
select * from job_listing

that would generate:

JobListing

[giacomocavalieri]

For now I'm not sure how to implement this nicely or if this could be a nice addition to Squirrel, there's some things to address. For example, what if someone gives the same name to two returned values?

-- returning: wibble
select * from job_listing;

-- returning: wibble
select 1;

This opens to a couple of questions:

• should squirrel just fail? But that might be surprising if someone actually wanted this (for example if they two queries both returning Users or whatever)
• or should squirrel check that the two types are the same structurally and allow to merge the two together? I'm not really sure, it could be really nice, or really fiddly 😆

For now I'll keep this on hold and see what the main pain points are using the library and if this could actually be a nice addition to address those, thank you so much for the suggestion!

[lpil]

What does merge mean?

[hfjallemark]

Yeah I have no obvious solution in mind either. But option two actually sounds appealing (from a user perspective at least) -- it would allow me to have the same exact type returned from different queries which would in turn allow me to use that one type for different things (e.g. json encoders, other business rules and so on). For example now I have to create two JSON encoders for the exact same thing which is pretty annoying:

-- get_job_listings.sql
select id, title, description from job_listing

-- get_job_listing_by_id.sql
select id, title, description from job_listing where id = $1

fn encode_job_listing(job_listing: GetJobListingsRow) {
  json.object([
    #("description", json.nullable(job_listing.description, of: json.string)),
    #("id", json.int(job_listing.id)),
    #("title", json.string(job_listing.title)),
  ])
}

fn encode_job_listing_by_id(job_listing: GetJobListingByIdRow) {
  json.object([
    #("description", json.nullable(job_listing.description, of: json.string)),
    #("id", json.int(job_listing.id)),
    #("title", json.string(job_listing.title)),
  ])
}

The most common use case would probably be where you get a list of rows and then get the same row but by id like above, but I'm certain there are other scenarios as well. I think the ability to control both the name of the type and whether it's shared between queries would be a powerful feature and allow me to generate useful types from squirrel that can be passed around in my app instead of squirrel being a 1:1 with the db/query and then require additional mapping to other custom types.

[mine-tech-oficial]

Maybe there could be -- returning_shared: and -- returning: to dictate whether the type should merge or give an error if there's another type with that name. Or maybe some configuration that dictates the behaviour for all types.

[giacomocavalieri]

#70 (comment)

Here's more feedback from Mark!

[giacomocavalieri]

To add to this, I'm not really sure how it would work: would the programmer just give a name to a type that can then be reused by the queries? Or should it point to an existing type from your codebase? I can see folks arguing for both alternatives yet I'm not entirely satisfied by either, especially the second one.

Now imagine we're going for the first option: you can specify a type name for squirrel to use and if you use the same name it assumes you mean the same type and it will try and share the same definition for both queries' result set. There's a couple of open questions I can think of:

1. What happens if the columns have incompatible types? How do we present this error in a clear way?

   -- first_query.sql
   -- returning: MyType
   select 1 as res;
   
   -- second_query.sql
   -- returning: MyType
   select "a" as res;
   
   -- What would MyType be in that case?

• What if the columns share the same type but have different names? Would we loose labels? Or would that be an error reported during code generation?

   -- first_query.sql
   -- returning: MyType
   select 1 as res;
  
   -- second_query.sql
   -- returning: MyType
   select 1 as different_name;
  
   -- What would MyType be in that case?

[lpil]

One can create types in PostgreSQL.

CREATE TYPE person AS (
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);

Perhaps if this row type is returned then a Gleam type could be used for it

postgres=# SELECT ROW('Alice', 'Smith', 30)::person as the_row;
     the_row
------------------
 (Alice,Smith,30)
(1 row)

It can be used unnested without the row being in a single column if functions are used

CREATE FUNCTION get_person() RETURNS person AS $$
SELECT 'John', 'Doe', 40;
$$ LANGUAGE sql;

[giacomocavalieri]

The problem is custom types do not allow constraints like not null; so even if Postgres had the type information about each row of the custom type, they would all end up as optional values

[lpil]

Wow! What a limitation!

[petejodo]

IMO the behavior should try to match as closely as possible the behaviors you expect in actual gleam code. That means treating the directory of sql files as 1:1 with gleam modules. So if you have --returning: MyType in two files that don't match exactly (and therefore cannot be deduped), column/alias ordering mattering, it should be seen as if writing that type twice within a gleam module i.e.

error: Duplicate type definition
  ┌─ /src/main.gleam:1:1
  │
1 │ pub type Person {
  │ ^^^^^^^^^^^^^^^ First defined here
  ·
5 │ pub type Person {
  │ ^^^^^^^^^^^^^^^ Redefined here

The type `Person` has been defined multiple times.
Names in a Gleam module must be unique so one will need to be renamed.

But it'd refer to the different sql files instead

[not-a-kiwi]

The returning annotation seems a good idea. In addition squirrel could detect and report identical return types that are not annotated, and ask to do so (but still process the files); in this way the user is suggested and guided into choosing whether to use different types or not.

There would be no need to offer an option to remove the warnings, as this should be done by annotations.