An Elixir SQLite3 library.
If you are looking for the Ecto adapater, take a look at the Ecto SQLite3 library.
Documentation: https://hexdocs.pm/exqlite Package: https://hex.pm/packages/exqlite
- Prepared statements are not cached.
- Prepared statements are not immutable. You must be careful when manipulating statements and binding values to statements. Do not try to manipulate the statements concurrently. Keep it isolated to one process.
- Simultaneous writing is not supported by SQLite3 and will not be supported here.
- All native calls are run through the Dirty NIF scheduler.
- Datetimes are stored without offsets. This is due to how SQLite3 handles date
and times. If you would like to store a timezone, you will need to create a
second column somewhere storing the timezone name and shifting it when you
get it from the database. This is more reliable than storing the offset as
+03:00
as it does not respect daylight savings time.
defp deps do
[
{:exqlite, "~> 0.11.6"}
]
end
config :exqlite, default_chunk_size: 100
default_chunk_size
- The chunk size that is used when multi-stepping when not specifying the chunk size explicitly.
This will vary depending on the operating system.
# tell exqlite that we wish to use some other sqlite installation. this will prevent sqlite3.c and friends from compiling
export EXQLITE_USE_SYSTEM=1
# Tell exqlite where to find the `sqlite3.h` file
export EXQLITE_SYSTEM_CFLAGS=-I/usr/include
# tell exqlite which sqlite implementation to use
export EXQLITE_SYSTEM_LDFLAGS=-L/lib -lsqlite3
After exporting those variables you can then invoke mix deps.compile
. Note if you
re-export those values, you will need to recompile the exqlite
dependency in order to
pickup those changes.
As of version 0.9, exqlite
supports loading database engines at runtime rather than compiling sqlite3.c
itself.
This can be used to support database level encryption via alternate engines such as SQLCipher
or the Official SEE extension. Once you have either of those projects installed
on your system, use the following environment variables during compilation:
# tell exqlite that we wish to use some other sqlite installation. this will prevent sqlite3.c and friends from compiling
export EXQLITE_USE_SYSTEM=1
# Tell exqlite where to find the `sqlite3.h` file
export EXQLITE_SYSTEM_CFLAGS=-I/usr/local/include/sqlcipher
# tell exqlite which sqlite implementation to use
export EXQLITE_SYSTEM_LDFLAGS=-L/usr/local/lib -lsqlcipher
Once you have exqlite
configured, you can use the :key
option in the database config to enable encryption:
config :exqlite, key: "super-secret'
The Exqlite.Sqlite3
module usage is fairly straight forward.
# We'll just keep it in memory right now
{:ok, conn} = Exqlite.Sqlite3.open(":memory:")
# Create the table
:ok = Exqlite.Sqlite3.execute(conn, "create table test (id integer primary key, stuff text)")
# Prepare a statement
{:ok, statement} = Exqlite.Sqlite3.prepare(conn, "insert into test (stuff) values (?1)")
:ok = Exqlite.Sqlite3.bind(conn, statement, ["Hello world"])
# Step is used to run statements
:done = Exqlite.Sqlite3.step(conn, statement)
# Prepare a select statement
{:ok, statement} = Exqlite.Sqlite3.prepare(conn, "select id, stuff from test")
# Get the results
{:row, [1, "Hello world"]} = Exqlite.Sqlite3.step(conn, statement)
# No more results
:done = Exqlite.Sqlite3.step(conn, statement)
# Release the statement.
#
# It is recommended you release the statement after using it to reclaim the memory
# asap, instead of letting the garbage collector eventually releasing the statement.
#
# If you are operating at a high load issuing thousands of statements, it would be
# possible to run out of memory or cause a lot of pressure on memory.
:ok = Exqlite.Sqlite3.release(conn, statement)
Exqlite supports loading run-time loadable SQLite3 extensions. A selection of precompiled extensions for popular CPU types / architectures is available by installing the ExSqlean package. This package wraps SQLean: all the missing SQLite functions.
alias Exqlite.Basic
{:ok, conn} = Basic.open("db.sqlite3")
:ok = Basic.enable_load_extension(conn)
# load the regexp extension - https://github.com/nalgeon/sqlean/blob/main/docs/re.md
Basic.load_extension(conn, ExSqlean.path_for("re"))
# run some queries to test the new `regexp_like` function
{:ok, [[1]], ["value"]} = Basic.exec(conn, "select regexp_like('the year is 2021', ?) as value", ["2021"]) |> Basic.rows()
{:ok, [[0]], ["value"]} = Basic.exec(conn, "select regexp_like('the year is 2021', ?) as value", ["2020"]) |> Basic.rows()
# prevent loading further extensions
:ok = Basic.disable_load_extension(conn)
{:error, %Exqlite.Error{message: "not authorized"}, _} = Basic.load_extension(conn, ExSqlean.path_for("re"))
# close connection
Basic.close(conn)
I needed an Ecto3 adapter to store time series data for a personal project. I didn't want to go through the hassle of trying to setup a postgres database or mysql database when I was just wanting to explore data ingestion and some map reduce problems.
I also noticed that other SQLite3 implementations didn't really fit my needs. At
some point I also wanted to use this with a nerves project on an embedded device
that would be resiliant to power outages and still maintain some state that
ets
can not afford.
We are using the Dirty NIF scheduler to execute the sqlite calls. The rationale behind this is that maintaining each sqlite's connection command pool is complicated and error prone.
Feel free to check the project out and submit pull requests.