sqlite_engine

# SqliteEngine

[![Latest release](https://img.shields.io/github/v/release/mindreframer/sqlite_engine?label=release)](https://github.com/mindreframer/sqlite_engine/releases/latest)
[![CI](https://github.com/mindreframer/sqlite_engine/actions/workflows/ci.yml/badge.svg)](https://github.com/mindreframer/sqlite_engine/actions/workflows/ci.yml)
[![Linux binaries](https://github.com/mindreframer/sqlite_engine/actions/workflows/linux-precompile.yml/badge.svg)](https://github.com/mindreframer/sqlite_engine/actions/workflows/linux-precompile.yml)
[![macOS binaries](https://github.com/mindreframer/sqlite_engine/actions/workflows/macos-precompile.yml/badge.svg)](https://github.com/mindreframer/sqlite_engine/actions/workflows/macos-precompile.yml)
[![License: MIT](https://img.shields.io/badge/license-MIT-blue.svg)](https://github.com/mindreframer/sqlite_engine/blob/main/LICENSE)

SqliteEngine lets Elixir applications use SQLite through the familiar
`DBConnection` interface. It works well for ordinary SQLite databases and also
includes tools for concurrent writes and small Luau functions that run close to
the data.

## Highlights

- A straightforward `SqliteEngine.query/4` API built on `DBConnection`.
- A lower-level API through `SqliteEngine.Sqlite3` when more control is needed.
- Prebuilt binaries for supported Linux and macOS systems.
- SQLite Bedrock support for WAL2 and `BEGIN CONCURRENT`.
- Luau procedures with limits on runtime and database access.

## Installation

Until the package is published on Hex, install the tagged release from GitHub:

```elixir
def deps do
  [
    {:sqlite_engine,
     github: "mindreframer/sqlite_engine",
     tag: "v0.1.0"}
  ]
end
```

Then run:

```bash
mix deps.get
```

SqliteEngine currently requires Elixir 1.20 and OTP 29. Prebuilt binaries are
downloaded automatically for:

- Linux x86_64 and ARM64, using glibc or musl (including Alpine)
- macOS Intel and Apple Silicon

Windows and Android are not supported.

## Quick start

```elixir
{:ok, db} = SqliteEngine.start_link(database: "app.db")

SqliteEngine.query!(db, """
CREATE TABLE IF NOT EXISTS notes (
  id INTEGER PRIMARY KEY,
  body TEXT NOT NULL
)
""")

SqliteEngine.query!(db, "INSERT INTO notes (body) VALUES (?)", ["Read the docs"])

result = SqliteEngine.query!(db, "SELECT id, body FROM notes ORDER BY id")
IO.inspect(result.rows)
```

Use `SqliteEngine.query/4` instead of `query!/4` when you want `{:ok, result}` or
`{:error, error}` rather than an exception.

## Luau procedures

SqliteEngine can store and run small Luau functions close to the database. This
is useful when several reads and writes should be handled as one command.
Execution time, memory use, SQL work, and result sizes are limited so a procedure
cannot run without bounds.

```elixir
alias SqliteEngine.Procedures

{:ok, procedure} =
  Procedures.install(db, "local input = ...; return {answer=input.value + 1}")

{:ok, %{"answer" => 42}} =
  Procedures.call(db, procedure.key, %{"value" => 41})
```

See [Native Luau application procedures](guides/luau.md) for transactions,
security settings, stored results, testing, and telemetry.

## Concurrent writes

The bundled SQLite build comes from the Bedrock branch and supports WAL2 and
`BEGIN CONCURRENT`. These are advanced, opt-in SQLite features that can help
some applications with several writers. They do not remove normal SQLite locks
or transaction conflicts, so test them with a workload similar to your own.

You do not need these features for regular SQLite use.

## Configuration

Most options are passed when starting a connection:

```elixir
{:ok, db} =
  SqliteEngine.start_link(
    database: "app.db",
    busy_timeout: 5_000,
    journal_mode: :wal
  )
```

Application-wide settings use the `:sqlite_engine` namespace:

```elixir
config :sqlite_engine,
  default_chunk_size: 100,
  type_extensions: [MyApp.TypeExtension]
```

Custom types implement `SqliteEngine.TypeExtension`.

## Building from source

To skip the prebuilt binary and compile locally, run these commands from a
SqliteEngine source checkout:

```bash
bin/setup_native_deps.sh
SQLITE_ENGINE_FORCE_BUILD=1 mix compile
```

A source build needs Git, a C/C++17 compiler, CMake, Make or Ninja, Tcl, and the
usual platform build tools. Dependency versions are locked by the files in
`native/locks/`, so normal setup does not silently move to a newer SQLite or
Luau version.

Useful build settings:

- `SQLITE_ENGINE_FORCE_BUILD=1` — always build locally.
- `SQLITE_ENGINE_USE_SYSTEM=1` — use the SQLite installed on the machine.
- `SQLITE_ENGINE_OFFLINE=1` — avoid network access after the locked sources have
  been placed in `tmp/native-deps/`.

A system SQLite build may not include Bedrock concurrency or extension support.
Use `SqliteEngine.Sqlite3.build_info/0` to see what the running binary contains.

## Migrating from Exqlite

SqliteEngine uses new module, application, configuration, and native library
names. Existing applications should read
[Migrating from Exqlite](guides/migrating_from_exqlite.md), back up their
databases, clear old build artifacts, and fully restart the Erlang VM.

The existing Ecto SQLite adapter is built for Exqlite and is not currently
supported by SqliteEngine. Direct `DBConnection` use is supported.

## Things to know

- Do not share a prepared statement between processes at the same time.
- Pass binary data as `{:blob, binary}`.
- SQLite datetime values do not preserve time-zone offsets.
- Performance depends heavily on transactions, schema design, storage, and
  write contention. Benchmark with your own workload.

## More information

- [Latest release and prebuilt binaries](https://github.com/mindreframer/sqlite_engine/releases/latest)
- [Luau procedure guide](guides/luau.md)
- [Migration guide](guides/migrating_from_exqlite.md)
- [Changelog](CHANGELOG.md)
- [Native dependency notices](NOTICE.md)

## Attribution

SqliteEngine is a fork of [Exqlite](https://github.com/elixir-sqlite/exqlite)
and preserves its MIT license. SQLite is public domain. Luau is included under
its packaged license files. See [NOTICE.md](NOTICE.md) for details.