common_sql

A minimal database driver abstraction layer for Gleam.

common_sql defines a shared interface — types and functions — that is driver-agnostic. Pick a driver package for your database and pass it to common_sql functions. Your application code only ever imports common_sql.

Available drivers

Usage

gleam add common_sql common_sql_sqllite
# or
gleam add common_sql common_sql_postgresql

SQLite example

import common_sql as sql
import common_sql_sqllite
import gleam/dynamic/decode

type User {
  User(id: Int, name: String)
}

pub fn main() {
  let driver = common_sql_sqllite.driver()

  let user_decoder = {
    use id <- decode.field(0, decode.int)
    use name <- decode.field(1, decode.string)
    decode.success(User(id:, name:))
  }

  // with_connection opens the connection, runs the callback, then closes
  // automatically — even if the callback returns an error.
  use conn <- sql.with_connection(driver, "file:mydb.sqlite3")
  sql.execute(
    driver,
    conn,
    sql.Sql("SELECT id, name FROM users WHERE id = ?"),
    [sql.PInt(1)],
    user_decoder,
  )
}

PostgreSQL example

import common_sql as sql
import common_sql_postgresql
import gleam/dynamic/decode

type User {
  User(id: Int, name: String)
}

pub fn main() {
  let driver = common_sql_postgresql.driver()

  let user_decoder = {
    use id <- decode.field(0, decode.int)
    use name <- decode.field(1, decode.string)
    decode.success(User(id:, name:))
  }

  use conn <- sql.with_connection(driver, "postgres://user:pass@localhost/mydb")
  sql.execute(
    driver,
    conn,
    sql.Portable("SELECT id, name FROM users WHERE id = $1"),
    [sql.PInt(1)],
    user_decoder,
  )
}

If you need the connection object beyond a single block, use connect and close directly:

let assert Ok(conn) = sql.connect(driver, "postgres://localhost/mydb")
// ... multiple queries ...
sql.close(driver, conn)

Parameters

Use the Param type to pass values to queries:

SQL and portability

common_sql.execute accepts a Query value instead of a plain string:

Use Portable when you want a single query string that works with any driver:

// Works with both SQLite and PostgreSQL drivers:
sql.execute(driver, conn, sql.Portable("SELECT id FROM users WHERE id = $1"), [sql.PInt(1)], decode.int)

Limitations of sql.Portable()

For sqlite, $N inside SQL string literals is not distinguished from a real placeholder.

Error handling

All fallible operations return Result(_, DbError):

pub type DbError {
  QueryError(String)       // query failed or row decode failed
  ConnectionError(String)  // could not establish a connection
}

Implementing a new driver

A driver is a Driver(conn) value — a record of three functions generic over the driver's own opaque connection type.

import common_sql.{Driver, DbError, Param, QueryError, ConnectionError}
import gleam/dynamic

pub opaque type Conn {
  Conn(// ... internal handle ...)
}

pub fn driver() -> Driver(Conn) {
  Driver(
    driver_type: "mydb",
    connect: fn(url) {
      // Open a real connection, return Ok(Conn(...)) or Error(ConnectionError(...))
      todo
    },
    execute: fn(conn, query, params) {
      // Run the query, return Ok(List(dynamic.Dynamic)) or Error(QueryError(...))
      // `query` is common_sql.Sql(sql_string) or common_sql.Portable(sql_string).
      // Extract the SQL string and convert placeholders if your driver requires it.
      // Do NOT decode rows here — return raw Dynamic values.
      todo
    },
    close: fn(conn) {
      // Tear down the connection, return Nil.
      // This is always called by with_connection after the callback finishes.
      todo
    },
  )
}

Responsibilities

Drivers return List(dynamic.Dynamic) — one Dynamic per row. common_sql.execute applies the caller-supplied decode.Decoder(a) to each row and collects the results.

Design notes

• with_connection is the preferred API. It guarantees close is always called, even when the callback returns an error.
• No connection pooling. Use a pooling library on top if needed.
• No query building. Write SQL directly.
• No ORM. Map rows yourself with gleam/dynamic/decode.
• Decoding is centralised. Drivers stay simple; all decoding logic lives in common_sql.
• Only gleam_stdlib is required. This package has no Hex dependencies beyond the standard library.

Development

gleam test   # run the test suite
gleam build  # compile the package

Licence

MIT