mosql

Install from PyPI with pip install modal-mosql. The Python import name remains mosql.

SQLite with an explicit durability boundary: each committed transaction is published either by committing the volume the database lives on, or by appending an LTX record to an ordered log while the hot database stays on local disk. mosql is the durability layer under modo.

import mosql

with mosql.SQLite("/data/state.sqlite", volume=volume) as sql:
    sql.exec("CREATE TABLE IF NOT EXISTS counters (name TEXT PRIMARY KEY, value INT)")

    with sql.transaction():
        sql.exec(
            "INSERT INTO counters VALUES (?, 1) "
            "ON CONFLICT(name) DO UPDATE SET value = value + 1",
            "requests",
        )

    value = sql.exec("SELECT value FROM counters WHERE name = ?", "requests").one()[
        "value"
    ]

volume is any object with a synchronous commit() -> None; the database must live inside what that commit publishes.

Contract

• The database runs in WAL mode with synchronous=FULL, foreign keys on, and automatic checkpoints off. Do not change journal_mode; attached databases are not published.
• exec() runs one or more statements atomically. Bindings apply to the last statement, whose rows come back as dicts (.one(), .all(), iteration).
• Nested transaction() blocks use savepoints. The outermost block publishes once if it wrote any page; exceptions roll back. Read-only transactions and no-op writes publish nothing.
• transaction(read_only=True) enforces a read-only snapshot from its first database read. SQLite rejects writes, DDL, PRAGMAs, and ATTACH/DETACH before execution, including cached statements and nested scopes. A nested transaction(read_only=False) cannot relax this restriction. This protects managed SQL, not arbitrary Python filesystem or network effects.
• Access through one handle is thread-safe and serialized. Exactly one process may own a database; close it before ownership moves.

If publication fails after SQLite commits, exec() raises DurabilityError. The write may already be durable, so do not replay the SQL: call sync() to retry the publication. The next ordinary transaction also retries it before starting. An explicit read-only transaction instead raises DurabilityError until pending publication is resolved, without exposing unpublished state or attempting publication itself.

Concurrent acknowledged snapshots

with sql.snapshot(timeout=30.0) as reader:
    rows = reader.exec("SELECT * FROM events").all()

snapshot() opens an independent, read-only connection to the existing WAL file. Its snapshot is established during admission, before yielding the reader. Writers retain their usual serialization; a writer-priority gate prevents new snapshots from seeing a local commit before durable publication completes. Read handlers do not hold that gate or the writer connection's lock. A pending publication failure rejects new snapshots; existing acknowledged snapshots remain readable. The reader exposes exec(), nested transaction(), and the published position captured at admission, and closes when the scope exits.

At most eight snapshots are active per writer handle; excess admission raises TimeoutError immediately. The timeout covers admission and execution. Long SQL is interrupted, while arbitrary Python is checked when it returns. This is not forced thread cancellation. Live snapshots defer WAL truncation and can grow the WAL until they end. close() and abort() reject a writer with active snapshots; its owner must drain snapshots first. Snapshot connections cannot mutate, publish, or checkpoint the database. The timeout is finite and positive.

Log and checkpoint stores

from mosql import CheckpointStore, DurableLog, SQLite

log: DurableLog  # append(record, position), records(after_txid), discard(through_txid)
checkpoints: CheckpointStore  # restore, save, validate_recovery

with SQLite("/tmp/state.sqlite", log=log, checkpoints=checkpoints) as sql:
    sql.exec("INSERT INTO events VALUES (?)", "created")

Each committed transaction is read from the verified WAL frames and encoded as a checksummed LTX v3 record; exec() returns once log.append() does. After checkpoint_bytes of records (default 64 MiB, None disables) and on close, the WAL is truncated, checkpoints.save() publishes the database image, and log.discard() drops the covered records. A failed checkpoint is logged and the log stays complete.

Opening deletes the local database, restores the newest checkpoint, replays the log tail, and verifies the result against its recorded checksum; any mismatch raises RecoveryError. CRC64-ISO uses the native fastcrc implementation with identical bytes and checksum parameters; all existing checks remain enabled. CheckpointStore.validate_recovery(position) must reject recovery if concurrent checkpoint publication invalidated the reconstructed position.

After an external atomic claim of an unused durable namespace, pass fresh=True to SQLite, DictStore, and (when used) TieredStore to start from empty state without checkpoint, journal, or archive discovery. SQLite refuses an existing local database or WAL/SHM sidecar. These flags do not claim remote storage; the caller must ensure that only the successful claimant uses them. Duplicate opens and retries with an uncertain claim outcome must use normal recovery. Create-only journal fencing and publication checks still apply.

TieredStore and DictStore implement both interfaces on Modal primitives.

TieredStore

import modal
from mosql import DictStore, TieredStore, SQLite

state = modal.Dict.from_name("actor-state", create_if_missing=True)
volume = modal.Volume.from_name("actor-volumes", create_if_missing=True, version=2)

journal = DictStore(state, "orders", flush_interval=0.05)
with TieredStore(journal, volume, "/mnt/vol/orders") as store:
    with SQLite("/tmp/orders.sqlite", log=store, checkpoints=store) as sql:
        sql.exec("INSERT INTO events VALUES (?)", "created")
        store.archive()  # copy journaled records onto the volume

The Dict journal is the synchronous boundary: exec() is acknowledged exactly when it is with DictStore alone, fenced by the same create-only puts. Behind that line, archive() copies journaled records into position-named segment files on the volume and drops them from the Dict only after the volume.commit() that covers them; checkpoints publish the database image to the volume. The Dict therefore holds only the unarchived tail, so its seven-day entry expiry can threaten at most the records written since the last archive — and close() forces a checkpoint, so a cleanly closed database leaves nothing in the Dict at all.

Recovery stitches the tiers back together: newest intact image, then archived segments, then the journal tail, deduplicated by transaction id and verified against the LTX position chain. A file left torn by an interrupted commit is skipped and its records can be recovered from a retained journal copy, which is never popped ahead of the commit that covers it.

Archive-frontier validation skips segment files at or below a checksummed checkpoint or verified prefix. Retained archives remain available for fallback: if the newest checkpoint is corrupt, recovery verifies the tail after the older intact checkpoint. Checkpoint checksums, uncovered archive validation, and the concurrent-publication check still run.

TieredStore.checkpointed exposes the position restored from a verified image or successfully published by this store. segment_bytes() counts only archives after that position, excluding older segments retained for fallback. Failed checkpoint publication does not advance this maintenance frontier.

retain_transactions (default 0) keeps a journal repair tail after archive publication, and retain_checkpoints (default 1) keeps checkpoint generations and archive segments needed by the oldest retained image. Modo selects 128 transactions and two images. Retention increases storage usage and is a repair window, not a guarantee against loss of all copies. TieredStore merges journal and archive records during recovery and rejects conflicting copies. Modo adds commit receipts and a persistent initialization marker on top of TieredStore; standalone TieredStore does not independently establish the acknowledged head.

archive() is one self-contained stage/commit/confirm cycle. A host that batches a single volume commit across many stores calls stage_archive() on each, commits once, then calls confirm_archive() on each. Use sql.checkpoint(force=True) to checkpoint at lifecycle moments (an actor going idle, a handoff) and sql.position to compute how far archiving lags behind the journal.

DictStore

An advanced check_writer callback can replace per-publication boundary scans with an external monotonic ownership check. All writers of the namespace must obey that protocol, and retired owners must never become live again. The callback runs after journal publication, including retries, and must raise for a stale owner. The default retains the built-in boundary checks. Modo supplies its immutable routing-generation guard; arbitrary leases or cached booleans do not satisfy this contract.

DictStore implements both DurableLog and CheckpointStore on a modal.Dict:

import modal
from mosql import DictStore, SQLite

state = modal.Dict.from_name("actor-state", create_if_missing=True)

with DictStore(state, "orders", flush_interval=0.05) as store:
    with SQLite("/tmp/orders.sqlite", log=store, checkpoints=store) as sql:
        sql.exec("INSERT INTO events VALUES (?)", "created")
        store.flush()  # durability barrier

• The log is a sequence of numbered segments holding the LTX records appended since the previous segment. Large segments use content-addressed chunks (log_chunk_bytes, default 4 MiB) and a manifest published last. Recovery probes the consecutive numbered frontier in bounded parallel rounds, then fetches payloads backwards only through the checkpoint boundary. A small number of probes may touch older segments. Retained history stays readable on demand; retirement reads from the oldest end. Without a checkpoint, every segment is needed and recovery uses consecutive batches directly.
• Ownership fences carry unique retry-stable tokens. check_current() rejects a reader whose frontier was consumed or retired by another store. Hosts must call it before reads and claim a fence after recovery; appends still validate their own publication. Discard removes only a contiguous segment prefix.
• Segments use create-only puts and each append checks the discard boundary afterwards. Checkpoints and boundaries use versioned metadata: readers choose the maximum generation, and cleanup removes only lower generations. A writer paused after fencing cannot overwrite a newer checkpoint or regress the log boundary. Recovery detects checkpoints advancing across its reads and fails closed; retry opening with a fresh store in that case.
• Without flush_interval, every append returns once its segment is in the Dict. Boundary discovery enumerates Dict keys and reads the newest marker; this adds cost proportional to the shared Dict's key count. With an interval, appends are buffered and a background thread writes one segment per interval, so cost decouples from the transaction rate; flush() is the barrier and the loss window is one interval. close() flushes. Transient Dict errors are retried.
• Checkpoints are the database image in fixed-size chunks plus an immutable, versioned manifest written last. save prunes only lower generations.
• Custom Dict backends must implement keys() as well as get, put, and pop, and provide complete enumeration of existing keys. Metadata lookups must not omit keys that existed before enumeration began (concurrent newer publications may be observed or missed).
• One database per path; give a busy database its own Dict. Dict entries expire after seven days without access, so a database that is neither written nor read for a week is lost.

Storage format

The store reads versioned checkpoint manifests and journal boundaries only. Older mutable meta/checkpoint and meta/log layouts are unsupported. Use a fresh namespace for old-format data, or convert it explicitly before opening; the runtime does not migrate it. Interrupted chunk uploads without a manifest are not visible and their orphan chunks expire under Dict's retention policy.

Verify

uv run pytest
uv run --group modal modal run integration/modal_volume.py
uv run --group modal modal run integration/modal_dict.py

The durability protocols also have TLA+ models in specs/ (see the module headers for what each checks). To re-run the model checker (needs a JDK):

curl -sL -o specs/bin/tla2tools.jar \
  https://github.com/tlaplus/tlaplus/releases/latest/download/tla2tools.jar
java -cp specs/bin/tla2tools.jar tlc2.TLC -config specs/TieredStore.cfg specs/TieredStore.tla
java -cp specs/bin/tla2tools.jar tlc2.TLC -config specs/DictLog.cfg specs/DictLog.tla
java -cp specs/bin/tla2tools.jar tlc2.TLC -config specs/DictLogSplitBrain.cfg specs/DictLog.tla

DictLogSplitBrainNoCheck.cfg, DictLogSplitBrainNoFence.cfg, and DictLogSplitBrainNoMonotonicSave.cfg are expected to fail: they keep the split-brain counterexamples as regression checks for the fencing and monotonic publication in _dict.py. The model separates fencing from checkpoint publication; it abstracts complete metadata enumeration and does not establish the backend's listing or expiry behavior.

Alembic migrations

Install modal-mosql[alembic] to use the optional integration. SQLAlchemy and Alembic are not imported or required by the core API.

from mosql.alembic import upgrade

upgrade(sql, config="alembic.ini", revision="head")

config accepts a path or an Alembic Config object. Each database tracks its own revisions in alembic_version. One upgrade call applies all missing revisions inside a mosql transaction: schema, data, and version updates commit and publish together through the configured Volume or transaction log. A failure rolls them back. Inside an existing transaction, the upgrade uses a savepoint and publication waits for the outer transaction. An already-current database does not publish again. If publication raises DurabilityError, retry sql.sync(), not the migrations.

Create an ordinary Alembic environment with alembic init migrations. Its online migration path must use the supplied connection instead of opening another engine. For a migration environment used exclusively by mosql, env.py can be:

from alembic import context

context.configure(
    connection=context.config.attributes["connection"],
    transactional_ddl=True,
    render_as_batch=True,
)
with context.begin_transaction():
    context.run_migrations()

Write normal Alembic revision files using op.create_table, op.add_column, op.bulk_insert, and op.get_bind() for data migrations. SQLite batch table rebuilds are supported for tables without incoming foreign keys. A complete configuration and two revisions are in examples/.

Initial limits:

• Autocommit, isolation-level changes, raw transaction-control SQL, SQLAlchemy savepoints, and PRAGMAs other than dialect introspection are unsupported.
• op.drop_table and batch rebuilds reject tables referenced by foreign keys, including self-references. Foreign-key enforcement stays enabled; this guard prevents implicit cascade deletes during a rebuild. Raw SQL retains SQLite's own semantics and does not receive this Alembic operation guard.
• The helper runs upgrades. Generate revisions/autogenerate against a separate development database using a normal Alembic environment; the minimal env.py above requires a connection supplied by mosql. The actor database must not be opened separately by a CLI engine.
• Migration commands through this helper are serialized within a process because Alembic's context and op proxies are global. Migration code runs synchronously and should not start migrations on another thread and wait for them.

Run the example against your existing mosql handle with upgrade(sql, config="mosql/examples/alembic.ini") from the repository root. Package the configuration and revision directory with your application.