duckdb_engine

duckdb-engine is a SQLAlchemy dialect for DuckDB, the embedded analytical database. It registers the duckdb:// connection URL scheme, letting existing SQLAlchemy code — ORM models, Core queries, Alembic migrations, pandas’ read_sql/to_sql — run against DuckDB files, in-memory databases, or MotherDuck-hosted instances with no custom glue code.

Under the hood it subclasses SQLAlchemy’s PostgreSQL dialect, since DuckDB’s SQL syntax is itself derived from Postgres, then patches over the incompatibilities: swapping SERIAL primary keys for explicit Sequence objects, wrapping DuckDB’s native Python connection in a DBAPI2-compatible adapter, and mapping DuckDB-specific types (unsigned integers, HUGEINT) into SQLAlchemy’s type system. It also supports registering pandas DataFrames as queryable views, preloading DuckDB extensions, and attaching fsspec filesystems — features specific to how DuckDB is used in analytics workflows.

What You Get

• duckdb:// dialect registration - installs as a SQLAlchemy dialect entry point, so create_engine('duckdb:///:memory:') or a file/MotherDuck path just works.
• PostgreSQL-derived SQL compilation - reuses SQLAlchemy’s mature Postgres dialect internals for DDL/DML generation, patched only where DuckDB actually diverges.
• DuckDB-specific type support - maps unsigned integer types and other DuckDB-only types into SQLAlchemy’s type system via duckdb_engine.datatypes.
• Extension and filesystem hooks - connect_args support for preloading DuckDB extensions (e.g. httpfs) and registering fsspec filesystems (e.g. gcs, s3) at connection time.
• Alembic migration support - documents an AlembicDuckDBImpl pattern so schema migrations work against DuckDB targets.

Common Use Cases

• Local analytics prototyping - spin up an in-memory or file-backed DuckDB database through the same SQLAlchemy models used against Postgres/MySQL in production.
• Jupyter/IPython-SQL notebooks - query DuckDB directly in notebooks via ipython-sql, using SQLAlchemy as the connection layer.
• Pandas DataFrame querying - register DataFrames as views and run SQL over them without exporting to a separate database.
• MotherDuck-backed apps - connect SQLAlchemy applications to hosted MotherDuck databases using the same connection-string configuration keys.

Under The Hood

Architecture The dialect subclasses PGDialect/PGInspector/PGTypeCompiler from SQLAlchemy’s own PostgreSQL dialect, since DuckDB’s parser is Postgres-derived, then overrides only the pieces that diverge (e.g. DuckDBInspector.get_check_constraints catches DuckDB’s unsupported-feature errors and re-raises as NotImplementedError). A ConnectionWrapper/CursorWrapper pair adapts DuckDB’s native DuckDBPyConnection — which lacks autocommit and some DBAPI semantics — into the interface SQLAlchemy’s DefaultDialect expects, translating special-cased statements like register(...) and commit into native DuckDB calls. Configuration flows through a separate config.py module that introspects DuckDB’s own duckdb_settings() table to validate connect_args before applying them with typed literal processors.

Tech Stack Pure Python 3.9+ package built with Poetry, depending on duckdb>=0.5.0, sqlalchemy>=1.3.22, and packaging>=21; it registers itself under SQLAlchemy’s sqlalchemy.dialects entry-point group so no explicit import/registration is needed by consumers. Development tooling includes Ruff for linting, mypy in strict mode with the SQLAlchemy mypy plugin, pre-commit hooks, and nox for session-based test running across dependency combinations.

Code Quality A dedicated tests/ package covers basic CRUD (test_basic.py), DuckDB-specific type mapping (test_datatypes.py), integration scenarios (test_integration.py), and interop with pandas and PyArrow. Property-based testing via Hypothesis and snapshot testing via pytest-snapshot supplement conventional unit tests. Type coverage is strict (disallow_untyped_defs, disallow_incomplete_defs, warn_unused_ignores all enabled in mypy config), and GitHub Actions CI runs the suite with coverage reporting to Codecov on every push.

API Design The library asks for almost nothing extra from callers: because it registers via SQLAlchemy’s dialect entry-point mechanism, a bare create_engine('duckdb:///:memory:') call is enough to get a working engine, with no manual dialect import. Divergences from standard SQLAlchemy usage (auto-increment columns needing an explicit Sequence, chunksize limitations in older pandas.read_sql) are called out directly in the README rather than surfacing as opaque runtime errors. Extension preloading and filesystem registration are exposed as plain connect_args dict keys, keeping the DuckDB-specific surface area small and consistent with how SQLAlchemy already expects driver options to be passed.