DuckdbEx.Connection (DuckdbEx v0.2.0)

View Source

DuckDB connection management.

This module provides the primary interface to DuckDB databases, mirroring the functionality of the Python DuckDBPyConnection class.

Reference: duckdb-python/src/duckdb_py/include/duckdb_python/pyconnection/pyconnection.hpp

Overview

A connection represents an active session with a DuckDB database using the DuckDB CLI managed through erlexec. Connections can be:

  • In-memory (:memory:)
  • Persistent (file path)
  • Read-only or read-write

Examples

# Connect to an in-memory database
{:ok, conn} = DuckdbEx.Connection.connect(:memory)

# Execute a query
{:ok, result} = DuckdbEx.Connection.execute(conn, "SELECT 1")

# Close connection
DuckdbEx.Connection.close(conn)

Summary

Types

t()

Functions

begin(conn)

Begins a transaction.

checkpoint(conn)

Creates a checkpoint.

close(conn)

Closes the database connection.

commit(conn)

Commits the current transaction.

connect(database \\ :memory, opts \\ [])

Opens a connection to a DuckDB database.

cursor(conn)

Creates a duplicate of the current connection.

description(conn)

Returns the description of the last executed result set.

duplicate(conn)
execute(conn, sql_or_statement, params \\ [])

Executes a SQL query.

execute_result(conn, sql_or_statement, params \\ [])
executemany(conn, sql_or_statement, params_list \\ [])

Executes a SQL query multiple times with different parameter sets.

extract_statements(sql)

Extracts statements from a SQL string.

extract_statements(conn, sql)
fetch_all(conn)

Fetches all rows from a query result.

fetch_all(conn, sql)
fetch_many(conn, count \\ 1)

Fetches multiple rows from the last result.

fetch_many(conn, sql, count)
fetch_one(conn)

Fetches one row from a query result.

fetch_one(conn, sql)
fetchall(conn)
fetchmany(conn, count \\ 1)
fetchone(conn)
query(conn, sql, relation_alias \\ "", params \\ [])

Creates a relation from a SQL query with an optional alias.

read_csv(conn, path_or_paths, opts \\ [])

Creates a relation from a CSV file or list of files.

read_json(conn, path_or_paths, opts \\ [])

Creates a relation from a JSON file or list of files.

read_parquet(conn, path_or_paths, opts \\ [])

Creates a relation from a Parquet file or list of files.

rollback(conn)

Rolls back the current transaction.

rowcount(conn)

Returns the rowcount for the last result set.

sql(conn, sql, params \\ [])

Creates a relation from a SQL query.

table(conn, table_name)

Creates a relation from a table or view name.

transaction(conn, fun)

Executes a function within a managed transaction.

values(conn, values)

Creates a relation from values.

view(conn, view_name)

Creates a relation from a view name.

Types

t()

@type t() :: DuckdbEx.Port.t() | DuckdbEx.Cursor.t()

Functions

begin(conn)

@spec begin(t()) :: {:ok, term()} | {:error, term()}

Begins a transaction.

Starts a new transaction on the connection. All subsequent queries will be executed within the transaction context until commit or rollback is called.

Parameters

  • conn - The connection

Returns

  • {:ok, result} - Transaction started successfully
  • {:error, exception} - Failed to start transaction

Examples

{:ok, conn} = DuckdbEx.Connection.connect(:memory)
{:ok, _} = DuckdbEx.Connection.begin(conn)
{:ok, _} = DuckdbEx.Connection.execute(conn, "INSERT INTO users VALUES (1, 'Alice')")
{:ok, _} = DuckdbEx.Connection.commit(conn)

Reference: DuckDBPyConnection.begin() in Python

checkpoint(conn)

@spec checkpoint(t()) :: {:ok, term()} | {:error, term()}

Creates a checkpoint.

Forces a checkpoint of the write-ahead log (WAL) to the database file. This ensures all changes are persisted to disk.

Parameters

  • conn - The connection

Returns

  • {:ok, result} - Checkpoint created successfully
  • {:error, exception} - Failed to create checkpoint

Examples

{:ok, _} = DuckdbEx.Connection.checkpoint(conn)

Reference: DuckDBPyConnection.checkpoint() in Python

close(conn)

@spec close(t()) :: :ok

Closes the database connection.

After closing, the connection should not be used for any operations.

Parameters

  • conn - The connection to close

Returns

  • :ok

Examples

{:ok, conn} = DuckdbEx.Connection.connect(:memory)
:ok = DuckdbEx.Connection.close(conn)

Reference: DuckDBPyConnection.close() in Python

commit(conn)

@spec commit(t()) :: {:ok, term()} | {:error, term()}

Commits the current transaction.

Commits all changes made within the current transaction, making them permanent.

Parameters

  • conn - The connection

Returns

  • {:ok, result} - Transaction committed successfully
  • {:error, exception} - Failed to commit transaction

Examples

{:ok, _} = DuckdbEx.Connection.begin(conn)
{:ok, _} = DuckdbEx.Connection.execute(conn, "INSERT INTO users VALUES (1, 'Alice')")
{:ok, _} = DuckdbEx.Connection.commit(conn)

Reference: DuckDBPyConnection.commit() in Python

connect(database \\ :memory, opts \\ [])

@spec connect(
  String.t() | :memory | :default,
  keyword()
) :: {:ok, t()} | {:error, term()}

Opens a connection to a DuckDB database.

Parameters

  • database - Database path or :memory: for in-memory database
  • opts - Connection options (keyword list)
    • :read_only - Open in read-only mode (default: false)
    • :config - Database configuration map (for future use)

Returns

  • {:ok, conn} - Successfully opened connection
  • {:error, exception} - Connection failed

Examples

{:ok, conn} = DuckdbEx.Connection.connect(:memory)
{:ok, conn} = DuckdbEx.Connection.connect("/path/to/db.duckdb")
{:ok, conn} = DuckdbEx.Connection.connect(:memory, read_only: true)

Reference: duckdb.connect() in Python

cursor(conn)

@spec cursor(t()) :: {:ok, t()} | {:error, term()}

Creates a duplicate of the current connection.

description(conn)

@spec description(t()) :: {:ok, [tuple()] | nil} | {:error, term()}

Returns the description of the last executed result set.

duplicate(conn)

@spec duplicate(t()) :: {:ok, t()} | {:error, term()}

execute(conn, sql_or_statement, params \\ [])

@spec execute(t(), String.t() | DuckdbEx.Statement.t(), list() | map()) ::
  {:ok, t()} | {:error, term()}

Executes a SQL query.

Parameters

  • conn - The connection
  • sql - SQL query string
  • params - Query parameters (not yet implemented)

Returns

  • {:ok, result} - Query executed successfully
  • {:error, exception} - Query failed

Examples

{:ok, result} = DuckdbEx.Connection.execute(conn, "SELECT 1")

Reference: DuckDBPyConnection.execute() in Python

execute_result(conn, sql_or_statement, params \\ [])

@spec execute_result(t(), String.t() | DuckdbEx.Statement.t(), list() | map()) ::
  {:ok, map()} | {:error, term()}

executemany(conn, sql_or_statement, params_list \\ [])

@spec executemany(t(), String.t() | DuckdbEx.Statement.t(), list()) ::
  {:ok, t()} | {:error, term()}

Executes a SQL query multiple times with different parameter sets.

extract_statements(sql)

@spec extract_statements(String.t()) ::
  {:ok, [DuckdbEx.Statement.t()]} | {:error, term()}

Extracts statements from a SQL string.

extract_statements(conn, sql)

@spec extract_statements(t(), String.t()) ::
  {:ok, [DuckdbEx.Statement.t()]} | {:error, term()}

fetch_all(conn)

@spec fetch_all(t()) :: {:ok, [tuple()]} | {:error, term()}

Fetches all rows from a query result.

This is a convenience function that executes a query and returns all rows.

Parameters

  • conn - The connection
  • sql - SQL query string

Returns

  • {:ok, rows} - List of row tuples
  • {:error, exception} - Query failed

Examples

{:ok, rows} = DuckdbEx.Connection.fetch_all(conn, "SELECT * FROM users")

Reference: DuckDBPyConnection.execute().fetchall() in Python

fetch_all(conn, sql)

@spec fetch_all(t(), String.t()) :: {:ok, [tuple()]} | {:error, term()}

fetch_many(conn, count \\ 1)

@spec fetch_many(t(), non_neg_integer()) :: {:ok, [tuple()]} | {:error, term()}

Fetches multiple rows from the last result.

Parameters

  • conn - The connection
  • count - Number of rows to fetch (default: 1)

fetch_many(conn, sql, count)

@spec fetch_many(t(), String.t(), non_neg_integer()) ::
  {:ok, [tuple()]} | {:error, term()}

fetch_one(conn)

@spec fetch_one(t()) :: {:ok, tuple() | nil} | {:error, term()}

Fetches one row from a query result.

This is a convenience function that executes a query and returns the first row.

Parameters

  • conn - The connection
  • sql - SQL query string

Returns

  • {:ok, row} - Row tuple or nil
  • {:error, exception} - Query failed

Examples

{:ok, row} = DuckdbEx.Connection.fetch_one(conn, "SELECT * FROM users LIMIT 1")

Reference: DuckDBPyConnection.execute().fetchone() in Python

fetch_one(conn, sql)

@spec fetch_one(t(), String.t()) :: {:ok, tuple() | nil} | {:error, term()}

fetchall(conn)

fetchmany(conn, count \\ 1)

fetchone(conn)

query(conn, sql, relation_alias \\ "", params \\ [])

@spec query(t(), String.t(), String.t(), list() | map()) :: DuckdbEx.Relation.t()

Creates a relation from a SQL query with an optional alias.

read_csv(conn, path_or_paths, opts \\ [])

@spec read_csv(t(), String.t() | list(), keyword() | map()) :: DuckdbEx.Relation.t()

Creates a relation from a CSV file or list of files.

read_json(conn, path_or_paths, opts \\ [])

@spec read_json(t(), String.t() | list(), keyword() | map()) :: DuckdbEx.Relation.t()

Creates a relation from a JSON file or list of files.

read_parquet(conn, path_or_paths, opts \\ [])

@spec read_parquet(t(), String.t() | list(), keyword() | map()) ::
  DuckdbEx.Relation.t()

Creates a relation from a Parquet file or list of files.

rollback(conn)

@spec rollback(t()) :: {:ok, term()} | {:error, term()}

Rolls back the current transaction.

Reverts all changes made within the current transaction.

Parameters

  • conn - The connection

Returns

  • {:ok, result} - Transaction rolled back successfully
  • {:error, exception} - Failed to rollback transaction

Examples

{:ok, _} = DuckdbEx.Connection.begin(conn)
{:ok, _} = DuckdbEx.Connection.execute(conn, "INSERT INTO users VALUES (1, 'Alice')")
{:ok, _} = DuckdbEx.Connection.rollback(conn)

Reference: DuckDBPyConnection.rollback() in Python

rowcount(conn)

@spec rowcount(t()) :: integer()

Returns the rowcount for the last result set.

sql(conn, sql, params \\ [])

@spec sql(t(), String.t(), list() | map()) :: DuckdbEx.Relation.t()

Creates a relation from a SQL query.

Returns a lazy relation that can be composed with other operations before execution. The SQL is not executed until a fetch operation is called.

Parameters

  • conn - The connection
  • sql - SQL query string

Returns

A %DuckdbEx.Relation{} struct

Examples

# Create relation (not executed yet)
relation = DuckdbEx.Connection.sql(conn, "SELECT * FROM users")

# Chain operations
result = relation
|> DuckdbEx.Relation.filter("age > 25")
|> DuckdbEx.Relation.fetch_all()

Reference: DuckDBPyConnection.sql() in Python

table(conn, table_name)

@spec table(t(), String.t()) :: DuckdbEx.Relation.t()

Creates a relation from a table or view name.

Returns a lazy relation representing the entire table or view. The table is not queried until a fetch operation is called.

Parameters

  • conn - The connection
  • table_name - Name of the table or view

Returns

A %DuckdbEx.Relation{} struct

Examples

# Create relation from table
relation = DuckdbEx.Connection.table(conn, "users")

# Chain operations
active_users = relation
|> DuckdbEx.Relation.filter("status = 'active'")
|> DuckdbEx.Relation.fetch_all()

Reference: DuckDBPyConnection.table() in Python

transaction(conn, fun)

@spec transaction(t(), (t() -> term())) :: {:ok, term()} | {:error, term()}

Executes a function within a managed transaction.

This is the recommended way to use transactions. The function is executed within a transaction context. If the function completes successfully, the transaction is committed. If an exception is raised or an error occurs, the transaction is automatically rolled back.

Parameters

  • conn - The connection
  • fun - A function that takes the connection as an argument

Returns

  • {:ok, result} - Transaction completed successfully, returns the function's result
  • {:error, exception} - Transaction failed or was rolled back

Examples

# Successful transaction
{:ok, result} = DuckdbEx.Connection.transaction(conn, fn conn ->
  {:ok, _} = DuckdbEx.Connection.execute(conn, "INSERT INTO users VALUES (1, 'Alice')")
  {:ok, _} = DuckdbEx.Connection.execute(conn, "INSERT INTO users VALUES (2, 'Bob')")
  :success
end)

# Transaction with automatic rollback on error
{:error, _} = DuckdbEx.Connection.transaction(conn, fn conn ->
  {:ok, _} = DuckdbEx.Connection.execute(conn, "INSERT INTO users VALUES (1, 'Alice')")
  raise "Something went wrong!"
end)

Reference: Similar to Python context manager pattern with DuckDB transactions

values(conn, values)

@spec values(t(), term()) :: DuckdbEx.Relation.t()

Creates a relation from values.

A list of values is treated as a single row, while a list of tuples/lists is treated as multiple rows.

view(conn, view_name)

@spec view(t(), String.t()) :: DuckdbEx.Relation.t()

Creates a relation from a view name.