# How to test a SQLite migration on a copy of the database in a sandbox

Upload a copy of the `.db` file, run the migration in one transaction, check `integrity_check` and `foreign_key_check`, then download it.

**On Runtime SQLite needs no install at all.** The `sqlite3` command-line tool
is in the default image, and Python's built-in `sqlite3` module comes with
Python 3.12, so a sandbox is ready to run a migration the moment it starts. An
agent that writes the migration can try it on a real copy of production data,
in a machine that holds nothing else, and a bad migration costs one sandbox,
not a database. Ubuntu 24.04 ships SQLite 3.45.1, checked 25 September 2026.

## Run a migration and get a verdict

The runner applies the migration in one transaction, rolls back on any error,
and reports what it found as JSON:

```python check
from withruntime import Sandbox

RUNNER = '''
import json, sqlite3, sys

con = sqlite3.connect(sys.argv[1])
con.execute("PRAGMA foreign_keys = ON")
with open(sys.argv[2]) as handle:
    script = handle.read()
try:
    con.executescript("BEGIN;\\n" + script + "\\nCOMMIT;")
    status = "applied"
except sqlite3.Error as error:
    con.rollback()
    status = f"rolled back: {error}"

print(json.dumps({
    "status": status,
    "user_version": con.execute("PRAGMA user_version").fetchone()[0],
    "integrity": con.execute("PRAGMA integrity_check").fetchone()[0],
    "foreign_key_violations": con.execute("PRAGMA foreign_key_check").fetchall(),
}))
'''

with Sandbox.create() as sbx:
    sbx.files.upload("./app.db", "/workspace/app.db")
    sbx.files.upload("./migrations/0002_add_created_at.sql", "/workspace/migration.sql")
    sbx.files.write("/workspace/migrate.py", RUNNER)

    sbx.network.set(internet=False)
    result = sbx.exec(["python3", "/workspace/migrate.py", "/workspace/app.db", "/workspace/migration.sql"],
                      check=True, timeout_ms=600_000)
    print(result.stdout)
    sbx.files.download("/workspace/app.db", "app.migrated.db")
```

```ts check
import { readFile } from "node:fs/promises";
import { Sandbox } from "withruntime";

await using sbx = await Sandbox.create();
await sbx.files.upload("./app.db", "/workspace/app.db");
await sbx.files.upload("./migrations/0002_add_created_at.sql", "/workspace/migration.sql");
await sbx.files.write("/workspace/migrate.py", await readFile("./scripts/migrate.py", "utf8"));

await sbx.network.set({ internet: false });
const result = await sbx.exec(
  ["python3", "/workspace/migrate.py", "/workspace/app.db", "/workspace/migration.sql"],
  { check: true, timeoutMs: 600_000 },
);
const verdict = JSON.parse(result.stdout) as { status: string; integrity: string };
console.log(verdict.status, verdict.integrity);
await sbx.files.download("/workspace/app.db", "./app.migrated.db");
```

A run on 25 September 2026 of a migration that deleted a row other rows still
referenced came back as:

```json
{
  "status": "rolled back: FOREIGN KEY constraint failed",
  "user_version": 0,
  "integrity": "ok",
  "foreign_key_violations": []
}
```

The `ALTER TABLE` earlier in the same script was undone with the rest, because
SQLite's schema changes take part in the transaction, and `user_version` stayed
at 0.

## What each check tells you

| Check                      | What it catches                                         | Pass looks like |
| -------------------------- | ------------------------------------------------------- | --------------- |
| The transaction            | Any statement that fails; the whole migration is undone | `"applied"`     |
| `PRAGMA foreign_keys = ON` | Rows that break a foreign key, as the change happens    | No error        |
| `PRAGMA foreign_key_check` | Violations already in the file, even with keys off      | An empty list   |
| `PRAGMA integrity_check`   | Corruption in pages, indexes and records                | `ok`            |
| `PRAGMA user_version`      | Which migration the file is at, if you number them      | The new number  |

SQLite leaves foreign keys off in every new connection unless a program turns
them on, which is why the runner sets it first. A migration that only ever ran
with keys off can break references without a word; here it fails on a copy.

## Copy a live database safely

A database in WAL mode keeps recent commits in a `-wal` file beside it, so
copying only the `.db` can miss them. Take a consistent copy first, on the
machine that owns the database:

| Method                      | Command                                  |
| --------------------------- | ---------------------------------------- |
| SQLite CLI online backup    | `sqlite3 app.db ".backup copy.db"`       |
| SQL, also compacts the copy | `sqlite3 app.db "VACUUM INTO 'copy.db'"` |
| Python                      | `src.backup(sqlite3.connect("copy.db"))` |

Upload `copy.db`; it needs no `-wal` file.

## Let the agent explore the data first

An agent writing a migration usually wants to see the schema and a few rows.
The CLI answers in one command each, with no Python:

```bash no-run
sqlite3 -readonly /workspace/app.db ".schema users"
sqlite3 -readonly -json /workspace/app.db "SELECT * FROM users LIMIT 5"
```

`-readonly` stops a stray statement from changing the copy before the
migration runs; `-json` prints rows in a shape a model reads without parsing a
table. Both run as ordinary `exec` calls.

## Big databases

- **Disk:** the default 4 GiB disk has about 2.5 GiB free. A migration that
  rebuilds a table, or a `VACUUM`, needs room for a second copy of it, so ask
  for `diskMiB` of at least three times the file.
- **Transfers:** `files.upload` sends large files in parallel 1 MiB chunks,
  each checked by SHA-256, and resumes after a dropped connection.
- **Keep it for later:** pause the sandbox instead of downloading, and wake it
  when the next migration is ready. A paid account keeps a paused sandbox 30
  days by default, and up to 365.

## Related

- [DuckDB in a sandbox](/integrations/duckdb)
- [Run untrusted Python code safely](/languages/python)
- [Turn off sandbox internet](/how-to/turn-off-sandbox-internet)
- [What is in the sandbox](/docs/sandbox-environment#what-is-installed)

## Sources

Checked 25 September 2026.

- [SQLite PRAGMA statements](https://sqlite.org/pragma.html):
  `foreign_keys`, `foreign_key_check`, `integrity_check`, `user_version`
- [SQLite foreign key support](https://sqlite.org/foreignkeys.html): foreign
  keys are off by default for each connection
- [VACUUM INTO](https://sqlite.org/lang_vacuum.html) and the
  [sqlite3 CLI](https://sqlite.org/cli.html): `.backup`, `-readonly`, `-json`
- [Write-ahead logging](https://sqlite.org/wal.html): the `-wal` file
- [sqlite3 in Ubuntu noble](https://packages.ubuntu.com/noble/sqlite3): 3.45.1
- [Python sqlite3](https://docs.python.org/3.12/library/sqlite3.html):
  `executescript` and `Connection.backup`

Facts on this page were checked on 25 September 2026.