Types and Caveats

Type support

Most SQLAlchemy core types map directly to DuckDB. DuckDB-specific helpers live in duckdb_sqlalchemy.datatypes.

Type DuckDB name Notes
UInt8, UInt16, UInt32, UInt64, UTinyInteger, USmallInteger, UInteger, UBigInteger UTINYINT, USMALLINT, UINTEGER, UBIGINT Unsigned integers
TinyInteger TINYINT Signed 1-byte integer
HugeInteger, UHugeInteger, VarInt HUGEINT, UHUGEINT, VARINT 128-bit and variable-length integers
Struct STRUCT Nested fields via dict of SQLAlchemy types
Map MAP Key/value mapping
Union UNION Union types via dict of SQLAlchemy types

Struct fields and Map keys and values run their declared SQLAlchemy Python bind and result processors, recursively through nested containers. This includes TypeDecorator, non-native Enum, JSON and Numeric conversion. A whole SQL NULL container remains None. STRUCT input dictionaries retain their keys and are matched to declared fields using DuckDB’s ASCII case-insensitive naming rules.

DuckDB-specific type examples

from sqlalchemy import Column, MetaData, Table
from sqlalchemy import Integer, String
from duckdb_sqlalchemy.datatypes import Map, Struct, Union, VarInt

metadata = MetaData()

events = Table(
    "events",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("payload", Struct({"user": String, "action": String})),
    Column("tags", Map(String, String)),
    Column("attributes", Union({"priority": Integer, "label": String})),
    Column("trace_id", VarInt),
)

Parameter binding

Use named parameters in SQLAlchemy expressions and let the dialect pick the placeholder style:

from sqlalchemy import text

stmt = text("SELECT * FROM events WHERE event_id = :event_id")
conn.execute(stmt, {"event_id": 123})

Execution options

DuckDB-specific execution options are available on connections and statements:

with engine.connect().execution_options(
    duckdb_arrow=True,
    duckdb_copy_threshold=10000,
    insertmanyvalues_page_size=1000,
) as conn:
    conn.execute(stmt)

Arrow results consume the cursor; fetch rows or Arrow, not both. Reading .arrow after fetching rows raises InvalidRequestError.

Interleaved statements

All cursors of a SQLAlchemy connection share one DuckDB connection. If you run another statement while an earlier result is still unread (for example an ORM lazy load inside a loop), the dialect first buffers the rest of the earlier result in memory so it is not truncated. Fetch errors propagate and preserve the pending cursor. Arrow and DataFrame fetches are not available on a buffered result. Read large results fully, or use a separate connection, to avoid the memory cost.

Arrow batch readers own their result until consumed or closed; another statement on that connection raises InvalidRequestError while the reader remains active. See performance guidance for streaming and direct Arrow ingestion.

Row counts and regular expressions

result.rowcount reports the rows changed by an INSERT, UPDATE, DELETE, or MERGE. It is -1 for statements with a RETURNING clause and when several parameter sets run through DuckDB’s executemany() (batched UPDATE and DELETE, and text() INSERTs). A Core insert() with several parameter sets is counted in full when it fits in one insertmanyvalues_page_size page (1000 rows by default) or uses the register fast path (duckdb_copy_threshold, 10,000 rows by default). Between those sizes it runs in pages and rowcount reports the last page only. column.regexp_match(pattern) compiles to regexp_matches(), which matches anywhere in the string; flags are passed as DuckDB regex options (for example flags="i").

Reflection

Reflection reads DuckDB’s catalog functions (duckdb_tables(), duckdb_columns(), duckdb_constraints(), duckdb_indexes()), not the PostgreSQL pg_catalog emulation, so it sees DuckDB types and attached databases.

Which table a name refers to

from sqlalchemy import inspect

inspector = inspect(conn)
inspector.get_table_names(schema="analytics.main")
inspector.get_foreign_keys("orders", schema="analytics.main")
inspector.has_schema("analytics.staging")

Constraint names

DuckDB names every constraint itself (child_parent_id_id_fkey, parent_code_key, parent_n_check) and ignores names given in DDL, so reflected constraint names can differ from the names in your models. Foreign keys cannot cross schemas in DuckDB, so a reflected foreign key always refers to a table in the same schema.

Nested types

STRUCT, MAP, and UNION columns reflect as Struct, Map, and Union with their member types, so a reflected table recreates the same columns. Lists reflect as SQLAlchemy ARRAY. Fixed-size DuckDB arrays reflect as duckdb_sqlalchemy.datatypes.FixedArray(item_type, size), preserving their length when metadata is copied or Alembic generates a migration. Lists of fixed arrays, fixed arrays of lists, and nested fixed arrays retain their shape.

from sqlalchemy import Float
from duckdb_sqlalchemy.datatypes import FixedArray

embedding_type = FixedArray(Float(), 384)  # DOUBLE[384]

DuckDB enforces the size at insertion. FixedArray supports SQLAlchemy indexing and item bind/result processors. ARRAY(Float()) remains a variable-length list.

A nested type reflects as NullType when one of its members is an ENUM, because DuckDB does not report the enum’s type name inside a nested type, or when a member type is not recognized. Declare those columns in your models.

MAP keys and values honor their declared SQLAlchemy Python bind and result processors, including TypeDecorator and nested MAP/LIST members. SQL NULL, empty maps and NULL values inside a map remain distinct.

Enums

inspector.get_enums() lists the ENUM types created with CREATE TYPE in the current database and schema, with their labels. Pass schema="name" or schema="db.schema" for another schema, or schema="*" for all of them.

Unsupported statements

DuckDB does not support these, so the statements SQLAlchemy emits for them fail:

Isolation levels

DuckDB has a single transaction isolation level. The dialect accepts isolation_level="AUTOCOMMIT" (no BEGIN is issued) and the default "READ COMMITTED"; other levels raise ArgumentError.

Numeric precision

Numeric/DECIMAL values are bound and returned as decimal.Decimal without passing through float. Use Numeric(asdecimal=False) or Float for floats. Generic Float() and Float(precision > 24) compile to DOUBLE (64 bits); REAL and Float(24) use 32 bits. Existing FLOAT columns retain their original precision until explicitly migrated.

Decimal division

DuckDB divides DECIMAL values in DOUBLE, so Numeric division can come back slightly off, for example 7.00000000000000088818 instead of 7. Pass exact_decimal_division=True to create_engine() to divide exactly:

engine = create_engine("duckdb:///app.duckdb", exact_decimal_division=True)

With the option on, a / b is exact when both sides are columns or casts with a declared Numeric(precision, scale) or integer type, and at least one side is Numeric. The result is a DECIMAL(38, R) rounded half away from zero, where R is the larger of the two scales, at least 16, and limited to what fits in 38 digits. Dividing by zero returns NULL instead of infinity or NaN.

Other divisions keep DuckDB’s DOUBLE result: integer by integer, Float, Numeric without precision and scale, bound literals and function results whose type is unknown, and quotients that could need more than 22 integer digits (precision - scale of the dividend plus the scale of the divisor). Exact division uses 128-bit integer division and is much slower than DOUBLE. On 10 million DECIMAL(18, 6) rows it took about 2.5 seconds instead of 30 milliseconds, so turn it on where exact results matter more than speed.

Floor division

a // b compiles to FLOOR(a / b), including for two integer columns. DuckDB computes a / b in DOUBLE, so the result is a DOUBLE, and it is inexact once an operand or the quotient is larger than 2**53 (9,007,199,254,740,992). For example 9007199254740993 // 1 returns 9007199254740992.0. The result rounds toward negative infinity like Python, so -5 // 2 is -3.

For exact integer results on large values, use DuckDB’s integer division operator, which truncates toward zero (-5 // 2 is -2 there), through a.op("//")(b), or cast the operands to Numeric with exact_decimal_division=True.

Auto-increment columns

DuckDB does not support PostgreSQL SERIAL. For an integer primary key that SQLAlchemy treats as autoincrement, the dialect creates an implicit <table>_<column>_seq sequence with the table, uses it as the column default, and drops it with the table:

from sqlalchemy import Column, Integer, MetaData, Table

users = Table("users", MetaData(), Column("id", Integer, primary_key=True))

Declare an explicit Sequence only when you need a custom name or start value:

from sqlalchemy import Column, Integer, Sequence, Table, MetaData

user_id_seq = Sequence("user_id_seq")
users = Table(
    "users",
    MetaData(),
    Column("id", Integer, user_id_seq, server_default=user_id_seq.next_value(), primary_key=True),
)

Multiprocessing (fork)

DuckDB’s Python bindings are not fork-safe. Creating a new connection in a multiprocessing child process created with fork can raise runtime errors (for example, RuntimeError: thread::join failed: No such process), especially with MotherDuck or file-backed connections. Prefer spawn or forkserver, and initialize engines/connections in the child process.

DuckDB canonicalizes CHAR(n) and VARCHAR(n) to unbounded VARCHAR; reflected string lengths are therefore None. It generates constraint names and supports table/column comments, but does not support constraint comments or identity columns. Use sequences for generated integer keys. A plain Identity() falls back to a sequence because SQLAlchemy ignores unsupported identity syntax; identity options raise CompileError so their requested behavior cannot silently change.