duckdb-simple

duckdb-simple provides a high-level Haskell interface to DuckDB inspired by the APIs of sqlite-simple and postgresql-simple. It builds on the low-level bindings exposed by duckdb-ffi and provides a focused API for opening connections, running queries, binding parameters, and decoding typed results—including the full set of DuckDB scalar types (signed/unsigned integers, decimals, hugeints, intervals, precise and timezone-aware temporals, blobs, enums, bit strings, and bignums).

Getting Started

{-# LANGUAGE OverloadedStrings #-}

import Database.DuckDB.Simple
import Database.DuckDB.Simple.Types (Only (..))

main :: IO ()
main =
  withConnection ":memory:" \conn -> do
    _ <- execute_ conn "CREATE TABLE items (id INTEGER, name TEXT)"
    _ <- execute conn "INSERT INTO items VALUES (?, ?)" (1 :: Int, "banana" :: String)
    rows <- query_ conn "SELECT id, name FROM items ORDER BY id"
    mapM_ print (rows :: [(Int, String)])

Key Modules

• Database.DuckDB.Simple – connections, prepared statements, execution, queries, metadata, and error handling.
• Database.DuckDB.Simple.ToField / ToRow – typeclasses and helpers for preparing positional or named parameters.
• Database.DuckDB.Simple.FromField / FromRow – typeclasses for decoding query results, with generic deriving support for product types.
• Database.DuckDB.Simple.Generic – automatic encoding/decoding of Haskell ADTs as DuckDB STRUCTs and UNIONs via GHC generics and the ViaDuckDB deriving-via helper.
• Database.DuckDB.Simple.LogicalRep – structured value types (StructValue, UnionValue) for working with DuckDB's composite types.
• Database.DuckDB.Simple.Types – shared types (Query, Null, Only, (:.), SQLError).
• Database.DuckDB.Simple.Function – register scalar Haskell functions that can be invoked directly from SQL.

Querying Data

import Database.DuckDB.Simple
import Database.DuckDB.Simple.Types (Only (..))

fetchNames :: Connection -> IO [Maybe String]
fetchNames conn = do
  _ <- execute_ conn "CREATE TABLE names (value TEXT)"
  _ <- executeMany conn "INSERT INTO names VALUES (?)"
    [Only (Just "Alice"), Only (Nothing :: Maybe String)]
  fmap fromOnly <$> query_ conn "SELECT value FROM names ORDER BY value IS NULL, value"

The execution helpers return the number of affected rows (Int) so callers can assert on data changes when needed.

Named Parameters

duckdb-simple supports both positional (?) and named parameters. Named parameters are bound with the (:=) helper exported from Database.DuckDB.Simple.ToField.

import Database.DuckDB.Simple
import Database.DuckDB.Simple.ToField (NamedParam ((:=)))

insertNamed :: Connection -> IO Int
insertNamed conn =
  executeNamed conn
    "INSERT INTO events VALUES ($kind, $payload)"
    ["$kind" := ("metric" :: String), "$payload" := ("ok" :: String)]

DuckDB does not allow mixing positional and named placeholders within the same SQL statement; the library preserves DuckDB’s error message in that situation. DuckDB does not support savepoints. This library does not provide withSavepoint.

If the number of supplied parameters does not match the statement’s declared placeholders—or if you attempt to bind named arguments to a positional-only statement—duckdb-simple raises a FormatError before executing the query.

Decoding rows

FromRow is powered by a RowParser, which means instances can be written in a monadic/Applicative style and even derived generically for product types:

{-# LANGUAGE DeriveAnyClass #-}
{-# LANGUAGE DeriveGeneric #-}

import Database.DuckDB.Simple
import GHC.Generics (Generic)

data Person = Person
  { personId :: Int
  , personName :: Text
  }
  deriving stock (Show, Generic)
  deriving anyclass (FromRow)

fetchPeople :: Connection -> IO [Person]
fetchPeople conn = query_ conn "SELECT id, name FROM person ORDER BY id"

Helper combinators such as field, fieldWith, and numFieldsRemaining are available when a custom instance needs fine-grained control.

Generic Encoding with ViaDuckDB

The Database.DuckDB.Simple.Generic module provides automatic encoding and decoding of Haskell algebraic data types as DuckDB STRUCTs and UNIONs via GHC generics.

Product Types as STRUCTs

Product types (records) are automatically encoded as DuckDB STRUCT values:

{-# LANGUAGE DeriveGeneric #-}
{-# LANGUAGE DerivingVia #-}

import Data.Int (Int64)
import Data.Text (Text)
import Database.DuckDB.Simple
import Database.DuckDB.Simple.Generic (ViaDuckDB (..))
import GHC.Generics (Generic)

data User = User
  { userId :: Int64
  , userName :: Text
  }
  deriving stock (Eq, Show, Generic)
  deriving (DuckDBColumnType, ToField, FromField) via (ViaDuckDB User)

-- Round-trip through the database
storeAndFetchUser :: Connection -> User -> IO [User]
storeAndFetchUser conn user = do
  _ <- execute_ conn "CREATE TABLE users (data STRUCT(userId BIGINT, userName TEXT))"
  _ <- execute conn "INSERT INTO users VALUES (?)" (Only user)
  fmap fromOnly <$> query_ conn "SELECT data FROM users"

Sum Types as UNIONs

Sum types are encoded as DuckDB UNION values, with each constructor becoming a union member:

data Shape
  = Circle Double
  | Rectangle Double Double
  | Point
  deriving stock (Eq, Show, Generic)
  deriving (DuckDBColumnType, ToField, FromField) via (ViaDuckDB Shape)

-- Store and retrieve shape data
storeShape :: Connection -> Shape -> IO [Shape]
storeShape conn shape = do
  _ <- execute_ conn
    "CREATE TABLE shapes (s UNION(Circle STRUCT(field1 DOUBLE), \
    \Rectangle STRUCT(field1 DOUBLE, field2 DOUBLE), Point STRUCT()))"
  _ <- execute conn "INSERT INTO shapes VALUES (?)" (Only shape)
  fmap fromOnly <$> query_ conn "SELECT s FROM shapes"

Nullary constructors (like Point) are encoded with a null payload. Non-record constructors use positional field names (field1, field2, etc.).

Arrays and Lists

DuckDB arrays (fixed-length) and lists (variable-length) are also supported:

import Data.Array (Array, listArray)

storeArray :: Connection -> IO [Array Int Int]
storeArray conn = do
  _ <- execute_ conn "CREATE TABLE arrays (vals INTEGER[3])"
  let arr = listArray (0, 2) [1, 2, 3]
  _ <- execute conn "INSERT INTO arrays VALUES (?)" (Only arr)
  fmap fromOnly <$> query_ conn "SELECT vals FROM arrays"

storeList :: Connection -> IO [[Int]]
storeList conn = do
  _ <- execute_ conn "CREATE TABLE lists (vals INTEGER[])"
  _ <- execute conn "INSERT INTO lists VALUES (?)" (Only [1, 2, 3])
  fmap fromOnly <$> query_ conn "SELECT vals FROM lists"

Infinite dates and timestamps

Use Database.DuckDB.Simple.Time when a column can contain temporal infinity. Its Date, LocalTimestamp, and UTCTimestamp types wrap Day, LocalTime, and UTCTime in Unbounded: NegInfinity, Finite value, or PosInfinity. These types support parameters, results, and fields in generic composites. UTCTimestamp binds as TIMESTAMPTZ.

import Database.DuckDB.Simple.Time

infiniteDates :: Connection -> IO [Only Date]
infiniteDates conn = query conn "SELECT ?::DATE" (Only (PosInfinity :: Date))

The ordinary Day, LocalTime, and UTCTime instances reject infinity with a conversion error. Use Maybe Date to distinguish SQL NULL from infinity. Floating-point NaN and infinities remain valid Float and Double values.

Manual STRUCT and UNION Handling

Temporal fields retain their SQL units when composite values are rebound. The FieldDate, FieldTimestamp, and FieldTimestampTZ constructors hold Unbounded values. Wrap finite payloads in Finite when constructing them. For TIMESTAMP_S or TIMESTAMP_MS values outside the TIMESTAMP range, use an explicit parameter cast, such as SELECT ?::STRUCT(value TIMESTAMP_S). DuckDB otherwise attempts to convert these parameters to microseconds.

For more control, you can work directly with StructValue and UnionValue from Database.DuckDB.Simple.LogicalRep:

import Database.DuckDB.Simple.LogicalRep (StructValue (..), UnionValue (..))
import Database.DuckDB.Simple.FromField (FieldValue (..))

manualStruct :: Connection -> IO [(StructValue FieldValue, UnionValue FieldValue)]
manualStruct conn = do
  _ <- execute_ conn
    "CREATE TABLE composite (s STRUCT(a INT, b INT), \
    \u UNION(x INT, y VARCHAR))"
  [(s, u)] <- query_ conn
    "SELECT {'a': 1, 'b': 2}, \
    \CAST(union_value(x := 42) AS UNION(x INT, y VARCHAR))"
  _ <- execute conn "INSERT INTO composite VALUES (?, ?)" (s, u)
  query_ conn "SELECT s, u FROM composite"

Resource Management

• withConnection and withStatement wrap the open/close lifecycle and guard against exceptions; use them whenever possible to avoid leaking C handles.
• All intermediate DuckDB objects (results, prepared statements, values) are released immediately after use. Query helpers return a Haskell list of all rows. Folds and cursors decode rows incrementally, while DuckDB retains the materialized native result until it is exhausted, reset, or closed.
• execute/query variants reset statement bindings each run so prepared statements can be reused safely.

For concurrent workers, use a separate connection per worker. A connection can move between threads or be shared when the application serializes access. Hold that lock for the whole transaction or cursor lifetime, including close. DuckDB serializes native query calls, but this does not protect the Haskell handle state or prevent another call from interfering with an active cursor. Statements and connections do not provide their own lock. A callback must not execute another query on its active connection or close that connection.

Link your executable with ghc-options: -threaded to allow prompt cancellation of native queries, including Ctrl-C. On cancellation, the library interrupts DuckDB and waits for the native call to return before it releases resources and propagates the exception. Cancellation is cooperative: native code and Haskell callbacks must return before cleanup can finish.

Close or reset an abandoned statement to release its result. DuckDB can retain native result buffers until that result is destroyed. Use withStatement for manual iteration. fold releases the result after success or an exception. The accumulator determines Haskell memory use.

Metadata helpers

• columnCount and columnName expose prepared-statement metadata so you can inspect result shapes before executing a query.
• execute returns the number of affected rows. Use SQL RETURNING clauses when you need generated identifiers.

Cursors and folds

fold, fold_, and foldNamed decode one row at a time from DuckDB's result chunks. DuckDB 1.5 materializes the native result before the first row is returned. These functions avoid a complete Haskell row list, but native memory use still depends on the result size. The native API for starting a streaming result is deprecated; the default interface uses the supported execution API.

import Database.DuckDB.Simple.Types (Only (..))

sumValues :: Connection -> IO Int
sumValues conn =
  fold_ conn "SELECT n FROM stream_fold ORDER BY n" 0 $ \acc (Only n) ->
    pure (acc + n)

For manual cursor-style iteration, use nextRow/nextRowWith on an open Statement to pull rows one at a time and decide when to stop.

Cursors support the same column types as eager queries, including STRUCT and UNION values with nested collections and NULLs.

Optional native streaming

Database.DuckDB.Simple.Deprecated.Streaming provides fold, fold_, foldNamed, nextRow, and nextRowWith with native streaming enabled. Import it qualified:

import qualified Database.DuckDB.Simple.Deprecated.Streaming as Streaming

streamSum :: Connection -> IO Int
streamSum conn =
  Streaming.fold_ conn "SELECT i FROM range(1000000) t(i)" 0 $ \acc (Only n) ->
    pure (acc + n)

The import emits a deprecation warning because DuckDB has deprecated the execution entry point. DuckDB can still materialize some queries. Streaming does not bound the memory used by query operators.

The first cursor fetch selects the execution mode until an explicit reset. Switching between default and streaming nextRow calls retains that mode. Keep the connection dedicated to the active stream; another query on that connection can invalidate it. Cancellation interrupts native chunk fetching as well as execution.

This module also provides foldArrow and foldArrow_ for streaming Arrow batches. They use the same supported Arrow conversion and scoped ownership as Database.DuckDB.Simple.Arrow.

Arrow batches

Database.DuckDB.Simple.Arrow provides foldArrow and foldArrow_ for clients that consume the Arrow C Data Interface. Each callback receives a separate schema and array batch. It can read them or pass them to an Arrow consumer that releases or moves them. The fold releases any remaining contents on success, failure, or cancellation. The consumer owns any contents it moves.

The original pointers are valid only during the callback. To retain contents, a consumer must move the root structs into its own storage and set the source release fields to NULL. Moved contents remain valid after the query and connection close. Empty results do not produce a callback.

For example, the dataframe-arrow-bridge package can copy each batch into a Haskell DataFrame and release the Arrow objects:

import qualified DataFrame.IO.Arrow as DataFrame
import qualified Database.DuckDB.Simple.Arrow as Arrow
import Foreign.Ptr (castPtr)

frames <- Arrow.foldArrow_ conn "SELECT id::BIGINT, name::VARCHAR FROM people" [] $ \acc schema array -> do
  frame <- DataFrame.arrowToDataframe (castPtr schema) (castPtr array)
  pure (frame : acc)
-- Reverse frames to recover the query's batch order.

The bridge currently imports signed 32-bit and 64-bit integers, Float, Double, and text columns. It is a test dependency of this repository; applications that use it must declare their own dependency on dataframe-arrow-bridge.

Arrow export uses DuckDB's schema and chunk conversion API. DuckDB materializes the native result before callbacks start, so its memory use depends on the result size. The older Arrow query and scan bindings remain available through Database.DuckDB.FFI.Deprecated and emit deprecation warnings.

Feature Coverage

• Connections, prepared statements, positional/named parameter binding.
• High-level execution (execute*) and eager queries (query*, queryNamed).
• Cursor and fold helpers (fold, foldNamed, fold_, nextRow) that decode native result chunks one row at a time.
• Comprehensive scalar type support: signed/unsigned integers, HUGEINT/UHUGEINT, decimals (with width/scale), intervals, precise and timezone-aware temporals, enums, bit strings, blobs, bignums, and UUIDs.
• Composite types: STRUCTs, UNIONs, LISTs, fixed-length ARRAYs, and MAPs with full encoding/decoding support.
• Generic encoding/decoding: automatic STRUCT/UNION mapping for Haskell ADTs via GHC generics and the ViaDuckDB deriving-via helper.
• Row decoding via FromField/FromRow, with generic deriving for product types.
• User-defined scalar functions backed by Haskell functions (including IO and nullable arguments).
• Transaction helpers (withTransaction) and metadata accessors (columnCount, columnName).

User-Defined Functions

Scalar Haskell functions can be registered with DuckDB connections and used in SQL expressions. Argument and result types reuse the existing FromField and FunctionResult machinery, so Maybe values and IO actions work out of the box.

import Data.Int (Int64)
import Database.DuckDB.Simple
import Database.DuckDB.Simple.Function (createFunction, deleteFunction)
import Database.DuckDB.Simple.Types (Only (..))

registerAndUse :: Connection -> IO [Only Int64]
registerAndUse conn = do
  createFunction conn "hs_times_two" (\(x :: Int64) -> x * 2)
  result <- query_ conn "SELECT hs_times_two(21)" :: IO [Only Int64]
  deleteFunction conn "hs_times_two"
  pure result

Exceptions raised while the function executes are propagated back to DuckDB as SQLError values, and deleteFunction issues a DROP FUNCTION IF EXISTS statement to remove the registration. DuckDB registers C API scalar functions as internal entries; attempting to drop them this way will yield an error, which the library surfaces as an SQLError.

Tests

The test suite is built with tasty and covers connection management, statement lifecycle, parameter binding, and query execution.

cabal test duckdb-simple-test --test-show-details=direct