Using SQLite and asyncio effectively

When using Piccolo with SQLite, there are some best practices to follow.

asyncio => lots of connections

With asyncio, we can potentially open lots of database connections, and attempt to perform concurrent database writes.

SQLite doesn’t support such concurrent behavior as effectively as Postgres, so we need to be careful.

One write at a time

SQLite can easily support lots of transactions concurrently if they are reading, but only one write can be performed at a time.

---

Transactions

SQLite has several transaction types, as specified by Piccolo’s TransactionType enum:

class piccolo.engine.sqlite.TransactionType(value, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)

See the SQLite docs for more info.

deferred = 'DEFERRED'

exclusive = 'EXCLUSIVE'

immediate = 'IMMEDIATE'

Which to use?

When creating a transaction, Piccolo uses DEFERRED by default (to be consistent with SQLite).

This means that the first SQL query executed within the transaction determines whether it’s a READ or WRITE:

• READ - if the first query is a SELECT

• WRITE - if the first query is something like an INSERT / UPDATE / DELETE

If a transaction starts off with a SELECT, but then tries to perform an INSERT / UPDATE / DELETE, SQLite tries to ‘promote’ the transaction so it can write.

The problem is, if multiple concurrent connections try doing this at the same time, SQLite will return a database locked error.

So if you’re creating a transaction which you know will perform writes, then create an IMMEDIATE transaction:

from piccolo.engine.sqlite import TransactionType

async with Band._meta.db.transaction(
    transaction_type=TransactionType.immediate
):
    # We perform a SELECT first, but as it's an IMMEDIATE transaction,
    # we can later perform writes without getting a database locked
    # error.
    if not await Band.exists().where(Band.name == 'Pythonistas'):
        await Band.objects().create(name="Pythonistas")

Multiple IMMEDIATE transactions can exist concurrently - SQLite uses a lock to make sure only one of them writes at a time.

If your transaction will just be performing SELECT queries, then just use the default DEFERRED transactions - you will get improved performance, as no locking is involved:

async with Band._meta.db.transaction():
    bands = await Band.select()
    managers = await Manager.select()

---

timeout

It’s recommended to specify the timeout argument in SQLiteEngine.

DB = SQLiteEngine(timeout=60)

Imagine you have a web app, and each endpoint creates a transaction which runs multiple queries. With SQLite, only a single write operation can happen at a time, so if several connections are open, they may be queued for a while.

By increasing timeout it means that queries are less likely to timeout.

To find out more about timeout see the Python sqlite3 docs.