🐿️ squirrel - type safe SQL in Gleam

squirrel_demo.mp4

What's Squirrel?

If you need to talk with a database in Gleam you'll have to write something like this:

import gleam/pgo
import decode/zero

pub type FindSquirrelRow {
  FindSquirrelRow(name: String, owned_acorns: Int)
}

/// Find a squirrel and its owned acorns given its name.
///
pub fn find_squirrel(db: pgo.Connection, name: String) {
  let squirrel_row_decoder = {
    use name <- zero.field(0, zero.string)
    use owned_acorns <- zero.field(1, zero.int)
    zero.success(FindSquirrelRow(name:, owned_acorns:))
  }

  "
  select
     name,
     owned_acorns
   from
     squirrel
   where
     name = $1
  "
  |> pgo.execute(db, [pgo.text(name)], zero.run(_, squirrel_row_decoder))
}

This is probably fine if you have a few small queries but it can become quite the burden when you have a lot of queries:

• The SQL query you write is just a plain string, you do not get syntax highlighting, auto formatting, suggestions... all the little niceties you would otherwise get if you where writing a plain *.sql file.
• This also means you loose the ability to run these queries on their own with other external tools, inspect them and so on.
• You have to manually keep in sync the decoder with the query's output.

One might be tempted to hide all of this by reaching for something like an ORM. Squirrel proposes a different approach: instead of trying to hide the SQL it embraces it and leaves you in control. You write the SQL queries in plain old *.sql files and Squirrel will take care of generating all the corresponding functions.

A code snippet is worth a thousand words, so let's have a look at an example. Instead of the hand written example shown earlier you can instead just write the following query:

-- we're in file `src/squirrels/sql/find_squirrel.sql`
-- Find a squirrel and its owned acorns given its name.
select
  name,
  owned_acorns
from
  squirrel
where
  name = $1

And run gleam run -m squirrel. Just like magic you'll now have a type-safe function find_squirrel you can use just as you'd expect:

import gleam/pgo
import squirrels/sql

pub fn main() {
  let db = todo as "the pgo connection"
  // And it just works as you'd expect:
  let assert Ok(pgo.Returned(_rows_count, rows)) = sql.find_squirrel("sandy")
  let assert [FindSquirrelRow(name: "sandy", owned_acorns: 11_111)] = rows
}

Behind the scenes Squirrel generates the decoders and functions you need; and it's pretty-printed, standard Gleam code (actually it's exactly like the hand written example I showed you earlier)! So now you get the best of both worlds:

• You don't have to take care of keeping encoders and decoders in sync, Squirrel does that for you.
• And you're not compromising on type safety either: Squirrel is able to understand the types of your query and produce a correct decoder.
• You can stick to writing plain SQL in *.sql files. You'll have better editor support, syntax highlighting and completions.
• You can run each query on its own: need to explain a query? No big deal, it's just a plain old *.sql file.

Usage

First you'll need to add Squirrel to your project as a dev dependency:

gleam add squirrel --dev

# Remember to add these packages if you haven't yet, they are needed by the
# generated code to run and decode the read rows!
gleam add gleam_pgo
gleam add decode

Then you can ask it to generate code running the squirrel module:

gleam run -m squirrel

And that's it! As long as you follow a couple of conventions Squirrel will just work:

• Squirrel will look for all *.sql files in any sql directory under your project's src directory.
• Each sql directory will be turned into a single Gleam module containing a function for each *.sql file inside it. The generated Gleam module is going to be located in the same directory as the corresponding sql directory and it's name is sql.gleam.
• Each *.sql file must contain a single SQL query. And the name of the file is going to be the name of the corresponding Gleam function to run that query.

Let's make an example. Imagine you have a Gleam project that looks like this

├── src
│   ├── squirrels
│   │   └── sql
│   │       ├── find_squirrel.sql
│   │       └── list_squirrels.sql
│   └── squirrels.gleam
└── test
    └── squirrels_test.gleam

Running gleam run -m squirrel will create a src/squirrels/sql.gleam file defining two functions find_squirrel and list_squirrels you can then import and use in your code.

Talking to the database

In order to understand the type of your queries, Squirrel needs to connect to the Postgres server where the database is defined. To connect, it will read the DATABASE_URL env variable that has to be a valid connection string with the following format:

postgres://user:password@host:port/database

If a DATABASE_URL variable is not set, Squirrel will instead read your Postgres env variables and use the following defaults if one is not set:

• PGHOST: "localhost"
• PGPORT: 5432
• PGUSER: "root"
• PGDATABASE: the name of your Gleam project
• PGPASSWORD: ""

Supported types

Squirrel takes care of the mapping between Postgres types and Gleam types. This is needed in two places:

• Gleam values need to be encoded into Postgres values when you're filling in the holes of a prepared statement ($1, $2, ...)
• Postgres values need to be decoded into a Gleam ones when you're reading the rows returned by a query.

The types that are currently supported are:

postgres type	encoded as	decoded as
bool	Bool	Bool
text, char, bpchar, varchar	String	String
float4, float8, numeric	Float	Float
int2, int4, int8	Int	Int
json, jsonb	Json	String
uuid	Uuid	Uuid
bytea	BitArray	BitArray
date	#(Int, Int, Int) with #(year, month, day)	#(Int, Int, Int) with #(year, month, day)
timestamp	#(#(Int, Int, Int), (#(Int, Int, Int)) with #(#(year, month, day), #(hour, minute, second))	#(#(Int, Int, Int), (#(Int, Int, Int)) with #(#(year, month, day), #(hour, minute, second))
<type>[] (where <type> is any supported type)	List(<type>)	List(<type>)
user-defined enum	Gleam custom type	Gleam custom type

Enums

If your queries deal with user-defined enums Squirrel will automatically turn each one of those into a corresponding Gleam type to make sure your code is type safe.

For example, consider the following enum:

create type squirrel_colour as enum (
  'light_brown',
  'grey',
  'red'
);

Squirrel turns that into a Gleam type that looks like this:

pub type SquirrelColour {
  LightBrown
  Grey
  Red
}

Squirrel will convert all the enum name and enum variants into PascalCase to make sure the generated Gleam code is valid. Notice how this transformation might result in having a name that is still not valid Gleam code; for example if you had an enum variant '1_first' that would become 1First which is not valid Gleam!

Squirrel won't try and trim invalid characters from the names and instead will fail letting you know you should change those names into something that can be turned into valid Gleam code.

FAQ

What flavour of SQL does squirrel support?

Squirrel only has support for Postgres.

Why isn't squirrel configurable in any way?

By going the "convention over configuration" route, Squirrel enforces that all projects adopting it will always have the same structure. If you need to contribute to a project using Squirrel you'll immediately know which directories and modules to look for.

This makes it easier to get started with a new project and cuts down on all the bike shedding: "Where should I put my queries?", "How many queries should go in on file?", ...

References

This package draws a lot of inspiration from the amazing yesql and sqlx.

Contributing

If you think there’s any way to improve this package, or if you spot a bug don’t be afraid to open PRs, issues or requests of any kind! Any contribution is welcome 💜