commit d3796200d8d6a96e32c6b83e50321db7ebfb7161
parent b067e95c8802a34e87604a809eeb49b2cd2388ce
Author: Silas Brack <silasbrack@gmail.com>
Date: Sat, 13 Jun 2026 13:27:58 +0200
refactor: move version bumps into migration SQL files
Each .sql file owns its own BEGIN IMMEDIATE, version bump, and
COMMIT. The runner just creates the migration_version table,
checks the current version, and runs pending SQL files. No
transaction management in Rust.
This makes the migration atomic: if the schema change succeeds
but the version bump fails, the whole transaction rolls back.
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Diffstat:
4 files changed, 27 insertions(+), 55 deletions(-)
diff --git a/migrations/0001_initial_schema.sql b/migrations/0001_initial_schema.sql
@@ -1,3 +1,5 @@
+BEGIN IMMEDIATE;
+
CREATE TABLE IF NOT EXISTS user (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT UNIQUE NOT NULL,
@@ -115,3 +117,6 @@ CREATE TRIGGER IF NOT EXISTS vote_delete_comment AFTER DELETE ON vote WHEN old.t
UPDATE comment SET score = score - 1 WHERE id = old.target_id;
UPDATE user SET karma = karma - 1 WHERE id = (SELECT created_by FROM comment WHERE id = old.target_id);
END;
+
+UPDATE migration_version SET version = 1;
+COMMIT;
diff --git a/migrations/0002_email_verification.sql b/migrations/0002_email_verification.sql
@@ -1,2 +1,7 @@
+BEGIN IMMEDIATE;
+
ALTER TABLE user ADD COLUMN email_verified INTEGER NOT NULL DEFAULT 0;
ALTER TABLE user ADD COLUMN verification_token TEXT;
+
+UPDATE migration_version SET version = 2;
+COMMIT;
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,7 @@ 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;
+UPDATE migration_version SET version = 3;
+COMMIT;
+
PRAGMA foreign_keys=ON;
diff --git a/src/migrations.rs b/src/migrations.rs
@@ -12,31 +12,24 @@ pub fn run(conn: &Connection) -> Result<(), String> {
)
.map_err(|e| format!("Failed to create migration_version table: {e}"))?;
+ // Seed version row if missing
+ let count: i64 = conn
+ .query_row("SELECT COUNT(*) FROM migration_version", [], |row| row.get(0))
+ .map_err(|e| format!("Failed to read migration_version: {e}"))?;
+ if count == 0 {
+ conn.execute("INSERT INTO migration_version (version) VALUES (0)", [])
+ .map_err(|e| format!("Failed to seed migration_version: {e}"))?;
+ }
+
let current_version: i64 = conn
- .query_row(
- "SELECT COALESCE(MAX(version), 0) FROM migration_version",
- [],
- |row| row.get(0),
- )
- .unwrap_or(0);
+ .query_row("SELECT version FROM migration_version", [], |row| row.get(0))
+ .map_err(|e| format!("Failed to read version: {e}"))?;
let total = MIGRATIONS.len() as i64;
-
if current_version >= total {
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,
@@ -46,25 +39,8 @@ pub fn run(conn: &Connection) -> Result<(), String> {
for i in current_version..total {
eprintln!(" Applying migration {}...", i);
-
- // Each migration runs in BEGIN IMMEDIATE with the version bump.
- // If the migration SQL needs to disable foreign keys or run
- // PRAGMAs, it does so before the BEGIN in the .sql file itself.
- conn.execute_batch(MIGRATIONS[i as usize]).map_err(|e| {
- format!("Migration {i} failed: {e}")
- })?;
-
- // Version bump in its own transaction
- conn.execute_batch("BEGIN IMMEDIATE;")
- .map_err(|e| format!("Migration {i}: failed to begin version bump: {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}"));
- }
-
- conn.execute_batch("COMMIT;")
- .map_err(|e| format!("Migration {i}: failed to commit version bump: {e}"))?;
+ conn.execute_batch(MIGRATIONS[i as usize])
+ .map_err(|e| format!("Migration {} failed: {}", i, e))?;
}
eprintln!("Migrations complete. Database is now at version {}", total);
@@ -101,24 +77,6 @@ mod tests {
}
#[test]
- fn incremental_migration_bumps_version() {
- let conn = Connection::open_in_memory().unwrap();
- run(&conn).unwrap();
-
- for expected in 1..=MIGRATIONS.len() as i64 {
- let version: i64 = conn
- .query_row(
- "SELECT version FROM migration_version WHERE version >= ?1",
- [expected],
- |row| row.get(0),
- )
- .unwrap();
-
- assert!(version >= expected);
- }
- }
-
- #[test]
fn foreign_keys_on_after_migrations() {
let conn = Connection::open_in_memory().unwrap();
conn.execute_batch("PRAGMA foreign_keys=ON;").unwrap();
