Sqlcx gives you a way to create and query SQLCipher (encrypted SQLite) databases.

Basic Example

iex> {:ok, db} = Sqlcx.open(":memory:")
iex> Sqlcx.exec(db, "CREATE TABLE t (a INTEGER, b INTEGER, c INTEGER)")
:ok
iex> Sqlcx.exec(db, "INSERT INTO t VALUES (1, 2, 3)")
:ok
iex> Sqlcx.query(db, "SELECT * FROM t")
{:ok, [[a: 1, b: 2, c: 3]]}
iex> Sqlcx.query(db, "SELECT * FROM t", into: %{})
{:ok, [%{a: 1, b: 2, c: 3}]}

Configuration

Sqlcx uses the Erlang library esqlcipher which accepts a timeout parameter for almost all interactions with the database. The default value for this timeout is 5000 ms. Many functions in Sqlcx accept a :db_timeout option that is passed on to the esqlcipher calls and also defaults to 5000 ms. If required, this default value can be overridden globally with the following in your config.exs:

config :sqlcx, db_timeout: 10_000 # or other positive integer number of ms

Another esqlcipher parameter is :db_chunk_size. This is a count of rows to read from native sqlcipher and send to erlang process in one bulk. For example, consider a table mytable that has 1000 rows. We make the query to get all rows with db_chunk_size: 500 parameter:

Sqlcx.query(db, "select * from mytable", db_chunk_size: 500)

in this case all rows will be passed from native sqlcipher OS thread to the erlang process in two passes. Each pass will contain 500 rows. This parameter decrease overhead of transmitting rows from native OS sqlcipher thread to the erlang process by chunking list of result rows. Please, decrease this value if rows are heavy. Default value is 5000. If you’re in doubt what to do with this parameter, just leave it as is. The default value will be ok in almost all cases.

config :sqlcx, db_chunk_size: 500 # if most of the database rows are heavy