pyxpo-sqlite

SQLite databases that persist across app restarts, for pyxpo apps. The API follows expo-sqlite. The phone's Python ships the sqlite3 module, so this package is pure Python: no native code and no rebuild of the native shell.

pip install pyxpo-sqlite

[tool.pyxpo]
modules = ["sqlite"]

import pyxpo_sqlite as SQLite

db = await SQLite.open_database_async("app.db")
await db.exec_async("""
    PRAGMA journal_mode = WAL;
    CREATE TABLE IF NOT EXISTS todo (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER DEFAULT 0);
""")
res = await db.run_async("INSERT INTO todo (title) VALUES (?)", "Buy milk")
print(res.last_insert_row_id, res.changes)

row = await db.get_first_async("SELECT * FROM todo WHERE id = ?", res.last_insert_row_id)  # dict or None
rows = await db.get_all_async("SELECT * FROM todo WHERE done = $done", {"$done": 0})         # list of dicts
async for row in db.get_each_async("SELECT * FROM todo"):                                   # one at a time
    ...

async def mark_all():
    await db.run_async("UPDATE todo SET done = 1")
await db.with_transaction_async(mark_all)     # COMMIT, or ROLLBACK if mark_all raises

Functions

SQLiteDatabase

Values: positional (run_async(sql, 1, "a")), one list or tuple, or one dict for named parameters. Dict keys may keep the prefix used in the SQL ({"$id": 1} for $id, :id, @id) or drop it. Rows map column names to Python values: int, float, str, bytes or None.

Every method also has a _sync form that blocks (for example get_all_sync), and camelCase names work (getAllAsync, runAsync, lastInsertRowId). Async methods run on one worker thread per connection, so the UI stays responsive. SQL errors raise the sqlite3 exceptions (sqlite3.OperationalError, sqlite3.IntegrityError, ...).

Provider and hook

from pyxpo_sqlite import SQLiteProvider, use_sqlite_context

async def migrate(db):
    await db.exec_async("CREATE TABLE IF NOT EXISTS todo (id INTEGER PRIMARY KEY, title TEXT)")

def App():
    return SQLiteProvider(TodoList(), database_name="app.db", on_init=migrate, fallback=Text("Loading..."))

@component
def TodoList():
    db = use_sqlite_context()
    ...

SQLiteProvider props: database_name, options, directory, on_init(db) (awaited before the children render), on_error(error) (without it the error is raised to the nearest ErrorBoundary) and fallback.

Not supported

Change listeners (addDatabaseChangeListener), enableChangeListener, SQLCipher encryption, loading extensions and the key-value Storage export of expo-sqlite (use pyxpo-storage).

Platforms

iOS, Android and desktop. The bundled Python on iOS and Android is 3.13. On desktop, serialize and deserialize need Python 3.11 or later and raise sqlite3.NotSupportedError before that. On desktop, databases live in .pyxpo-data/documents/SQLite under the working directory.