🐿 squirrel - type safe SQL in Gleam | The Gleam Programming Language | Page 2

unreal jay Jun 22, 2025, 2:32 PM

#

sure, but you could also use the, you know, defacto tool for guaranteeing it's also correctly formatted :D

shadow shale Jun 22, 2025, 2:33 PM

#

Pfff too easy

#

Everyone can do that

hallow silo Jun 22, 2025, 2:33 PM

#

I think you're the only person who that is true for 😛

unreal jay Jun 22, 2025, 2:34 PM

#

couldn't you also snapshot test against stuff after it's formatted?

forest vector Jun 22, 2025, 2:34 PM

#

😐

#

unreal jay Jun 22, 2025, 2:35 PM

#

tbf hayleigh this is also partially on you for omitting the --stdin line in your screenshot before /s

forest vector Jun 22, 2025, 2:35 PM

#

😂

shadow shale Jun 22, 2025, 2:35 PM

#

Yeah I was going to say it's Hayleigh's fault here

#

Well if more formatting bugs start appearing in the future at least I know an easy way to fix them

#

But I'm ready to bet it won't be a problem for a long time

unreal jay Jun 22, 2025, 2:36 PM

#

I find it a wee bit crazy that gleam format has like 3 options you can run it with and 2 of them would have simplified jaks life if he knew beforehand

shadow shale Jun 22, 2025, 2:37 PM

#

To be 100% honest even if I knew I think I wouldn't have used it anyway 💀

viscid dagger Jun 22, 2025, 2:37 PM

#

we're all guilty of 'why use solution when engineer own solution do trick?'

shadow shale Jun 22, 2025, 2:37 PM

#

What can I say, I love writing formatters

viscid dagger Jun 22, 2025, 2:37 PM

#

legendary

#

honestly

unreal jay Jun 22, 2025, 2:37 PM

#

but aren't you... writing a formatter you already wrote once? 😂

#

ah but in a new language this time ig

shadow shale Jun 22, 2025, 2:37 PM

#

It's more fun!!

viscid dagger Jun 22, 2025, 2:37 PM

#

think about how much the humble wheel could be improved, if only someone would reinvent it

raven abyss Jun 22, 2025, 3:09 PM

#

forest vector

this way is so much better, you can do all of this AND then compare the output vs your own formatting function to make sure they match

crimson wave Jun 26, 2025, 8:39 AM

#

Will basic scaffolding of auth/ crud ever be part of the squirrel scope? If not, it might be a chance for some application (CLI) to take up this space, handling codegen for migrations and basic crud and applying migrations, specifically for squirrel

shadow shale Jun 26, 2025, 8:40 AM

#

Nope nothing squirrel would ever do

jagged nova Jul 4, 2025, 12:00 AM

#

@shadow shale you've got some explaining to do despair

danielle@minnie ~/D/chat (main)> psql -h 192.168.64.14 -p 5432 -U chat-gen
psql (18beta1 (Postgres.app), server 17.5 (Debian 17.5-1.pgdg120+1))
Type "help" for help.

~# ^D\q
danielle@minnie ~/D/chat (main)> DATABASE_URL="postgres://chat-gen:[email protected]:5432/chat-gen?sslmode=disable" gleam run -m squirrel
   Compiled in 0.03s
    Running squirrel.main
Error: Cannot establish TCP connection

I couldn't connect to `192.168.64.14` at port 5432 because the connection timed
out.

#

I should probably give some context here: 192.168.64.14 is the IP address of a container

#

Spawned with Apple's new container CLI https://github.com/apple/container

#

Which uses Apple's own containerization library https://github.com/apple/containerization

#

figured it out

#

It takes too long to connect

#

and squirrel doesn't have a configurable timeout despair

hallow silo Jul 4, 2025, 12:23 AM

#

How come it takes a long time to connect?

#

how long is too long?

jagged nova Jul 4, 2025, 12:30 AM

#

I have absolutely no idea why it takes so long to be honest

#

My assumption would be something in the networking stack that Apples new containerisation framework uses is slow

#

But upping the timeout from 1s to 5s works for me

shadow shale Jul 4, 2025, 6:36 AM

#

Sooo what should I do?

vast cloud Jul 4, 2025, 7:12 AM

#

shadow shale Sooo what should I do?

app/toml configuration with default timeout?

viscid dagger Jul 4, 2025, 7:30 AM

#

while you're looking into this Jak, reckon you can bump to Mug 3?

shadow shale Jul 4, 2025, 7:40 AM

#

Oh yeah! I’ll work on that

viscid dagger Jul 4, 2025, 7:43 AM

#

<3 love you

hallow silo Jul 4, 2025, 8:49 AM

#

shadow shale Sooo what should I do?

PostgreSQL URLs can include a connect timeout as a parameter

shadow shale Jul 4, 2025, 5:17 PM

#

I've published 3.1.0

@jagged nova you can now use the PGCONNECT_TIMEOUT env variable or the connect_timeout query parameter to change the timeout (and I've raised the default to 5 seconds)
@viscid dagger I've updated the mug dependency to use the latest major!

shadow shale Jul 7, 2025, 1:54 PM

#

Released v4.0.0, Squirrel now uses pog@4, and timestamp, date and time are all decoded using the gleam_time types!

jagged nova Jul 7, 2025, 2:01 PM

#

pog

shadow shale Jul 7, 2025, 2:01 PM

#

pog indeed

lucid bluff Jul 10, 2025, 2:40 PM

#

❯ gleam run -m squirrel
   Compiled in 0.09s
    Running squirrel.main
src/squirrel.gleam:191
Error(Nil)
src/squirrel.gleam:193
Error(Nil)
Error: Cannot authenticate

Invalid password for user `postgres`.

Hint: You can change the default password used to authenticate by setting the
`PGPASSWORD` environment variable.

I have this issue I did not have on another project, but can't find the reason why it does not work in this brand new project.

the two Error(Nil) are an addition of echo to print the env variables in the source code of squirrel:

  let user = envoy.get("PGUSER") |> echo |> result.unwrap(default_user)
  let password =
    envoy.get("PGPASSWORD") |> echo |> result.unwrap(default_password)

any idea how I can unblock myself? I have a .env with the variables set:

PGHOST="localhost"
PGPORT=5432
PGDATABASE="postgres"
PGUSER="postgres"
PGPASSWORD="postgres"
PGCONNECT_TIMEOUT=5

shadow shale Jul 10, 2025, 2:43 PM

#

Squirrel won't read .env files, are you somehow setting the env variables?

lucid bluff Jul 10, 2025, 2:45 PM

#

hhh faiil! my bad, i forgot envoy do read the env var but not the .env itself 🫣

shadow shale Jul 10, 2025, 2:46 PM

#

No worries!

pallid lantern Jul 13, 2025, 7:35 AM

#

Hi! This is a great library, saves me great amount of time working with DB.
However I'm having trouble with numeric type in Postgres. Squirrel generates it as Option(Float) which is correct. But I get UnexpectedResultType([DecodeError("Float", "Int", ["0"])]) error when running query. I think some values that do not have fractional parts are interpreted as integers and give error.
I made workaround like this select cast(coalesce(amount, 0) as float) as amount. But this should work without this hack right?

shadow shale Jul 13, 2025, 7:50 AM

#

Ah, interesting! Yeah I think it should work without coalescing, could you open an issue please?

pallid lantern Jul 13, 2025, 7:58 AM

#

ok

shadow shale Jul 28, 2025, 9:54 PM

#

I've released v4.1.0!

Now squirrel will pick up sql files that are in test and dev as well as src
Now squirrel supports .sql files using do $$ blocks
Fixed a bug that would result in the squirrel check command randomly failing

shadow shale Jul 31, 2025, 2:01 PM

#

I've released v4.2.0!

Now squirrel also adds a doc comment at the top of all generated files, telling you where the queries come from
When generating code squirrel will refuse to override existing files it didn't generate

shadow shale Aug 25, 2025, 2:25 PM

#

I've released v4.4.1 (don't ask what happened to 4.3.0 and 4.4.0):

now Squirrel is adding type annotations to the code it generates

forest vector Aug 25, 2025, 2:26 PM

#

based

#

what happaned to 4.3.0 and 4.4.0

shadow shale Aug 25, 2025, 2:27 PM

#

4.3.0, I forgot to share it
4.4.0 I forgot to annotate the return type and rushed for a fix

thorn anvil Aug 26, 2025, 10:34 AM

#

I'm a bit out of the loop but I have projects using squirrel where I do some post processing to find & replace things like pog.calendar_date(arg_3) with pog.nullable(pog.calendar_date, arg_3) so that a nullable value is handled properly. This works because the types are inferred correctly after the find & replace and I suspect having concrete types will disrupt this. Have their been developments on this? Again, super out of the loop. How should I be managing nullable values?

shadow shale Aug 26, 2025, 11:00 AM

#

Can you show the query?

thorn anvil Aug 26, 2025, 1:45 PM

#

Thanks for asking. I'll come back to you with that. Hopefully this evening.

shadow shale Aug 26, 2025, 1:45 PM

#

No, thank you!

thorn anvil Aug 26, 2025, 5:47 PM

#

Ok, I have:

INSERT INTO
    actionable_items (
        name,
        project_id,
        today,
        properties,
        created_at
    )
VALUES
    ($1, $2, $3, $4, NOW())
RETURNING
    id;

#

Where:

\d actionable_items;
                                                   Table "public.actionable_items"
           Column           |           Type           | Collation | Nullable |                       Default
----------------------------+--------------------------+-----------+----------+------------------------------------------------------
 id                         | integer                  |           | not null | nextval('actionable_items_id_seq'::regclass)
 name                       | character varying        |           | not null |
 project_id                 | integer                  |           | not null | nextval('actionable_items_project_id_seq'::regclass)
 created_at                 | timestamp with time zone |           | not null |
 completed_at               | timestamp with time zone |           |          |
 deleted_at                 | timestamp with time zone |           |          |
 today                      | date                     |           |          |
 actionable                 | boolean                  |           | not null | false
 properties                 | jsonb                    |           | not null | '{}'::jsonb
 task_integration_source_id | integer                  |           |          |
 integration_task_id        | text                     |           |          |
Indexes:
    "actionable_items_pkey" PRIMARY KEY, btree (id)
    "actionable_items_unique_integration_ids" UNIQUE CONSTRAINT, btree (task_integration_source_id, integration_task_id)
Foreign-key constraints:
    "actionable_items_project_id_fkey" FOREIGN KEY (project_id) REFERENCES projects(id)
    "actionable_items_task_integration_source_id_fkey" FOREIGN KEY (task_integration_source_id) REFERENCES task_integration_sources(id)
Referenced by:
    TABLE "up_next" CONSTRAINT "up_next_actionable_item_id_fkey" FOREIGN KEY (actionable_item_id) REFERENCES actionable_items(id)

#

And the generated code has:

  |> pog.query
  |> pog.parameter(pog.text(arg_1))
  |> pog.parameter(pog.int(arg_2))
  |> pog.parameter(pog.calendar_date(arg_3))
  |> pog.parameter(pog.text(json.to_string(arg_4)))
  |> pog.returning(decoder)
  |> pog.execute(db)

Which indicates that squirrel doesn't consider the third argument today which is nullable in the table to be nullable.

I'm likely making some mistake but as I said I'm a bit out of the loop and I currently have a python script that runs after squirrel code gen to add the nullable in

trail escarp Aug 26, 2025, 6:13 PM

#

thorn anvil And the generated code has: ``` |> pog.query |> pog.parameter(pog.text(arg_1...

https://github.com/giacomocavalieri/squirrel/discussions/82#discussioncomment-13184531

shadow shale Aug 26, 2025, 6:19 PM

#

So that's a know limitation, because of how postgres words query parameters cannot be inferred as nullable. There's two ways you could go about this

#

You could have your query insert null if you pass in a special zero value

insert into actionable_items (name, project_id, today, properties, created_at)
values (
$1,
$2,
case $3
 when '1900-01-01' then null
 else $3
end,
$4,
now()
)
returning id;

#

Or, given this query is quite short and simple, duplicate it! Have one version that adds null and one that adds the value and pick one in Gleam based on your needs

insert into actionable_items (name, project_id, today, properties, created_at)
values ($1, $2, null, $3, now())
returning id;

-- and another one in a different file
insert into actionable_items (name, project_id, today, properties, created_at)
values ($1, $2, $3, $4, now())
returning id;

#

Neither is ideal but it's the best one can do without doing any find and replace, which I think would be more tedious

thorn anvil Aug 26, 2025, 8:34 PM

#

Thanks for explaining. I appreciate you taking the time. Good to understand the situation. I think I would benefit if the docs covered it in some way. Either in the types or the FAQ section but you have a better idea about how often it comes up.

shadow shale Oct 24, 2025, 2:21 PM

#

I've released Squirrel 4.5.0:

now when there's an error in any of the queries squirrel will not do any code generation, which would usually lead the codebase in a broken state.
I've added a nice hint for when someone tries to use a timestamptz instead of a timestamp

hallow silo Oct 24, 2025, 2:22 PM

#

oooh very nice

surreal niche Oct 24, 2025, 4:11 PM

#

shadow shale I've released Squirrel 4.5.0: - now when there's an error in any of the queries ...

Thank you ! Squirrel is one my favorite library 💜

shadow shale Oct 24, 2025, 4:25 PM

#

Thank you!!

gritty oasis Oct 24, 2025, 8:57 PM

#

shadow shale Neither is ideal but it's the best one can do without doing any find and replace...

Do you have any thoughts/plans regarding solving this issue?
I think it can be solved with schema introspection or maybe additional annotations?

shadow shale Oct 24, 2025, 8:58 PM

#

Nothing new besides what I’ve shared 😔

#

Schema introspection is not doable unless you implement a sql query parser I’m afraid

gritty oasis Oct 24, 2025, 9:02 PM

#

No worries, I was just interested in your perspective 🤗 And yeah I think you are right about parser

gritty oasis Oct 25, 2025, 10:51 PM

#

shadow shale Schema introspection is not doable unless you implement a sql query parser I’m a...

What is your opinion on using C psql query parser like https://github.com/pganalyze/libpg_query?

GitHub

GitHub - pganalyze/libpg_query: C library for accessing the Postgre...

C library for accessing the PostgreSQL parser outside of the server environment - pganalyze/libpg_query

shadow shale Oct 26, 2025, 7:45 AM

#

I don’t think adding aC dependency to squirrel would be the way to move forward with this

#

The ideal solution would be adding to postgres the kind of features we need to enable better typing in squirrel

hallow silo Oct 26, 2025, 11:56 PM

#

Also parrot already implements that approach

stone peak Nov 2, 2025, 7:26 PM

#

Hello squirrels ! Anyone knows if there's a way to create a custom type such as type UserID = String so I could type my column id text not null primary key as a UserID ? 🤔 I'm afraid that's not possible, and that's okay I'll add my own wrapper on top of it.
I don't know if other people would be interested in that, and I'm afraid this would make the sql be non standard then… :/

shadow shale Nov 2, 2025, 7:32 PM

#

Hello there! Nope there's no way to

#

Also relevant to point out that a type alias does not give you type safety, so if you really want to keep user ids from other strings to not mix those up you'd have to use a custom type wrapper:

pub type UserId {
  UserId(String)
}

stone peak Nov 2, 2025, 7:36 PM

#

Oh my, I completely though that was the case 🤦 Thanks for the correction !

#

Also, insert queries don't have named parameters ? I see the code is generated as arg_1, arg_2 etc…

shadow shale Nov 2, 2025, 7:39 PM

#

stone peak Oh my, I completely though that was the case 🤦 Thanks for the correction !

Yeah it's easy to get that wrong, happy to help! One other small note is that when you have acronyms convention is to use PascalCase: Html and not HTML, Json and not JSON, Id and not ID, ...

stone peak Nov 2, 2025, 7:39 PM

#

Sorry, some Go habbit got the better of me 😅

shadow shale Nov 2, 2025, 7:40 PM

#

stone peak Also, insert queries don't have named parameters ? I see the code is generated a...

That's an unfortunate limitation we inherit form postgres, It doesn't allow to have named query parameters sadly. There's an open discussion on how squirrel could support it but so far no design is really worth it

#

In an ideal world Postgres will add support for named arguments and we could use those

stone peak Nov 2, 2025, 7:41 PM

#

Well, we have Gleam, nice libraries and nicer people, we're not that far from ideal world 💜

shadow shale Nov 2, 2025, 7:43 PM

#

We have it so good in Gleam land 🥹💜

shadow shale Jun 3, 2026, 1:04 PM

#

I've released Squirrel 4.7.0, there's a couple of bug fixes for code that was not formatted properly and now squirrel can try and generate better argument names for queries. For example:

select squirrel.name
from squirrel
where squirrel_id = $1

The generated function is going to have the following definition:

pub fn select_squirrel(
  db: pog.Connection,
  squirrel_id: Int
) -> Result(pog.Returned(GetLoginLinkRow), pog.QueryError) {
  // ... the generated code ...
}

forest vector Jun 3, 2026, 1:05 PM

#

jak i have a nit

shadow shale Jun 3, 2026, 1:07 PM

#

Tell me

forest vector Jun 3, 2026, 1:08 PM

#

db 👎
database/connection 👍

#

:3

shadow shale Jun 3, 2026, 1:08 PM

#

Oh you're totally right, let me change that

#

That was old jak being lazy

pine crag Jun 3, 2026, 1:08 PM

#

maybe this has already been proposed and shot down, but wouldn't it be nicer to generate lables for the arguments?

shadow shale Jun 3, 2026, 1:08 PM

#

it was shot down

#

I don't want to commit to that yet, because then any change in how labels are picked would be a breaking change for squirrel users

forest vector Jun 3, 2026, 1:09 PM

#

not any change

pine crag Jun 3, 2026, 1:09 PM

#

I expected that, just to lazy to look it up 😅

forest vector Jun 3, 2026, 1:09 PM

#

i tried arguing for this kwando but jak rules with an iron fist

pine crag Jun 3, 2026, 1:10 PM

#

it is okay to be wrong 😅

#

Understandable, I just think it would be an ergonomic win once you get more than like 3 arguments. I've messed up the ordering several times, especially for queries like my_query(pog.Connection, String, String, String, String, String, String, String)

forest vector Jun 3, 2026, 1:27 PM

#

mhm mhm

hallow silo Jun 3, 2026, 1:39 PM

#

It would be a big win but we don’t have a way to reliably set names yet

#

ATM it’s a best effort to pick something appropriate-ish, but APIs should be designed and shouldn’t change between versions

jagged nova Jun 3, 2026, 2:15 PM

#

Gimme magic comments that let me name them (don’t do this for me, I don’t actually use squirrel so I shouldn’t influence the design)

shadow shale Jun 3, 2026, 2:21 PM

#

Yeah Louis is right that’s why it’s not doing labels (and probably never will until Postgres supports names query parameters)

hallow silo Jun 3, 2026, 2:31 PM

#

There could be another package that is like Squirrel but adds additional non-PostgreSQL native functionality

#🐿 squirrel - type safe SQL in Gleam