# common_sql_sqlite

SQLite driver for [`common_sql`](../common_sql), implemented with [`sqlight`](https://hex.pm/packages/sqlight).

**For usage documentation, examples, and the full API reference, see the [`common_sql` README](../common_sql/README.md).**

This package exposes a single `driver()` function. Add it alongside `common_sql`:

```sh
gleam add common_sql common_sql_sqlite
```

```gleam
import common_sql as sql
import common_sql_sqlite

pub fn main() {
  let driver = common_sql_sqlite.driver()
  use conn <- sql.with_connection(driver, "file:mydb.sqlite3")
  // see common_sql README for full examples
}
```

## SQL placeholders

Use `sql.Sql("… WHERE id = ?")` for SQLite-native syntax. Use `sql.Portable("… WHERE id = $1")` to write queries in PostgreSQL style — the driver converts `$1, $2, …` to `?` automatically, so the same string works across both SQLite and PostgreSQL drivers.

**Limitations of `sql.Portable()`** 

`$N` inside SQL string literals is not distinguished from a real placeholder.

## SQLite connection strings

See the [SQLite URI documentation](https://sqlite.org/uri.html). Common examples:

```gleam
"file:mydb.sqlite3"             // file-based database
":memory:"                      // in-memory (useful for tests)
"file:memdb1?mode=memory&cache=shared"  // named shared-memory
"file:data.db?mode=ro"          // read-only
```

> **Booleans:** SQLite has no native boolean type. `PBool(True)` is stored as
> `1`, `PBool(False)` as `0`. Read boolean columns back with `sqlight.decode_bool()`
> or `decode.int`.

## Development

Requires a C compiler for the `esqlite` NIF.

```sh
gleam test   # run the integration tests
gleam build  # compile the package
```

## Licence

MIT