# sqlite
[](https://github.com/max-au/sqlite/actions) [](https://hex.pm/packages/sqlite) [](https://hexdocs.pm/sqlite)
sqlite3 NIF bindings for Erlang.
See `sqlite:query/3` for the data type conversion details. Current version
assumes UTF8 encoding for all TEXT columns.
All functions that may affect scheduling run in the dirty I/O scheduler.
Keep an eye on the dirty scheduler utilisation, and bump the number when
necessary (using `+SDio` argument).
## Build
Use `rebar3 compile` to build the application using included SQLite3
amalgamation. Specify `USE_SYSTEM_SQLITE` environment variable to
build against sqlite version provided by the system (tested with
sqlite 3.37.2 and newer).
sqlite3 amalgamation build flags are accepted through `CFLAGS`, e.g.
`CFLAGS="-DSQLITE_USE_URI" rebar3 compile`.
For a debug build, specify `DEBUG=1` environment variable. Both
NIF bindings and sqlite amalgamation files are affected by this flag. Remember
to run `rebar3 clean` when changing build flavour.
Requires OTP 24 and above (older versions do not support extended error
specification per EEP-54). Find a list of supported Operating Systems in
the corresponding section.
## Quick start
The easiest way to try it out is to run `rebar3 shell` within the cloned
project folder.
```
1> Conn = sqlite:open("mem", #{flags => [memory]}). %% open an in-memory database
#Ref<0.1238832725.87949353.240438>
2> sqlite:query(Conn, "CREATE TABLE kv (key INTEGER PRIMARY KEY AUTOINCREMENT, val INTEGER)").
[]
3> Prepared = sqlite:prepare(Conn, "INSERT INTO kv (val) VALUES (?1)", #{persistent => true}).
#Ref<0.1238832725.87949353.240439>
4> [sqlite:execute(Prepared, [Seq]) || Seq <- lists:seq(1, 100)].
[[],[],[],[],[],[]|...]
5> sqlite:query(Conn, "SELECT * FROM kv").
[{1,1}, {2,2}, <...>
```
## Configuration
Due to significant performance difference observed for in-memory databases,
there is an option to fall back to sqlite built-in memory allocation
functions. Set `enif_malloc` configuration variable to `false` (the default
is `true`) to let sqlite use built-in allocators instead of the recommended
`enif_alloc`. As a consequence, memory allocated by sqlite3 will no longer
be visible via usual Erlang functions. For example `erlang:memory/1` won't be
able to report sqlite3 allocations). Use this option only when you need that
last bit of performance.
## Profiling with Linux perf
Ensure ERTS with frame pointers enabled (`frmptr` flavour) is available.
```bash
export ERL_FLAGS="+JPperf true -emu_type frmptr"
export CFLAGS="-fno-omit-frame-pointer""
# run the benchmark test case
rebar3 ct --suite sqlite_QUITE --case benchmark_prepared
# take 10 second of perf profiling
perf record -g -p `pidof rebar3` -F 500 -- sleep 10
# convert to a flame graph
perf script | ./stackcollapse-perf.pl > out.perf-folded
./flamegraph.pl out.perf-folded > perf.svg
```
## Operating system support
Initial version has been tested on Linux (Ubuntu), Mac OS, FreeBSD 13 and
Windows 10.
### Mac OS
If you see Apple Silicon (ARM64) build failing with this diagnostic:
```
Undefined symbols for architecture arm64:
"_enif_alloc", referenced from:
```
Clear the `LDFLAGS` variable with `unset LDFLAGS` and try again.
### Windows
Windows build, with `rebar3` as an escript, assuming Ericsson official installation
into `C:\Program Files\Erlang OTP`. If you have it elsewhere, set `ERTS_INCLUDE_DIR`
environment variable appropriately, to the `usr/include` subfolder of your installation.
Build requires `x64 Native Tools Command Prompt`. Tested with VS 2022. Ensure
to see the slogan: `[vcvarsall.bat] Environment initialized for: 'x64'`
```
# set up PATH to escript/erl
set PATH=%PATH%;C:\Program Files\Erlang OTP\bin
# download or build rebar3 (assuming it is now in the current directory)
# use rebar3 to build sqlite and run Common Test
escript.exe rebar3 ct
```
## Missing Features for 1.0
* performance: use "trylock" and then reschedule instead of dirty NIF from the beginning
* performance: transparently handle BUSY and LOCKED instead of busy wait in dirty scheduler
* asynchronous APIs (using message exchange, e.g. for `query` and `execute`)
* diagnostic routines to enumerate prepared statements/statements running
## Features beyond 1.0
These features will not make it into 1.0, but are useful and may be implemented
in the following releases:
* non-experimental sqlite hooks support (commit, preupdate, rollback, update, wal)
* sqlite snapshot, vfs, blob and serialization support
* improved performance for concurrent access to the same connection/statement,
running on a normal scheduler and yielding
* support for atom() and map() type conversion
* named parameters with '@' and Tcl syntax
