APSW in colour (async)

What is async?

A common problem for programmers is how to run several tasks at the same time, such as some database, network, and file access. You don't want to do them one after the other because that will take the sum of the times of the individual items.

You will eventually end up with a solution that lets you start the multiple tasks and wait for them all to complete, such as Python's concurrent.futures. It works, but quickly gets clumsy when you want to add timeouts, and cancelling tasks if their peers failed.

The "secret" about code that does networking is that it spends most of its time doing nothing, just waiting for more data to be received and responses to be sent. Rather than use heavy weight threads and processes, it instead uses an event loop in a single thread managing task progress with incoming and outgoing data, as well as timeouts. In the olden days that meant a loop using select, and its modern version io_uring. Writing code with this is painful though, especially when network code can go through multiple phases tracking their context, any of which could error. It made code hard to read, hard to write, impossible to debug, and had names like callback hell and pyramid of doom.

A new pattern emerged called async / await. The idea is that calls don't return the result directly, but rather something you can await when you need the result or error. It is easier to read, write, debug etc.

# old way - does them one after the other
response1 = fetch("url1")
response2 = fetch("url2")
response3 = fetch("url3")

# new way - ask an async framework to start them all first ...
task1 = asyncio.create_task(fetch("url1"))
task2 = asyncio.create_task(fetch("url2"))
task3 = asyncio.create_task(fetch("url3"))

# ... and then process the responses as
# they complete
response1 = await task1
if response1.status == 200:
    # etc
    ...

But this has an important consequence. fetch in the first block returns a direct result, while in the second block it returns something that has to be await-ed. In language implementations it also required functions like fetch to be marked as async because they needed different internals, and there are restrictions because only async functions can work with an event loop.

This led to the humorous (to developers) post What Colour is Your Function. It describes the world today for many programming languages including Python where you have to colour your functions.

Consequences

It is very common for there to exist libraries providing some functionality, and a parallel variant that provides an async interface to the same functionality. Often the async version just calls the non-async version in the background, while providing equivalent async marked APIs.

But this is a lot of work - the async variant has to essentially duplicate the API surface of the non-async library, and keep it in sync. In practise that means the async ones have a subset of the API, and lag changes. Even documentation and type information can lag, because this is a thankless work.

A few libraries now do it the other way around, writing the async version as the primary, and then having a secondary library to make sync versions of the same code. It is still a lot of work and duplication.

Async Python SQLite

The Python standard library provides sqlite3, but it isn't async.

Several folks have made async versions that call it, with these being the ones I found: aiosqlite, aiosqlite3, asqlite, py-asqlite3, asqlite3, fastsqlite3, rapptz-asqlite, anysqlite3, sqlite-anyio, anyio-sqlite, anysqlite

There are multiple Python event loops including asyncio (part of Python standard library), trio (a better API), curio (OG that recently became EOL), and anyio (abstracts the actual implementation). The various async versions support different subsets.

Behind the scenes, the implementations are the same. Provide an async function that gathers its parameters, and send them to a background thread for execution, providing an awaitable result. Some go further and provide iteration of query results, so you can use async for. None of them provide the full API of the sqlite3 module.

And none of them let you use an async function in the numerous callbacks SQLite provides. So for example you can't register a function named fetch and then call it from SQL.

The existence of so many async SQLite wrappers shows the demand is there, but the maintenance, event loop support, and Python version updates over time is hard work and hard to keep up.

APSW async principles

I had been considering providing async APSW for a while, and kept thinking about the trade-offs and functionality. That led to a list of principles.

Virtual tables

Virtual tables are the natural case for async. The examples given are to fetch information via network requests to the cloud, and that is a perfect match for async. Note this is calling async code from APSW, not APSW being async itself, but the user would want both at the same time.

No separate package

I am not going to publish a separate async-apsw or similar package. It would require separate documentation, separate maintenance, a system could end up with different versions of sync and async APSW installed etc.

Complete

I am not going to do a small subset of the APSW API. It is important to me that everything you can do from C code with SQLite, you can do from Python code using APSW. That means you should be able to do all the things in async too.

Worker thread

The SQLite C code is inherently synchronous and does not have non-blocking or cooperative scheduling APIs. The only practical approach is to run the SQLite code in a background worker thread. It is possible to have pools of threads and not care which one is used for each call, and APSW is thread safe for this, but that makes things harder to debug, document, and test. It would also result in deadlocks on re-entrant calls. So one thread is the best solution, especially as it ensures a predictable order of operations.

Ergonomic

A common pattern with async wrappers is having different names for doing the same thing as sync versions. So if a developer wants to register a callback there would be register_callback method and a register_async_callback method. Wherever possible that should be avoided, because code should be able to figure it out.

Developers using the sync interface must continue to find using the APSW interface simple and pleasant. That must also be the case for async developers.

Automated maintenance

I am not going to do duplicate manual work. As each SQLite release adds and updates APIs, I have to manually update the APSW C code to reflect that. But I really am not going to repeat the work again for async.

APSW has extensive checking code and tooling behind the scenes for argument parsing, error handling, SQLite mutex handling, database of all objects, methods, attributes, docstrings, and more. I often deliberately update code wrong to verify the tooling detects problems. It must also catch issues with async code too.

What I dreaded

It seemed like the approach would be an automated version of what the other wrappers wrote by hand. For example for Connection.set_busy_timeout I'd generate something like this for an AsyncConnection class.

async def set_busy_timeout(self, milliseconds: int) -> None:
    # there would have to be a call to send execution to the worker
    # thread (exec_in_thread in this case).  The AsyncConnection
    # would have the underlying sync connection as a member,
    return self._exec_in_thread(
        self._sync_connection.set_busy_timeout,
         milliseconds)

But it is ugly. I want immediate exceptions for arguments such as passing a string instead of an int for milliseconds. And I'd have a very hard time verifying the code is correct with automated tooling. It gets more complicated adding in cursors, blobs, backups, sessions etc. (Again notice how the human written async wrappers omit virtually all of those.)

I also have the vast majority of the coding of APSW in C. This results in better performance because of the direct connection of the Python C API and the SQLite C API, but also I extensively use statement expressions in debug and testing builds to verify the integrity of Python and SQLite objects, mutex and GIL state, etc. Those are not visible at the Python level.

No arbitrariness

Unless there are overwhelming good reasons, there shouldn't be limitations. They are annoying to test, annoying to document, annoying to developers. For example that means you should be able to have sync and async connections in the same process. You should be able to use asyncio, trio, anyio, or anything else that comes along in the future.

I should provide defaults, but where more specialised behaviour is needed it should be possible to do so.

It isn't easy

Doing it right and complete is hard. The first 90% is trivial - generate code like above. But the last 10% is the hard part and what has to be right. I look through the issues and pull requests of existing similar projects to see what the edge cases and feature requests are. It is important to look at the closed issues too. This has saved me several times from bad design decisions, and shows what should be covered in the test suite. For example here are the issues for the popular aiosqlite project.

It begins

My first test is what happens if I provide an async function as a callback? The answer was that calling it gave back a coroutine object. At the C level they can be detected by calling PyCoro_CheckExact. A call to asyncio.run_coroutine_threadsafe will send that to the event loop, and let me wait for the result.

static PyObject*
Connection_something(PyObject *self, PyObject *const *fast_args, Py_ssize_t fast_nargs, PyObject *fast_kwnames)
{
    /* Sets an exception and returns if connection is closed */
    CHECK_CLOSED();

    {
        /* tool generated and updated code that converts
           parameters into C types goes here */
    }

    /* check database mutex can be acquired in order to
       correctly handle concurrency */
    DBMUTEX_ENSURE();

    /* do the actual calls into SQLite here */

Success

It works, it is robust, it is easy to use, and meets all the original goals, especially future maintenance as SQLite and Python update their C APIs. The final result provides complete async support across the entire APSW API, including callbacks, virtual tables, and advanced SQLite features. The implementation supports asyncio, Trio, and AnyIO without requiring separate packages or duplicated APIs.

This is ably demonstrated by the tour.

Thanks