SqliteEngine

Copy Markdown View Source

Latest release CI Linux binaries macOS binaries License: MIT

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

Add SqliteEngine to your dependencies:

def deps do
  [
    {:sqlite_engine, "~> 0.1.1"}
  ]
end

Then run:

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

{: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.

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

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

Application-wide settings use the :sqlite_engine namespace:

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:

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, 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

Attribution

SqliteEngine is a fork of Exqlite and preserves its MIT license. SQLite is public domain. Luau is included under its packaged license files. See NOTICE.md for details.