ducky - v0.4.0

ducky

Native DuckDB driver for Gleam.

Quick Start

import ducky
import gleam/int
import gleam/io
import gleam/result

pub fn main() {
  use conn <- ducky.with_connection(":memory:")

  // Create our duck pond
  use _ <- result.try(ducky.exec(conn, "
    CREATE TABLE ducks (name TEXT, quack_volume INT, is_rubber BOOLEAN)
  "))
  use _ <- result.try(ducky.exec(conn, "
    INSERT INTO ducks VALUES
      ('Sir Quacksalot', 95, false),
      ('Duck Norris', 100, false),
      ('Mallard Fillmore', 72, false),
      ('Squeaky', 0, true)
  "))

  // Find the loudest quacker
  use result <- result.map(ducky.query(conn, "
    SELECT name, quack_volume FROM ducks
    WHERE is_rubber = false
    ORDER BY quack_volume DESC LIMIT 1
  "))

  case result.rows {
    [ducky.Row([ducky.Text(name), ducky.Integer(volume)])] ->
      io.println(name <> " wins at " <> int.to_string(volume) <> " decibels!")
    _ -> io.println("The pond is empty...")
  }
}
// => Duck Norris wins at 100 decibels!

Types

Connection

opaque </>

An opaque connection to a DuckDB database.

pub opaque type Connection

DataFrame

</>

A complete query result with column metadata.

pub type DataFrame {
  DataFrame(columns: List(String), rows: List(Row))
}

Constructors 

  • DataFrame(columns: List(String), rows: List(Row))

Error

</>

Errors that can occur during DuckDB operations.

pub type Error {
  ConnectionFailed(reason: String)
  QuerySyntaxError(message: String)
  UnsupportedParameterType(type_name: String)
  StatementFinalized
  DatabaseError(message: String)
}

Constructors 

  • ConnectionFailed(reason: String)

    Connection to database failed.

  • QuerySyntaxError(message: String)

    SQL query has syntax errors.

  • UnsupportedParameterType(type_name: String)

    Unsupported parameter type in query.

  • StatementFinalized

    Statement has been finalized and cannot be used.

  • DatabaseError(message: String)

    Generic error from DuckDB.

Row

</>

A single row from a query result.

pub type Row {
  Row(values: List(Value))
}

Constructors 

  • Row(values: List(Value))

Statement

opaque </>

An opaque prepared statement for repeated execution.

Prepared statements allow you to compile a SQL query once and execute it multiple times with different parameters, avoiding repeated parsing overhead.

pub opaque type Statement

Value

</>

A value from a DuckDB result set.

pub type Value {
  Null
  Boolean(Bool)
  TinyInt(Int)
  SmallInt(Int)
  Integer(Int)
  BigInt(Int)
  Float(Float)
  Double(Float)
  Decimal(String)
  Text(String)
  Blob(BitArray)
  Timestamp(Int)
  Date(Int)
  Time(Int)
  Interval(months: Int, days: Int, nanos: Int)
  List(List(Value))
  Array(List(Value))
  Map(dict.Dict(String, Value))
  Struct(dict.Dict(String, Value))
  Union(tag: String, value: Value)
}

Constructors 

  • Null
  • Boolean(Bool)
  • TinyInt(Int)
  • SmallInt(Int)
  • Integer(Int)
  • BigInt(Int)
  • Float(Float)
  • Double(Float)
  • Decimal(String)
  • Text(String)
  • Blob(BitArray)
  • Timestamp(Int)
  • Date(Int)
  • Time(Int)
  • Interval(months: Int, days: Int, nanos: Int)
  • List(List(Value))
  • Array(List(Value))
  • Map(dict.Dict(String, Value))
  • Struct(dict.Dict(String, Value))
  • Union(tag: String, value: Value)

Values

append_rows

</> 
pub fn append_rows(
  conn: Connection,
  table: String,
  rows: List(List(Value)),
) -> Result(Int, Error)

Bulk-appends rows via DuckDB’s appender API. Bypasses SQL parsing.

Atomic: all rows succeed or none are committed on error. The table name is resolved by catalog lookup, not SQL interpolation. Empty rows return Ok(0) without a NIF call.

Examples

let assert Ok(count) = append_rows(conn, "users", [
  [int(1), text("Alice")],
  [int(2), text("Bob")],
  [int(3), text("Charlie")],
])
// count == 3

blob

</> 
pub fn blob(value: BitArray) -> Value

Creates a blob parameter value.

bool

</> 
pub fn bool(value: Bool) -> Value

Creates a boolean parameter value.

close

</> 
pub fn close(conn: Connection) -> Result(Nil, Error)

Closes a database connection.

Examples

let assert Ok(conn) = connect(":memory:")
let assert Ok(_) = close(conn)

connect

</> 
pub fn connect(path: String) -> Result(Connection, Error)

Opens a connection to a DuckDB database.

Must call close() when done. Use with_connection() instead for automatic cleanup.

Examples

connect(":memory:")
// => Ok(Connection(...))

connect("data.duckdb")
// => Ok(Connection(...))

date

</> 
pub fn date(days: Int) -> Value

Creates a date parameter value (days since Unix epoch).

decimal

</> 
pub fn decimal(value: String) -> Value

Creates a decimal parameter value from a string representation.

exec

</> 
pub fn exec(conn: Connection, sql: String) -> Result(Nil, Error)

Executes a SQL statement that returns no rows.

Use for DDL and DML statements (CREATE, INSERT, UPDATE, DELETE, etc.). For statements that return rows, use query or query_params.

Examples

exec(conn, "CREATE TABLE users (id INT, name VARCHAR)")
// => Ok(Nil)

exec(conn, "INSERT INTO users VALUES (1, 'Alice')")
// => Ok(Nil)

execute

</> 
pub fn execute(
  stmt: Statement,
  params: List(Value),
) -> Result(DataFrame, Error)

Executes a prepared statement with parameters.

Returns a DataFrame with the query results, or an empty DataFrame for DDL/DML statements.

Examples

let assert Ok(stmt) = prepare(conn, "SELECT * FROM users WHERE age > ?")
let assert Ok(result) = execute(stmt, [int(18)])
// result.rows contains matching users

field

</> 
pub fn field(value: Value, name: String) -> option.Option(Value)

Get a field value from a struct by field name.

Returns None if the value is not a Struct or the field does not exist.

Examples

let person = Struct(dict.from_list([#("name", Text("Alice")), #("age", Integer(30))]))
field(person, "name")
// => Some(Text("Alice"))

field(person, "unknown")
// => None

finalize

</> 
pub fn finalize(stmt: Statement) -> Result(Nil, Error)

Finalizes a prepared statement, releasing its resources.

After finalization, the statement cannot be used again. For automatic cleanup, prefer with_statement.

Examples

let assert Ok(stmt) = prepare(conn, "SELECT 1")
// ... use the statement ...
let assert Ok(_) = finalize(stmt)

float

</> 
pub fn float(value: Float) -> Value

Creates a float parameter value.

get

</> 
pub fn get(row: Row, index: Int) -> option.Option(Value)

Get a value from a row by column index.

Examples

let row = Row([Integer(1), Text("Alice")])
get(row, 0)
// => Some(Integer(1))

get(row, 5)
// => None

int

</> 
pub fn int(value: Int) -> Value

Creates an integer parameter value.

interval

</> 
pub fn interval(
  months months: Int,
  days days: Int,
  nanos nanos: Int,
) -> Value

Creates an interval parameter value.

null

</> 
pub fn null() -> Value

Creates a null parameter value.

nullable

</> 
pub fn nullable(
  inner: fn(a) -> Value,
  value: option.Option(a),
) -> Value

Creates a nullable parameter value.

Examples

nullable(int, Some(42))
// => Integer(42)

nullable(int, None)
// => Null

path

</> 
pub fn path(conn: Connection) -> String

Returns the database path for a connection.

prepare

</> 
pub fn prepare(
  conn: Connection,
  sql: String,
) -> Result(Statement, Error)

Prepares a SQL statement for repeated execution.

Validates the SQL syntax immediately and returns a statement handle. Use execute to run the statement with parameters.

Examples

let assert Ok(stmt) = prepare(conn, "INSERT INTO users (name, age) VALUES (?, ?)")
let assert Ok(_) = execute(stmt, [text("Alice"), int(30)])
let assert Ok(_) = execute(stmt, [text("Bob"), int(25)])
let assert Ok(_) = finalize(stmt)

Performance

DuckDB caches parsed query plans internally, so repeated executions with different parameters benefit from the cached plan. This can provide speedups for bulk operations.

query

</> 
pub fn query(
  conn: Connection,
  sql: String,
) -> Result(DataFrame, Error)

Executes a SQL query and returns structured results.

The query runs on a dirty scheduler to avoid blocking the BEAM. Results are loaded into memory. For large datasets, use LIMIT/OFFSET or filter in SQL to reduce memory usage.

Examples

query(conn, "SELECT id, name FROM users WHERE active = true")
// => Ok(DataFrame(columns: ["id", "name"], rows: [...]))

// For large datasets, paginate:
query(conn, "SELECT * FROM users LIMIT 1000 OFFSET 0")

query_params

</> 
pub fn query_params(
  conn: Connection,
  sql: String,
  params: List(Value),
) -> Result(DataFrame, Error)

Executes a parameterized SQL query with bound parameters to prevent SQL injection.

Examples

query_params(conn, "SELECT * FROM users WHERE id = ? AND age > ?", [
  int(42),
  int(18),
])
// => Ok(DataFrame(...))

Security

Always use this function when including user input in queries:

// UNSAFE - SQL injection risk
query(conn, "SELECT * FROM users WHERE name = '" <> user_input <> "'")

// SAFE - parameters are properly escaped
query_params(conn, "SELECT * FROM users WHERE name = ?", [text(user_input)])

text

</> 
pub fn text(value: String) -> Value

Creates a text parameter value.

time

</> 
pub fn time(micros: Int) -> Value

Creates a time parameter value (microseconds since midnight).

timestamp

</> 
pub fn timestamp(micros: Int) -> Value

Creates a timestamp parameter value (microseconds since Unix epoch).

transaction

</> 
pub fn transaction(
  conn: Connection,
  callback: fn(Connection) -> Result(a, Error),
) -> Result(a, Error)

Executes operations within a transaction.

Commits on success, rolls back on error.

Examples

transaction(conn, fn(conn) {
  use _ <- result.try(query(conn, "UPDATE accounts ..."))
  query(conn, "SELECT * FROM accounts")
})

with_connection

</> 
pub fn with_connection(
  db_path: String,
  callback: fn(Connection) -> Result(a, Error),
) -> Result(a, Error)

Executes operations with automatic connection cleanup.

Connection closes automatically on success or error.

Examples

use conn <- with_connection(":memory:")
query(conn, "SELECT 42")

with_statement

</> 
pub fn with_statement(
  conn: Connection,
  sql: String,
  callback: fn(Statement) -> Result(a, Error),
) -> Result(a, Error)

Executes operations with a prepared statement, ensuring cleanup.

The statement is automatically finalized when the callback returns, regardless of success or failure.

Examples

use stmt <- with_statement(conn, "INSERT INTO users (name) VALUES (?)")
list.try_each(names, fn(name) {
  execute(stmt, [text(name)])
  |> result.map(fn(_) { Nil })
})
✨ Search Document