duckdb-sqlalchemy 1.5.1

DuckDB SQLAlchemy dialect for DuckDB and MotherDuck

duckdb-sqlalchemy

duckdb-sqlalchemy is a DuckDB SQLAlchemy dialect for DuckDB and MotherDuck. It supports SQLAlchemy Core and ORM APIs for local DuckDB and MotherDuck connections.

For new projects, this repository is the recommended dialect when you want production-oriented defaults, explicit MotherDuck guidance, and a clear migration path from older package names.

The dialect handles pooling defaults, bulk inserts, type mappings, and cloud-specific configuration.

Why choose duckdb-sqlalchemy today

• SQLAlchemy compatibility: Core, ORM, Alembic, and reflection.
• MotherDuck support: Token handling, attach modes, session hints, and read scaling helpers.
• Operational defaults: Pooling defaults, transient retry for reads, and bulk insert optimization via Arrow/DataFrame registration.
• Active release cadence: Tracks current DuckDB releases with a long-term support posture.

Area	duckdb-sqlalchemy (this repo)	duckdb_engine
Package/module name	duckdb-sqlalchemy / duckdb_sqlalchemy	duckdb-engine / duckdb_engine
SQLAlchemy driver URL	duckdb://	duckdb://
MotherDuck workflow coverage	Dedicated URL helper (MotherDuckURL), connection guidance, and examples	No dedicated MotherDuck usage section in the upstream README
Operational guidance	Documented pooling defaults, read-scaling helpers, and bulk insert patterns	Basic configuration guidance in upstream README
Migration path	Explicit migration guide from older package names	Migration to this package is documented in this repo
Project direction	Release policy, changelog, roadmap, and docs site are maintained here	Upstream README focuses on the core driver usage

Coming from duckdb_engine?

If you already use duckdb-engine, migration is straightforward:

• keep the SQLAlchemy URL scheme (duckdb://)
• install duckdb-sqlalchemy
• switch imports to duckdb_sqlalchemy

See the full guide: docs/migration-from-duckdb-engine.md.

Project lineage

This project is a heavily modified fork of Mause/duckdb_engine and continues to preserve upstream history in CHANGELOG.md.

Current direction in this repository:

• package and module rename to duckdb-sqlalchemy / duckdb_sqlalchemy
• production-oriented defaults for local DuckDB and MotherDuck deployments
• docs-first maintenance with versioned release notes and a published docs site

Compatibility

Component	Supported versions
Python	3.9+
SQLAlchemy	1.3.22+ (CI-tested: 1.3, 1.4, 2.0.48)
DuckDB	0.5.0+ (CI-tested currently: 1.1.3 to 1.5.1)

Install

pip install duckdb-sqlalchemy

Quick start (DuckDB)

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import declarative_base, Session

Base = declarative_base()


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String)


engine = create_engine("duckdb:///:memory:")
Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add(User(name="Ada"))
    session.commit()
    assert session.query(User).one().name == "Ada"

Quick start (MotherDuck)

export MOTHERDUCK_TOKEN="..."

from sqlalchemy import create_engine

engine = create_engine("duckdb:///md:my_db")

MotherDuck uses the md: database prefix. Tokens are picked up from MOTHERDUCK_TOKEN (or motherduck_token) automatically. If your token has special characters, URL-escape it or pass it via connect_args.

Connection URLs

DuckDB URLs follow the standard SQLAlchemy shape:

duckdb:///<database>?<config>

Examples:

duckdb:///:memory:
duckdb:///analytics.db
duckdb:////absolute/path/to/analytics.db
duckdb:///md:my_db?attach_mode=single&access_mode=read_only&session_hint=team-a

Use the URL helpers to build connection strings safely:

from duckdb_sqlalchemy import URL, MotherDuckURL

local_url = URL(database=":memory:", memory_limit="1GB")
md_url = MotherDuckURL(database="md:my_db", attach_mode="single")

Configuration and pooling

This dialect defaults to SingletonThreadPool for exact :memory:, QueuePool for named in-memory and empty database URLs, and NullPool for file/MotherDuck connections. You can override pooling explicitly. For long-lived MotherDuck pools, use the performance helper or configure QueuePool, pool_pre_ping, and pool_recycle.

See docs/configuration.md and docs/motherduck.md for detailed guidance.

For manual WAL syncs after SQLAlchemy writes, prefer the explicit duckdb_sqlalchemy.checkpoint(conn) helper over issuing raw CHECKPOINT through a transactional SQLAlchemy connection.

Documentation

• docs/index.md - GitHub Pages entrypoint
• docs/README.md - Docs index
• docs/overview.md - Overview and quick start
• docs/getting-started.md - Minimal install + setup walkthrough
• docs/migration-from-duckdb-engine.md - Migration guide from older dialects
• docs/connection-urls.md - URL formats and helpers
• docs/motherduck.md - MotherDuck setup and options
• docs/configuration.md - Connection configuration, extensions, filesystems
• docs/olap.md - Parquet/CSV scans and ATTACH workflows
• docs/pandas-jupyter.md - DataFrame registration and notebook usage
• docs/types-and-caveats.md - Type support and known caveats
• docs/alembic.md - Alembic integration

Docs site (GitHub Pages):

https://leonardovida.github.io/duckdb-sqlalchemy/

Examples

• examples/sqlalchemy_example.py - end-to-end example
• examples/motherduck_read_scaling_per_user.py - per-user read scaling pattern
• examples/motherduck_queuepool_high_concurrency.py - QueuePool tuning
• examples/motherduck_multi_instance_pool.py - multi-instance pool rotation
• examples/motherduck_arrow_reads.py - Arrow results + streaming
• examples/motherduck_attach_modes.py - workspace vs single attach mode

Development workflow

Install development dependencies:

pip install -e ".[dev,devtools]"

Run quick checks in your current environment:

pytest
nox -s ty
pre-commit run --all-files

Run the full compatibility matrix (slow):

nox -s tests

Release and support policy

• Long-term maintenance: intended to remain supported.
• Compatibility: track current DuckDB and SQLAlchemy releases while preserving SQLAlchemy semantics.
• Breaking changes: only in major/minor releases with explicit notes in CHANGELOG.md.
• Security: open an issue with details; fixes are prioritized.

Changelog and roadmap

• CHANGELOG.md - release notes
• ROADMAP.md - upcoming work and priorities

Contributing

See AGENTS.md for repo-specific workflow, tooling, and PR expectations. We welcome issues, bug reports, and high-quality pull requests.

License

MIT. See LICENSE.txt.