commit d06bc806010b43254f0596de9c8b9d8963138827
parent f479a5849d37f1b821cab77ef2af73893a118276
Author: Silas Brack <silasbrack@gmail.com>
Date: Sat, 13 Jun 2026 13:07:00 +0200
refactor: let migrations manage their own transactions
Each migration now controls its own BEGIN/COMMIT. The runner
applies migrations one at a time and updates the version after
each success. This allows migrations to use PRAGMA statements
and CREATE VIRTUAL TABLE which don't work inside transactions.
Migration 3 now uses BEGIN IMMEDIATE for exclusive write access
during the STRICT table recreation.
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Diffstat:
2 files changed, 23 insertions(+), 24 deletions(-)
diff --git a/migrations/0003_strict_tables.sql b/migrations/0003_strict_tables.sql
@@ -3,6 +3,7 @@
-- 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;
@@ -169,4 +170,5 @@ 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
@@ -6,13 +6,16 @@ const MIGRATIONS: &[&str] = &[
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 current_version: i64 = conn
+ let mut current_version: i64 = conn
.query_row(
"SELECT COALESCE(MAX(version), 0) FROM migration_version",
[],
@@ -26,6 +29,17 @@ pub fn run(conn: &Connection) -> Result<(), String> {
return Ok(());
}
+ // Ensure we have a row to update
+ if current_version == 0 {
+ let count: i64 = conn
+ .query_row("SELECT COUNT(*) FROM migration_version", [], |row| row.get(0))
+ .map_err(|e| format!("Failed to count migration_version: {e}"))?;
+ if count == 0 {
+ conn.execute("INSERT INTO migration_version (version) VALUES (0)", [])
+ .map_err(|e| format!("Failed to insert migration_version: {e}"))?;
+ }
+ }
+
eprintln!(
"Running migrations {} through {} (current version: {})",
current_version,
@@ -33,37 +47,20 @@ pub fn run(conn: &Connection) -> Result<(), String> {
current_version
);
- conn.execute_batch("BEGIN TRANSACTION;")
- .map_err(|e| format!("Failed to begin transaction: {e}"))?;
-
for i in current_version..total {
eprintln!(" Applying migration {}...", i);
+
conn.execute_batch(MIGRATIONS[i as usize]).map_err(|e| {
- let _ = conn.execute_batch("ROLLBACK;");
format!("Migration {} failed: {}", i, e)
})?;
- }
- let count: i64 = conn
- .query_row("SELECT COUNT(*) FROM migration_version", [], |row| {
- row.get(0)
- })
- .map_err(|e| format!("Failed to count migration_version rows: {e}"))?;
-
- if count == 0 {
- conn.execute(
- "INSERT INTO migration_version (version) VALUES (?1)",
- [total],
- )
- .map_err(|e| format!("Failed to insert migration version: {e}"))?;
- } else {
- conn.execute("UPDATE migration_version SET version = ?1", [total])
+ // 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}"))?;
- }
- conn.execute_batch("COMMIT;")
- .map_err(|e| format!("Failed to commit transaction: {e}"))?;
+ current_version = i + 1;
+ }
- eprintln!("Migrations complete. Database is now at version {}", total);
+ eprintln!("Migrations complete. Database is now at version {}", current_version);
Ok(())
}
