Gleam database tutorial
Postgres, pog, and Squirrel
Table of Contents
Postgres will be your database
If you are trying to build something for the world in 2026 and you are using a relational DB this is the one you need to know. (sqlite has its uses too, sure - never use anything from microsoft or oracle)
pog is the Gleam client you want. It’s good, widely used, and supported by lpil, creator of gleam. It’s the standard.
very quickly you are going to want to use squirrel. It’s good, widely used, and supported by giacomocavalieri, a core gleam dev.
yes, you will need to write SQL. no you don’t need an ORM. SQL is good, ORMs are … mixed: they make easy things easier but hard things harder. You may think you can avoid learning SQL, but you can not, and that’s actually fine. Giacomo has a relevant video (oh wow, they published this as I was writing this post) I’m finding that LLMs can be very helpful suggesting ways to write your queries.
psqlis the admin tool for postgres. I don’t recommend the GUI tool pgAdmin4 - it’s an insultingly gigantic, bloated mess. There are probably some good GUI tools but I haven’t found one that is free and excellent. psql is always available and does everything, but you have to learn some commands.here’s a short one
here’s the things I always have to look up
database vs schema :
postgres-server → databases → schemas → tables
technically the tables live under a schema and the schemas live under the database - don’t worry about it, just use the ‘public’ schema. your server can have many databases.user vs role - Postgres refers to ‘roles’ meaning ‘a user acting in this <the role> capacity’ - don’t worry about it it’s just the user.
users and owners. to start with, create a single user that you and your code will connect as. make sure they are the owner of the database and have all the grants. You can narrow all this down and make more restricted access later.
the “postgres” user is like the superuser for the whole postgres server, you need to be able to connect as this user. write its password down in your password manager. this is the user that creates other users.
psql command reference
\l list databases
\c <database name> : connect to db
\d describe tables in the connected db
\du describe users and their roles
Make a database
Not teaching databases today. Here’s just a minimal practical example to get started.
Create database and a user, grant all, and change ownership
$ psql
CREATE DATABASE example_db;
CREATE USER example_user WITH ENCRYPTED PASSWORD 'abc123';
GRANT ALL PRIVILEGES ON DATABASE example_db TO example_user;
ALTER DATABASE example_db OWNER TO example_user;
\l
...
\qreconnect at example_user to example_db and create a table and add a record.
$ psql -U example_user example_db
CREATE TABLE mytable (
id serial PRIMARY KEY, -- auto_incrementing PK
name text NOT NULL UNIQUE, -- unique name
ts timestamp NOT NULL DEFAULT now(),
score integer
);
\d
List of relations
Schema | Name | Type | Owner
--------+----------------+----------+--------------
public | mytable | table | example_user
public | mytable_id_seq | sequence | example_user
(2 rows)
INSERT INTO mytable (name, score) VALUES ('Alice', 42);
SELECT * FROM mytable;
id | name | ts | score
----+-------+----------------------------+-------
1 | Alice | 2025-12-09 23:15:31.920194 | 42Connection string
Used to be that you would get all the individual elements like HOST and PORT from discrete environment variables, but now everyone uses a database connection URI which has it all in one:
postgresql://[user[:password]@]host[:port][/dbname]Fun facts!
gleam seems to not allow any parameters at the end of the string. Connection strings are defined as
postgresql://[user[:password]@]host[:port][/dbname][?param1=value1&...]but the my call to url_config returned an Error when I had ?param… parts. The docs seem to indicate it should work, but that was crashing my program.the password (well, and everything else too) needs to be URL-encoded. Here is a real example - the password has quotes and a bracket that had to be encoded - ugh.
DATABASE_URL=postgres://myuser:Ace%22%5DRj54w2@localhost:5432/mydbIn your code you could just assign that value to a string and use it for testing, but really you must move it out into a environment variable.
read environment variables with envoy
or read from a “.env” file with dotenv_gleam
Set your environment variable however you want, check that it’s in you environment with :
$ echo $DATABASE_URL
postgres://... blah blah blahinstall envoy:
gleam add envoywrite file: src/db_example.gleam
import envoy
pub fn main() {
let assert Ok(db_url) = envoy.get(”DATABASE_URL”)
echo db_url
}run it and see your connection string from the environment:
$ gleam run -m db_example
Compiling envoy
Compiling proto
Compiled in 0.35s
Running db_example.main
src/db_example.gleam:5
“postgres://myuser:Ace%22%5DRj54w2@localhost:5432/mydb”Connect and query from gleam
A ton of stuff has to come together now so you can connect and read from your database. Here a minimal practical example you can learn from:
import envoy
import gleam/erlang/process
import pog
import gleam/otp/static_supervisor as supervisor
import gleam/dynamic/decode
pub fn main() {
// read the environment var with the connection string
let assert Ok(db_url) = envoy.get(”DATABASE_URL”)
echo “DATABASE_URL: “<> db_url
// you will need a Name to access the db connection pool
let db_pool_name = process.new_name(”db_pool”)
// load the connection config from the db_url
let assert Ok(config) = pog.url_config(db_pool_name, db_url)
echo config
// make a child spec from the config for your pool
let pool_child_spec = config
|> pog.pool_size(5)
|> pog.supervised
// start a supervisor with the child spec
let _ = supervisor.new(supervisor.RestForOne)
|> supervisor.add(pool_child_spec)
|> supervisor.start
// connect
let db = pog.named_connection(db_pool_name)
echo db
// the query SQL
let query_sql = “SELECT name, score FROM mytable WHERE id = $1”
// how to decode the results into gleam values
let row_decoder = {
use name <- decode.field(0, decode.string)
use score <- decode.field(1, decode.int)
decode.success(#(name, score))
}
// build a query from the sql, a parameter, and a decoder. run it.
let id_param = pog.int(1)
let assert Ok(data) = pog.query(query_sql)
|> pog.parameter(id_param)
|> pog.returning(row_decoder)
|> pog.execute(db)
// dig into the result data :
assert data.count == 1
assert data.rows == [#(”Alice”, 42)]
echo data
}
I swear you have to actually read that line by line. look up stuff you don’t understand. Key Concepts from the top down:
import alias because
static_supervisoris kinda longlet assert Ok(db_url)if it’s not Ok let it crash.process.new_nameNames are essential when working with processes and supervisorschild specis the recipe that defines worker processes under a supervisorstarting a supervisor - not hard at all
decoders! take external data and turn it into properly typed gleam values
SQL statement with numbered parameters like “$1”
OK! That works. The connection stuff only has to happen once and could be put off into a helper function. That one line of SQL became like 10 lines of decoder and result fetcher and you’re going to end up with dozens of queries. It’s squirrel time 🐿️
Use squirrel to build your API
this is a marathon. but you’re almost there. 🐿️ is a clever utility that reads files containing SQL queries and automatically builds gleam code in a special module that gives you a nice way to run the queries from your gleam code with much less boilerplate. Its docs are pretty good, so go and read what the squirrel has to say.
From your project directory (the one that contains /src/):
gleam add squirrel --dev mkdir src/sqlsave the little SQL query from above into a file
src/sql/get_mytable.sql
SELECT name, score FROM mytable WHERE id = $1run squirrel
gleam run -m squirrelit will build a new file with the generated functions and types:
/src/sql.gleam
in your code you can now:
import sql
...
// add this at the end of the main() function above
// is uses the same db connection as before "db"
// and takes the one parameter we defined in the SQL
let assert Ok(data) = sql.get_mytable(db, 1)
echo data.rowsSquirrel creates a function and a Row type for each .sql file using the name of the file. So from src/sql/get_mytable.sql I get sql.get_mytable() and sql.GetMytableRow. This is MUCH nicer than doing all that decoding yourself.
A little aside about transactions:
I wasn’t going to get into transactions here, but it was not super clear in the docs and this hint will help someone. To get a transaction you have to do your call through pog.transaction() - it then calls your intended function. It can return a handy TransactionError telling you if it rolled back and exactly what postgres had a problem with.
Go build something.
Oh and try out your public hosting environment early just to be sure it all works. Make your dev environment look similar in case they have any funny requirements (like database versions or whatever).
Production Environment
Your hosting service will have some way for you to turn on the database and they will provide a connection string that your code will use. probably it will be in an environment variable like “DATABASE” - maybe you have to set it yourself. There are lots of alternatives on how to get the connection info to your code: ENV vars, .env files (aka dotenv), other shared secret files. Just don’t check in the secret codes to github.
You might be able to connect to the DB from your dev environment if they give you an external connection - I’d keep that channel closed except for an emergency. You should always be able to ssh or get a terminal through the hosting service and use psql from that command line.
Fear and Loathing
not addressed here:
security best practices. don’t check in secrets. use good passwords. allow minimal access. have backups.
managed migrations so you can change your DB schema with some confidence. Looks like cigogne is the preferred tool, but I have not used it yet.
replication, backups, scaling, sharding, high-availability
data safety, privacy, and how to do development and debugging using data from production
performance and tuning. logs, indexes, optimizations, caching, and hardware.
These are “success problems” - don’t worry about them until you have something working at all.