{"id":2487,"library":"duckdb-engine","title":"DuckDB-Engine","description":"duckdb-engine is an SQLAlchemy driver for DuckDB, a high-performance analytical in-process SQL database system. It enables Python applications to interact with DuckDB databases using SQLAlchemy's ORM and SQL Expression Language. Currently at `v0.17.0`, it receives frequent updates addressing bug fixes and introducing features like filesystem registration.","status":"active","version":"0.17.0","language":"en","source_language":"en","source_url":"https://github.com/Mause/duckdb_engine","tags":["SQLAlchemy","DuckDB","database","analytics","driver","ORM"],"install":[{"cmd":"pip install duckdb-engine","lang":"bash","label":"Pip"},{"cmd":"conda install duckdb-engine -c conda-forge","lang":"bash","label":"Conda"}],"dependencies":[{"reason":"Provides the underlying analytical database engine.","package":"duckdb","optional":false},{"reason":"The ORM and SQL Expression Language framework this driver extends.","package":"SQLAlchemy","optional":false},{"reason":"Runtime dependency for package metadata handling.","package":"packaging","optional":false}],"imports":[{"symbol":"create_engine","correct":"from sqlalchemy import create_engine"}],"quickstart":{"code":"from sqlalchemy import create_engine, text\n\n# Connect to an in-memory DuckDB database\nengine = create_engine(\"duckdb:///:memory:\")\n\nwith engine.connect() as connection:\n    # Execute a simple DDL statement\n    connection.execute(text(\"CREATE TABLE users (id INTEGER, name VARCHAR)\"))\n    connection.commit()\n\n    # Execute an INSERT statement\n    connection.execute(text(\"INSERT INTO users (id, name) VALUES (:id, :name)\"), {\"id\": 1, \"name\": \"Alice\"})\n    connection.execute(text(\"INSERT INTO users (id, name) VALUES (:id, :name)\"), {\"id\": 2, \"name\": \"Bob\"})\n    connection.commit()\n\n    # Execute a SELECT statement and fetch results\n    result = connection.execute(text(\"SELECT id, name FROM users\"))\n    for row in result:\n        print(f\"ID: {row.id}, Name: {row.name}\")","lang":"python","description":"This quickstart demonstrates how to create an in-memory DuckDB database using `duckdb-engine` with SQLAlchemy, create a table, insert data, and query it."},"warnings":[{"fix":"Upgrade your Python environment to version 3.9 or newer.","message":"Python 3.8 support was dropped in `v0.16.0`. Attempting to use `duckdb-engine` with Python 3.8 will fail.","severity":"breaking","affected_versions":"<0.16.0"},{"fix":"Ensure `duckdb-engine` is `v0.15.1` or newer. For concurrent operations, particularly writes, ensure each thread uses its own distinct connection (or cursor from a connection pool) to the database.","message":"Prior to `v0.15.1`, there were potential panics in multi-threaded environments. DuckDB generally follows a single-writer, multiple-reader concurrency model.","severity":"gotcha","affected_versions":"<0.15.1"},{"fix":"For auto-incrementing ID columns, explicitly use `sqlalchemy.Sequence()` in your model definitions.","message":"SQLAlchemy's `SERIAL` datatype, typically used for auto-incrementing primary keys in PostgreSQL, is not directly supported by DuckDB, which can lead to issues with ORM-generated schemas.","severity":"gotcha","affected_versions":"all"},{"fix":"Consult the official DuckDB documentation for supported SQL features and adjust your SQLAlchemy ORM or SQL Expression Language constructs accordingly to avoid unsupported syntax.","message":"The `duckdb-engine` dialect is derived from PostgreSQL, and as such, SQLAlchemy may attempt to use PostgreSQL-only features not supported by DuckDB's SQL parser.","severity":"gotcha","affected_versions":"all"},{"fix":"Upgrade to `duckdb-engine` `v0.14.1` or newer, which sets `div_is_floordiv` to `False` by default, ensuring more predictable division casts.","message":"Division operations might produce unexpected results due to default casting behavior (`div_is_floordiv=True` by default) prior to `v0.14.1`.","severity":"gotcha","affected_versions":"<0.14.1"},{"fix":"Use the URI format `duckdb:///md:<my_database>?motherduck_token=<my_token>`. It is recommended to manage `MOTHERDUCK_TOKEN` as an environment variable rather than hardcoding.","message":"Connecting to MotherDuck databases requires specific connection string parameters, including an optional `motherduck_token` for authentication.","severity":"gotcha","affected_versions":"all"}],"env_vars":null,"last_verified":"2026-04-10T00:00:00.000Z","next_check":"2026-07-09T00:00:00.000Z"}