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	2.0.0+ on Python <3.14; 2.0.45+ on Python 3.14+; SQLAlchemy 2.1 requires Python 3.11+ (tested: 2.0.0, 2.0.52, 2.1.0rc2)
DuckDB	0.5.0+ (tested currently: 1.3.0 to 1.5.5)

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. Access tokens are picked up from MOTHERDUCK_TOKEN (or motherduck_token) automatically. OAuth flows can pass motherduck_oauth_token through the URL or connect_args["config"]. If a 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_name=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, empty database URLs, and local file paths, and NullPool for MotherDuck connections. You can still 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.

Metadata

Release files for duckdb-sqlalchemy 1.5.5.6

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for duckdb-sqlalchemy 1.5.5.6
File	Size	Uploaded
duckdb_sqlalchemy-1.5.5.6.tar.gz	238.3 kB	Sep 14, 2026	Details

Built distribution (wheel)

Table of built distributions (wheels) for duckdb-sqlalchemy 1.5.5.6
File	Interpreter	ABI	Platform	Reset
duckdb_sqlalchemy-1.5.5.6-py3-none-any.whl 77.9 kB Sep 14, 2026	Python 3	none	any	Details

Total release size: 316.1 kB

Release files / duckdb_sqlalchemy-1.5.5.6.tar.gz

Download URL	duckdb_sqlalchemy-1.5.5.6.tar.gz
Size	238.3 kB
Tags	Source
SHA-256 checksum How to use checksums	`ce316628f9a11341ad6fe071fd0d4b98e3cce0dbf50f229586095795cebc8e6e`
BLAKE2b-256 checksum How to use checksums	`10a80e4447dda8d2328080a37c4f3eb3beb63401cf70ace4754e841f1350d4e5`
Upload date	Sep 14, 2026
Uploaded using Trusted Publishing? What is trusted publishing?	Yes
Uploaded via	`twine/7.0.0 CPython/3.13.14`

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

Identity

Publishing platform: GitHub Actions
Publishing repository: github.com/leonardovida/duckdb-sqlalchemy
Publishing commit: github.com/leonardovida/duckdb-sqlalchemy/tree/06ce409cd4675fe2752db7403a5a866640780550

Workflow

Publishing configuration: github.com/leonardovida/duckdb-sqlalchemy/blob/06ce409cd4675fe2752db7403a5a866640780550/.github/workflows/publish.yaml
Publishing logs: github.com/leonardovida/duckdb-sqlalchemy/actions/runs/34839568023/attempts/1

Artifact

Subject: duckdb_sqlalchemy-1.5.5.6.tar.gz
SHA-256 checksum: ce316628f9a11341ad6fe071fd0d4b98e3cce0dbf50f229586095795cebc8e6e Verifying attestations

Release files / duckdb_sqlalchemy-1.5.5.6-py3-none-any.whl

Download URL	duckdb_sqlalchemy-1.5.5.6-py3-none-any.whl
Size	77.9 kB
Tags	Python 3
SHA-256 checksum How to use checksums	`7e07f0c0b162e9419cb3316046771b5bd4a2af7ab6a5349aa9ec74d3ab6ebcde`
BLAKE2b-256 checksum How to use checksums	`b6e36d7347e19f379de4e42e1d08ba80f4de0f4a959f7d8e9fadb0778ac0d10b`
Upload date	Sep 14, 2026
Uploaded using Trusted Publishing? What is trusted publishing?	Yes
Uploaded via	`twine/7.0.0 CPython/3.13.14`

Provenance

Identity

Publishing platform: GitHub Actions
Publishing repository: github.com/leonardovida/duckdb-sqlalchemy
Publishing commit: github.com/leonardovida/duckdb-sqlalchemy/tree/06ce409cd4675fe2752db7403a5a866640780550

Workflow

Publishing configuration: github.com/leonardovida/duckdb-sqlalchemy/blob/06ce409cd4675fe2752db7403a5a866640780550/.github/workflows/publish.yaml
Publishing logs: github.com/leonardovida/duckdb-sqlalchemy/actions/runs/34839568023/attempts/1

Artifact

Subject: duckdb_sqlalchemy-1.5.5.6-py3-none-any.whl
SHA-256 checksum: 7e07f0c0b162e9419cb3316046771b5bd4a2af7ab6a5349aa9ec74d3ab6ebcde Verifying attestations