Polars ADBC write_database commits inside the call: autocommit behavior, transaction control, and SQLAlchemy alternative

Learn why Polars with engine='adbc' commits during write_database, how autocommit really works, and how to get proper transaction control using SQLAlchemy.

Coordinating multiple writes in one transaction is a common requirement: either every change lands in the database, or none of them do. When using Polars with engine="adbc", it’s natural to expect that disabling autocommit and calling commit() at the end would deliver exactly that. Yet, writes still appear immediately, one after another. Here is why that happens and how to work with it without surprises.

Minimal example

import adbc_driver_postgresql.dbapi as pgx
import polars as pl
link = pgx.connect("postgresql://username:password@host:port/database")
frame = pl.DataFrame({"a": [1, 2, 3], "b": [4, 5, 6]})
frame.write_database(
    "public.table1",
    connection=link,
    engine="adbc",
)
frame.transpose().write_database(
    "public.table2",
    connection=link,
    engine="adbc",
)
link.commit()

What’s really going on

According to PEP 249 (Python DB-API 2.0), autocommit must be off by default. The ADBC driver follows that rule. You can see this reflected in the connection class signature and by checking the option dynamically: even if you don’t pass anything, autocommit starts off. If you do pass an override, the value must be the exact string "false" (not a boolean), and it is case-sensitive.

import adbc_driver_postgresql.dbapi as pgx
import polars as pl
conn = pgx.connect(
    "postgresql://username:password@host:port/database",
    conn_kwargs={"adbc.connection.autocommit": "false"}
)
print(conn.adbc_connection.get_option("adbc.connection.autocommit"))  # expected to be 'false'

Even more, if you pass "true" on connect, the connection will still behave as if autocommit is off until you explicitly enable it afterward:

conn.adbc_connection.set_autocommit(True)

This is where the key detail surfaces. Polars performs an internal commit inside DataFrame.write_database() when engine="adbc". If autocommit is enabled, that internal commit fails because committing with autocommit already on is invalid. That’s how the internal commit becomes visible:

adbc_driver_manager.ProgrammingError: INVALID_STATE: 
[libpq] Cannot commit when autocommit is enabled

It isn’t advertised in the public API docs, but the Polars source confirms that when engine is adbc, write_database() calls commit() before returning, and there’s no parameter to switch that behavior off.

The root cause

The database driver is not silently committing behind your back. Autocommit begins off and remains off unless you turn it on manually. The immediate writes are the result of Polars calling commit() internally during write_database() for the ADBC engine. Disabling autocommit on the connection won’t help because the commit is triggered by Polars itself after each write.

Trying to pass engine_options={"autocommit": False} won’t change anything either. Those options are forwarded to the ADBC ingestion path, which doesn’t accept a parameter by that name.

Ways to proceed

If you need a multi-statement transaction that you control end-to-end, there are two viable paths. One is to change the behavior in Polars at the source level by removing the internal commit in the ADBC write path. The other is to switch to the SQLAlchemy engine for this operation. With engine="sqlalchemy", write_database() routes through pandas.DataFrame.to_sql(), which respects the current transaction state: if you supply an SQLAlchemy connection that is already in a transaction, it will not commit on your behalf. In practical terms, this means you can issue several writes and then decide when to commit or roll back.

# Conceptual example of the SQLAlchemy path (transaction handling respected)
# frame.write_database("public.table1", connection=sa_conn, engine="sqlalchemy")
# frame.transpose().write_database("public.table2", connection=sa_conn, engine="sqlalchemy")
# sa_conn.commit()  # commits only if you decide to

Why this matters

Atomicity across multiple tables is a reliability baseline for data pipelines. If one write succeeds and the next fails, you risk partial state and hard-to-debug inconsistencies. Understanding where a commit happens prevents surprises in production and preserves transactional guarantees you intended to rely on. It also clarifies that toggling autocommit is not the lever to pull here; the behavior lives in the calling library, not the driver.

Conclusion

If you need to manually control when a batch of Polars writes becomes visible, avoid the ADBC engine path that commits internally. Use engine="sqlalchemy" for transactional control, or modify the ADBC write behavior in Polars if you maintain that code. When inspecting connection state, remember that the ADBC option key is adbc.connection.autocommit and values must be exact strings like "false". Finally, don’t rely on engine_options to force autocommit off for ADBC ingestion; it won’t be recognized.