syz-cluster/pkg/db: test migrations

Add a test that verifies that we have correct down migrations.
Fix the down migrations sql file.

4 files changed, 66 insertions, 18 deletions

diff --git a/syz-cluster/db-mgmt/main.go b/syz-cluster/db-mgmt/main.go
index 17ad70f1d..7d362e929 100644
--- a/syz-cluster/db-mgmt/main.go
+++ b/syz-cluster/db-mgmt/main.go
@@ -66,7 +66,7 @@ func main() {
 		switch os.Args[1] {
 		case "migrate":
 			log.Printf("running schema migrations")
-			err = db.RunMigrations(ctx, uri.Full)
+			err = db.RunMigrations(uri.Full)
 		case "run":
 			if len(os.Args) < 3 {
 				app.Fatalf("second argument is the SQL query to run")
diff --git a/syz-cluster/pkg/db/migrations/1_initialize.down.sql b/syz-cluster/pkg/db/migrations/1_initialize.down.sql
index 050a78ac4..04bdb97e2 100644
--- a/syz-cluster/pkg/db/migrations/1_initialize.down.sql
+++ b/syz-cluster/pkg/db/migrations/1_initialize.down.sql
@@ -1,10 +1,32 @@
-BEGIN TRANSACTION;
+-- Delete constraints first as these may create ciruclar dependencies.
+ALTER TABLE Patches DROP CONSTRAINT FK_SeriesPatches;
+ALTER TABLE Builds DROP CONSTRAINT FK_Series;
+ALTER TABLE Sessions DROP CONSTRAINT FK_SeriesSessions;
+ALTER TABLE Series DROP CONSTRAINT FK_SeriesLatestSession;
+ALTER TABLE SessionTests DROP CONSTRAINT FK_SessionResults;
+ALTER TABLE SessionTests DROP CONSTRAINT FK_BaseBuild;
+ALTER TABLE SessionTests DROP CONSTRAINT FK_PatchedBuild;
+ALTER TABLE Findings DROP CONSTRAINT FK_SessionCrashes;
+ALTER TABLE Findings DROP CONSTRAINT FK_TestCrashes;
+ALTER TABLE SessionReports DROP CONSTRAINT FK_SessionReports;
+ALTER TABLE ReportReplies DROP CONSTRAINT FK_ReplyReportID;
 
-DROP TABLE Series;
-DROP TABLE Patches;
-DROP TABLE Sessions;
+-- Spanner does not let drop tables without first deleting the indices.
+DROP INDEX SeriesByPublishedAt;
+DROP INDEX SeriesByExtID;
+DROP INDEX PatchesBySeriesAndSeq;
+DROP INDEX LastSuccessfulBuild;
+DROP INDEX SessionsByFinishedAt;
+DROP INDEX NoDupFindings;
+DROP INDEX NoDupSessionReports;
+DROP INDEX SessionReportsByStatus;
+DROP INDEX SessionReportsByMessageID;
+
+DROP TABLE ReportReplies;
+DROP TABLE Findings;
 DROP TABLE SessionTests;
+DROP TABLE SessionReports;
+DROP TABLE Patches;
 DROP TABLE Builds;
-DROP TABLE Findings;
-
-COMMIT;
+DROP TABLE Sessions;
+DROP TABLE Series;
diff --git a/syz-cluster/pkg/db/spanner.go b/syz-cluster/pkg/db/spanner.go
index 8db081c14..3135038b3 100644
--- a/syz-cluster/pkg/db/spanner.go
+++ b/syz-cluster/pkg/db/spanner.go
@@ -104,24 +104,29 @@ func dropSpannerDB(ctx context.Context, uri ParsedURI) error {
 //go:embed migrations/*.sql
 var migrationsFs embed.FS
 
-func RunMigrations(ctx context.Context, uri string) error {
-	sourceDriver, err := iofs.New(migrationsFs, "migrations")
+func RunMigrations(uri string) error {
+	m, err := getMigrateInstance(uri)
 	if err != nil {
 		return err
 	}
+	return m.Up()
+}
+
+func getMigrateInstance(uri string) (*migrate.Migrate, error) {
+	sourceDriver, err := iofs.New(migrationsFs, "migrations")
+	if err != nil {
+		return nil, err
+	}
 	s := &migrate_spanner.Spanner{}
 	dbDriver, err := s.Open("spanner://" + uri + "?x-clean-statements=true")
 	if err != nil {
-		return err
+		return nil, err
 	}
 	m, err := migrate.NewWithInstance("iofs", sourceDriver, "spanner", dbDriver)
-	if err == migrate.ErrNoChange {
-		// This is not a problem.
-		return nil
-	} else if err != nil {
-		return err
+	if err != nil {
+		return nil, err
 	}
-	return m.Up()
+	return m, nil
 }
 
 func NewTransientDB(t *testing.T) (*spanner.Client, context.Context) {
@@ -162,7 +167,7 @@ func NewTransientDB(t *testing.T) (*spanner.Client, context.Context) {
 		t.Fatal(err)
 	}
 	t.Cleanup(client.Close)
-	err = RunMigrations(ctx, uri.Full)
+	err = RunMigrations(uri.Full)
 	if err != nil {
 		t.Fatal(err)
 	}
diff --git a/syz-cluster/pkg/db/spanner_test.go b/syz-cluster/pkg/db/spanner_test.go
new file mode 100644
index 000000000..ec181409e
--- /dev/null
+++ b/syz-cluster/pkg/db/spanner_test.go
@@ -0,0 +1,21 @@
+// Copyright 2025 syzkaller project authors. All rights reserved.
+// Use of this source code is governed by Apache 2 LICENSE that can be found in the LICENSE file.
+
+package db
+
+import (
+	"testing"
+
+	"github.com/stretchr/testify/require"
+)
+
+func TestMigrations(t *testing.T) {
+	// Run, rollback and then again apply all DB migrations.
+	client, _ := NewTransientDB(t)
+	m, err := getMigrateInstance(client.DatabaseName())
+	require.NoError(t, err)
+	err = m.Down()
+	require.NoError(t, err, "migrating down failed")
+	err = m.Up()
+	require.NoError(t, err, "migrating up again failed")
+}