# 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

| Package | Database | Hex link |
|---------|----------|----------|
| [`common_sql_sqlite`](../common_sql_sqlite) | SQLite (via `sqlight`) | [hex.pm/packages/sqlight](https://hex.pm/packages/sqlight) |
| [`common_sql_postgresql`](../common_sql_postgresql) | PostgreSQL (via `pog`) | [hex.pm/packages/pog](https://hex.pm/packages/pog) |

## Usage

```sh
gleam add common_sql common_sql_sqlite
# or
gleam add common_sql common_sql_postgresql
```

### SQLite example

```gleam
import common_sql as sql
import common_sql_sqlite
import gleam/dynamic/decode

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

pub fn main() {
  let driver = common_sql_sqlite.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

```gleam
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:

```gleam
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:

| Constructor       | Gleam type |
|-------------------|----------|
| `PInt(Int)`       | `Int`    |
| `PString(String)` | `String` |
| `PFloat(Float)`   | `Float`  |
| `PBool(Bool)`     | `Bool`   |
| `PNull`           | SQL NULL |

### SQL and portability

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

| Constructor | Description |
|-------------|-------------|
| `Sql(String)` | Driver-native SQL, passed as-is. Use `?` for SQLite or `$1` for PostgreSQL. |
| `Portable(String)` | SQL with PostgreSQL-style `$1, $2, …` placeholders. Drivers that need a different syntax (e.g. SQLite) convert them automatically. |

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

```gleam
// 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)`:

```gleam
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.

```gleam
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

| Concern             | Where it lives          |
|---------------------|-------------------------|
| Connection handling | Driver package          |
| Param marshalling   | Driver package          |
| Row decoding        | `common_sql` (`execute`) |
| Error normalisation | Driver package → `DbError` |

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

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

## Licence

MIT