sqlite-fast-migration-skill

---
name: sqlite-fast-migration-skill
description: High-speed cross-machine SQLite database migration using dump + gzip compression + local rebuild. Replaces slow, fragile rsync for GB-sized databases.
license: MIT
---

# SQLite Fast Migration Skill

> **Replace `rsync`-based direct `.db` copy** — use SQLite's built-in `.dump`, gzip compression, and local rebuild to migrate GB-sized SQLite databases across machines, solving slow transfer speeds and file corruption.

## When to Use

Migrating, backing up, or copying large SQLite databases (hundreds of MB to GB) from a remote server to your local machine. Especially useful when:

- The database has many indexes, making the `.db` file bloated with redundant data
- The source database is being written to during transfer (risk of corruption)
- You have limited bandwidth or a slow network connection
- `rsync` reports `database disk image is malformed` or takes too long

For small databases (~250 MB or less), direct `rsync` is fine — no need for this approach.

## Problem

The traditional approach — `rsync` copying the entire `.db` binary file — has two major flaws:

1. **Inefficient transfers** — SQLite stores indexes to speed up queries, but indexes are just redundant copies of the data. A single index can account for 50% of the total disk space, wasting massive bandwidth during transfer.

2. **File corruption** — Large databases take a long time to copy. During transfer, the live source database may be updated, causing `rsync` to read a mix of old and new data. The result is a corrupted file with `database disk image is malformed`.

## Solution

**Stop transferring raw `.db` binaries.** Instead, use a three-phase approach: **SQL dump + compressed transfer + local rebuild**.

| Phase | What | Why |
|-------|------|-----|
| **Deduplicate** | `sqlite3 .dump` converts the binary DB to SQL text | Indexes become single `CREATE INDEX` statements — no redundant index data transferred |
| **Compress** | gzip the SQL text | SQL text is highly repetitive, achieving excellent compression ratios |
| **Static snapshot** | Transfer the fixed text file | The dump is a point-in-time snapshot, immune to concurrent writes on the source |
| **Rebuild locally** | `sqlite3` executes the SQL script | Auto-creates tables, inserts data, and rebuilds all indexes — identical to the source |

### Measured Results (3.4 GB production database)

| File | Disk Usage | Note |
|------|-----------|------|
| Original SQLite .db | 3.4 GB | Includes massive index redundancy |
| SQLite dump (plain text) | 1.3 GB | Business data only, indexes reduced to CREATE statements |
| gzip compressed dump | **240 MB** | **14× compression** vs original |

## Procedure

The standard 6-step pipeline:

```bash
# ── 1. Remote: dump and compress ──
ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"

# ── 2. Local: pull the compressed package ──
rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz

# ── 3. Clean up remote temp file ──
ssh username@server "rm my_remote_database.db.txt.gz"

# ── 4. Local: decompress ──
gunzip my_local_database.db.txt.gz

# ── 5. Local: rebuild database from SQL (auto creates tables, inserts data, builds indexes) ──
cat my_local_database.db.txt | sqlite3 my_local_database.db

# ── 6. Clean up local temp text file ──
rm my_local_database.db.txt
```

### Quick one-liners

Dump + compress only (for later manual processing):

```bash
sqlite3 explorer.db .dump | gzip -c > explorer.db.txt.gz
```

Rebuild from local SQL text only:

```bash
cat my_local_database.db.txt | sqlite3 my_reconstructed_database.db
```

### What NOT to do (anti-pattern)

```bash
# Don't do this — slow and fragile for large files
rsync --progress username@server:my_remote_database.db my_local_database.db
```

## Pitfalls

- **Index redundancy is the main culprit** — the dump approach reduces indexes to single-line `CREATE INDEX` statements, achieving up to 14× compression. Always dump first, don't copy the `.db` raw.
- **Dump is a static snapshot** — it captures the database at a single point in time, immune to concurrent writes. This eliminates the version-mixing corruption that plagues `rsync`.
- **Locking under high concurrency** — if the source database is under heavy write load, `.dump` may wait for a lock. Run during off-peak hours, or use `.backup` first and dump the backup.
- **Local rebuild has a cost** — rebuilding indexes locally consumes CPU and disk I/O, but this is negligible compared to the time saved on network transfer.
- **Clean up temp files** — always remove `*.txt.gz` and `*.txt` afterward to avoid cluttering server and local disk.
- **Zero dependencies** — only needs `sqlite3` CLI and standard compression tools (gzip/gunzip). No plugins, services, or frameworks required.

## Verification

After migration, verify:

1. Opening the local database produces no `database disk image is malformed` error
2. Query the local database — row counts, table structure, and indexes match the remote source
3. Overall migration time is significantly shorter than `rsync` with the raw `.db` file under the same network conditions
4. No `*.txt.gz` or `*.txt` temp files remain on either server or local machine

## References

- [A faster way to copy SQLite databases between computers](https://alexwlchan.net/2025/copying-sqlite-databases/) — original technical inspiration
- [SQLite CLI documentation (.dump command)](https://www.sqlite.org/cli.html)

## License

MIT