EctoLibSql.Pragma (EctoLibSql v0.8.8)

Helper functions for executing SQLite PRAGMA statements.

PRAGMA statements are SQLite's configuration mechanism. This module provides convenient wrapper functions for common PRAGMA operations.

Common Use Cases

Enable foreign key constraints
Set journal mode (WAL for better concurrency)
Configure synchronisation level
Query database configuration

Examples

# Enable foreign keys
{:ok, state} = EctoLibSql.connect(database: "app.db")
:ok = EctoLibSql.Pragma.enable_foreign_keys(state)

# Set WAL mode
{:ok, _result} = EctoLibSql.Pragma.set_journal_mode(state, :wal)

# Check current foreign keys setting
{:ok, result} = EctoLibSql.Pragma.foreign_keys(state)
# result.rows => [[1]] if enabled, [[0]] if disabled

Integration with Ecto

PRAGMA statements are often executed during repository initialisation:

# In your Repo module
def init(_type, config) do
  {:ok, Keyword.put(config, :after_connect, &set_pragmas/1)}
end

defp set_pragmas(conn) do
  with {:ok, state} <- DBConnection.get_connection_state(conn),
       :ok <- EctoLibSql.Pragma.enable_foreign_keys(state),
       {:ok, _} <- EctoLibSql.Pragma.set_journal_mode(state, :wal) do
    :ok
  end
end

Summary

Functions

disable_foreign_keys(state)

Disable foreign key constraints.

enable_foreign_keys(state)

Enable foreign key constraints.

foreign_keys(state)

Query the current foreign keys setting.

journal_mode(state)

Query the current journal mode.

query(state, pragma_stmt)

Execute a raw PRAGMA statement.

set_journal_mode(state, mode)

Set the journal mode.

set_synchronous(state, level)

Set the synchronous mode.

set_user_version(state, version)

Set the user version number.

synchronous(state)

Query the current synchronous setting.

table_info(state, table_name)

Get information about a table's columns.

table_list(state)

List all tables in the database.

user_version(state)

Get the user version number.

Functions

disable_foreign_keys(state)

Disable foreign key constraints.

Parameters

state: Connection state

Returns

:ok on success
{:error, reason} on failure

Examples

:ok = EctoLibSql.Pragma.disable_foreign_keys(state)

enable_foreign_keys(state)

@spec enable_foreign_keys(EctoLibSql.State.t()) :: :ok | {:error, term()}

Enable foreign key constraints.

By default, SQLite does not enforce foreign key constraints. This function enables them for the current connection.

Parameters

state: Connection state

Returns

:ok on success
{:error, reason} on failure

Examples

:ok = EctoLibSql.Pragma.enable_foreign_keys(state)

Notes

This setting is per-connection and must be set each time you connect. Consider setting it in your Repo's after_connect callback.

foreign_keys(state)

Query the current foreign keys setting.

Parameters

state: Connection state

Returns

{:ok, result} where result.rows is [[1]] if enabled, [[0]] if disabled
{:error, reason} on failure

Examples

{:ok, result} = EctoLibSql.Pragma.foreign_keys(state)
enabled? = result.rows == [[1]]

journal_mode(state)

Query the current journal mode.

Parameters

state: Connection state

Returns

{:ok, result} where result.rows contains the current mode
{:error, reason} on failure

Examples

{:ok, result} = EctoLibSql.Pragma.journal_mode(state)
# result.rows => [["wal"]] or [["delete"]], etc.

query(state, pragma_stmt)

@spec query(EctoLibSql.State.t(), String.t()) ::
  {:ok, EctoLibSql.Result.t()} | {:error, term()}

Execute a raw PRAGMA statement.

This is the low-level function that all other PRAGMA helpers use.

Parameters

state: Connection state
pragma_stmt: The complete PRAGMA statement (e.g., "PRAGMA foreign_keys = ON")

Returns

{:ok, result} with query result
{:error, reason} on failure

Examples

{:ok, result} = EctoLibSql.Pragma.query(state, "PRAGMA foreign_keys = ON")
{:ok, result} = EctoLibSql.Pragma.query(state, "PRAGMA table_info(users)")

set_journal_mode(state, mode)

Set the journal mode.

SQLite supports several journal modes:

:delete - Default mode, deletes journal file after transaction
:wal - Write-Ahead Logging, better for concurrent access
:memory - Keep journal in memory
:persist - Keep journal file but zero it out
:truncate - Truncate journal file instead of deleting
:off - No journal (dangerous, not recommended)

Parameters

state: Connection state
mode: One of :delete, :wal, :memory, :persist, :truncate, :off

Returns

{:ok, result} with the new journal mode
{:error, reason} on failure

Examples

{:ok, result} = EctoLibSql.Pragma.set_journal_mode(state, :wal)
# result.rows => [["wal"]]

Recommendations

For applications with concurrent reads/writes, use :wal mode:

EctoLibSql.Pragma.set_journal_mode(state, :wal)

set_synchronous(state, level)

Set the synchronous mode.

Controls how often SQLite syncs data to disk:

:off (0) - No syncing (fastest, risk of corruption)
:normal (1) - Sync at critical moments (good balance)
:full (2) - Sync after every write (safest, slowest)
:extra (3) - Even more syncing than FULL

Parameters

state: Connection state
level: One of :off, :normal, :full, :extra, or integer 0-3

Returns

{:ok, result} on success
{:error, reason} on failure

Examples

{:ok, _} = EctoLibSql.Pragma.set_synchronous(state, :normal)

Recommendations

Production: :normal or :full (with WAL mode, :normal is usually sufficient)
Development: :normal
Never use :off in production

set_user_version(state, version)

Set the user version number.

Parameters

state: Connection state
version: Integer version number

Returns

{:ok, result} on success
{:error, reason} on failure

Examples

{:ok, _} = EctoLibSql.Pragma.set_user_version(state, 42)

synchronous(state)

Query the current synchronous setting.

Parameters

state: Connection state

Returns

{:ok, result} where result.rows contains the current level (0-3)
{:error, reason} on failure

Examples

{:ok, result} = EctoLibSql.Pragma.synchronous(state)
# result.rows => [[2]] for FULL, [[1]] for NORMAL, etc.

table_info(state, table_name)

Get information about a table's columns.

This is useful for introspection and debugging.

Parameters

state: Connection state
table_name: Name of the table (string or atom)

Returns

{:ok, result} with column information
{:error, reason} on failure

Examples

{:ok, result} = EctoLibSql.Pragma.table_info(state, :users)
# result.rows => [
#   [0, "id", "INTEGER", 0, nil, 1],
#   [1, "name", "TEXT", 1, nil, 0],
#   ...
# ]

Each row contains: [cid, name, type, notnull, dflt_value, pk]

table_list(state)

List all tables in the database.

Parameters

state: Connection state

Returns

{:ok, result} with table names
{:error, reason} on failure

Examples

{:ok, result} = EctoLibSql.Pragma.table_list(state)
# result.rows => [["users"], ["posts"], ...]

user_version(state)

Get the user version number.

SQLite databases can store a user version number (typically used for schema versioning).

Parameters

state: Connection state

Returns

{:ok, result} where result.rows contains the version number
{:error, reason} on failure

Examples

{:ok, result} = EctoLibSql.Pragma.user_version(state)
# result.rows => [[42]]