Metadata-Version: 2.3
Name: modal-mosql
Version: 0.2.1
Summary: Durable SQLite transactions on explicitly committed volumes
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Typing :: Typed
Requires-Dist: fastcrc>=0.3.6,<0.4
Requires-Dist: alembic>=1.13,<2 ; extra == 'alembic'
Requires-Dist: sqlalchemy>=2.0,<2.1 ; extra == 'alembic'
Requires-Python: >=3.10
Provides-Extra: alembic
Description-Content-Type: text/markdown

# 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](..).

```python
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

```python
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

```python
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

```python
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`:

```python
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

```bash
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):

```bash
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.

```python
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:

```python
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/`](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.
