duckdb-simple: High-level DuckDB interface inspired by sqlite-simple and postgresql-simple

[ database, library, mpl ] [ Propose Tags ] [ Report a vulnerability ]

A high-level DuckDB interface with typed parameters and results, prepared statements, chunked folds, transactions, and Haskell scalar functions. The API follows the style of sqlite-simple and postgresql-simple.

The backend uses the generated V2 API from duckdb-ffi. Upstream marks that API as unstable, so this release requires exactly DuckDB 2.0.0. It provides shared environments and instances, function registration, native streaming, and Arrow batches.


[Skip to Readme]

Flags

Manual Flags

NameDescriptionDefault
dataframe-tests

Test Arrow export with the dataframe library.

Disabled
extension

Build loadable extensions with private C callback helpers.

Disabled
quack-tests

Test authenticated local Quack connections with supplied extensions.

Disabled

Use -f <flag> to enable a flag, or -f -<flag> to disable that flag. More info

Downloads

Maintainer's Corner

Package maintainers

For package maintainers and hackage trustees

Candidates

Versions [RSS] 0.1.0.0, 0.1.1.0, 0.1.1.1, 0.1.1.2, 0.1.2.0, 0.1.2.1, 0.1.2.2, 0.1.2.3, 0.1.2.4, 0.1.5.0, 0.1.5.1, 0.1.5.2, 0.2.0.0, 0.3.0.0, 1.0.0.0
Change log CHANGELOG.md
Dependencies array (>=0.5 && <0.6), base (>=4.18 && <5), bytestring (>=0.11 && <0.13), containers (>=0.6 && <0.9), duckdb-ffi (>=2.0.0.0 && <2.0.1), geometry-simple (>=0.1.1.0 && <0.2), hs-bindgen-runtime (>=1.0.0.0 && <1.1), text (>=2.0 && <2.2), time (>=1.12 && <1.16), transformers (>=0.6 && <0.7), uuid (>=1.3 && <1.4) [details]
Tested with ghc ==9.10.3, ghc ==9.12.4, ghc ==9.14.1, ghc ==9.6.7, ghc ==9.8.4
License MPL-2.0
Author Matthias Pall Gissurarson
Maintainer mpg@mpg.is
Uploaded by tritlo at 2026-11-18T22:25:49Z
Category Database
Home page https://github.com/Tritlo/duckdb-haskell
Bug tracker https://github.com/Tritlo/duckdb-haskell/issues
Source repo head: git clone https://github.com/Tritlo/duckdb-haskell.git(duckdb-simple)
Distributions
Reverse Dependencies 1 direct, 0 indirect [details]
Downloads 530 total (58 in the last 30 days)
Rating (no votes yet) [estimated by Bayesian average]
Your Rating
  • λ
  • λ
  • λ
Status Docs uploaded by user
Build status unknown [no reports yet]

Readme for duckdb-simple-1.0.0.0

[back to package description]

duckdb-simple

duckdb-simple is a Haskell interface to DuckDB. Its API is like the APIs of sqlite-simple and postgresql-simple. It uses the duckdb-ffi bindings. It opens connections, runs queries, binds parameters, and decodes results into Haskell types. It supports DuckDB scalar types, for example signed and unsigned integers, decimals, HUGEINT and UHUGEINT, intervals, dates, times, and timestamps of each precision and with a time zone, blobs, enums, bit strings, and bignums.

The backend uses the generated DuckDB V2 API. Upstream marks the complete V2 API as unstable: "The C API v2 is not yet stable. Its interface may change in upcoming releases." This release therefore requires exactly DuckDB 2.0.0. Creating an environment raises SQLError for another library version.

The examples use the OverloadedStrings extension for Query and Text literals.

Shared environments and instances

Use an explicit instance when several connections must share catalogs and configuration. Each connection has at most one open native result. Use a separate connection for each concurrent worker. Wait for child creation and all worker operations to finish before calling closeInstance or closeEnvironment. Closing a parent also closes its children.

withEnvironment $ \environment ->
  withInstance environment [("threads", "2")] $ \instance_ -> do
    attachDatabase instance_ ":memory:" "shared" []
    setDefaultDatabase instance_ "shared"
    withConnectionOn instance_ $ \writer -> do
      execute_ writer "CREATE TABLE items(id BIGINT)"
      execute writer "INSERT INTO items VALUES (?)" (Only (42 :: Int))
    withConnectionOn instance_ $ \reader ->
      query_ reader "SELECT id FROM items" :: IO [Only Int]

Closing an environment closes its instances, connections, and statements. DuckDB can keep an instance alive that createInstance did not create. In that case, closeEnvironment raises an SQLError and keeps the environment open. Closing an instance closes its connections and statements. Each close operation is idempotent. open and withConnection continue to create private parents.

V2 facilities

  • Config lists options with effective values, typed defaults, aliases, and supported scopes. It reads and writes typed values and registers custom options. The update function of a custom option gets the new value as a FieldValue. V2.Config registers custom options with native builders.
  • Catalog.describeTable copies resolved names, column types, default flags, generated-column flags, and catalog read-only status. A column type keeps the alias of DuckDB at each depth as a LogicalTypeAlias, for example JSON and the name of a type from registerCustomType. A column of a type from CREATE TYPE name AS base has the base type, and an ENUM from CREATE TYPE has no name.
  • V2.LogicalRep.logicalTypeToRep factory logical copies a native logical type into a pure LogicalTypeRep. Keep the factory valid throughout the conversion. The function does not destroy the logical type handle.
  • Tokenizer.tokenize returns token kinds, UTF-8 byte offsets, token bytes, and the native indication of an unterminated final token.
  • Function registers scalar, aggregate, and table functions with Haskell values. Registration registers custom types and replacement scans, and has the type and parameter declarations. See Typed registration.
  • V2.Registration supports scalar, aggregate, table, cast, and custom-type registration, replacement scans, function properties, and parameter kinds, names, and defaults. Haskell callbacks use typed V2 info handles. The library retains their function pointers and contains callback exceptions. The advanced extension builders accept a borrowed native extension handle and context. Scalar type resolution runs before binding. Table callbacks can report multi-file column identifiers and metadata after binding.
  • Copy registers COPY formats and multi-file table functions with Haskell values. V2.Copy has the native COPY callbacks, with batch transfer, flush, statistics, progress, and multi-file setup.
  • FileSystem supports sequential and positioned reads and writes, file size, position, seek, sync, path tests, expansion, globbing, movement, and removal.
  • Logging registers native log sinks with managed write and flush callbacks. It copies timestamps, severity, scope, and available context identifiers. Callbacks can run concurrently. They must not log or change logging settings. V2.Logging writes a message through the context of a native callback.
  • Pragma registers query and execution callbacks. A callback gets the arguments of the call as a FromRow row. V2.Pragma has the callbacks that get native handles.
  • Extension has the typed registrations for a loadable extension. It also registers scalar and table macros during extension initialization. The native macro builder has no connection target; use SQL CREATE MACRO on a connection. V2.Registration.registerMacroFunctionWith gives the native macro builder to a configuration action.
  • V2.FunctionDocs sets descriptions, examples, and categories on function builders.
  • QueryMetrics enables profiling and copies query and operator metric trees.
  • Internal.withCollectionStatement binds native column collections as named table inputs for one scoped prepared-statement action. The caller retains the collections.

The pinned V2 API does not expose custom file-system registration or collation lookup. DuckDB 2.0 rejects an ARRAY type with zero elements. An empty LIST is valid. The direct query_ and execute_ paths support statements that expand during parsing, including dynamic PIVOT. Prepared dynamic PIVOT supports repeated execution with a changing schema. It does not support parameters or a cacheable plan. Read its schema after execution starts. VARIANT decoding uses SQL metadata and native casts on a helper connection. An unknown custom GEOMETRY CRS label uses a helper SQL query when a parameter needs its type. GEOMETRY results use a native cast to BLOB. Native GEOMETRY collections require the same Z/M dimensions on every member, including empty members. The standalone WKB codec retains mixed layouts.

Getting started

{-# LANGUAGE BlockArguments #-}
{-# LANGUAGE OverloadedStrings #-}

import Database.DuckDB.Simple

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.Conversion: explicit IO and conversion errors for custom decoders. runConversion returns IO (Ok a).
  • 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, TupleValue, 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 SQL can call, and casts, aggregate functions, and table functions.
  • Database.DuckDB.Simple.Registration, Copy, Pragma, and Config: register custom types, replacement scans, COPY formats, pragmas, and custom options with Haskell values.

Types lists the SQL type of each Haskell type. Errors describes the exceptions that a program can catch.

Querying data

import Database.DuckDB.Simple

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). Callers can use this count to check data changes.

An Integer or a Natural parameter binds as a HUGEINT, a UHUGEINT, or a BIGNUM value, or raises an error, depending on its size and target type: note 1 of Types gives the rule.

In DuckDB 2.0.0 the casts from BIGNUM to the integer types have defects: some fail, and some store a wrong value with no error (issue 41). A SizedArray or a list of Integer or Natural values takes the element type of its target when that type is an integer type or DECIMAL, so DuckDB checks each element. A BigNum value, and a SizedArray or a list of BigNum values, still have these defects for an integer target. For each other target, and for a parameter with no known type, a SizedArray, a list, or a Map of Integer or Natural values takes the type that its elements give, as a scalar Integer and a SQL list literal of the HUGEINT and BIGNUM values do: [1, 2^64] binds as HUGEINT[], and [1, 2^200] as BIGNUM[]. An empty list and a list of only NULL elements get the type "NULL"[], as [] and [NULL] in SQL, because an Integer has no fixed SQL type. The rule follows the column type name BIGNUM, so an all-NULL list of BigNum values with no target also gets "NULL"[].

An IntervalValue binds as an INTERVAL, and DuckDB keeps its months, days, and microseconds. DuckDB compares intervals with 30 days for each month, so INTERVAL 30 DAY = INTERVAL 1 MONTH. A TimeWithZone binds as a TIMETZ with its time and its offset in seconds east of UTC (timeWithZoneOffsetSeconds). timeWithZone makes a value from a Data.Time TimeZone, and timeWithZoneZone gives the zone of a value, with the seconds of its offset dropped. DuckDB stores an offset from -15:59:59 to +15:59:59, that is from -57599 to 57599 seconds, and a value reads back equal to the bound value. A time with digits below one microsecond loses these digits, as in SQL text. A DecimalValue binds as a DECIMAL with its own width and scale. DuckDB casts it to the type of its target: a smaller scale rounds half away from zero, and a value that the target cannot hold raises an error. A SizedArray, a list, or a typed row (a row type with TypedRow) of DecimalValue does not compile: note 14 of Types.

query_ and execute_ parse all input statements before execution. A syntax error prevents every statement in that input from running. An execution error can leave effects from earlier statements. Use withTransaction when those effects must roll back together.

Named parameters

duckdb-simple supports both positional (?) and named parameters. Bind a named parameter with (:=) from Database.DuckDB.Simple.

import Database.DuckDB.Simple

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

DuckDB does not allow positional and named placeholders in the same SQL statement. The library preserves DuckDB's error message in that case. DuckDB does not support savepoints. This library does not provide withSavepoint.

Parameter names use DuckDB's ASCII case rules. For example, $amount and $AMOUNT refer to the same parameter. Supplying both raises a FormatError. Non-ASCII characters retain their case. Session variables can supply omitted named parameters. DuckDB rejects an omitted parameter that has no variable value.

duckdb-simple raises a FormatError before it executes the query in these cases:

  • The number of positional parameters is not the number of placeholders.
  • You bind positional parameters to a statement with named placeholders.
  • You supply more named parameters than the statement declares.
  • You supply a name that the statement does not declare.
  • You supply one parameter two times, for example as $amount and $AMOUNT.
  • You bind named parameters to a statement with only positional placeholders.

bind and bindNamed reset the statement cursor before validation. If validation or value conversion fails, the previous set of bindings remains. A successful bindNamed replaces the explicit bindings. Current session variables can supply omitted parameters when the statement executes.

Decoding rows

The fromRow method of a FromRow instance is a RowParser. Write it with Applicative or Monad functions, or derive the instance with Generic for a product type:

{-# LANGUAGE DeriveAnyClass #-}
{-# LANGUAGE DeriveGeneric #-}
{-# LANGUAGE DerivingStrategies #-}
{-# LANGUAGE OverloadedStrings #-}

import Data.Text (Text)
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"

Use field, fieldWith, and numFieldsRemaining for more control in a custom instance.

A FromField instance reads its Haskell type's SQL type, for example BIGINT for Int64. It also reads other SQL types when it can return the value of DuckDB's cast. The "Reads" column and notes 3 to 5 of Types list these types.

Decoders return Conversion a. This follows the postgresql-simple style: a conversion can run IO and report conversion errors. This library's runConversion takes no connection argument. The query and callback APIs run conversions for you. The decoder types are:

type FieldParser a = Field -> Conversion a

fromField :: FromField a => Field -> Conversion a
parseRow :: RowParser a -> [Field] -> Conversion a
runConversion :: Conversion a -> IO (Ok a)

For example, runConversion (fromField field :: Conversion Int64) returns Ok value or Errors errors in IO. Import the runner from Database.DuckDB.Simple.Conversion and the result constructors from Database.DuckDB.Simple.Ok. Use liftConversion to run IO inside a custom decoder. IO exceptions, including cancellation, propagate to the caller. Sequential and applicative conversions stop at the first conversion failure. Alternative tries the second branch if the first fails. If both fail, it combines their diagnostics.

A decoder for a registered function's arguments runs inside its callback. Do not run another query on that callback's connection from the decoder. Use another connection. See User-defined functions for the connection rules. fieldType reads its scoped logical type without taking the connection lock.

fieldType :: Field -> Conversion LogicalTypeRep reads the SQL type of a column, or of an element, key, or value in a LIST, ARRAY, or MAP. It preserves aliases such as JSON. A query result or callback owns the native metadata. The library converts a column's type to LogicalTypeRep when a decoder requests fieldType and caches a successful result within that scope. This metadata action does no tree traversal or ENUM dictionary copy until requested. Structured value readers can also build type trees to decode their values. If cancellation interrupts a metadata read while the scope remains open, another call can retry it. The cache does not store exceptions.

Native metadata is available while a query parser runs. A cursor retains its metadata until exhaustion, reset, or statement closure. Callback metadata is available during that callback invocation. An escaped native fieldType action raises SQLError with the message duckdb-simple: field type scope is closed, including when its result was cached before closure. Read a pure LogicalTypeRep snapshot inside the scope if you need the type later. Snapshots remain usable after the statement, connection, instance, and environment close.

Metadata scopes release their native types when they close. They use no metadata finalizers. Existing statement and connection finalizers are unchanged. Field has no Eq or Show instance. A value in a VARIANT has the type VARIANT, and so does a Field made from a FieldValue alone. In those cases, the stored value determines the type. logicalTypeSQL gives the SQL type text, for example DECIMAL(18,3). A custom decoder can check the type:

newtype Cents = Cents DecimalValue

instance FromField Cents where
  fromField field = do
    logical <- fieldType field
    case logical of
      LogicalTypeDecimal _ 2 -> Cents <$> fromField field
      _ -> returnError Incompatible field "expected a DECIMAL with the scale 2"

A field of a ViaDuckDB record reads with its type when its DuckValue instance sets duckFromTypedField = typedFromField. The library's instances do this. Generic decoders have these types:

duckFromField :: DuckValue a => FieldValue -> Conversion a
duckFromTypedField :: DuckValue a => LogicalTypeRep -> FieldValue -> Conversion a
typedFromField :: FromField a => LogicalTypeRep -> FieldValue -> Conversion a
genericFromFieldValue :: (Generic a, GFromField (Rep a)) => FieldValue -> Conversion a

Encoding and logical type declarations remain pure.

Types

Each row gives a Haskell type and these facts:

  • Binds as: the SQL type of a parameter of this type.
  • Declared: the SQL type that a typed registration declares, if it differs from "Binds as". This applies to columns and parameters of createTableFunction, and arguments and results of createAggregate and createFunction. "none" means that the type has no declaration. registerCopyFromFunction declares no type. The COPY FROM table supplies the column types. The row type must have the same number of columns. DuckDB casts each row value to its column's type.
  • Reads: the SQL types that the FromField instance reads.
  • Function: the use of the type in createFunction, as an argument, a result, or both. See the paragraph after the notes.
Haskell type Binds as Declared Reads Function
Bool BOOLEAN BOOLEAN, TINYINT to BIGINT (2) argument, result
Int8, Int16, Int32, Int64, Int TINYINT, SMALLINT, INTEGER, BIGINT, BIGINT TINYINT to BIGINT, HUGEINT, UHUGEINT (3) argument, result
Word8, Word16, Word32, Word64, Word UTINYINT, USMALLINT, UINTEGER, UBIGINT, UBIGINT each integer type (3) argument, result
Integer HUGEINT (1) each integer type, BIGNUM (3) argument, result
Natural HUGEINT (1) UHUGEINT (10) each integer type, BIGNUM (3) argument, result
BigNum BIGNUM BIGNUM argument, result
Double DOUBLE DOUBLE, FLOAT, TINYINT to BIGINT, DECIMAL (4) argument, result
Float FLOAT as Double (4) argument, result
DecimalValue DECIMAL of the value none (14) DECIMAL none
Text, String VARCHAR VARCHAR, TINYINT to BIGINT, BOOLEAN, ENUM (5) argument, result
ByteString BLOB BLOB, BIT, GEOMETRY (5) argument, result
BitString BIT BIT argument, result
UUID UUID UUID, VARCHAR (5) argument, result
Day, Unbounded Day DATE (7) DATE, each timestamp type (6) argument, result
TimeOfDay TIME (7) TIME, TIME_NS, each timestamp type (6) argument, result
LocalTime, Unbounded LocalTime TIMESTAMP (7) each timestamp type (6), DATE (5) argument, result
UTCTime, Unbounded UTCTime TIMESTAMPTZ (7) TIMESTAMPTZ, TIMESTAMPTZ_NS (5) argument, result
TimeWithZone TIMETZ TIMETZ argument, result
IntervalValue INTERVAL INTERVAL argument, result
[a] LIST LIST, ARRAY argument, result
SizedArray n a ARRAY of size n (8) ARRAY, LIST (8) argument, result
Map k v MAP MAP argument, result
StructValue FieldValue STRUCT (9) none STRUCT none
TupleValue FieldValue TUPLE (9) none TUPLE none
UnionValue FieldValue UNION (9) none UNION none
ViaDuckDB a STRUCT or UNION (10) STRUCT or UNION argument, result
Maybe a as a, or NULL as a NULL, or as a as a
G.Geometry GEOMETRY without a CRS GEOMETRY argument, result
RawGeometry GEOMETRY with its CRS none (14) GEOMETRY none
Variant VARIANT VARIANT, and the values that a VARIANT can hold (13) argument, result
FieldValue the type of its constructor (11) none each type none
Null "NULL" (12) NULL argument, result (12)

Notes:

  1. A value from -2^127 to 2^127 - 1 binds as a HUGEINT. DuckDB then checks that the value fits its target: for example, a negative value for an unsigned column raises an error. A value outside that range binds as a UHUGEINT for a UHUGEINT parameter that can hold it. It binds as a BIGNUM for a parameter that has no integer or DECIMAL type. For each other integer or DECIMAL parameter, it raises an SQLError of the type ErrorTypeConversion. Bind a BigNum to bind a BIGNUM value in each case. Querying Data gives the defects of the casts from BIGNUM in DuckDB 2.0.0, and the rule for a list, a SizedArray, or a Map of Integer values.

  2. The value 0 reads as False, and each other value reads as True.

  3. "Each integer type" is TINYINT to UHUGEINT. A value that the Haskell type cannot hold raises a library conversion error: an SQLError with no type. This includes a negative value for an unsigned type or for Natural. An ENUM value does not read as an integer type. Write enum_code(c)::BIGINT in SQL for its index, or c::INTEGER for the value of its label.

  4. A read returns the value of DuckDB's c::DOUBLE or c::FLOAT cast. This includes a DECIMAL, and a BIGINT read as Float. As seen in CI, the DuckDB 2.0.0 build for macOS on Apple Silicon rounds some BIGINT values above 2^53 two times in its cast to FLOAT, and the library gives the nearest value there. A finite value that Float cannot hold raises a conversion error of the library, an SQLError with no type.

  5. A read of another SQL type returns the value of DuckDB's cast to the Haskell type's SQL type, or an error if the value does not fit. For example, Int8 reads a BIGINT that fits in a TINYINT, and Double reads a DECIMAL with the value of c::DOUBLE. Thus Text reads a BOOLEAN as true or false, and Text and String read an ENUM as its label, the text of c::VARCHAR; a FieldEnum holds the index and the label. ByteString reads a BIT or a GEOMETRY as the bytes of c::BLOB. UUID reads a VARCHAR in the form a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11, and another form that the cast reads, for example with braces, raises an error. A read that needs the TimeZone setting of the session, or another text than the cast gives, raises the error of an incompatible column. For each such read, write the SQL in the parentheses in place of the column c (note 3 gives the SQL for an ENUM read as an integer type):

    • Text from FLOAT or DOUBLE (c::VARCHAR).
    • ByteString from VARCHAR (encode(c)).
    • LocalTime from TIMESTAMPTZ or TIMESTAMPTZ_NS (c AT TIME ZONE 'UTC').
    • UTCTime from DATE, TIMESTAMP_S, TIMESTAMP_MS, or TIMESTAMP (c::TIMESTAMP AT TIME ZONE 'UTC'), and from TIMESTAMP_NS (c AT TIME ZONE 'UTC').

    The Unbounded forms of LocalTime and UTCTime follow the same rule. The Haddock of each FromField instance gives the SQL types that it reads.

  6. "Each timestamp type" is TIMESTAMP_S, TIMESTAMP_MS, TIMESTAMP, and TIMESTAMP_NS. TIMESTAMP alone is the type with microseconds. Day reads the date of a timestamp, and TimeOfDay reads its time. A nanosecond type keeps its nanoseconds. An infinite value raises a conversion error of the library, an SQLError with no type, for Day, TimeOfDay, LocalTime, and UTCTime. The Unbounded forms read it. Use Maybe Date to distinguish SQL NULL from infinity. Date, LocalTimestamp, and UTCTimestamp from Database.DuckDB.Simple.Time are the Unbounded forms.

  7. A value with digits below a microsecond keeps them for a nanosecond parameter, and as a scalar value that fits the range of the nanosecond type for a VARIANT parameter: Infinite dates and timestamps gives the rule. The range of Day is 5877642-06-25 (BC), the year -5877641 of Data.Time, to 5881580-07-10. The range of LocalTime and UTCTime is 290309-12-22 (BC) 00:00:00 to 294247-01-10 04:00:54.775806. A value outside its range raises an SQLError of the type ErrorTypeConversion. A target of the type TIMESTAMP_NS or TIMESTAMPTZ_NS has a smaller range, about the years 1677 to 2262, and a value outside it gets the same type in each position. A TimeOfDay with 60 seconds binds as the next second, as make_time of DuckDB gives it: 23:59:60 binds as 24:00:00. A later time, for example 23:59:60.5, and a field outside its own range, for example 61 minutes, raise an SQLError of the type ErrorTypeConversion. Infinite dates and timestamps gives the full rule.

  8. A DuckDB ARRAY type has a size, for example BIGINT[3]. A SizedArray n a is the Haskell type of that ARRAY: an Array Int a of exactly n values, with the bounds (0, n - 1). sizedArray makes one from an Array with any bounds and gives Nothing for an Array of another count. The value has the elements in their order, and sizedArrayToArray gives its Array. sizedArrayFromList makes one from a list, and toList gives the values. A SizedArray binds as an ARRAY of size n, and a typed registration declares that type in each position, also as a field of a record through ViaDuckDB. It reads an ARRAY and a LIST of n elements, as the cast of DuckDB to the ARRAY does. A query that reads a value of another length raises an SQLError. Its message names the column type, the SizedArray type, and the length. In a typed registration, DuckDB rejects an ARRAY of another size when it binds the call. It casts a LIST of the same length to the ARRAY with no error, by its own implicit cast, and a LIST of another length raises an SQLError when the statement runs. Use one type for each need:

    • An ARRAY of a fixed size: a SizedArray n a.
    • An ARRAY of a size that varies: a list. A list reads an ARRAY of each size, and DuckDB casts a bound list to an ARRAY column of the same length.
    • An ARRAY value that the program builds at run time: a FieldArray.

    A DuckDB ARRAY has 1 to 100000 elements. A SizedArray or a FieldArray parameter with no element or with more than 100000 elements raises an SQLError of the type ErrorInputInvalid, and a typed registration raises the same error for a SizedArray of such a size. Bind an empty list for an empty LIST.

  9. A STRUCT, TUPLE, or UNION parameter binds with the types of its own fields or members. These types keep an alias, so a value that a query reads from a STRUCT(k JSON) column binds as STRUCT(k JSON). These values have no static field types, so a typed registration with such a type raises an error. Use a record with ViaDuckDB.

  10. A record binds as a STRUCT, and a sum type binds as a UNION. A typed registration declares the STRUCT or UNION type of a record or a sum type that derives DuckDBColumnType and ToDuckValue via ViaDuckDB, also in a list or a MAP. Each field type comes from DuckValue. An Integer declares HUGEINT and a Natural declares UHUGEINT, as a field, a column of a typed row, an argument, and a result. A value that this type cannot hold raises an SQLError of the type ErrorTypeConversion: use BigNum, which declares BIGNUM, for a larger value. DuckDB computes with these types: HUGEINT arithmetic stays HUGEINT and raises an error on overflow. DuckDB casts a non-negative integer literal to a UHUGEINT argument, but no signed column or typed value without an explicit cast. sum() over a UHUGEINT column gives a DOUBLE value, and so do UHUGEINT + INTEGER and HUGEINT + UHUGEINT for columns and typed values. A SizedArray field declares the ARRAY of its size (see note 8). A duckLogicalType that gives a LogicalTypeAlias, for example LogicalTypeAlias "JSON" (LogicalTypeScalar LogicalTypeIdVarchar), declares that alias type in a typed registration and in a parameter, and a value of the field reads as its base type. See Generic Encoding with ViaDuckDB.

  11. Each constructor binds as its SQL type, with these exceptions:

    • A FieldEnum binds its label as a VARCHAR.
    • A FieldTimestamp binds as TIMESTAMP, also when it came from another timestamp type. Infinite dates and timestamps gives the rule for digits below a microsecond.
    • An empty FieldArray raises an SQLError of the type ErrorInputInvalid, because a DuckDB ARRAY has at least one element.
    • An empty FieldList or FieldMap, and a FieldList, a FieldArray, or a FieldMap with only FieldNull elements, bind as the SQL list literal does: they take the element type of a parameter with a known type. For a parameter with no known type, [] and [NULL, NULL] have the type "NULL"[], and MAP {} has the type MAP("NULL", "NULL").

    See FieldValue parameters.

  12. typeof gives "NULL", with the quotes, for a Null parameter and for a Null declaration. A Null argument takes only a NULL value. A call with another value is a Binder Error. A record cannot have a Null field.

  13. A Variant reads a value that a VARIANT can hold: a VARIANT column, or a column of another type with the same shape. That is a value of each scalar type other than ENUM, for example an INTEGER or a GEOMETRY with no CRS, a LIST of such values, and a STRUCT in the form of a VARIANT object: each field of the type VARIANT, the keys in ascending order. At each depth, it refuses an ENUM, an ARRAY, a MAP, a TUPLE, a UNION, another STRUCT, and a GEOMETRY with a CRS, with the error of an incompatible column, because the cast of DuckDB to VARIANT changes these values. Write c::VARIANT in SQL to read such a column. It also refuses a type that is or contains JSON, at each depth, because DuckDB casts JSON to VARIANT by parsing the text: the error says DuckDB casts JSON to VARIANT by parsing the text; write c::VARIANT, and c::VARIANT gives the parsed value.

  14. DecimalValue and RawGeometry have no DuckDBColumnType and no ToDuckValue instance, so a SizedArray, a list, or a typed row of either does not compile: GHC reports No instance for 'DuckDBColumnType DecimalValue' (or RawGeometry), or the same for ToDuckValue in a typed row. One column type cannot give the width and scale of each DecimalValue. A declared GEOMETRY type has no CRS, so it would remove the CRS of each RawGeometry. A composite parameter holds a RawGeometry as a FieldGeometry.

A value of a VARIANT column reads as its stored value, so the row of the stored type applies. A value in a LIST, ARRAY, MAP, STRUCT, or UNION reads with the same rules, and so does a field of a ViaDuckDB record. A SQL type that is not in "Reads" raises the error of an incompatible column. See Errors.

A function registered with createFunction or createAggregate declares the "Declared" type, or the "Binds as" type if "Declared" is empty, for each argument and its result. An argument type needs FromField and a declaration. A result type needs ToDuckValue and a declaration. Some result types write each value directly into the data of the result vector: Bool (BOOLEAN), an integer of 8 to 64 bits (TINYINT to BIGINT, UTINYINT to UBIGINT), Float (FLOAT), Double (DOUBLE), Text and String (VARCHAR), Maybe of one of them, and a newtype that derives FunctionResult from one of them (deriving newtype (FunctionResult)). Each other result type, for example a record or a list, requires a native value for each row. DuckDB uses its default cast to convert that value to the declared type. For example, it rounds a DECIMAL to the declared scale and raises an error if the value does not fit.

An aggregate result encoder receives an instance factory. This factory resolves built-in types. DuckDB resolves the result declaration on the registration host, so a custom encoder can return the underlying value of a catalog type. See User-Defined Functions.

Errors

A program can catch these exceptions:

  • SQLError: an error of DuckDB, an input check of the library, a failed internal check of the library, or a result value that does not convert.
  • FormatError: parameters that do not agree with the placeholders of the statement.
  • The exception of a cancellation, for example from timeout.

A failed check of the library state, or of a vector, type, or value from DuckDB, raises an SQLError of the type ErrorRuntimeInternal. Such an error indicates a defect in the library or DuckDB, not an input error. Report it. Inside a callback, the statement that runs the callback fails with the type ErrorInputInvalid and the text Invalid Input Error: duckdb-simple: ..., because a callback does not pass the type ErrorRuntimeInternal; a cast gives ErrorTypeMismatch (see the errors of a callback below). Using a closed environment, instance, connection, or statement raises an SQLError with no type. A DuckDB version mismatch and reading callback state with the wrong type also raise untyped SQLErrors.

SQLError has four fields:

  • sqlErrorMessage is the text of the error. For an error of DuckDB, it is the text of DuckDB, for example Binder Error: ....
  • sqlErrorType is the error category, as Just an ErrorType. For a DuckDB error, it is DuckDB's error code. The library assigns a type to an input check, a lock wait, or a failed internal check. ErrorType has one constructor for each error code of the V2 C API, for example ErrorQueryBinder and ErrorTypeConversion.
  • sqlErrorQuery is the SQL text of the statement for an error during its preparation or execution, and for a result value that does not convert. It is Nothing for an error outside a statement, for example when a connection opens. An error of a parameter value can also have Nothing.
  • sqlErrorBody is DuckDB's error text without the type prefix. It is Nothing for a library error.

An input check of the library raises an SQLError. A check for a value outside the range of its SQL type gives the type ErrorTypeConversion, the type of the Conversion Error that DuckDB gives the same value in SQL: an Integer that its parameter or its declared type cannot hold, a date or a timestamp outside the range of DATE or TIMESTAMP of DuckDB (note 7), a nanosecond timestamp outside its storage, a time of day that is not a TIME value, a TIMETZ offset outside -57599 to 57599 seconds, an Integer or Natural field of a record outside HUGEINT or UHUGEINT, a DecimalValue that its width cannot hold, and an ENUM label that is not in the dictionary of a declared ENUM type. Each other input check gives the type ErrorInputInvalid. This includes a value of the wrong shape or type, also where DuckDB gives another type for the same input, for example a UNION tag that is not a member, a leaf built from another type, a STRUCT field that does not match its type, or a blank GEOMETRY CRS label. Examples are a VARIANT object with two equal keys, a registration with an argument type that the library cannot read, and a DECIMAL width above 38.

A statement in a callback that waits for a connection lock longer than connectionCallbackLockWait raises an SQLError of the type ErrorResourceInUse (User-Defined Functions gives the rule). A statement on a connection that streams a result, for example in the step of a fold of Database.DuckDB.Simple.Streaming, also gives ErrorResourceInUse, and so does a close of an environment while an instance that createInstance did not create is alive.

A result value that its Haskell type cannot read raises an SQLError with no type. The error includes the query. Its message describes the first field that fails:

  • The error of an incompatible column, for a SQL type that the instance does not read: duckdb-simple: column NAME has type TYPE but expected HASKELLTYPE. The message can end with : and a detail, for example : expected NULL for Null. TYPE is the type of the column in the text of DuckDB, as typeof gives it, for example DECIMAL(18,2), TIMESTAMP_NS, TIMESTAMP WITH TIME ZONE, ENUM('a', 'b'), INTEGER[], or JSON. logicalTypeSQL gives that text. For a VARIANT column, TYPE is the type of the stored value in the same text, for example DOUBLE or TIMESTAMP WITH TIME ZONE. A value that does not hold its full type, for example a LIST or a STRUCT in a VARIANT, gives the name of its kind: LIST or STRUCT. The library reads the diagnostic type only when the conversion's failure is selected. HASKELLTYPE is the type of the instance that reads, for example Text for String.
  • duckdb-simple: column NAME is NULL but expected HASKELLTYPE for NULL in a type without Maybe.
  • duckdb-simple: field FIELD: and the error of the value for a field of a ViaDuckDB record, for example duckdb-simple: field pairValue: a value has type DOUBLE but expected Int64.
  • A conversion error of the library, for a value that the instance cannot convert: the message of the instance, for example integer value out of bounds.

The library creates a ResultError for each failure and puts its text in the SQLError. It does not raise the ResultError. A custom FromField instance reports a failure with returnError and a constructor of ResultError. returnError defers its metadata read until the final conversion fails. If an Alternative branch succeeds, the library discards the other branch's error without reading its diagnostic metadata. A completed ResultError contains pure data. Its rendering and comparison perform no native IO. query and query_ raise the error and give no rows. A fold raises it at the row that fails. A row type with more or fewer fields than the result has columns raises an SQLError with the text column index N out of bounds.

An exception in a registered Haskell function, for example one registered with createFunction, fails the statement that runs the function. That statement raises an SQLError. An SQLError with a type keeps its type, and its text gets one prefix. For example, a function that runs a failing query on another connection fails the outer statement with ErrorTypeConversion and the text Conversion Error: ... of the inner query, and a check of the library in a result, for example a time of day after 24:00:00, keeps its type ErrorTypeConversion. A failed cast is the exception: it gives ErrorTypeMismatch (see below). Each other exception, an SQLError with no type, and an SQLError of one of the 16 types below give ErrorInputInvalid and the text Invalid Input Error: followed by the message of the exception: the message of an SQLError, the text of a userError, or the displayException text of another exception. An argument that does not convert gives an SQLError of the type ErrorInputInvalid with a text that names the argument, for example duckdb-simple: argument arg0 is NULL, but Int64 cannot hold NULL for a scalar function. For an aggregate or a table function, the text names the function after the prefix, for example duckdb-simple: tf_int: argument arg0 .... DuckDB ignores an exception of a log sink.

These 16 types do not pass through a callback:

  • ErrorQueryBinder, ErrorDatabaseCatalog, ErrorResourceConnection, ErrorQueryParameterNotAllowed, ErrorQueryParser, ErrorConfigurationPermission, and ErrorResourceInUse. DuckDB expects these types before the execution of a statement. Under the DuckDB setting SYNTACTIC_ERRORS_DO_NOT_INVALIDATE, it does not invalidate an explicit transaction after them, and a callback raises during the execution.
  • ErrorRuntimeInternal and ErrorRuntimeFatal. DuckDB can invalidate the database after them.
  • ErrorApi, ErrorIOFileNotFound, ErrorIOReadFailure, ErrorIOEof, ErrorInputParameterInvalid, ErrorInputMetadataDeferred, and ErrorIONetwork. DuckDB 2.0.0 cannot raise them again with their own type.

When an error of a callback fails a statement in an explicit transaction, DuckDB aborts that transaction, also under the setting SYNTACTIC_ERRORS_DO_NOT_INVALIDATE: User-Defined Functions gives what this means for a COMMIT.

An exception in the callback of registerCast in Database.DuckDB.Simple.V2.Registration fails the cast with an SQLError of the type ErrorTypeMismatch, also an SQLError with another type. Its message is Mismatch Type Error: and the text of the exception. When DuckDB casts a constant through the cast while it binds the query, for example the INTEGER literal of range(10) with a cast from INTEGER to BIGINT, the error has the type ErrorInputInvalid and the text Failed to cast value: before the text of the exception, and the database stays usable. The type ErrorTypeMismatch is the same for each pair of types, because of a property of DuckDB 2.0.0. DuckDB plans casts between some pairs of types as casts that cannot fail, including registered casts. Examples are casts to VARCHAR, including from an ENUM, a LIST, or a STRUCT, and a widening cast such as INTEGER to BIGINT or FLOAT to DOUBLE. For such a cast, DuckDB changes an error of the type ErrorInputInvalid, ErrorInputOutOfRange, or ErrorTypeConversion to an INTERNAL error. It does the same for an error type that it does not map, for example ErrorApi. An INTERNAL error during the execution of a query invalidates the database. Then each statement on each connection of the database fails until a new instance opens the database. An error of the type ErrorTypeMismatch keeps the database usable, and it aborts an explicit transaction. In TRY_CAST, DuckDB discards the error and keeps the rows that the callback wrote, so the callback must write each row, for example NULL for a failed row. TRY(CAST(...)) raises the error, because TRY catches only the types ErrorInputInvalid, ErrorInputOutOfRange, and ErrorTypeConversion. DuckDB still plans such a cast as one that cannot fail, so it can run the cast for a row that another condition of the query excludes, for example in WHERE c < 2500 AND CAST(c AS VARCHAR) <> 'x'. In DuckDB 2.0.0, only a guard in a CASE expression, for example CASE WHEN d <> 0 THEN CAST(c AS VARCHAR) <> 'x' ELSE false END, kept the cast from the excluded rows. A cast that a program registers with the native API of duckdb-ffi also gets the INTERNAL error for such a pair and such an error type. A V2 cast can report the code DUCKDB_V2_ERROR_TYPE_MISMATCH in their place.

An asynchronous exception, for example from timeout or Ctrl-C, cancels a running query. The library interrupts DuckDB, waits until the native call returns, and raises the same exception again. Catch that exception, not SQLError. See Resource Management for -threaded.

FormatError has the fields formatErrorMessage, formatErrorQuery, and formatErrorParams (the parameter values as text). The library raises it before it executes the statement, in the cases that Named Parameters lists. A FormatError is not an SQLError, so catch it separately.

This function gives Nothing when the table items has no column value:

import Control.Exception (throwIO, try)
import Database.DuckDB.Simple

countValues :: Connection -> IO (Maybe Int)
countValues conn = do
  result <- try (query_ conn "SELECT count(value) FROM items")
  case result of
    Right [Only n] -> pure (Just n)
    Right _ -> pure Nothing
    Left err
      | sqlErrorType err == Just ErrorQueryBinder -> pure Nothing
      | otherwise -> throwIO err

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

The generic encoder converts product types (records) to 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

The generic encoder converts sum types to DuckDB UNION values. Each constructor becomes 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"

A constructor without fields (like Point) has an empty STRUCT as its payload. DuckDB shows this payload as {}, and it is not NULL. Non-record constructors use positional field names (field1, field2, etc.).

Arrays and lists

The library supports fixed-length DuckDB arrays and variable-length lists:

{-# LANGUAGE DataKinds #-}

import Control.Monad (forM_)
import Data.Array (listArray)

storeArray :: Connection -> IO [SizedArray 3 Int]
storeArray conn = do
  _ <- execute_ conn "CREATE TABLE arrays (vals INTEGER[3])"
  forM_ (sizedArray (listArray (0, 2) [1, 2, 3]) :: Maybe (SizedArray 3 Int)) $ \arr ->
    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] :: [Int]))
  _ <- execute conn "INSERT INTO lists VALUES (?)" (Only ([] :: [Int]))
  fmap fromOnly <$> query_ conn "SELECT vals FROM lists"

A Haskell list binds as a LIST parameter and reads a LIST result. Put a list parameter in Only, because a list as a row is a row of parameters. A String stays one VARCHAR parameter. A SizedArray binds as an ARRAY of its size (note 8 of Types). DuckDB casts a LIST or an ARRAY to the type of a LIST column. It casts a LIST to an ARRAY column only when the lengths are equal.

A list or an array parameter with scalar elements takes its element type from the column type name (duckdbColumnTypeFor) of the element. Some elements take the element type of the target instead: Integer, Natural, and BigNum elements for an integer or DECIMAL element type, and otherwise the type of their values (see above), and TimeOfDay, LocalTime, and UTCTime elements for a nanosecond element type (see Infinite dates and timestamps). An empty list binds as an empty LIST of its element type, except an empty list of Integer or Natural values with no integer or DECIMAL target (see above). An empty array raises an error: note 8 of Types.

A list or an array of STRUCT values, UNION values, generic records, lists, or arrays gets the type that DuckDB gives a SQL list literal of the same elements. When the elements that are not NULL have one type, the list has that type. When their types are different, DuckDB uses their common type: {'a': 1} and {'b': 2} give STRUCT(a INTEGER, b INTEGER)[], with NULL for a field that an element does not have. A BIGINT field and a DOUBLE field give a DOUBLE field, so 9007199254740993 becomes 9007199254740992.0, as in SQL. Elements with no common type, such as {'a': 1} and {'a': 'x'}, raise the SQLError of DuckDB. The column type of an INSERT does not change the type of the list: DuckDB casts the list to the column type after it gives the list its type. A list or an array of such elements that are all NULL, and an empty list, take the element type of the parameter when the parameter has a known type, as for INSERT INTO t VALUES ([]) and ([NULL, NULL]) in SQL. For a parameter with no known type, an empty list or a list of NULL elements gets the type of the SQL literal, "NULL"[], and an empty Map of such values gets the type NULL for its values, as MAP {} does. A generic record through ViaDuckDB has a declared type, so for a parameter with no known type an empty list or a list of NULL records gets the STRUCT or UNION type of the record.

A Map from Data.Map binds as a MAP parameter and reads a MAP result. The pairs go in ascending key order. The keys and the values follow the element rules of a list, and an empty Map binds as an empty MAP of its key and value types. DuckDB compares two MAP values pair by pair in their order, so WHERE m = ? finds a stored MAP only when it has the same pairs in ascending key order. DuckDB rejects a NULL key and two equal keys, for example two NaN keys of a Map Double. When reading a MAP, a decoder rejects keys that become equal after conversion. For example, the integer keys 1 and 2 both decode as True in a Map Bool v.

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
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: note 6 of Types. Floating-point NaN and infinities remain valid Float and Double values.

A TimeOfDay, a LocalTime, or a UTCTime parameter, and an Unbounded form, keeps its digits below a microsecond for a nanosecond parameter: TIME_NS, TIMESTAMP_NS, or TIMESTAMPTZ_NS. Use an explicit cast, such as SELECT ?::TIMESTAMP_NS, or a column with that type. A VARIANT parameter also gets the nanosecond value, when the value fits the range of the nanosecond type, because a VARIANT holds TIME_NS, TIMESTAMP_NS, and TIMESTAMPTZ_NS values. This applies only to a scalar value: a list, a SizedArray, or a Map of time values for a VARIANT parameter binds microsecond elements, and so does an element of a VARIANT[] list or a value of a MAP with VARIANT values, because one list has one element type for all its elements. Each other parameter, and a parameter with no known type, gets the microsecond value: the digits below a microsecond are discarded, as for the SQL literal. Only a value with such digits reads the parameter type. An explicit cast such as ?::TIMESTAMP_NS makes a nanosecond parameter. For a TIMESTAMP column, DuckDB then rounds the value to the microsecond. A plain ? truncates the digits. Values inside collections, STRUCTs, MAPs, and UNIONs follow their target types. The library refreshes prepared metadata when it resets or rebinds a statement. One executeMany call reads the parameter types once. A column type that changes during the call does not change how its later rows bind. Use separate parameters when one expression requires microseconds and another requires nanoseconds.

A date or a timestamp outside the range of DuckDB raises an SQLError of the type ErrorTypeConversion when it binds, also as an element of a list, a SizedArray, or a Map, inside a STRUCT, a TUPLE, or a UNION, and in a VARIANT. For a Day, that range is the range of DATE, from -5877641-06-25 to 5881580-07-10. For a LocalTime or a UTCTime (in UTC), it is the range of TIMESTAMP, TIMESTAMPTZ, TIMESTAMP_S, and TIMESTAMP_MS, from -290308-12-22 00:00:00 to 294247-01-10 04:00:54.775806. The dates are in the notation of Data.Time. A TIMESTAMP_NS or TIMESTAMPTZ_NS target has a smaller range, about the years 1677 to 2262. A value outside that range also gets the type ErrorTypeConversion in each position. The position of the value determines whether the library or DuckDB raises the error:

  • A parameter: the error of the library for a value outside the range of DuckDB, and the Conversion Error of DuckDB for each other such value, for example in the year 2300.
  • An element of a list, a SizedArray, or a Map: the error of the library. For a value in the range of DuckDB, its text is "TIMESTAMP value out of finite range".
  • A field of a STRUCT, an element of a TUPLE, or a member of a UNION: the error of the library, "TIMESTAMP value out of finite range", also for a value outside the range of DuckDB.
  • In each position, a LocalTime or a UTCTime (in UTC) from 2262-04-11 23:47:16.854775807 to 23:47:16.854775999 gets that error of the library.

In the notation of Data.Time, the year -5877641 is 5877642 BC and the year -290308 is 290309 BC. The rule holds also for a finite Unbounded value. DuckDB does not accept a value outside its range: its SQL literals reject it, and a stored value can read as an infinity or make DuckDB raise an INTERNAL error. A leap second, a TimeOfDay with 60 seconds, binds as the next second, as make_time and make_timestamp of DuckDB give it. The SQL literal '23:59:60' raises an error. A time after 24:00:00, for example 23:59:60.5, and a field outside its own range, for example 61 minutes, raise an SQLError of the type ErrorTypeConversion, also as the result of a typed function or a row of a table function.

Manual STRUCT, TUPLE, and UNION handling

SQL tuples, such as row(42, 'text'), decode as TupleValue. Read their positional fields in tupleValueFields and types in tupleValueTypes. FieldTuple and LogicalTypeTuple preserve empty tuples independently of empty STRUCT values. STRUCT fields retain their names in StructValue. LogicalTypeCollatedVarchar preserves a VARCHAR field's collation in composite parameters. Its comparisons use that collation after a round trip.

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. TIMESTAMP_S and TIMESTAMP_MS fields use the finite range described in Infinite dates and timestamps. An explicit parameter cast does not extend that range.

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

import Database.DuckDB.Simple
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))"
    :: IO [(StructValue FieldValue, UnionValue FieldValue)]
  _ <- execute conn "INSERT INTO composite VALUES (?, ?)" (s, u)
  query_ conn "SELECT s, u FROM composite"

Low-level ToDuckValue.toDuckValue takes a ValueFactory, which is the V2 factory handle, and logicalTypeFromRep takes the same handle. Database.DuckDB.Simple.V2.LogicalRep has logicalTypeFromRep. Get the factory of a connection with withConnectionHandle and withConnectionFactory from Database.DuckDB.Simple.Internal. Keep owned values and logical types within the lifetime of the factory's owner.

FieldValue parameters

A FieldValue binds as a parameter, so a row that a query reads as [FieldValue] binds back as a row. A scalar constructor binds as the DuckDB type of the constructor, with the parameter instance of its payload. FieldStruct, FieldTuple, and FieldUnion bind with their own types, and FieldGeometry binds with its CRS. FieldList, FieldArray, and FieldMap take the element type that their elements give, with the rule of a SQL list literal (see Arrays and Lists). They read the parameter type, so that a list read from a TIME_NS[] column binds back with its nanoseconds.

TIME aliases keep their alias and exact microsecond value when they bind inside a composite. The library uses public vector storage because the default TIME-to-alias cast fails in DuckDB 2.0.0. Other aliases require native cast support.

Some values bind with another type than the column that they came from. A FieldTimestamp binds as a TIMESTAMP, or as a TIMESTAMP_NS when it has digits below a microsecond and the parameter has the type TIMESTAMP_NS or VARIANT (see Infinite dates and timestamps). The same holds for FieldTime and FieldTimestampTZ. A FieldText binds as a VARCHAR with no collation, also when it came from a JSON value. A value from a VARIANT column binds as its payload. A FieldEnum binds its label as a VARCHAR. FieldNull binds as an untyped NULL. DuckDB casts each of these values to the type of a column with no loss, with one exception: a time value with digits below a microsecond in a FieldList or a FieldMap for a VARIANT parameter gets the microsecond value, so a VARIANT array of TIMESTAMP_NS values loses these digits when it binds back. A VARIANT object reads as a FieldStruct with VARIANT fields and keeps them.

An empty or all-NULL FieldList, FieldArray, or FieldMap follows note 11 of Types. Inside a FieldStruct, a FieldTuple, or a FieldUnion, a FieldEnum binds its label to the declared ENUM type, and a label that is not in the dictionary raises an SQLError of the type ErrorTypeConversion, as the cast of DuckDB gives for that label. FieldValue has no DuckDBColumnType and no ToDuckValue instance, because no one DuckDB type holds each of its values.

Resource management

  • withConnection and withStatement open and close resources, including when an exception occurs. Use them whenever possible to avoid leaking C handles.
  • The query functions release their results, prepared statements, and values after use, and return a Haskell list of all rows. Folds and cursors decode rows incrementally from owned native chunks. The materialized functions of Database.DuckDB.Simple drain and close the native result before row callbacks; the streaming functions of Database.DuckDB.Simple.Streaming do not.
  • execute/query variants reset statement bindings each run so prepared statements can be reused safely.

For concurrent workers, use a separate connection per worker. Threads can also share one connection. DuckDB permits one open result for each connection, so each connection has a lock. query, execute, and the materialized folds hold this lock from the execution of a statement until its native result closes. Statement preparation holds it only while DuckDB prepares the statement. Metadata binding holds it only while DuckDB binds the statement. A call from another thread waits. The lock does not cover these cases. Serialize them in the application:

  • A transaction. Statements of other threads run inside it.
  • One Statement that two threads use.
  • An explicit stream from Database.DuckDB.Simple.Streaming. Each fetch of the stream holds the lock, but its result stays open between calls, and another query on that connection fails until the stream is complete or closed.
  • close. Wait for all operations on the connection before you close it.

A callback must not run a statement on the connection that runs the callback, or close that connection: User-Defined Functions gives the rule, its time limit, and its limits.

Link your executable with ghc-options: -threaded. A program that registers a callback needs it, because DuckDB can call a callback from several threads at the same time: User-Defined Functions gives the rule. It also allows 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.

Native work that can take a long time runs in a helper thread so that an asynchronous exception can interrupt it. The materialized functions run the native calls of one result in one helper thread. A bound thread, such as the main thread, changes operating system threads to wait for a helper thread. Thus a small query takes longer from a bound thread than from an unbound thread. Run code with many small queries in a thread made with forkIO, or wrap the main action in Control.Concurrent.runInUnboundThread.

Call closeStatement or clearStatementBindings on 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. This API 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 materialized functions close the native result before row delivery. A row callback can run another query on the same connection.

import Database.DuckDB.Simple

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 the query functions, including STRUCT and UNION values with nested collections and NULLs. GEOMETRY and VARIANT work with the query functions, cursors, and folds.

Optional native streaming

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

import Database.DuckDB.Simple
import qualified Database.DuckDB.Simple.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)

This module uses V2 execution and incremental chunk fetching. DuckDB can still materialize some queries. Streaming does not bound the memory used by query operators.

The first cursor fetch selects the execution mode. The mode stays until bind, bindNamed, clearStatementBindings, or executeStatement releases the result. Switching between materialized and streaming nextRow calls retains that mode. Keep the connection dedicated to the explicit stream. Until the stream is complete, DuckDB rejects other statements on that connection with the ErrorResourceInUse error of DuckDB. If you close an incomplete stream, DuckDB aborts its statement. In autocommit mode, DuckDB rolls back the writes of that statement. 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.

Each array has at most connectionArrowBatchSize rows. Set this option in ConnectionOptions. The default is defaultArrowBatchSize, 2048 rows. DuckDB can give shorter arrays. Opening a connection rejects a value that is not positive. For DuckDB's batch size, set 131072.

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 V2 Arrow results. The materialized foldArrow retains owned batches and closes the native result before callbacks start. Its memory use depends on the result size. The streaming foldArrow delivers each batch while the native result remains open.

VARIANT

VARIANT results decode to the FieldValue of the stored value. Existing FromField instances read scalar payloads. Objects decode to FieldStruct values with VARIANT fields in DuckDB's native key order (lexicographic). Arrays decode to FieldList and preserve element order. The decoder uses SQL type metadata and native casts. It preserves scalar types, precision, and nested values without reading DuckDB's private payload format.

query_ conn "SELECT 42::BIGINT::VARIANT" :: IO [Only Int64]

SQL NULL VARIANT values decode to FieldNull. Variant from Database.DuckDB.Simple.Variant wraps a FieldValue. Its ToField instance binds the payload as VARIANT. variantObject builds an object from named fields. It can read the bound value back directly:

query conn "SELECT ?"
  (Only (Variant (FieldList [FieldInt64 1, FieldInt64 2])))
  :: IO [Only Variant]

A scalar payload keeps its native type, such as TINYINT or DECIMAL(4,2). Time and timestamp payloads bind as microsecond types, or as nanosecond types when they have sub-microsecond digits and fit the nanosecond range. So a TIMESTAMP_S result binds back as a TIMESTAMP when its value fits. Each element of a list payload and each field of an object payload follow the same rule, so one list payload can hold both types. A Haskell list, a SizedArray, or a Map of time values for a VARIANT parameter is not a payload: it binds microsecond elements (see Infinite dates and timestamps). A value outside the range of DuckDB raises an SQLError of the type ErrorTypeConversion. Lists, arrays, and STRUCT fields bind as VARIANT values. A VARIANT does not keep a UNION, a MAP, an ENUM, or the CRS of a GEOMETRY, so these payloads raise an error at any depth: a VARIANT keeps only the member value of a UNION, stores a MAP as a list of key and value objects, and stores an ENUM as its text. Object parameters reject duplicate keys and keys that contain NUL. An object with only an empty key also raises an error because DuckDB treats its STRUCT type as a tuple. DuckDB 2.0 permits an empty key with other named keys. You can also bind a plain value and cast it in SQL, as in ?::VARIANT.

A Variant result reads only a value that a VARIANT can hold: note 13 of Types gives the shapes, and the refusal of a JSON type.

For query results the library uses a separate helper connection for VARIANT decoder metadata. It caches one prepared VARIANT metadata statement and the types for VARIANT casts. It batches VARIANT metadata queries. It constructs VARIANT types directly through the native factory. This preserves an explicit stream on the caller's connection. The helper connection has the settings of the database, not the caller's session settings. Metadata preparation needs an expression depth of 11. A GLOBAL max_expression_depth below 11 fails preparation with a Binder Error, including preparation for a connection's COPY TO. Extension COPY TO and typed callback arguments use an independent instance with default expression depth. A prepared plan on a query helper can remain usable after the limit is lowered. The query for unusual object keys still binds on each execution and can fail at a lower limit.

New DuckDB 2.0 databases use storage format 2.0, which supports VARIANT columns. The library does not change an existing database's storage compatibility setting. VARIANT columns require storage format v1.5.0 or later.

GEOMETRY

Use Geometry from Data.Geometry for decoded shapes. The geometry-simple package supplies the type and unboxed coordinate vectors. duckdb-simple supplies its ToField and FromField instances:

import qualified Data.Geometry as G
import qualified Data.Vector.Unboxed as U
import Database.DuckDB.Simple

let line = G.LineString (G.CoordinatesXY (U.fromList [G.XY 0 0, G.XY 1 2, G.XY 3 4]))
rows <- query conn "SELECT ?" (Only line) :: IO [Only G.Geometry]

Points and coordinate sequences store their XY, XYZ, XYM, or XYZM layout. G.EmptyPoint G.DimXYZ is an empty XYZ point. Use Maybe G.Geometry for SQL NULL. Use decodeWKT from Data.Geometry.WKT to parse text without a database.

G.Geometry has no CRS metadata. Reading it returns the coordinates without their CRS label. Binding it creates a GEOMETRY with no CRS. To attach a label, use ST_SetCRS(?, ?) with a shape and CRS text. This does not transform coordinates.

Use RawGeometry from Database.DuckDB.Simple.Geometry to read WKB and CRS without decoding coordinates. Its rawGeometryWKB and rawGeometryCRS fields own their data. They remain usable after the connection closes. Existing ByteString result decoding also returns WKB.

A RawGeometry parameter binds as a GEOMETRY value with the same WKB bytes and its own CRS. DuckDB then casts it to the type of the parameter, as it casts a GEOMETRY value in SQL text. A value without a CRS takes the CRS of the target. A target without a CRS removes the CRS of the value, with no error. A target with another CRS raises SQLError. Use Maybe RawGeometry for SQL NULL. A SizedArray, a list, and a typed row cannot hold a RawGeometry (note 14 of Types). A composite parameter holds it as FieldGeometry (see below):

import Database.DuckDB.Simple.Geometry

[Only raw] <- query_ conn "SELECT 'POINT Z (1 2 3)'::GEOMETRY('OGC:CRS84')" :: IO [Only RawGeometry]
rows <- query conn "SELECT ?" (Only raw) :: IO [Only RawGeometry]

For explicit SQL import, use system.main.ST_GeomFromWKB(?) with the WKB bytes. To attach a CRS, use system.main.ST_SetCRS(system.main.ST_GeomFromWKB(?), ?) with the bytes and CRS text. Omit ST_SetCRS for no CRS: a NULL CRS argument returns NULL geometry. CRS text can be a recognized name, a custom label, or WKT2/PROJJSON text. DuckDB can normalize WKB byte order and reduce a known CRS definition to its registered identifier. The qualified names select the built-ins even if a user macro has the same name.

fromRawGeometry decodes the shape and drops CRS metadata. toRawGeometry encodes a shape with no CRS. These helpers follow the geometry-simple contracts. NaN in both WKB point X and Y denotes an empty point. Empty multi-geometries and collections have no stored layout tag in the decoded representation. Keep the raw bytes when these details must survive.

Structured parameters use encodeWKT and DuckDB's native cast. The WKT writer combines layouts in multi-geometries and polygon rings. It fills absent Z or M with NaN. DuckDB's WKT parser limits nesting to 16 levels and rejects empty polygon rings and mixed collection layouts. DuckDB 2.0 also rejects WKB with mixed Z/M member layouts. Explicit WKB import preserves native NaN payload bits that WKT cannot retain.

FieldGeometry contains a raw result. Result decoding retains WKB and CRS, including inside LIST, ARRAY, MAP, STRUCT, and UNION values. A STRUCT, TUPLE, or UNION parameter binds each FieldGeometry as a GEOMETRY with the same WKB bytes and CRS, also inside LIST, ARRAY, and MAP values. Thus a value that a query returns binds again without a change. The CRS of the value must be equal to the CRS of its declared type. A mismatch raises SQLError with the type ErrorInputInvalid, for example for a value with the CRS OGC:CRS84 in a field of type GEOMETRY. A Variant parameter binds a FieldGeometry without a CRS as a GEOMETRY payload. A VARIANT does not keep the CRS of a GEOMETRY, so a FieldGeometry with a CRS in a Variant raises SQLError.

LogicalTypeGeometry describes CRS metadata. Composite parameters retain it. Recognized names, such as OGC:CRS84, and full WKT2/PROJJSON definitions use direct V2 type construction. For an unknown custom label, parameter binding reads a SQL-derived type with ST_SetCRS on a helper connection. This happens on demand, including for nested types, typed NULLs, empty collections, and inactive UNION members. Binding the same custom label again repeats the lookup. A blank label or a label with NUL raises SQLError at bind time.

Query results retain a custom CRS label. Typed registrations and direct low-level logicalTypeFromRep calls use native type construction. They require a recognized name or a full definition. A custom label in a declared argument, result, cast source or target, pragma parameter, or table-function column fails at registration. DuckDB casts a plain GEOMETRY argument to its declared type, which removes the CRS. Direct type construction raises an error if DuckDB drops the CRS under ignore_unknown_crs.

The CRS in a cast must be a constant. DuckDB rejects a parameter as a type modifier, so ?::GEOMETRY(?) is not valid. To use a CRS that is known only at run time, write it into the query text as a SQL string literal, and double each single quote. A cast accepts a CRS that DuckDB recognizes, such as OGC:CRS84, or a full WKT2 or PROJJSON definition. Unless an extension recognizes it, DuckDB rejects other identifiers, such as EPSG:4326, and custom names. ST_SetCRS also accepts custom names, but it returns GEOMETRY with no CRS for a NULL input.

A bound composite without a CRS also changes the type of expressions that combine it with CRS data. UNION ALL and COALESCE of GEOMETRY and GEOMETRY('OGC:CRS84') give GEOMETRY with no CRS for all rows. Cast the parameter before you combine it with other values.

See geometry-simple for the seven supported families, construction checks, and codec normalization rules.

Feature coverage

  • Connections, prepared statements, positional/named parameter binding.
  • High-level execution (execute*) and the query functions (query*, queryNamed).
  • Cursor and fold helpers (fold, foldNamed, fold_, nextRow) that decode native result chunks one row at a time.
  • Scalar types: 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 typed parameters and results.
  • 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).
  • Aggregate functions, table functions, replacement scans, COPY formats, and pragmas. See Typed registration.
  • Transaction helpers (withTransaction) and metadata accessors (columnCount, columnName).

User-defined functions

Register a scalar Haskell function on a connection, and call it in SQL expressions. The arguments use FromField, and the result uses ToDuckValue. A function can accept and return Maybe values, and it can run an IO action.

An argument type needs FromField and DuckDBColumnType instances. Its SQL type is the duckdbTypeDeclaration of the type, as for a column of a typed row. A newtype with its own column type name (duckdbColumnTypeFor), for example DECIMAL(18,3), gives that text. Arguments use the same conversion as query results, including nested values. Registered casts do not change how the library reads GEOMETRY arguments. See Typed registration.

The SQL type of the result is the duckdbTypeDeclaration of the result type: an Int16 result is SMALLINT, and a Float result is FLOAT. DuckDB computes with that type. Thus hs_small(x) * hs_small(y) of an Int16 function raises an Out of Range Error when the product does not fit in SMALLINT, as 30000::SMALLINT * 30000::SMALLINT does. Cast the result to a wider type first. Some result types write directly into the result vector. Each other result type requires a native value for each row. Types lists them.

{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE ScopedTypeVariables #-}

import Data.Int (Int64)
import Database.DuckDB.Simple

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

An exception in the function fails the statement with an SQLError: Errors gives its text. deleteFunction issues a DROP FUNCTION IF EXISTS statement and reports native errors. DuckDB registers C API scalar functions as internal entries and rejects their deletion. The function remains registered after this SQLError. Deleting an absent name succeeds. To replace a function, register the name again. deleteFunction has a GHC WARNING pragma, so each use causes a compile-time warning. A build with -Werror must allow that warning, for example with -Wno-error=deprecations.

A program that registers a callback, for example of a scalar function, a cast, an aggregate function, a table function, a COPY format, a replacement scan, a pragma, or a log sink, must be linked with ghc-options: -threaded. DuckDB can call a callback from several of its threads at the same time. Without -threaded, the GHC runtime does not support calls from several operating system threads at the same time, so the program can crash.

Without -threaded, registering a COPY format with callbacks or a log sink raises ErrorInputInvalid regardless of the settings. DuckDB can open a COPY file on an asynchronous worker, which can also write to the log sink. For other callbacks, registration raises ErrorInputInvalid when the DuckDB setting threads is above 1. This check only reads the setting at registration. It cannot prevent another connection from raising the thread count later.

With -threaded, each call of a callback needs a capability of the GHC runtime. With one capability, which is the default, a callback from a second DuckDB thread waits until the capability is free, and the operating system switches threads for each such call. A query that makes many callbacks on several DuckDB threads is then much slower. Give the program as many capabilities as DuckDB has threads, for example with ghc-options: -threaded -rtsopts "-with-rtsopts=-N", or set the DuckDB setting threads to 1.

A callback must not run a statement on the connection that runs the callback, and it must not close that connection. Use another connection of the same instance. This rule applies to each registered callback, for example of a scalar function, a cast, an aggregate function, a table function, a COPY format, a replacement scan, a pragma, or a log sink. query, execute, the materialized folds, statement preparation, and each fetch of an explicit stream hold the lock of the connection while DuckDB runs these callbacks. The lock is not reentrant, so a statement in the callback waits for the lock. With more than one DuckDB thread, DuckDB can also run a callback of an explicit stream between two fetches, while no fetch holds the lock. Then a statement in the callback on the same connection fails at once with the ErrorResourceInUse error of DuckDB, because the stream has an open result.

That wait has a time limit. connectionCallbackLockWait in ConnectionOptions sets it in microseconds. The default is defaultCallbackLockWait, 10 seconds, and Nothing sets no time limit. When the time limit ends, the statement in the callback raises an SQLError of the type ErrorResourceInUse, and its text gives the rule. If the callback does not catch the error, the outer statement fails with the type ErrorInputInvalid, because a callback does not pass the type ErrorResourceInUse (Errors gives the list; a cast gives ErrorTypeMismatch), and its message contains the text of that error. The connection can run the next statement. DuckDB ignores the error of a log sink. The time limit applies to each wait of a callback for the lock of a connection. It applies also to another connection that a different statement uses. If callbacks share a connection that runs slow statements, set a larger time limit or Nothing for that connection.

In these cases the time limit does not apply, and a statement in the callback can wait indefinitely:

  • DuckDB runs the callback during a call that does not hold the lock of the connection, for example a log sink for a message of logConnectionMessage. Then the statement goes into DuckDB on the same connection.
  • The statement runs in a thread that the callback starts. The library marks only the thread of the callback.
  • The statement runs inside uninterruptibleMask.
  • The statement runs in the flush function of a log sink when DuckDB destroys the sink.

A statement in a callback on another connection of the same instance runs in its own transaction, also when the outer statement runs in an explicit transaction. It does not see rows that the outer transaction has not committed. A write to a row that the outer transaction changed fails immediately with DuckDB's TransactionContext Error: Conflict on update! error. The outer statement fails with that text. That failure aborts the outer transaction, as each error during the execution of a statement does in DuckDB: each later statement in the transaction fails, and a COMMIT returns with no error but stores no change of the transaction.

A callback that refers to a connection keeps that connection and its instance reachable while DuckDB keeps the callback, possibly until the database closes. The garbage collector cannot close them while they remain reachable. The program must close them.

Typed registration

These functions register SQL objects with Haskell values. Their signatures have no native handle. Database.DuckDB.Simple.Extension has the same functions for a loadable extension. An exception in a Haskell function gives an SQL error with the message of the exception (see Errors). The examples use these imports:

{-# LANGUAGE BlockArguments #-}
{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE ScopedTypeVariables #-}

import Data.Int (Int64)
import Data.Text (Text)
import qualified Data.Text as Text
import qualified Data.Text.IO as TextIO
import Database.DuckDB.Simple
import Database.DuckDB.Simple.Copy
import Database.DuckDB.Simple.FromField (Field (..), FieldValue (..))
import Database.DuckDB.Simple.Pragma (PragmaCallback (..), registerPragmaFunction)
import Database.DuckDB.Simple.Registration

The library has no typed cast in this release. A registered cast can give wrong rows with no error, because the optimizer of DuckDB 2.0.0 decides from the two types of a cast alone whether it can move the cast away from a column in a filter or a join. In a run, it also gave min and max from the statistics of the column without the cast. The cast function then does not run on that column, or, in a comparison of two columns, it runs on one side only. In runs with DuckDB 2.0.0, a cast from INTEGER to BIGINT, and a cast between BIGINT and a custom type with the base type BIGINT, gave wrong rows when its function changed the value. registerCast in Database.DuckDB.Simple.V2.Registration registers a native cast, and its Haddock gives the warning. For a conversion that changes the value, register a scalar function with createFunction (see User-Defined Functions).

A registered cast does not change a value that the library reads or binds, on a connection and in the callbacks of an extension. The library reads GEOMETRY and VARIANT values with the built-in casts, and it builds GEOMETRY, ENUM, and VARIANT values with the default cast of DuckDB. For example, a cast from GEOMETRY to BLOB changes the result of CAST(g AS BLOB), but not a GEOMETRY value that a query gives or that a callback reads. Of the casts that the library performs itself, only setConfigValue and setConfigOption use registered casts. DuckDB casts the value to the type of the option, as SQL SET does.

createAggregate registers an aggregate function as a fold. Its result needs ToDuckValue and a static duckdbTypeDeclaration, as a scalar function result does. Lists, maps, and records or sum types through ViaDuckDB can be results. A Maybe a result declares the type of a, and Nothing gives NULL. Types with no static declaration, such as StructValue, are errors at registration. See Types for the encoding rules. Aggregates support GROUP BY, DISTINCT, FILTER, ORDER BY, and window functions.

aggregateExample :: Connection -> IO [Only Int64]
aggregateExample conn = do
  createAggregate conn "haskell_sum" Aggregate
    { aggregateInitial = 0 :: Int64
    , aggregateStep = \total (Only value) -> pure (total + value)
    , aggregateCombine = \a b -> pure (a + b)
    , aggregateResult = pure
    }
  query_ conn "SELECT haskell_sum(i) FROM range(10) t(i)" -- [Only 45]

The library keeps the live aggregate states in a table with stripes. Two DuckDB threads wait for each other only when they change the same stripe. connectionAggregateStripes in ConnectionOptions sets the number of stripes for each aggregate that createAggregate registers through the connection. The default is defaultAggregateStripes, 64. No result depends on the number.

Scalar and aggregate registrations create a private helper instance when an argument needs VARIANT decoding. In a loadable extension, this helper also supplies the factory for type metadata, GEOMETRY reads, and aggregate result encoders. The registration shares the instance across queries until the last native callback owner releases it. Each VARIANT vector uses a separate connection and decoder state. The helper uses threads=1 and async_threads=0, with no background worker pool. A registration on a connection uses its existing instance for type metadata and GEOMETRY reads.

createTableFunction registers a table function that gives its rows as a list. DuckDB reads only the columns that the query uses, also in WHERE and ORDER BY. For a query that uses no column, such as SELECT count(*), DuckDB reads the first column. The library does not evaluate the other columns of a row, so an exception in such a column does not fail the query. The library evaluates each row that it writes to its constructor. This evaluates each strict field of the row (a field with !, or each field with StrictData).

tableExample :: Connection -> IO [(Int64, Int64)]
tableExample conn = do
  createTableFunction conn "squares" ["n", "square"] \(Only count) ->
    pure [(n, n * n) | n <- [0 .. count - 1 :: Int64]]
  query_ conn "SELECT n, square FROM squares(3)" -- [(0, 0), (1, 1), (2, 4)]

The type of a column, a parameter, or an aggregate argument comes from duckdbTypeDeclaration: notes 8, 9, and 10 of Types give the rule for a SizedArray, records, sum types, and StructValue, TupleValue, and UnionValue. Arguments use the same conversion as query results.

The decoder uses SQL to inspect VARIANT values. For callback arguments it copies the rows into a private DuckDB instance. The helper has no access to the host's catalog, settings, or registered casts. A concrete SQL argument type avoids this work when all rows have a known type. A decoding error fails the calling statement under the usual callback error rules.

registerReplacementScan gives the parts of an unknown table name to the function. The function can give a SELECT statement that DuckDB reads instead.

scanExample :: Connection -> IO [Only Int64]
scanExample conn = do
  registerReplacementScan conn \parts ->
    pure (if parts == ["numbers"] then Just (ReplaceWithQuery "SELECT i FROM range(3) t(i)") else Nothing)
  query_ conn "SELECT i FROM numbers ORDER BY i" -- [Only 0, Only 1, Only 2]

registerCopyFunction registers a COPY format with a COPY TO side and a COPY FROM side. DuckDB keeps the first registration of a format name. Thus register a format with two directions in one call.

copyExample :: Connection -> Text -> IO [Only Text]
copyExample conn path = do
  registerCopyFunction conn "haskell_lines" linesTo \info ->
    map Only . Text.lines <$> TextIO.readFile (copyFromPath info)
  _ <- execute conn "COPY (SELECT i::VARCHAR AS line FROM range(3) t(i)) TO ? (FORMAT haskell_lines)" (Only path)
  _ <- execute_ conn "CREATE TABLE copied (line VARCHAR)"
  _ <- execute conn "COPY copied FROM ? (FORMAT haskell_lines)" (Only path)
  query_ conn "SELECT line FROM copied ORDER BY line" -- [Only "0", Only "1", Only "2"]

linesTo :: CopyTo () FilePath
linesTo =
  CopyTo
    { copyToBind = \_ -> pure ()
    , copyToInit = \info -> TextIO.writeFile (copyInitFilePath info) "" >> pure (copyInitFilePath info)
    , copyToSink = \info rows -> TextIO.appendFile (copySinkGlobalState info) (Text.unlines (map line rows))
    , copyToFinalize = \_ -> pure ()
    }
  where
    line [Field{fieldValue = FieldText text}] = text
    line _ = error "haskell_lines: each row must have one VARCHAR value"

registerPragmaFunction gives the arguments of the call to the callback as a FromRow row. A QueryPragma callback gives SQL that DuckDB runs in place of the PRAGMA statement.

pragmaExample :: Connection -> IO [Only Int64]
pragmaExample conn = do
  registerPragmaFunction conn "repeat_answer" [parameter "times" (TypeExpression "BIGINT")] $
    QueryPragma \(Only (times :: Int64)) -> pure ("SELECT 42::BIGINT FROM range(" <> Text.pack (show times) <> ")")
  query_ conn "PRAGMA repeat_answer(2)" -- [Only 42, Only 42]

A typed registration has more overhead for each row than raw vector access. Use Database.DuckDB.Simple.V2.Registration, V2.Copy, V2.Pragma, and V2.Config for speed, and for filter pushdown, parallel table scans, and scalar functions with a bind callback or variadic parameters. A typed table function has projection pushdown (see createTableFunction above), but no filter pushdown.

Loadable extensions

Database.DuckDB.Simple.Extension registers the typed functions of this package from a loadable DuckDB extension. Build the extension with the extension flag of duckdb-ffi and of duckdb-simple. The module's Haddock describes the entry point, typed registrations, and resource lifetimes. See dev/extensions/README.md in this repo. CI builds and tests the example.

Tests

The package has five test suites. Each suite tests the library through a database.

  • duckdb-simple-test is the main suite, built with tasty. It covers connections, statements, parameters, results, streaming, Arrow batches, cancellation, COPY functions, VARIANT and GEOMETRY values, typed registrations, and callbacks.
  • duckdb-simple-leak-test counts native threads and resident memory through repeated queries, failures, and cancellation.
  • duckdb-simple-nonthreaded-test is linked without -threaded. It checks the error for the registration of a callback in such a program.
  • duckdb-simple-dataframe-test tests the Arrow export with the dataframe library. It needs the flag dataframe-tests.
  • duckdb-simple-quack-test tests authenticated local Quack connections. It needs the flag quack-tests, and the variables DUCKDB_HTTPFS_EXTENSION and DUCKDB_QUACK_EXTENSION must name the extension files.
cabal test all --test-show-details=direct
cabal test duckdb-simple-test --test-show-details=direct
cabal test duckdb-simple-dataframe-test -fdataframe-tests

cabal test all runs the first three suites. The repository's CONTRIBUTING.md gives the rules for new tests and the variables for a test run.