Hardcharger™ SQLAlchemy Dialect
SQLAlchemy Core, the ORM, pandas and Alembic on Hardcharger: remote DuckDB 2.0, over Chargewire.
sqlalchemy-hardcharger registers the hardcharger:// URL scheme with
SQLAlchemy, so anything that takes a SQLAlchemy engine works against a
Hardcharger™ server, GizmoData's DuckDB 2.0 server. It runs on
the native Hardcharger ADBC driver over
Chargewire HTTP(S), generates DuckDB SQL, and reflects schemas through DuckDB's own catalog.
Install
pip install sqlalchemy-hardcharger
Python 3.11 or later, SQLAlchemy 2.0 or 2.1. Installs adbc-driver-hardcharger>=2.4.9,<3.
Quick start
from sqlalchemy import create_engine, text
engine = create_engine("hardcharger://hardcharger:change-me@localhost:31337?tls=false")
with engine.connect() as conn:
print(conn.execute(text("SELECT 42 AS answer")).scalar())
42
TLS is on unless the URL says ?tls=false (a local development server). The port defaults to
31337, and to 443 for a GizmoData Cloud host.
The ORM
A plain integer primary key numbers itself, as it does on PostgreSQL or SQLite:
from sqlalchemy import ForeignKey, create_engine, func, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship
engine = create_engine("hardcharger://hardcharger:change-me@localhost:31337?tls=false")
class Base(DeclarativeBase):
pass
class Author(Base):
__tablename__ = "authors"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
books: Mapped[list["Book"]] = relationship(back_populates="author")
class Book(Base):
__tablename__ = "books"
id: Mapped[int] = mapped_column(primary_key=True)
author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))
title: Mapped[str]
author: Mapped[Author] = relationship(back_populates="books")
Base.metadata.create_all(bind=engine)
with Session(engine) as session:
ada = Author(name="Ada", books=[Book(title="Notes"), Book(title="Sketch")])
session.add(ada)
session.commit()
print(ada.id, [book.id for book in ada.books])
counts = select(Author.name, func.count(Book.id)).join(Book).group_by(Author.name)
print(session.execute(counts).all())
1 [1, 2]
[('Ada', 2)]
DuckDB has neither SERIAL nor identity columns, so the dialect gives such a key a sequence:
CREATE TABLE authors is preceded by CREATE SEQUENCE IF NOT EXISTS authors_id_seq, the column
gets DEFAULT nextval('authors_id_seq'), and dropping the table drops the sequence. A column
declared with Identity(start=…, increment=…) gets one too, key or not, with those options. An
explicit Sequence("name") is used as given.
Core: upserts and RETURNING
from sqlalchemy import Column, Integer, MetaData, String, Table, create_engine
from sqlalchemy_hardcharger import insert
engine = create_engine("hardcharger://hardcharger:change-me@localhost:31337?tls=false")
metadata = MetaData()
stock = Table(
"stock",
metadata,
Column("sku", String, primary_key=True),
Column("qty", Integer, nullable=False),
)
metadata.create_all(bind=engine)
with engine.begin() as conn:
conn.execute(stock.insert(), [{"sku": "bolt", "qty": 10}, {"sku": "nut", "qty": 4}])
upsert = insert(stock).values(sku="bolt", qty=25)
upsert = upsert.on_conflict_do_update(
index_elements=[stock.c.sku], set_={"qty": upsert.excluded.qty}
)
print(conn.execute(upsert.returning(stock.c.sku, stock.c.qty)).all())
sold = stock.update().where(stock.c.sku == "nut").values(qty=stock.c.qty - 1)
print(conn.execute(sold.returning(stock.c.qty)).scalar())
[('bolt', 25)]
3
sqlalchemy_hardcharger.insert is SQLAlchemy's PostgreSQL insert(): on_conflict_do_update() and
on_conflict_do_nothing() compile to DuckDB's ON CONFLICT.
pandas
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine("hardcharger://hardcharger:change-me@localhost:31337?tls=false")
frame = pd.DataFrame({"city": ["Oslo", "Lima", "Pune"], "temp_c": [4.5, 19.0, 31.5]})
frame.to_sql(name="weather", con=engine, index=False, if_exists="replace")
warm = pd.read_sql(
sql="SELECT city, temp_c FROM weather WHERE temp_c > 10 ORDER BY city", con=engine
)
print(warm.to_dict(orient="records"))
[{'city': 'Lima', 'temp_c': 19.0}, {'city': 'Pune', 'temp_c': 31.5}]
For loading millions of rows, use the driver's Arrow bulk ingest (see
The driver underneath): it sends Arrow batches in parallel instead of
INSERT statements.
The URL
hardcharger://<user>:<password>@<host>[:<port>][/<catalog>][?option=value&...]
| Part or option | Default | Meaning |
|---|---|---|
host, port |
port 31337 (443 on GizmoData Cloud) | The server |
user, password |
Password sign-in | |
/catalog (or catalog=) |
the server's default | Catalog to make current |
schema= |
main |
Schema to make current |
tls= |
true |
false: plain HTTP, for a local development server |
tls_skip_verify= |
false |
true: don't verify the server's certificate (development only) |
tls_root_certs= |
system trust store | CA certificates to trust: a PEM file path |
tls_client_cert=, tls_client_key= |
Client certificate and key for mutual TLS (PEM file paths) | |
tls_server_name= |
the host | Name to check the certificate against, when connecting by IP or through a tunnel |
auth_type= |
password |
external: sign in through a browser (SSO). client_certificate: sign in with the TLS client certificate |
access_token= |
A Hardcharger access token, in place of a user and password (see the note on tokens below) | |
subject_token= |
An identity provider's token, exchanged for an access token | |
oauth_timeout= |
300 |
Seconds to wait for the browser sign-in |
open_browser= |
true |
false: print the sign-in URL instead of opening a browser |
share_sign_in= |
per engine, with auth_type=external |
false: one browser sign-in per connection. A name: share the sign-in with other engines given the same name |
resume_timeout= |
600 |
Seconds to wait for a stopped GizmoData Cloud cluster to start |
session_tag=, query_tag= |
JSON objects recorded with the session and its statements | |
hardcharger.<name>= |
A session setting, e.g. hardcharger.query_timeout=60 |
|
adbc.chargewire.statement.*, adbc.chargewire.ipc_compression, adbc.chargewire.ingest.parts, adbc.chargewire.retry.max_attempts, adbc.chargewire.session.reconnect |
Driver options that tune statements |
An option the dialect doesn't know is an error, never ignored. Everything connect() of the driver
takes can be passed as create_engine(url, connect_args={...}): see the
driver's Python documentation.
A URL is often configuration that someone other than the application's author supplies, so a URL
can only choose among the options above: it can't name a program to run (the driver's browser
command), adopt another session, or set a driver option outside that list. Those are for
connect_args, in the application's own code.
Tokens. SQLAlchemy hides a URL's password when it prints an engine or a URL, and nothing else:
an access_token= or subject_token= in the URL shows in repr(engine) and in logs that print
the URL. Pass a token in code instead:
from sqlalchemy import create_engine
engine = create_engine("hardcharger://db.example.com", connect_args={"access_token": token})
Sign in through your identity provider. The browser opens once per engine: the engine's pooled connections share the sign-in, and another engine in the same process (another user of the same application) gets its own.
from sqlalchemy import create_engine
engine = create_engine("hardcharger://db.example.com?auth_type=external")
Catalogs, schemas and reflection
DuckDB names a table catalog.schema.table. Wherever SQLAlchemy takes a schema, give either a
schema of the current catalog ("sales") or a catalog and schema ("lake.sales"):
from sqlalchemy import MetaData, Table, create_engine, inspect, text
engine = create_engine("hardcharger://hardcharger:change-me@localhost:31337?tls=false")
with engine.begin() as conn:
conn.execute(text("ATTACH ':memory:' AS lake"))
conn.execute(text("CREATE SCHEMA lake.sales"))
conn.execute(
text("CREATE TABLE lake.sales.orders (id INTEGER PRIMARY KEY, total DECIMAL(10,2))")
)
inspector = inspect(engine)
print([name for name in inspector.get_schema_names() if name.startswith("lake")])
print(inspector.get_table_names(schema="lake.sales"))
orders = Table("orders", MetaData(), autoload_with=engine, schema="lake.sales")
print([(column.name, str(column.type)) for column in orders.columns])
['lake.main', 'lake.sales']
['orders']
[('id', 'INTEGER'), ('total', 'DECIMAL(10, 2)')]
get_schema_names() lists the current catalog's schemas by name and every other attached catalog's
as catalog.schema. Reflection covers tables, views, temporary tables, columns (with defaults,
comments and generated columns), primary and foreign keys, unique and check constraints, indexes,
sequences and comments, read from duckdb_tables(), duckdb_columns(), duckdb_constraints() and
friends.
Types
SQLAlchemy's generic types map to DuckDB's:
| SQLAlchemy | Hardcharger | Notes |
|---|---|---|
Integer, BigInteger, SmallInteger |
INTEGER, BIGINT, SMALLINT |
|
Float, Double |
DOUBLE |
A Python float is 8 bytes, so Float is a DOUBLE; Float(precision=24), FLOAT or REAL ask for 4 bytes |
Numeric(p, s) |
DECIMAL(p, s) |
Decimal in Python. Numeric() with no precision is DuckDB's DECIMAL(18, 3) |
String, String(n), Text |
VARCHAR |
DuckDB keeps no length |
Boolean, Date, Time, DateTime |
BOOLEAN, DATE, TIME, TIMESTAMP |
A time-zone-aware value given to DateTime() keeps its wall-clock time and loses its offset, as on PostgreSQL |
DateTime(timezone=True) |
TIMESTAMP WITH TIME ZONE |
An aware value is that instant; a naive one is read in the session's time zone. Returned in the session's time zone |
Time(timezone=True) |
TIME WITH TIME ZONE |
Keeps its offset |
Interval |
INTERVAL |
timedelta; a month reads as 30 days |
Uuid |
UUID |
uuid.UUID, or text with as_uuid=False |
LargeBinary |
BLOB |
|
JSON |
JSON |
Keys, paths and typed access: col["a"], col[("a", 0)].as_integer(). A key is always taken literally, also one that starts with $ or / |
Enum("a", "b") |
ENUM('a', 'b') |
With name=, a type of its own (CREATE TYPE) |
ARRAY(Integer) |
INTEGER[] |
A Python list. A fixed-size INTEGER[3] reflects as a list |
And DuckDB's own, from sqlalchemy_hardcharger: TINYINT, UTINYINT, USMALLINT, UINTEGER,
UBIGINT, HUGEINT, UHUGEINT, BIGNUM, TIMESTAMP_S, TIMESTAMP_MS, TIMESTAMP_NS, TIME_NS,
BIT, VARIANT, GEOMETRY, Struct, Map and Union.
VARIANT holds a value of any type in each row, and reads and writes Python values:
from sqlalchemy import Column, Integer, MetaData, Table, create_engine, select
from sqlalchemy_hardcharger import VARIANT
engine = create_engine("hardcharger://hardcharger:change-me@localhost:31337?tls=false")
metadata = MetaData()
events = Table(
"events", metadata, Column("id", Integer, primary_key=True), Column("payload", VARIANT)
)
metadata.create_all(bind=engine)
with engine.begin() as conn:
conn.execute(
events.insert(),
[{"payload": 42}, {"payload": "text"}, {"payload": {"user": "ada", "tags": ["a", "b"]}}],
)
print(conn.execute(select(events.c.payload).order_by(events.c.id)).scalars().all())
[42, 'text', {'tags': ['a', 'b'], 'user': 'ada'}]
(A VARIANT object's keys come back in sorted order.)
Struct, Map and Union columns read as Python values (dict, a list of pairs, the member's
value) and are written with SQL expressions such as func.struct_pack(...). A VARIANT integer
past 64 bits comes back as a float (it travels as JSON); HUGEINT, UHUGEINT and BIGNUM columns
hold such integers exactly.
Integer results are Python ints, including sum() over an integer column (a HUGEINT in
DuckDB). Integer parameters are sent as INTEGER, so func.round(column, 2) and
date_column + 5 work as they do with a literal.
Transactions and the connection pool
- DuckDB transactions run under snapshot isolation:
isolation_levelis"SNAPSHOT"(the default) or"AUTOCOMMIT". DDL is transactional. - A transaction begins at the first statement, not when a connection is checked out, and a connection returned to the pool holds none open. A connection that ran nothing sends nothing.
- Two transactions that write the same rows conflict: the second gets an error and retries after a
rollback. There are no row locks, so
SELECT ... FOR UPDATEruns as a plainSELECT. - There are no savepoints:
begin_nested()raises, with that reason. - The server ends a session that sits idle. The driver opens a new one for the next statement,
with the URL's catalog and schema current again, so an idle pool keeps working. A session whose
state was changed with
SETorUSEin SQL is not replaced silently: its next statement fails as a disconnect, and the pool opens a fresh connection. - A session the server ended for good (an administrator's
KILL SESSION, a revoked token) is treated as a disconnect: the pool discards the connection and opens another. - Use the connection's
commit()androllback().BEGIN,COMMITandROLLBACKsent as SQL text are followed (a rawBEGINon anAUTOCOMMITconnection is rolled back when the connection returns to the pool), but only as whole statements of their own. - On a GizmoData Cloud cluster that stops when idle, leave
pool_pre_pingoff: a ping is a statement, and a statement starts the cluster.
Alembic
Migrations work with no extra setup: importing the dialect registers it with
Alembic. Each migration runs in a transaction. Autogenerate
compares types as DuckDB stores them (String(50) and Text are both VARCHAR) and matches
unique constraints by their columns, since DuckDB names constraints itself. op.drop_table()
also drops the sequence that numbered the table's key. Operations DuckDB has no statement for
fail as they would by hand: dropping or adding a constraint on an existing table, and altering a
table that has an index (see below).
Good to know about DuckDB
These are properties of DuckDB 2.0 that differ from PostgreSQL. Each is pinned by a test in
tests/integration, so this list stays true.
- Foreign keys can't
CASCADE,SET NULLorSET DEFAULT, and can't be added to an existing table (ALTER TABLE ... ADD CONSTRAINT), so tables that reference each other in a cycle can't be created. - Deleting a parent row and its children takes two transactions: delete and commit the children, then delete the parent. DuckDB checks the foreign key against rows deleted earlier in the same transaction. In a table that references itself, a row another row points at can't be deleted in the same statement as that row, and its key columns can't be updated.
- Constraint names are DuckDB's own (
<table>_<columns>_pkey,_key,_fkey,_check): the name a constraint was declared with isn't kept, so reflection returns DuckDB's. - A table with an index takes few alterations: adding a column works; renaming, retyping or dropping one needs the index dropped first.
- Generated columns are virtual (
Computed(..., persisted=True)is refused). - One transaction writes to one catalog. Writing to a second attached catalog in the same transaction fails; commit between them.
- Bulk ORM inserts that need server-generated keys back run one
INSERT ... RETURNINGper row, because DuckDB doesn't promise the orderRETURNINGgives a multi-row insert's rows in. Give the model a client-side key (default=uuid.uuid4) or use Coreinsert()with a list of rows where that matters.
The driver underneath
connection.connection.driver_connection is the Hardcharger ADBC connection, with its Arrow
interface and Hardcharger helpers:
import pyarrow as pa
from sqlalchemy import create_engine, text
engine = create_engine("hardcharger://hardcharger:change-me@localhost:31337?tls=false")
with engine.connect() as conn:
driver_connection = conn.connection.driver_connection
table = pa.table({"id": list(range(100_000)), "squared": [i * i for i in range(100_000)]})
with driver_connection.cursor() as cur:
cur.adbc_ingest(table_name="squares", data=table, mode="create")
cur.execute(operation="SELECT max(squared) AS biggest FROM squares")
print(cur.fetch_arrow_table().to_pydict())
print(conn.execute(text("SELECT count(*) FROM squares")).scalar())
{'biggest': [9999800001]}
100000
Development and testing
uv venv && uv pip install -e ".[dev]"
make lint # ruff and mypy
make test # unit tests, and integration tests against a real Hardcharger server
make suite # SQLAlchemy's own dialect compliance suite, against a real server
make readme # runs every example in this README and compares its output
The hardcharger development dependency downloads the released server binary on first use and
runs it on a free port; HARDCHARGER_SERVER_BINARY names another build to test. A server is
required: the integration tests, the compliance suite and the README examples fail, never skip,
when it can't start.
CI runs all of it on Python 3.11–3.15 and on both SQLAlchemy series, once more with the lowest
dependency versions pyproject.toml allows, and publishes from version tags with PyPI Trusted
Publishing only after every job has passed on the tagged commit. See
release setup.
License
Apache-2.0. Built on SQLAlchemy (MIT), whose PostgreSQL compilers the dialect extends. Hardcharger and GizmoData are trademarks of GizmoData.
Metadata
Release files for sqlalchemy-hardcharger 1.0.0
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | Provenance | Actions |
|---|---|---|---|---|
| sqlalchemy_hardcharger-1.0.0.tar.gz | 222.3 kB | With provenance | Details for sqlalchemy_hardcharger-1.0.0.tar.gz |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| sqlalchemy_hardcharger-1.0.0-py3-none-any.whl | Python 3 | none | any | Details for sqlalchemy_hardcharger-1.0.0-py3-none-any.whl |
Total release size: 265.6 kB
Release files / sqlalchemy_hardcharger-1.0.0.tar.gz
| Download URL | sqlalchemy_hardcharger-1.0.0.tar.gz |
|---|---|
| Size | 222.3 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
546413ae9595018eabd590ba16a37e966a6be8f1760d58444af35e9e21a90fd8
|
|
BLAKE2b-256 checksum How to use checksums |
21cbf12973b2f7cc1477936913a71754eefdf0e2dc4a263f2b2625478a564698
|
| 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 13, 2026.
Transparency logRelease files / sqlalchemy_hardcharger-1.0.0-py3-none-any.whl
| Download URL | sqlalchemy_hardcharger-1.0.0-py3-none-any.whl |
|---|---|
| Size | 43.3 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
7d104a70792553f231f8912bc1a65b702d4d334feb7f2b30adb8872dabf994b8
|
|
BLAKE2b-256 checksum How to use checksums |
96b82a61e47041a5947c9a31c2ef67e030f77d1d84c5554de6dc39c3d52e98ce
|
| 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 13, 2026.
Transparency logSecurity for sqlalchemy-hardcharger 1.0.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.
Attestation for sqlalchemy_hardcharger-1.0.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 13, 2026.
Transparency logA 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.