PGL

pgl is a PostgreSQL client written in Gleam.

Features

• Implementation of PostgreSQL wire protocol
• SSL support
• SCRAM-SHA-256, Cleartext, and MD5 password authentication
• Connection pooling provided by db_pool
• PostgreSQL data types provided by pg_value
• Transaction support
• Savepoint support
• Pipelining

Example

import gleam/dynamic/decode
import gleam/list
import pgl
import pg_value

pub fn main() {
  let conf =
    pgl.config
    |> pgl.host("localhost")
    |> pgl.port(5432)
    |> pgl.database("my_db")
    |> pgl.username("postgres")
    |> pgl.password("postgres")

  let db = pgl.new(conf)

  let assert Ok(_) = pgl.start(db)

  let conn = pgl.connection(db)

  let assert Ok(queried) =
    "SELECT id, name FROM users WHERE id = $1"
    |> pgl.sql
    |> pgl.values([pg_value.int(1)])
    |> pgl.query(conn)

  let assert Ok(users) =
    queried.rows
    |> list.try_map(decode.run(_, {
      use id <- decode.field(0, decode.int)
      use name <- decode.field(1, decode.string)
      decode.success(#(id, name))
    }))

  let assert Ok(_) = pgl.shutdown(db)
}

URL Configuration

You can also configure a connection from a URL:

let assert Ok(conf) =
  "postgres://user:pass@localhost:5432/my_db?sslmode=verify-ca"
  |> pgl.from_url

Supported sslmode values: disable, require, verify-ca, verify-full.

Rows as Dicts

By default, rows are returned as tuples. To return rows as Dicts where columns are keyed by name:

let conf =
  pgl.config
  |> pgl.rows_as_dict(True)

Pipelining

Use pgl.batch to send multiple queries without waiting for each to complete, reducing network round trips:

let queries = [
  pgl.Query("INSERT INTO users (name) VALUES ($1)", [pg_value.text("Alice")]),
  pgl.Query("INSERT INTO users (name) VALUES ($1)", [pg_value.text("Bob")]),
]

let assert Ok(results) = pgl.batch(queries, conn)

Transactions and Savepoints

Use pgl.transaction for automatic commit/rollback:

let assert Ok(result) =
  pgl.transaction(conn, fn(tx) {
    let assert Ok(_) =
      "INSERT INTO users (name) VALUES ($1)"
      |> pgl.sql
      |> pgl.values([pg_value.text("Alice")])
      |> pgl.query(tx)

    Ok("done")
  })

If the callback returns Error or raises an exception, the transaction is rolled back. Nested savepoints are also supported:

pgl.transaction(conn, fn(tx) {
  pgl.savepoint(tx, fn(sp) {
    // If this fails, only the savepoint is rolled back
    Ok(Nil)
  })
})

Supported Versions

• PostgreSQL 15, 16, 17, 18
• Erlang/OTP 28+

Installation

gleam add pgl

Further documentation can be found at https://hexdocs.pm/pgl.

Development

Tests require a running PostgreSQL instance:

docker compose up      # Start PostgreSQL
gleam test             # Run the tests

Acknowledgements

Early iterations of this package were based on pgo and its influence remains. pog and postgrex were also helpful in writing this package.