# `Exqlite.Sqlite3`
[🔗](https://github.com/ebifriedrice/exqlite/blob/v0.44.2/lib/exqlite/sqlite3.ex#L1)

The interface to the NIF implementation.

# `bind_value`

```elixir
@type bind_value() ::
  NaiveDateTime.t()
  | DateTime.t()
  | Date.t()
  | Time.t()
  | number()
  | iodata()
  | {:blob, iodata()}
  | atom()
```

# `db`

```elixir
@type db() :: reference()
```

# `open_mode`

```elixir
@type open_mode() :: :readwrite | :readonly | :nomutex | :create
```

# `open_opt`

```elixir
@type open_opt() :: {:mode, :readwrite | :readonly | :create | [open_mode()]}
```

# `reason`

```elixir
@type reason() :: atom() | String.t()
```

# `row`

```elixir
@type row() :: list()
```

# `statement`

```elixir
@type statement() :: reference()
```

# `bind`

```elixir
@spec bind(
  statement(),
  [bind_value()] | %{optional(String.t()) => bind_value()} | nil
) :: :ok
```

Resets a prepared statement and binds values to it.

    iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
    iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?, ?, ?, ?, ?")
    iex> Sqlite3.bind(stmt, [42, 3.14, "Alice", {:blob, <<0, 0, 0>>}, nil])
    iex> Sqlite3.step(conn, stmt)
    {:row, [42, 3.14, "Alice", <<0, 0, 0>>, nil]}

    iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
    iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT :42, @pi, $name, @blob, :null")
    iex> Sqlite3.bind(stmt, %{":42" => 42, "@pi" => 3.14, "$name" => "Alice", :"@blob" => {:blob, <<0, 0, 0>>}, ~c":null" => nil})
    iex> Sqlite3.step(conn, stmt)
    {:row, [42, 3.14, "Alice", <<0, 0, 0>>, nil]}

    iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
    iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?")
    iex> Sqlite3.bind(stmt, [42, 3.14, "Alice"])
    ** (ArgumentError) expected 1 arguments, got 3

    iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
    iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?, ?")
    iex> Sqlite3.bind(stmt, [42])
    ** (ArgumentError) expected 2 arguments, got 1

    iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
    iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?")
    iex> Sqlite3.bind(stmt, [:erlang.list_to_pid(~c"<0.0.0>")])
    ** (ArgumentError) unsupported type: #PID<0.0.0>

# `bind_blob`

```elixir
@spec bind_blob(statement(), non_neg_integer(), binary()) :: :ok
```

Binds a blob value to a prepared statement.

    iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
    iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?")
    iex> Sqlite3.bind_blob(stmt, 1, <<0, 0, 0>>)
    :ok

# `bind_float`

```elixir
@spec bind_float(statement(), non_neg_integer(), float()) :: :ok
```

Binds a float value to a prepared statement.

    iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
    iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?")
    iex> Sqlite3.bind_float(stmt, 1, 3.14)
    :ok

# `bind_integer`

```elixir
@spec bind_integer(statement(), non_neg_integer(), integer()) :: :ok
```

Binds an integer value to a prepared statement.

    iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
    iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?")
    iex> Sqlite3.bind_integer(stmt, 1, 42)
    :ok

# `bind_null`

```elixir
@spec bind_null(statement(), non_neg_integer()) :: :ok
```

Binds a null value to a prepared statement.

    iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
    iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?")
    iex> Sqlite3.bind_null(stmt, 1)
    :ok

# `bind_parameter_count`

```elixir
@spec bind_parameter_count(statement()) :: non_neg_integer() | {:error, reason()}
```

Returns number of SQL parameters in a prepared statement.

    iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
    iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?, ?")
    iex> Sqlite3.bind_parameter_count(stmt)
    2

# `bind_text`

```elixir
@spec bind_text(statement(), non_neg_integer(), String.t()) :: :ok
```

Binds a text value to a prepared statement.

    iex> {:ok, conn} = Sqlite3.open(":memory:", [:readonly])
    iex> {:ok, stmt} = Sqlite3.prepare(conn, "SELECT ?")
    iex> Sqlite3.bind_text(stmt, 1, "Alice")
    :ok

# `cancel`

```elixir
@spec cancel(db() | nil) :: :ok | {:error, reason()}
```

Cancel a running query: wake any busy handler sleep and interrupt VDBE execution.

This is a superset of `interrupt/1` — it sets a cancel flag that the busy and
progress handlers observe, and also calls `sqlite3_interrupt()`. After a
cancel, the connection can be reused normally.

Use this when a query might be blocked either inside SQLite bytecode
execution or inside the busy handler waiting for a lock.

# `changes`

```elixir
@spec changes(db()) :: {:ok, integer()} | {:error, reason()}
```

Get the number of changes recently.

**Note**: If triggers are used, the count may be larger than expected.

See: https://sqlite.org/c3ref/changes.html

# `close`

```elixir
@spec close(db() | nil) :: :ok | {:error, reason()}
```

Closes the database and releases any underlying resources.

# `columns`

```elixir
@spec columns(db(), statement()) :: {:ok, [binary()]} | {:error, reason()}
```

# `deserialize`

```elixir
@spec deserialize(db(), String.t(), binary()) :: :ok | {:error, reason()}
```

Disconnect from database and then reopen as an in-memory database based on
the serialized binary.

# `enable_load_extension`

```elixir
@spec enable_load_extension(db(), boolean()) :: :ok | {:error, reason()}
```

Allow loading native extensions.

# `execute`

```elixir
@spec execute(db(), String.t()) :: :ok | {:error, reason()}
```

Executes an sql script. Multiple stanzas can be passed at once.

# `fetch_all`

```elixir
@spec fetch_all(db(), statement()) :: {:ok, [row()]} | {:error, reason()}
```

# `fetch_all`

```elixir
@spec fetch_all(db(), statement(), integer()) :: {:ok, [row()]} | {:error, reason()}
```

# `interrupt`

```elixir
@spec interrupt(db() | nil) :: :ok | {:error, reason()}
```

Interrupt a long-running query.

This calls `sqlite3_interrupt()` and is effective while SQLite is actively
executing a statement. It does not wake the custom busy handler while the
connection is sleeping and waiting on a lock. Use `cancel/1` when you need
to abort both statement execution and busy waits.

> #### Warning {: .warning}
> If you are going to interrupt a long running process, it is unsafe to call
> `close/1` immediately after. You run the risk of undefined behavior. This
> is a limitation of the sqlite library itself. Please see the documentation
> https://www.sqlite.org/c3ref/interrupt.html for more information.
>
> If close must be called after, it is best to put a short sleep in order to
> let sqlite finish doing its book keeping.

# `last_insert_rowid`

```elixir
@spec last_insert_rowid(db()) :: {:ok, integer()}
```

# `multi_step`

```elixir
@spec multi_step(db(), statement()) ::
  :busy | {:rows, [row()]} | {:done, [row()]} | {:error, reason()}
```

# `multi_step`

```elixir
@spec multi_step(db(), statement(), integer()) ::
  :busy | {:rows, [row()]} | {:done, [row()]} | {:error, reason()}
```

# `open`

```elixir
@spec open(String.t(), [open_opt()]) :: {:ok, db()} | {:error, reason()}
```

Opens a new sqlite database at the Path provided.

`path` can be `":memory"` to keep the sqlite database in memory.

## Options

  * `:mode` - controls the flags for sqlite3_open_v2 (see
    https://www.sqlite.org/c3ref/c_open_autoproxy.html). Defaults to
    `[:readwrite, :create]` (opens for reading and writing and creates the
    file if it does not exist).

    Single modes are permitted:
    - `:readwrite` - read/write to the database. Does not create the database
      if it is not present. Use in combination with `:create` to create the
      database if it does not exist.
    - `:readonly` - read-only (file must exist).
    - `:create` - creates the database if it does not exist.

    Combinations are permitted:
    - `[:readwrite, :create]` - read/write + create if needed. This is the
      default if not specified.
    - `[:readonly, :nomutex]`

    Note: `[:readwrite, :nomutex]` is not recommended.

# `prepare`

```elixir
@spec prepare(db(), String.t()) :: {:ok, statement()} | {:error, reason()}
```

# `release`

```elixir
@spec release(db(), statement()) :: :ok | {:error, reason()}
```

Once finished with the prepared statement, call this to release the underlying
resources.

This should be called whenever you are done operating with the prepared statement. If
the system has a high load the garbage collector may not clean up the prepared
statements in a timely manner and causing higher than normal levels of memory
pressure.

If you are operating on limited memory capacity systems, definitely call this.

# `reset`

```elixir
@spec reset(statement()) :: :ok | {:error, atom()}
```

Resets a prepared statement.

See: https://sqlite.org/c3ref/reset.html

# `serialize`

```elixir
@spec serialize(db(), String.t()) :: {:ok, binary()} | {:error, reason()}
```

Serialize the contents of the database to a binary.

# `set_authorizer`

```elixir
@spec set_authorizer(db(), [atom()]) :: :ok | {:error, reason()}
```

Set an authorizer that denies specific SQL operations.

Accepts a list of action atoms to deny. Any SQL statement that triggers a
denied action will fail with a "not authorized" error during preparation.

Pass an empty list to clear the authorizer.

## Action atoms

`:attach`, `:detach`, `:pragma`, `:insert`, `:update`, `:delete`,
`:create_table`, `:drop_table`, `:create_index`, `:drop_index`,
`:create_trigger`, `:drop_trigger`, `:create_view`, `:drop_view`,
`:alter_table`, `:reindex`, `:analyze`, `:function`, `:savepoint`,
`:transaction`, `:read`, `:select`, `:recursive`,
`:create_temp_table`, `:create_temp_index`, `:create_temp_trigger`,
`:create_temp_view`, `:drop_temp_table`, `:drop_temp_index`,
`:drop_temp_trigger`, `:drop_temp_view`, `:create_vtable`, `:drop_vtable`

## Examples

    # Block ATTACH and DETACH (prevent cross-database reads)
    :ok = Sqlite3.set_authorizer(conn, [:attach, :detach])

    # Clear the authorizer
    :ok = Sqlite3.set_authorizer(conn, [])

# `set_busy_timeout`

```elixir
@spec set_busy_timeout(db(), integer()) :: :ok | {:error, reason()}
```

Set the busy timeout in milliseconds without destroying the custom busy handler.

Unlike `PRAGMA busy_timeout` (which internally calls `sqlite3_busy_timeout()`
and replaces any custom handler), this function only updates the timeout value
that the custom busy handler reads. This preserves the ability to cancel
busy waits via `cancel/1`.

A timeout of `0` makes lock contention fail immediately with `SQLITE_BUSY`.
Larger values let SQLite keep retrying until the timeout expires or the wait
is cancelled.

This is the low-level API behind the `:busy_timeout` connection option.

# `set_commit_hook`

```elixir
@spec set_commit_hook(db(), pid() | nil) :: :ok | {:error, reason()}
```

Send commit notifications to a process.

Each time a transaction is committed on the given connection, the atom
`:committed` is sent to the pid provided as the second argument.

Passing `nil` as the second argument unregisters the hook.

## Restrictions

  * This is a notification-only hook. SQLite allows a commit hook to veto a
    commit (turning it into a rollback), but that requires a synchronous
    answer that a NIF callback cannot request from the BEAM, so commits are
    always allowed through. Use `Exqlite.Sqlite3.transaction_status/1` or
    handle the commit's return value when you need the outcome.
  * Only one pid can receive the notifications for a given connection at a
    time. If this function is called multiple times for the same connection,
    only the last pid will receive the notifications.
  * Hooks are unregistered when the connection is closed, so closing a
    connection with an open transaction does not emit notifications for the
    rollback performed by `close/1`.

See [`sqlite3_commit_hook`](https://www.sqlite.org/c3ref/commit_hook.html)
for more details.

# `set_log_hook`

```elixir
@spec set_log_hook(pid()) :: :ok | {:error, reason()}
```

Send log messages to a process.

Each time a message is logged in SQLite a message will be sent to the pid provided as the argument.

The message is of the form: `{:log, rc, message}`, where:

  * `rc` is an integer [result code](https://www.sqlite.org/rescode.html) or an [extended result code](https://www.sqlite.org/rescode.html#extrc)
  * `message` is a string representing the log message

See [`SQLITE_CONFIG_LOG`](https://www.sqlite.org/c3ref/c_config_covering_index_scan.html) and
["The Error And Warning Log"](https://www.sqlite.org/errlog.html) for more details.

## Restrictions

  * Only one pid can listen to the log messages at a time.
    If this function is called multiple times, only the last pid will
    receive the notifications

# `set_preupdate_hook`

```elixir
@spec set_preupdate_hook(db(), pid() | nil) :: :ok | {:error, reason()}
```

Send preupdate notifications with full row data to a process.

Before each insert, update, or delete is applied on the given connection, a
message is sent to the pid provided as the second argument carrying both
the old and the new row, which the update hook cannot provide.

The message is of the form: `{action, db_name, table, row_id, old_row, new_row}`,
where:

  * `action` is one of `:insert`, `:update` or `:delete`
  * `db_name` is a string representing the database name where the change
    took place (`"main"`, `"temp"`, or the name of an attached database)
  * `table` is a string representing the table name where the change took
    place
  * `row_id` is an integer representing the row id assigned by SQLite. It
    is always `0` for `WITHOUT ROWID` tables and cannot identify a row there
  * `old_row` is the complete row before the change, or `nil` for inserts
  * `new_row` is the complete row after the change, or `nil` for deletes

Passing `nil` as the second argument unregisters the hook.

## Restrictions

  * The hook only fires for changes to real tables. Changes to virtual
    tables and to system tables such as `sqlite_sequence` or
    `sqlite_stat1` are not reported.
  * Changes performed by triggers and foreign key actions fire additional
    preupdate events for the modified tables.
  * Only one pid can receive the notifications for a given connection at a
    time. If this function is called multiple times for the same connection,
    only the last pid will receive the notifications.
  * The preupdate hook requires SQLite compiled with
    `SQLITE_ENABLE_PREUPDATE_HOOK`, which the vendored build enables. When
    Exqlite is built against a system SQLite without it
    (`EXQLITE_USE_SYSTEM=1`, unless the flag is passed via
    `EXQLITE_SYSTEM_CFLAGS`), this function returns
    `{:error, :preupdate_hook_not_supported}`.

See [`sqlite3_preupdate_hook`](https://www.sqlite.org/c3ref/preupdate_hook.html)
for more details.

# `set_progress_handler_steps`

```elixir
@spec set_progress_handler_steps(db(), integer()) :: :ok | {:error, reason()}
```

Configure how often SQLite invokes the progress handler during statement execution.

The default is `1000` virtual machine steps.

Values less than `1` disable the progress handler. Larger values reduce the
overhead of cancellation checks at the cost of slower response to `cancel/1`
and `interrupt/1` while a query is running.

This is the low-level API behind the `:progress_handler_steps` connection
option.

# `set_rollback_hook`

```elixir
@spec set_rollback_hook(db(), pid() | nil) :: :ok | {:error, reason()}
```

Send rollback notifications to a process.

Each time a transaction is rolled back on the given connection, the atom
`:rolled_back` is sent to the pid provided as the second argument.

Passing `nil` as the second argument unregisters the hook.

## Restrictions

  * The rollback performed by `close/1` (or by connection teardown) on an
    open transaction does not emit a notification, because hooks are
    unregistered before teardown begins.
  * Only one pid can receive the notifications for a given connection at a
    time. If this function is called multiple times for the same connection,
    only the last pid will receive the notifications.

See [`sqlite3_rollback_hook`](https://www.sqlite.org/c3ref/commit_hook.html)
for more details.

# `set_update_hook`

```elixir
@spec set_update_hook(db(), pid()) :: :ok | {:error, reason()}
```

Send data change notifications to a process.

Each time an insert, update, or delete is performed on the connection provided
as the first argument, a message will be sent to the pid provided as the second argument.

The message is of the form: `{action, db_name, table, row_id}`, where:

  * `action` is one of `:insert`, `:update` or `:delete`
  * `db_name` is a string representing the database name where the change took place
  * `table` is a string representing the table name where the change took place
  * `row_id` is an integer representing the unique row id assigned by SQLite

## Restrictions

  * There are some conditions where the update hook will not be invoked by SQLite.
    See the documentation for [more details](https://www.sqlite.org/c3ref/update_hook.html)
  * Only one pid can listen to the changes on a given database connection at a time.
    If this function is called multiple times for the same connection, only the last pid will
    receive the notifications
  * Updates only happen for the connection that is opened. For example, there
    are two connections A and B. When an update happens on connection B, the
    hook set for connection A will not receive the update, but the hook for
    connection B will receive the update.

# `shrink_memory`

```elixir
@spec shrink_memory(db()) :: :ok | {:error, reason()}
```

Causes the database connection to free as much memory as it can. This is
useful if you are on a memory restricted system.

# `step`

```elixir
@spec step(db(), statement()) :: :done | :busy | {:row, row()} | {:error, reason()}
```

# `transaction_status`

```elixir
@spec transaction_status(db()) :: {:ok, :idle | :transaction}
```

---

*Consult [api-reference.md](api-reference.md) for complete listing*