Remember to commit when using datasette.execute_write_fn()#

I was writing some code for datasette-auth-tokens that used db.execute_write_fn() like this:

1
def expire_tokens(conn):
2
    # Expire all tokens that are due to expire
3
    conn.execute(
4
        """
5
        update _datasette_auth_tokens
6
        set token_status = 'E', ended_timestamp = :now
7
        where token_status = 'A'
8
        and expires_after_seconds is not null
9
        and (created_timestamp + expires_after_seconds) < :now
10
    """, {"now": int(time.time())})
11

12
await db.execute_write_fn(expire_tokens)

But I got this database table is locked error when I ran the tests:

1
  File ".../datasette/database.py", line 228, in in_thread
2
    return fn(conn)
3
           ^^^^^^^^
4
  File ".../datasette/database.py", line 254, in sql_operation_in_thread
5
    cursor.execute(sql, params if params is not None else {})
6
sqlite3.OperationalError: database table is locked: _datasette_auth_tokens

The fix was to add an explicit commit within that write function:

1
def expire_tokens(conn):
2
    # Expire all tokens that are due to expire
3
    conn.execute(
4
        """
5
        update _datasette_auth_tokens
6
        set token_status = 'E', ended_timestamp = :now
7
        where token_status = 'A'
8
        and expires_after_seconds is not null
9
        and (created_timestamp + expires_after_seconds) < :now
10
    """, {"now": int(time.time())})
11
    db.commit()

I think the right rule of thumb here is to always explicitly commit in any Datasette code that makes writes to the database in this way.