In this lesson, you will learn how to detect physical integrity issues in your local database files and apply programmatic recovery procedures to restore operational stability.
The Anatomy of Corruption
In lightweight databases like SQLite, corruption usually manifests as a mismatch between the file’s internal page checksums or a failure during a write operation—often triggered by a sudden process termination or hardware power loss. Since you are building offline-first architectures, you cannot rely on a cloud server to "fix" the file; you must handle it locally.
Step 1: Detection
Before attempting a repair, you must confirm the corruption. You can use the built-in integrity check commands provided by the database engine. If a query returns an error or a specific "database disk image is malformed" message, your recovery logic should trigger.
-- Check the internal structure of the database
PRAGMA integrity_check;
If this query returns anything other than ok, your file has structural inconsistencies.
Step 2: The Recovery Procedure
When corruption is detected, the most reliable path is a "dump and restore" strategy. You effectively create a fresh, empty database file and migrate the data from the damaged file into it. Because the database engine might fail to read corrupted tables, you should attempt this using the command-line interface or a dedicated API to export the salvageable data:
- Dump: Extract the remaining valid schema and data into a text-based SQL script.
- Rebuild: Create a brand new database file.
- Restore: Execute the SQL script against the new file.
In code, this looks like opening the damaged file as a secondary connection and copying rows to the primary connection:
-- Attach the corrupted file to your current session
ATTACH DATABASE 'corrupted.db' AS corrupted;
-- Copy non-corrupted records into the new, healthy tables
INSERT INTO main.users SELECT * FROM corrupted.users;
DETACH DATABASE corrupted;
Proactive Safeguards
While recovery is vital, prevention is better. Use Write-Ahead Logging (WAL) mode to ensure that transaction commits are atomic, and perform periodic Database Snapshotting (Lesson 24) to ensure you have a "known-good" restore point if the recovery procedure fails to salvage all rows.
Key Takeaway: Database corruption requires a two-step recovery plan: detect structural integrity failures using PRAGMA integrity_check, then perform a controlled migration of salvageable data from the corrupted file into a clean, new database instance.