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:
- 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.
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 anysql
directory under your project'ssrc
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 correspondingsql
directory and it's name issql.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.gleamRunning
gleam run -m squirrel
will create asrc/squirrels/sql.gleam
file defining two functionsfind_squirrel
andlist_squirrels
you can then import and use in your code.
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:https://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 projectPGPASSWORD
:""
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 , bchar , 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) |
<type>[] (where <type> is any supported type) |
List(<type>) |
List(<type>) |
Squirrel only has support for Postgres.
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?", ...
This package draws a lot of inspiration from the amazing yesql and sqlx.
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 π