Skip to main content

sqlalchemy-adbc

PyPI License: Apache 2.0 CI

Generic SQLAlchemy dialect for any Apache Arrow ADBC driver — so any tool that speaks SQLAlchemy (pandas, marimo, Superset, dbt, Polars) can read and write against Flight SQL, PostgreSQL, SQLite, Snowflake, and BigQuery through the Arrow-native wire path.

Why this exists

ADBC drivers speak Python DBAPI and return results as Arrow record batches — zero per-row materialization, no driver-specific JDBC/ODBC install. But most of the Python ecosystem wires into databases through SQLAlchemy, and ADBC doesn't ship a dialect. This package is a thin translation layer: SQLAlchemy URL → ADBC db_kwargs → DBAPI connection.

Install

pip install sqlalchemy-adbc

# With a driver extra:
pip install "sqlalchemy-adbc[flightsql]"
pip install "sqlalchemy-adbc[postgresql]"
pip install "sqlalchemy-adbc[sqlite]"
pip install "sqlalchemy-adbc[snowflake]"
pip install "sqlalchemy-adbc[bigquery]"

Quickstart

import sqlalchemy

# Flight SQL
engine = sqlalchemy.create_engine(
    "flightsql+adbc://flight-sql.example.com:443?tls=true",
    connect_args={"db_kwargs": {
        "adbc.flight.sql.authorization_header": f"Bearer {token}",
    }},
)

# SQLite
engine = sqlalchemy.create_engine("sqlite+adbc:///path/to/db.sqlite")

# PostgreSQL
engine = sqlalchemy.create_engine(
    "postgresql+adbc://user:pass@host:5432/mydb"
)

# Works with pandas, Polars, marimo SQL cells, etc.
import pandas as pd
df = pd.read_sql("SELECT 1 AS n", engine)

URL forms

URLs follow SQLAlchemy's <backend>+<driver>:// convention, with adbc as the driver. The pre-0.4 adbc+<backend>:// spelling still works but emits a DeprecationWarning and will be removed in 1.0.

Backend URL
Flight SQL flightsql+adbc://host:port?tls=true&authorization=...
Flight SQL flightsql+adbc://user:TOKEN@host:port?tls=true (bearer)
PostgreSQL postgresql+adbc://user:pass@host:port/database
SQLite sqlite+adbc:///path/to/db or sqlite+adbc:///:memory:
Snowflake snowflake+adbc://user:pass@account/db/schema?warehouse=W
BigQuery bigquery+adbc:///my-project?dataset=my_dataset

Flight SQL query-string options

Key Meaning
tls=true Use grpc+tls:// instead of grpc://
authorization=... Sets the ADBC AUTHORIZATION_HEADER db_kwarg
header.<name>=<v> Forwarded via ADBC's RPC_CALL_HEADER_PREFIX
(any other key) Passed through as a db_kwarg verbatim

Tested servers. CI runs the Flight SQL suite against GizmoSQL v1.42 (DuckDB backend): basic and bearer auth, type round trips, parameters, transactions, reflection and pandas.read_sql. Server-dependent: GizmoSQL's GetObjects reports no constraints or nullability, so reflected primary keys are empty and every column reflects nullable=True; its SQLite backend is not usable through the Go driver.

Proxies

The Flight SQL, Snowflake and BigQuery drivers are Go libraries that read HTTP_PROXY, HTTPS_PROXY and NO_PROXY (either case) from the process environment. Set them in the shell or launcher that starts Python. The Go runtime snapshots the environment at process start, so a variable written later from Python, by this package or anything else, is not seen by the drivers. A [proxy] section in the local config file (below) is still exported into the environment on the first connect: it serves this package's own HTTP calls (the OAuth2 token request) and anything in the process that reads the environment at request time, and a WARNING is logged so the gap is visible. A variable you already set always wins. PostgreSQL (libpq) and SQLite have no HTTP proxy concept.

Authentication

Pick a mode with auth= in the URL, or name a profile from the config file with auth_profile=:

Mode Flight SQL Snowflake Credentials
bearer default – URL password is the token (flightsql+adbc://u:TOKEN@host)
basic yes default URL user:password@; Flight SQL uses the Handshake
negotiate yes – Kerberos/SPNEGO via pip install "sqlalchemy-adbc[kerberos]"; SPN is HTTP@<host> unless a profile pins spn
oauth2 yes yes client credentials: oauth2_token_url=, URL client_id:client_secret@ (or a profile); optional oauth2_scope=, oauth2_audience=

Flight SQL runs the OAuth2 flow inside the driver and refreshes tokens itself. Snowflake receives a token minted by this package when each DBAPI connection opens (cached until it expires, so a pool does not re-request it); the Negotiate ticket on Flight SQL is likewise minted per connection. Snowflake's own authenticator= DSN options (key pair, Okta, external browser) keep working unchanged through the query string.

Config file

Secrets stay out of URLs by living in a profile. The file is read from $SQLALCHEMY_ADBC_CONFIG, else ~/.config/sqlalchemy-adbc/config.toml; it must be owned by you and not group- or world-writable, and the working directory is deliberately never searched. Any value may be env:NAME.

[proxy]
https_proxy = "http://proxy.corp.example:3128"
no_proxy    = "localhost,127.0.0.1,.corp.example"

[auth.drls-flight]
type = "oauth2"
token_url = "https://keycloak.example/realms/drls/protocol/openid-connect/token"
client_id = "marimo"
client_secret = "env:DRLS_FLIGHT_CLIENT_SECRET"
scope = "openid"

[auth.corp-kerberos]
type = "negotiate"
spn = "HTTP@flight-sql.corp.example"
engine = sqlalchemy.create_engine(
    "flightsql+adbc://flight-sql.corp.example:443?auth_profile=drls-flight"
)

A URL that names a profile may only adjust oauth2_scope and oauth2_audience; the token endpoint, SPN and client identity stay under the config owner's control, env: references resolve only from the file, and oauth2_client_secret is never accepted in a query string.

Status

Beta. Core dialect + reflection + Alembic autogenerate work on the ADBC drivers we exercise in CI (SQLite end-to-end, PostgreSQL via service container). Not 1.0 yet — we want a few months of real-world use and at least one release without breaking changes before committing to a stable API.

Full history in CHANGELOG.md. Download tags on GitHub Releases; published wheels on PyPI.

What works

  • SELECT / INSERT / CREATE TABLE / DROP TABLE / ALTER TABLE for every advertised driver
  • MetaData.reflect(bind=engine) — full Inspector contract backed by ADBC GetObjects, including columns, PK, FK, unique constraints, indexes (native PRAGMA / pg_catalog queries on SQLite and PG)
  • Alembic autogenerate — end-to-end, reusing SQLAlchemy's native backend DDL impls via standard dialect-name lookup
  • URL forms for Flight SQL, PostgreSQL, SQLite, Snowflake, BigQuery with correct special-character escaping in passwords
  • PostgreSQL typed decorators: JSONB / JSON / UUID / INET / CIDR / MACADDR, and all range and multirange types (int4range … tstzmultirange) plus hstore, returning SQLAlchemy's own Range / MultiRange / dict with working @>, &&, ? operators and reflection

Known limitations

  • Raw SQL readers see PostgreSQL range / hstore values as bytes — pd.read_sql("SELECT period FROM t", engine) and SQL Lab style queries carry no column type information, so the ADBC binary form reaches Python untouched. Typed columns via Core, ORM or reflection are decoded (#11)
  • Drivers exercised in CI — SQLite fully, PostgreSQL and Flight SQL (GizmoSQL) via service containers, Snowflake through the real driver against the fake endpoint under tests/fake_snowflake/. BigQuery has URL translation tests only (#12)
  • ADBC SQLite NOT NULL reporting — fixed upstream in adbc_driver_sqlite 1.13 (Python ≥ 3.11). On older drivers every column reflects as nullable=True (#13)

Tests run on Python 3.12–3.14 in CI, including PostgreSQL and Flight SQL integration tests against service containers; see the badge above for the current state.

Development

git clone https://github.com/drls-io/sqlalchemy-adbc
cd sqlalchemy-adbc
python3 -m venv .venv && source .venv/bin/activate
pip install -e ".[dev]"
pytest                 # unit tests + the fake Snowflake endpoint; live backends via DRLS_PG_URL / DRLS_FLIGHTSQL_URL / DRLS_SNOWFLAKE_URL
ruff check src/ tests/ && ruff format --check src/ tests/
mypy src tests
tox                    # same gates across Python 3.12–3.14 × SQLAlchemy 2.0/2.1 (tox.ini)

Flight SQL server

The Flight SQL suite (tests/test_flightsql_e2e.py) needs a server. CI uses GizmoSQL as a service container; locally:

docker run -d --name gizmosql -p 31337:31337 -e GIZMOSQL_USERNAME=ci \
  -e GIZMOSQL_PASSWORD=ci-password -e TLS_ENABLED=0 -e PRINT_QUERIES=0 \
  gizmodata/gizmosql:v1.42.2
export DRLS_FLIGHTSQL_URL="flightsql+adbc://ci:ci-password@127.0.0.1:31337?auth=basic&tls=false"
pytest tests/test_flightsql_e2e.py

Any Flight SQL server works through the same variable; tests that depend on GizmoSQL specifics are documented in the module.

Fake Snowflake endpoint

tests/fake_snowflake/ is an in-process HTTP server that speaks enough of Snowflake's wire protocol for the real adbc-driver-snowflake to log in, run SQL and reflect metadata, with SQLite executing the statements. Every pytest run drives the Snowflake dialect through the real driver against it: login (password and OAuth2 with a token the fake issued), CRUD and type round trips, executemany, reflection of tables, views and constraints, error propagation, proxy routing and the exact bindings put on the wire. The same behaviour suite (tests/test_snowflake_e2e.py) runs against a real account when DRLS_SNOWFLAKE_URL is set, so the fake and Snowflake are checked against each other by anyone with an account.

The fake is a model of Snowflake, not Snowflake. It does not reproduce Snowflake-only SQL rules (PARSE_JSON(?) inside VALUES is accepted here and rejected there), result chunking, async queries, stages or bulk ingest; result column labels of textual SQL follow SQLite (select id yields id where Snowflake yields ID); and NUMBER values are limited to what SQLite stores exactly (int64, or 15 significant digits), beyond which the fake raises a data conversion error rather than return a wrong value. Treat a green fake run as "the dialect and driver agree", and the live run as the truth.

Releasing

Publishing is automated via PyPI trusted publishing (OIDC, no API tokens). One-time setup on pypi.org: add a pending publisher for project sqlalchemy-adbc, owner drls-io, repository sqlalchemy-adbc, workflow publish.yml, environment pypi. Same for TestPyPI if you want dry-runs.

Cutting a release (X.Y.Z):

  1. On a branch, bump version in pyproject.toml and move the [Unreleased] entries in CHANGELOG.md under a new ## [X.Y.Z] — YYYY-MM-DD heading with a one-paragraph summary. Open a PR, let CI pass, merge.

  2. Tag the merge commit on main and push the tag:

    git checkout main && git pull
    git tag vX.Y.Z && git push origin vX.Y.Z
    

The Publish to PyPI workflow fires on the tag, builds sdist and wheel, uploads to pypi.org via trusted publishing and then creates the GitHub Release from that version's changelog section. For TestPyPI dry-runs use workflow_dispatch with target testpypi.

License

Apache-2.0. See LICENSE.

Metadata

Release files for sqlalchemy-adbc 0.7.0

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

Source distribution (sdist)

Source distribution for sqlalchemy-adbc 0.7.0
File Size Uploaded Provenance Actions
sqlalchemy_adbc-0.7.0.tar.gz 117.6 kB With provenance Details for sqlalchemy_adbc-0.7.0.tar.gz

Built distribution (wheel)

Table of built distributions (wheels) for sqlalchemy-adbc 0.7.0
File Interpreter ABI Platform Actions
sqlalchemy_adbc-0.7.0-py3-none-any.whl Python 3 none any Details for sqlalchemy_adbc-0.7.0-py3-none-any.whl

Total release size: 165.8 kB

Release files / sqlalchemy_adbc-0.7.0.tar.gz

Download URL sqlalchemy_adbc-0.7.0.tar.gz
Size 117.6 kB
Tags Source
SHA-256 checksum
How to use checksums
511dc6f9d6eb97eb902312356d218712d06bed045aad61cb0d6bec4276cb44a1
BLAKE2b-256 checksum
How to use checksums
49164eac5a9fbbfac5bd4661265f4bf5d963d818e8197ddd54e3cfcfba62634b
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.16

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.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 8, 2026.

Transparency log

Release files / sqlalchemy_adbc-0.7.0-py3-none-any.whl

Download URL sqlalchemy_adbc-0.7.0-py3-none-any.whl
Size 48.2 kB
Tags Python 3
SHA-256 checksum
How to use checksums
50dfab0a548cd36a70cde7e118d580711d55748c2ca82a28027ef78e3bd49485
BLAKE2b-256 checksum
How to use checksums
bbd157cc4a3077247517631616dda83304113445acacd5dd3746cdcab34bff1b
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.16

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.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 8, 2026.

Transparency log

Security for sqlalchemy-adbc 0.7.0

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.

Full provenance

Both files in this release have been uploaded with provenance metadata.

Select from 2 files

Attestation for sqlalchemy_adbc-0.7.0.tar.gz

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 8, 2026.

Transparency log

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page