Enhancement: add aiomysql adapter

[hasansezertasan]

Summary

sqlspec currently ships three MySQL/MariaDB adapters: asyncmy (async, Cython), mysqlconnector (sync, Oracle's official connector), and pymysql (sync, pure-Python). This proposal is to add a fourth adapter for aiomysql, an async MySQL driver built on top of PyMySQL and maintained under the aio-libs umbrella.

I want to flag upfront that asyncmy already covers the "async MySQL" slot, so this is as much a question as it is a request: is there appetite for a second async MySQL adapter, and if so, under what conditions?

Why this comes up in practice. Teams don't always get to pick their MySQL driver from a clean slate — aiomysql is frequently inherited through pre-existing design decisions, framework conventions, or dependencies already in the lockfile. Asking those teams to swap to asyncmy just to adopt sqlspec is a real friction point, even when asyncmy would be the technically superior choice on a greenfield project. Concretely, reasons aiomysql tends to stick around include:

1. Inherited codebases. aiomysql predates asyncmy and is the driver that SQLAlchemy, Tortoise ORM, and large swaths of the aio-libs ecosystem documented first. A lot of production code was written against it before asyncmy existed, and "rip out the driver" is rarely a small change.
2. Pure-Python runtime. No Cython extension, which matters for environments where asyncmy's wheels are unavailable or its build step fails (PyPy, some musl/Alpine images, air-gapped installs).
3. aio-libs governance alignment. Shops that standardize on the aio-libs family (aiopg, aioredis, etc.) for consistency reasons.
4. SSCursor / server-side streaming. aiomysql exposes PyMySQL's SSCursor for streaming large result sets without buffering, which could map onto sqlspec's existing streaming story.

Supporting aiomysql would let teams in situation (1) adopt sqlspec without a driver migration as a prerequisite.

Basic Example

from sqlspec.adapters.aiomysql import AiomysqlConfig

config = AiomysqlConfig(
    pool_config={
        "host": "localhost",
        "port": 3306,
        "user": "app",
        "password": "secret",
        "db": "app_db",
    }
)

async with config.provide_session() as session:
    result = await session.execute("SELECT id, name FROM users WHERE active = ?", (True,))
    for row in result:
        print(row)

Shape would mirror the existing asyncmy adapter for consistency.

Drawbacks and Impact

• Maintenance surface. Another adapter means another set of integration tests, another driver to keep up with upstream releases, and another CI matrix entry.
• Overlap with asyncmy. Users now face a "which async MySQL driver?" decision. Docs would need to explain the trade-off clearly (performance vs. portability/compatibility).
• aiomysql release cadence. Historically slower than asyncmy — worth confirming current maintenance status before committing.
• Test infra. Existing MySQL container fixtures should be reusable — likely low marginal cost for CI.

Unresolved questions

1. Is there maintainer appetite for a second async MySQL adapter at all, or should users with aiomysql-specific needs be directed to stay on asyncmy?
2. If accepted, should the adapter target feature parity with asyncmy from day one, or ship a minimal first cut and grow?
3. Should SSCursor streaming be part of the initial scope, or deferred?
4. Does the project have a stated policy on "one adapter per driver category," or is overlap acceptable when justified by portability?

---

Disclosure: This issue was drafted with AI assistance (Claude Code) based on my motivation and direction. Authored by @hasansezertasan — I take responsibility for its content and will engage with any follow-up discussion.