# 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 the file.

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

## Run a migration and get a verdict

The runner applies the migration in one transaction. It rolls back on any
error. It 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 returned:

```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. SQLite's
schema changes take part in the transaction, so `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. That is why the runner sets the pragma first. A migration that only
ever ran with keys off can break references without any warning. 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. Copying
only the `.db` file can therefore 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 needs to see the schema and a few rows. The
CLI returns each answer in one command, 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 can read 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, in 1 MiB chunks.
  Each chunk is checked by SHA-256. The upload resumes after a dropped
  connection.
- **Keep it for later:** pause the sandbox instead of downloading the file. Wake
  it when the next migration is ready. A paid account keeps a paused sandbox 30
  days by default, and up to 365 days.

## 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 October 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 the Ubuntu noble package index](http://archive.ubuntu.com/ubuntu/dists/noble-updates/main/binary-amd64/): 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 October 2026.