🐿️ squirrel - type safe SQL in Gleam

Package Version Hex Docs

Squirrel showcase

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

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 =
    decode.into({
      use name <- decode.parameter
      use owned_acorns <- decode.parameter
      FindSquirrelRow(name: name, owned_acorns: owned_acorns)
    })
    |> decode.field(0, decode.string)
    |> decode.field(1, decode.int)

  "select name, owned_acorns
   from   squirrel
   where  name = $1"
  |> pgo.execute(db, [pgo.text(name)], 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:

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:

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:

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:

Supported types

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

The types that are currently supported are:

postgres typeencoded asdecoded as
boolBoolBool
text, char, bchar, varcharStringString
float4, float8, numericFloatFloat
int2, int4, int8IntInt
json, jsonbJsonString
<type>[] (where <type> is any supported type)List(<type>)List(<type>)

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 💜

Search Document