commit 3020f927bc3b71cbcf06399a6d29e50b0d958225
parent d06bc806010b43254f0596de9c8b9d8963138827
Author: Silas Brack <silasbrack@gmail.com>
Date: Sat, 13 Jun 2026 13:11:10 +0200
fix: bump migration version inside the transaction
Version update now happens inside the same BEGIN IMMEDIATE
transaction as the migration SQL. If either fails, both roll back.
Prevents the case where a migration succeeds but the version bump
crashes, causing a non-idempotent migration to re-run.
Pre-transaction SQL (like PRAGMA foreign_keys=OFF) is specified
per-migration in the runner.
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Diffstat:
2 files changed, 37 insertions(+), 21 deletions(-)
diff --git a/migrations/0003_strict_tables.sql b/migrations/0003_strict_tables.sql
@@ -2,9 +2,6 @@
-- STRICT enforces column types at the SQLite level, preventing
-- silent type coercion (e.g., inserting a string into an INTEGER column).
-PRAGMA foreign_keys=OFF;
-BEGIN IMMEDIATE;
-
-- Drop all triggers first (they reference the old tables)
DROP TRIGGER IF EXISTS story_fts_insert;
DROP TRIGGER IF EXISTS story_fts_update;
@@ -170,5 +167,3 @@ CREATE TRIGGER vote_delete_comment AFTER DELETE ON vote WHEN old.target_type = 2
UPDATE user SET karma = karma - 1 WHERE id = (SELECT created_by FROM comment WHERE id = old.target_id);
END;
-COMMIT;
-PRAGMA foreign_keys=ON;
diff --git a/src/migrations.rs b/src/migrations.rs
@@ -1,21 +1,24 @@
use rusqlite::Connection;
-const MIGRATIONS: &[&str] = &[
- include_str!("../migrations/0001_initial_schema.sql"),
- include_str!("../migrations/0002_email_verification.sql"),
- include_str!("../migrations/0003_strict_tables.sql"),
+/// Each entry is (pre_sql, migration_sql).
+/// pre_sql runs outside the transaction (for PRAGMAs that can't run inside one).
+/// migration_sql runs inside BEGIN IMMEDIATE with the version bump.
+const MIGRATIONS: &[(&str, &str)] = &[
+ ("", include_str!("../migrations/0001_initial_schema.sql")),
+ ("", include_str!("../migrations/0002_email_verification.sql")),
+ (
+ "PRAGMA foreign_keys=OFF;",
+ include_str!("../migrations/0003_strict_tables.sql"),
+ ),
];
-/// Run pending migrations. Each migration manages its own transactions.
-/// The runner only wraps the version update in a transaction with the
-/// migration, so a failed migration doesn't bump the version.
pub fn run(conn: &Connection) -> Result<(), String> {
conn.execute_batch(
"CREATE TABLE IF NOT EXISTS migration_version (version INTEGER NOT NULL);",
)
.map_err(|e| format!("Failed to create migration_version table: {e}"))?;
- let mut current_version: i64 = conn
+ let current_version: i64 = conn
.query_row(
"SELECT COALESCE(MAX(version), 0) FROM migration_version",
[],
@@ -49,18 +52,36 @@ pub fn run(conn: &Connection) -> Result<(), String> {
for i in current_version..total {
eprintln!(" Applying migration {}...", i);
+ let (pre_sql, migration_sql) = MIGRATIONS[i as usize];
- conn.execute_batch(MIGRATIONS[i as usize]).map_err(|e| {
- format!("Migration {} failed: {}", i, e)
- })?;
+ // Run pre-transaction statements (PRAGMAs, etc.)
+ if !pre_sql.is_empty() {
+ conn.execute_batch(pre_sql)
+ .map_err(|e| format!("Migration {i} pre-sql failed: {e}"))?;
+ }
+
+ // Run migration + version bump in one transaction
+ conn.execute_batch("BEGIN IMMEDIATE;")
+ .map_err(|e| format!("Migration {i}: failed to begin: {e}"))?;
+
+ if let Err(e) = conn.execute_batch(migration_sql) {
+ let _ = conn.execute_batch("ROLLBACK;");
+ return Err(format!("Migration {i} failed: {e}"));
+ }
- // Update version after each successful migration
- conn.execute("UPDATE migration_version SET version = ?1", [i + 1])
- .map_err(|e| format!("Failed to update migration version: {e}"))?;
+ if let Err(e) = conn.execute("UPDATE migration_version SET version = ?1", [i + 1]) {
+ let _ = conn.execute_batch("ROLLBACK;");
+ return Err(format!("Migration {i}: failed to update version: {e}"));
+ }
- current_version = i + 1;
+ conn.execute_batch("COMMIT;")
+ .map_err(|e| format!("Migration {i}: failed to commit: {e}"))?;
}
- eprintln!("Migrations complete. Database is now at version {}", current_version);
+ // Restore foreign keys
+ conn.execute_batch("PRAGMA foreign_keys=ON;")
+ .map_err(|e| format!("Failed to restore foreign_keys: {e}"))?;
+
+ eprintln!("Migrations complete. Database is now at version {}", total);
Ok(())
}
