Using async drivers for Oracle and SQL Server

Oracle is reachable with oracledb in async mode and SQL Server with aioodbc — but check which is native and which wraps a synchronous driver in a thread pool, because a thread-backed driver gives the async API without async concurrency, and its limit is the thread pool rather than pool_size. This guide belongs to selecting async drivers for SQLite, MySQL and Postgres.

Quick Answer

Both backends have an async path, and both need their connection details spelled out more explicitly than PostgreSQL does.

Oracle, with oracledb in async mode:

from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

# The async URL token for oracledb was added in SQLAlchemy 2.0.25; check your version's
# documentation, and pin the version you tested against.
engine = create_async_engine(
    "oracle+oracledb_async://shop:secret@db.internal:1521/?service_name=ORCLPDB1",
    pool_size=5,
    max_overflow=5,
    pool_pre_ping=True,
)
Session = async_sessionmaker(engine, expire_on_commit=False)

SQL Server, with aioodbc:

import urllib.parse

from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

odbc = urllib.parse.quote_plus(
    "DRIVER={ODBC Driver 18 for SQL Server};"
    "SERVER=db.internal,1433;DATABASE=shop;"
    "UID=shop;PWD=secret;"
    "Encrypt=yes;TrustServerCertificate=no;"
)
engine = create_async_engine(
    f"mssql+aioodbc:///?odbc_connect={odbc}",
    pool_size=5,
    max_overflow=5,
    pool_pre_ping=True,
)
Session = async_sessionmaker(engine, expire_on_commit=False)

The odbc_connect form is worth using for SQL Server rather than assembling a URL: the ODBC driver name contains spaces and braces, and quoting the whole connection string once avoids a class of parsing problems. Above the engine, AsyncSession and every query construct behave exactly as they do on PostgreSQL.

Execution Context & Async Workflow Integration

The most important distinction between async drivers is not which database they talk to but whether they are natively asynchronous.

asyncpg implements the PostgreSQL wire protocol on asyncio directly: twenty concurrent queries are twenty sockets that one thread waits on. oracledb in its thin mode does the same for Oracle. Both scale with the connection pool and the database.

aioodbc and aiosqlite are wrappers: they run a synchronous driver — pyodbc and the standard library's sqlite3 — in a thread pool, and present an asyncio interface over it. The API is identical and the concurrency model is not. The number of queries that can be in flight is the number of worker threads, and connections beyond that sit idle while requests queue inside Python. Raising pool_size above the thread pool buys nothing.

Three consequences follow for a thread-backed driver. Size the thread pool deliberately rather than relying on the default, which is derived from the CPU count and is usually unrelated to how many database connections you want. Keep pool_size at or below the thread pool. And measure under concurrency, because a sequential benchmark cannot show the ceiling — the shape to look for is throughput that stops improving as concurrency rises, described in benchmarking asyncpg vs psycopg throughput.

Maturity is the other factor worth weighing honestly. SQLAlchemy's asyncio support was built around asyncpg, and that path carries by far the most production traffic. The Oracle and SQL Server async dialects are newer and less travelled, which does not make them unusable — it makes pinning versions, running your own integration tests, and reading the changelog before upgrading more important than usual.

Everything above the engine is unchanged. AsyncSession, loader options, begin_nested(), stream() and the pool all work the same way, which is the point of the abstraction: the driver choice does not reach the application's query code, only its configuration and its concurrency ceiling.

Resolving Warnings, Errors & Common Mistakes

The empty-string behaviour is the Oracle difference most likely to change application code, because it is silent. Oracle has no distinction between '' and NULL for VARCHAR2, so a form field submitted empty becomes NULL, a NOT NULL constraint rejects it, and a query for = '' never matches. Application-level normalisation is the only reliable answer:

from sqlalchemy.orm import validates


class Customer(Base):
    __tablename__ = "customers"
    # ...

    @validates("middle_name", "company")
    def _empty_to_none(self, key: str, value: str | None) -> str | None:
        return value or None       # store NULL rather than '' consistently

Doing it explicitly means the same code behaves identically on PostgreSQL, where '' and NULL are different values — which matters if the application supports both backends, and matters even more if it is being migrated from one to the other.

Feature availability is the other thing to check before designing around it. RETURNING exists on modern Oracle and on SQL Server as OUTPUT, with restrictions — SQL Server refuses OUTPUT on a table with certain triggers, for instance — and SQLAlchemy's bulk-insert optimisation adapts accordingly. Upserts differ too: Oracle has MERGE, SQL Server has MERGE with well-documented concurrency caveats, and neither maps onto PostgreSQL's ON CONFLICT as used in writing Postgres ON CONFLICT DO UPDATE upserts.

Advanced: The to_thread Alternative, and Pooling Choices

When the async driver for a backend is thread-backed anyway, running the synchronous driver explicitly through asyncio.to_thread() is a legitimate and sometimes better option: the driver is the mature one everyone tests, and you own the thread pool.

import asyncio
from concurrent.futures import ThreadPoolExecutor

from sqlalchemy import create_engine, select
from sqlalchemy.orm import Session, sessionmaker

from shop.models import Order

sync_engine = create_engine(
    "mssql+pyodbc:///?odbc_connect=" + odbc, pool_size=8, pool_pre_ping=True
)
SyncSession = sessionmaker(sync_engine, expire_on_commit=False)
executor = ThreadPoolExecutor(max_workers=8, thread_name_prefix="db")


def _open_orders(customer_id: int) -> list[dict]:
    with SyncSession() as session:
        rows = session.execute(
            select(Order.id, Order.total_cents)
            .where(Order.customer_id == customer_id, Order.status == "open")
        ).mappings().all()
        return [dict(row) for row in rows]


async def open_orders(customer_id: int) -> list[dict]:
    loop = asyncio.get_running_loop()
    return await loop.run_in_executor(executor, _open_orders, customer_id)

Three properties make this attractive for a secondary backend. The executor size and the pool size are matched explicitly, so the ceiling is visible in the code. The function returns plain data rather than ORM objects, which avoids passing session-bound objects across the boundary. And the driver is the one with a decade of production behind it.

The cost is that you now have two programming models in one codebase, and every call site has to remember the boundary. That is manageable when the backend is secondary — a legacy Oracle system a service reads from — and unpleasant as the primary data store. The general form of this bridge, in both directions, is covered in calling async SQLAlchemy from synchronous code.

Pooling also deserves a decision per backend. Oracle's client libraries have their own session pool, and oracledb can use it; layering SQLAlchemy's pool on top of a driver pool means two pools with two idle timeouts and two sets of statistics. Pick one — SQLAlchemy's, usually, so that pool_pre_ping, pool_recycle and the checkout metrics behave the way they do everywhere else in the application.

For SQL Server, the ODBC driver maintains connection pooling at the driver manager level on some platforms, which can interact with SQLAlchemy's pool in the same way. Disabling ODBC pooling and keeping SQLAlchemy's is the arrangement that is easiest to reason about, and the one whose behaviour matches the rest of this section's guidance on configuring async engines and connection pools.

Testing Against a Second Backend

A codebase that supports two backends needs its tests to run against both, because the differences are exactly the things no amount of reading catches. Three practices make that affordable.

Parametrise the engine fixture over the backends you support, and mark the tests that cannot run everywhere:

import os

import pytest
import pytest_asyncio
from sqlalchemy.ext.asyncio import create_async_engine

BACKENDS = {
    "postgresql": os.environ.get("TEST_POSTGRES_URL"),
    "oracle": os.environ.get("TEST_ORACLE_URL"),
    "mssql": os.environ.get("TEST_MSSQL_URL"),
}


@pytest_asyncio.fixture(params=[name for name, url in BACKENDS.items() if url])
async def engine(request):
    engine = create_async_engine(BACKENDS[request.param])
    engine.info["backend"] = request.param
    yield engine
    await engine.dispose()


postgres_only = pytest.mark.skipif(
    os.environ.get("TEST_BACKEND") != "postgresql",
    reason="uses PostgreSQL-specific features",
)

Skipping by feature rather than by backend name is better still: a test marked "needs JSONB" documents why it does not run, and keeps working when another backend gains the feature.

Write the portability tests that matter. Four behaviours are worth asserting per backend, because they are where the differences show up: an empty string round-trips as expected; a generated primary key is available after flush; a NULL sorts where the application assumes; and a Decimal keeps its scale. Each is three lines and each has caught a real difference.

Keep backend-specific SQL behind a boundary. Anything using JSONB, ON CONFLICT, FILTER or window frames needs a portable fallback or a clear statement that the feature is PostgreSQL-only. The cleanest arrangement is a small module per backend implementing one interface, so the choice is visible in one place and the rest of the application is backend-agnostic.

Finally, be realistic about what supporting two backends costs. Every feature is designed twice, tested twice, and constrained by the less capable of the two — which for PostgreSQL-specific features like JSONB and row-level security means giving up a lot. Supporting a second backend is worth it when a customer contract requires it, and a large ongoing tax when it was adopted "in case we need it". The single-backend alternative, with the capabilities that come from committing, runs through the rest of this site's guidance on advanced query patterns.

Frequently Asked Questions

Related

• Selecting Async Drivers for SQLite, MySQL and Postgres — The parent guide: driver options per database.
• Enabling SQLite foreign keys and WAL mode with aiosqlite — The other thread-backed driver, and its pragmas.
• Choosing between aiomysql and asyncmy for async MySQL — The MySQL equivalent of this decision.
• Calling async SQLAlchemy from synchronous code — Crossing the boundary in the other direction.