What does the code look like?

There is a Python object that corresponds to each SQLite object. For example this is an APSW Connection and the corresponding SQLite object. For each SQLite function on the object, there is a corresponding Python function or attribute. That code is written in C and looks like:

• Check if the object is closed and exception
• Parse the function arguments so that they get converted from Python types (abstract and concrete) into C types as used by SQLite
• Check if this is an async connection, and if so forward to the corresponding async controller which sends the call to a worker thread
• Acquire the database mutex to prevent concurrent usage conflicts
• Further conversion of types, or initiate usage of more complex ones like buffers
• Make the SQLite call
• Do housekeeping saving and updating values stored in the connection
• Convert the SQLite results back to Python objects
• Release the database mutex
• Return a Python result

Every Python call in all those conversions and usages can error because Python is a dynamic language, and those callbacks can do anything such as mutating objects, closing them, raising exceptions, taking a long time, running garbage collection, releasing the interpreter lock, etc.

Adding a typical method is around:

• 6 lines describing the arguments
• 1 line handling async
• 1 line to acquire and 1 to release the mutex
• 10 lines of conversions, including trivial error handling
• 1 line to call SQLite
• 50 lines to handle all the errors that could occur
• 20 lines of documentation
• 10 lines of test cases using the functionality
• 100 lines of test cases doing things wrong and making sure they are handled

Some are far more complex than this, involving callbacks, dealing with the Python interpreter lock, multiple SQLite calls needed, etc. It gives an idea of how little code there is to do the call, and just how much extra is needed for errors - an order of magnitude or more.

But APSW shouldn't have bugs ...

All code has bugs. It is somewhat hard to measure, because bugs are found while writing code, when testing is added, by pre-release testing, by users and customers, and importantly exist but are not recorded. If a bug is never encountered, or encountered but never realised, does it exist? This doesn't even address what a bug is. Depending on what you count, studies find bugs ranging from one every 10 lines, to one every few hundred lines.

The software development process tries to get zero bugs. APSW goes further than most, and here are the things that are done:

• Attention to detail paid during decades of development
• Maximum warnings from compilers
• Maximum warnings from Python
• Use a debug configuration of SQLite that does a lot more verification
• Use a debug configuration of Python that does a lot more verification
• Several APSW tools that produce a debug configuration - for example every SQLite call is annotated if it requires a mutex, and that is checked at runtime
• Use of compiler sanitizers which check every memory access for misuse
• Test suite that aims for 100% coverage of the C and Python code. It gets around 99%.
• The test suite includes code inspection tests - for example that every method and attribute of connections, cursors, blobs etc have a check for them being closed. There is separate code that calls after close to check again.
• Runtime testing with all the configurations, such as compilers, Python versions, 32/64 bit, Windows / Linux / MacOS. This is almost 200 combinations done during development repeatedly, and another 80 or so done for the release process
• An open development process and bug tracker so anyone else finding something can find and report them

The irony is that the simplest thing is by far the hardest to test. Every C programmer learns about calling the memory allocator and getting NULL (a glorified zero) back indicating you have run out of memory. The vast majority of APSW memory consumption directly and indirectly is in small dribs and drabs - 16 bytes here, 64 bytes there, such as adding an item to a list. In practise it is highly improbable that the specific lines of code would ever get an allocation failure. And even if they did, nothing useful could happen because reacting to it such as writing a log message would need more than that amount of memory!

If you look at most projects that have error handling code, realise that you'll see the error detection then handling pattern, but that that code has never actually run in practise because the tests can't make small memory allocations fail in that code or the code it calls. Asking a Python tuple for one of its members could fail, but making that happen involves writing a modified tuple class to do that, and orchestrating it with the test code - a lot of extra work.

So APSW has a fault injection framework to make sure all the code actually runs. It has a list of APIs, the way in which they fail (some return -1, some zero etc) and a mechanism for each call site to function normally, or simulate the appropriate error. That results in several thousand failures executed during testing. It includes one of the most difficult categories - failures in failure handling code while failures are happening.

So APSW shouldn't have bugs.

APSW does have bugs

Those reports make it clear bugs exist and are plentiful. I have now fixed almost 200 individual items, and more still await.

Why weren't the bugs already caught?

They should have been, and that is why I fixed all of them by hand. That lets me understand what the bug was, but far more importantly why it wasn't caught, and what could also be updated to detect or prevent that entire class of bug.

Why did I fix them?

Because I want to learn. I need to understand what happened and why. I want software to be better, and easier to develop. I have provided a lot of feedback to the folks whose tools produced the reports. And I have learned.

The fixes allow two things:

Final thoughts

SQLite is a great database, and Python is a great programming language. APSW unites them, and does the best job possible.

Sorry about the bugs - they are being fixed.

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

Genesis

In 2003 I got a new cell phone back when they were very small screens and physical keyboards of only the numbers. Trying to maintain contacts and other information was painful so I ended up creating BitPim which did all the work over a USB cable. It was implemented in Python using wxPython for the GUI.

Behind the scenes it needed to store information about contacts, calendars etc so I used the most expedient format possible - a Python dictionary (keys and values). It wasn't very large (many phones had limits of 100 contacts and calendar events).

It was trivial to read and write the dictionary to disk as is, and substantially similar to JSON. That also made it easy to edit, backup, compare etc outside of BitPim. But two things annoyed me...

1. You did manual save and load from the menus. That was completely normal for programs of the time, where the user was expected to manage the movement of data storage between permanent (ie still present after power events and reboots) and transient (ie in the memory of the process only). This sort of thing should be automatic, and thankfully is the majority of time these days.

2. I wanted undo and redo, but not just the simple approach commonly taken. I wanted to see what the data looked like at any point in time, a simple example being to see what a contact's phone number was 7 months ago. No information should ever be lost or deleted, and you should easily be able to recover older values.

It was getting later in 2004 and I had to solve this. The solution was obviously structured data, which meant a database.

Storage Options

The various networked databases were not an option. They were large and feature full, required complex configuration including administrators, users, and permissions, and dwarfed the size of BitPim. Installation instructions would be a nightmare especially since BitPim is cross platform and easy to install and run.

There were a family of local key value stores generally called dbm, which in theory could work, but would involve creating my own layer on top to structure and version data, execute queries, and similar work. In addition dbm often weren't available on Windows.

SQLite

The first release of SQLite in late 2000 had used dbm as the storage backend and added a layer of SQL on top. SQLite was intended as a developer alternative when the "real" database wasn't available. SQLite 2 released a year later dropped dbm for a custom btree storage engine. SQLite 1 and 2 only stored strings behind the scenes. (The hard part of storage is getting transactions right.)

In mid 2004 SQLite 3 was released and was a real local database, having learned the lessons from SQLite 2. It included:

• More effective file format
• Unicode strings
• Typed storage in the backend (ie not just strings), including binary objects
• Bound parameters
• Better transaction locking model
• Collations
• 64 bit rowids
• More advanced query optimisation
• Improvements in SQL compliance, error reporting etc

This was the obvious perfect fit for BitPim storage.

pysqlite

The existing Python binding to SQLite was named pysqlite. It had supported the earlier SQLite versions and upgraded to SQLite 3. (It was eventually adopted as the standard library sqlite3 module.) I started using it in BitPim, but quickly encountered several issues.

DBAPI

DBAPI / PEP249 defines a standard abstraction for accessing databases from Python, which does map well onto the common networked databases. It does not map well onto SQLite, so pysqlite had to resort to parsing SQL text to manage transactions. The exceptions didn't map well to SQLite's errors. I felt like I was fighting it just trying to do normal SQLite things, and had no interest in other databases.

Unicode

Unicode strings had been a new feature in Python 2 and SQLite 3, and pysqlite mostly got them right, there were a few places that were not, and doing so could cause backwards compatibility problems.

Threading

SQLite at that time could not be used across threads. Because BitPim had a GUI it meant the main thread was used for the GUI, and work was done in background threads. Even if you are diligent, destructors can run in any thread. It was important to me that cross thread usage was caught and became an error, and not silently ignored.

Ergonomics

The cursor was not an iterator so you couldn't use for row in cursor.execute('...') which was Pythonic. I added an iterator wrapper but it made the cursor a lot slower.

An annoyance (that still persists) is you could only execute one statement at a time. Multiple semi-colon separated statements in a query string gives an error.

Tracing and debugging

There was no support for tracing queries or returned rows. This is important during development.

Because SQLite does all its work in C, it is highly desirable to see what Python values it is working with especially when errors occur. You got no clue.

Error handling

Both SQLite and Python have error indicators. SQLite uses an integer code and error string, while Python uses a thread local pending exception. If a callback had an exception then all you got was an exception with the message user-defined function raised exception which was not useful. (Which function? What exception?) Even today all you get is this.

I'll write my own

It was apparent that many of these issues would not be addressed ever (DBAPI compliance was important), or could be done in the timeline I needed for BitPim. Since BitPim would only ever use SQLite, there was absolutely no need for abstractions, and I wanted to have every feature of SQLite available to me.

Principles

I didn't know it at the time, but some principles ended up happening over the years.

Source Code Control

I originally hosted everything on my web site, and used CVS for version control. Then things moved to SourceForge with Subversion source control. Then it was Google Code hosting with Mercurial source control. The final move was GitHub and git source control.

Despite all those transitions, full fidelity of the the source changes is available back to April 2006. The changelog goes all the way back to the beginning.

Documentation

The documentation was originally hand written HTML. It worked but meant there was no way to programmatically ensure it was complete and up to date. I was an early adopter of Sphinx which has been fantastic.

Behind the scenes I have comments in C source that are extracted to make documentation and help text, tools to check the entire SQLite API is wrapped, tools to help with type annotations in the doc, and several other doc related scripts.

Building

When I started the Python standard library included distutils which could be used for both compiled code and pure Python packages. The Python Packaging Authority now maintains a descendent for compiled code named setuptools which still works well for the purpose.

Perhaps the best thing they do is cibuildwheel which is able to compile and test the code under the supported Python versions, operating systems, chip architectures, and ABIs (eg musl vs glibc on Linux). It produces 50 different builds making it trivial for anyone with any of those combinations to install APSW.

Testing

For a few months I had a Python file that run exercising each of the APIs. I quickly moved to the standard library unittest and still use that (many third party frameworks have come and gone).

Very little of the testing code is about things that work, as both SQLite and Python fundamentally work and are themselves tested. The vast majority is API boundaries, and errors and exceptions. It gets especially gnarly because multiple exceptions can occur inside the top level call into SQLite (eg VFS error recovery, finalizers, callbacks). For Python 2 support I was stuck with unraisable errors, while, while Python 3 added chained exceptions.

I used coverage analysis of both Python and C code to make sure all the error/exception conditions were caused. That wasn't sufficient because there are functions such as allocating small amounts of memory, Python objects, or operations like appending to a list that are practically impossible to make fail. I used to have to wrap each location in a macro so that I could cause failure on demand, but that was tedious. It also meant the code didn't look normal, and was obfuscated for editors and analyzers.

Finally statement expressions came to the rescue, where I could macroize calls without having to do something manual in each location. The statement expression would query if that location (file, line, arg list) should fail and how, allowing my harness to then iterate through all failure locations. There are almost two thousand locations. This gets combined with a debug build of Python that does far more assertions and error checking to catch code issues - the most likely being calling into the Python C API while an exception is pending.

Because C code is involved, that means memory issues. I used valgrind to check everything was good. Its cachegrind mode is used for detailed profiling. Sanitizers significantly improved the testing because they saw the C level source code and so better catch issues - valgrind interprets the executable and so has to deduce what is going on.

Overall adding error handling to the code, and the corresponding testing is the vast majority of any code changes - usually three times as much as the actual normal code path. The good news is this gives a lot of assurance errors are handled well, even though most users are unlikely to ever encounter them!

A release test today involves:

• Running my checking tools that looks for code issues, missing SQLite APIs etc
• Running coverage to check all lines of C and Python (including the test suite itself) are run by testing
• 32 and 64 bit build and test of all supported Python versions on Windows, because it is most likely to have differences

• Megatest of all these permutations under Linux

  • 32 and 64 bit
  • Every supported Python version
  • All supported SQLite versions
  • Python in regular build, and in debug, assertions, all warnings build
  • APSW in regular and debug build (including SQLite debug/assertions configuration)
  • Default APSW SQLite configuration (everything on), and Debian default SQLite configuration (only some things on)

• The Github actions build then also tests the build on each operating system and configuration

It is very rare for the tests to catch any problems, and is delightful when they do.

Python code

For the longest time APSW was almost 100% C code. It was just gluing together the C APIs. Several years ago I realised that users were trying to do higher level things, and it was preferable to go beyond documentation, and also add that as a standard part of APSW.

I'm very pleased that best practices has been popular - almost all queries and code shows it being used.

The ext module is a nice collection of things. My favourite is three lines of code turns any Python function with positional and keyword arguments into a virtual table.

Size

The video below shows how the source tree has changed over time. It is often the same set of files for each release, updating C code, Python test code, tools, and documentation.

Lines of code is a silly metric, like weighing art, but is correlated with functionality, complexity, effort etc. Here are some comparisons between early 2006 when APSW was functionally complete, and the end of 2024 when it is very complete.

Popularity Contest

So just how popular is APSW?

There are 750 stars on GitHub putting it around position 45 thousand.

APSW is available packaged in most Linux, BSD, and even termux (Android).

From PyPI there are a million monthly downloads which puts it in the two thousands of all PyPI projects.

Final words

I've seen it said that project maintenance is a lot like gardening. You need to do occasional weeding and pruning, spruce things up, and generally just keep your hand in. I agree.

It has kept me up to date on Python's C API as well as Python library (I'm a fan of dataclasses). SQLite's changes (click the right column) have also been interesting, and I'm glad I did the work to wrap extensions like FTS5.

The long list of differences to the standard module shows the work continues to be worth doing.

Commentary

It was noticeable just how arbitrary each platform seemed. With the exception of OpenBSD, every platform had several configuration settings to their defaults. This does not result in any warnings of errors. It is generally the result of things like JSON which used to be off by default, and had to be explicitly enabled, but is now on by default. I don't know of any reasonable way for maintainers to find out that they are redundantly setting things to their default values.

SELECT name AS name, SUM(abc) AS abc_count, two.name AS two_name
  FROM contacts, two;

SELECT name, email FROM contacts WHERE name = ? AND age < ?;

SELECT ?73, ?97;

SELECT :foo, $bar, @bam;

Details

Each entry shows what looked different to me as I did this by visual inspection looking at the default build rules for each platform. You can get more information by looking at https://www.sqlite.org/compile.html and I've left off SQLITE, ENABLE etc from the names. The most recent version of the platform is what I looked at,

You can use pragma compile_options on your own platform to see what is in use. SQLite can have different defaults between the plain library and the cli making this a little more confusing.