[FEATURE] Support separate database SSH targets during sync

JKetelaar · JKetelaar · commit 3d3508ee5f64 · 2026-06-28T14:49:00.000+02:00
diff --git a/README.md b/README.md
@@ -46,6 +46,9 @@ Behavior:
 - argument auto-resolves against site ID/domain/server/user when possible
 - stores only file path mappings in config (`sync.files.items`)
 - database details are requested interactively for each run (not persisted)
+- database sync uses the selected target server by default, with an optional
+  database SSH host override for clustered setups where files and MySQL live on
+  separate servers
 
 ### Context-aware defaults
 
diff --git a/cmd/sync-database.go b/cmd/sync-database.go
@@ -21,9 +21,16 @@ type mysqlSyncConfig struct {
 	TargetHost        string
 	TargetPort        int
 	TargetPasswordEnv string
+	DatabaseSSH       syncSSHTarget
 	DumpFlags         []string
 }
 
+type syncSSHTarget struct {
+	Host     string
+	Username string
+	Port     int
+}
+
 func runDatabaseSync(cmd *cobra.Command, target ResolvedSyncTarget, _ *SyncSettings, dryRun bool, autoApprove bool) error {
 	if isNonInteractiveMode() {
 		return fmt.Errorf("database sync requires interactive prompts in current implementation; run with an interactive terminal")
@@ -41,13 +48,13 @@ func runDatabaseSync(cmd *cobra.Command, target ResolvedSyncTarget, _ *SyncSetti
 		return err
 	}
 
-	if isSameDatabaseTarget(target, databaseConfig) {
+	if isSameDatabaseTarget(databaseConfig) {
 		return fmt.Errorf("source and target resolve to the same database destination")
 	}
 
 	fmt.Println("Database sync plan:")
 	fmt.Printf("  Source: %s@%s:%d/%s\n", databaseConfig.SourceUser, databaseConfig.SourceHost, databaseConfig.SourcePort, databaseConfig.SourceDatabase)
-	fmt.Printf("  Target: %s@%s:%d/%s (via %s@%s:%d)\n", databaseConfig.TargetUser, databaseConfig.TargetHost, databaseConfig.TargetPort, databaseConfig.TargetDatabase, target.Username, target.Host, target.Port)
+	fmt.Printf("  Target: %s@%s:%d/%s (via %s@%s:%d)\n", databaseConfig.TargetUser, databaseConfig.TargetHost, databaseConfig.TargetPort, databaseConfig.TargetDatabase, databaseConfig.DatabaseSSH.Username, databaseConfig.DatabaseSSH.Host, databaseConfig.DatabaseSSH.Port)
 
 	if !autoApprove {
 		confirmed, confirmErr := promptYesNo("Continue with MySQL import into target?")
@@ -73,7 +80,7 @@ func runDatabaseSync(cmd *cobra.Command, target ResolvedSyncTarget, _ *SyncSetti
 
 	dumpArgs := buildMySQLDumpArgs(databaseConfig)
 	remoteCommand := buildRemoteMySQLImportCommand(databaseConfig, targetPassword)
-	sshArgs := []string{"-p", strconv.Itoa(target.Port), fmt.Sprintf("%s@%s", target.Username, target.Host), remoteCommand}
+	sshArgs := buildDatabaseSSHArgs(databaseConfig.DatabaseSSH, remoteCommand)
 
 	fmt.Printf("Running: MYSQL_PWD=<hidden> mysqldump %s | ssh %s\n", strings.Join(dumpArgs, " "), strings.Join(sshArgs, " "))
 
@@ -173,6 +180,11 @@ func promptMySQLSyncConfig(target ResolvedSyncTarget) (mysqlSyncConfig, error) {
 		return mysqlSyncConfig{}, err
 	}
 
+	databaseSSH, err := promptDatabaseSSHTarget(target)
+	if err != nil {
+		return mysqlSyncConfig{}, err
+	}
+
 	flagsRaw, err := promptText("Extra mysqldump flags (space-separated, optional)", "--single-transaction --quick", optionalValue, 0)
 	if err != nil {
 		return mysqlSyncConfig{}, err
@@ -189,10 +201,47 @@ func promptMySQLSyncConfig(target ResolvedSyncTarget) (mysqlSyncConfig, error) {
 		TargetHost:        strings.TrimSpace(targetHost),
 		TargetPort:        targetPort,
 		TargetPasswordEnv: strings.TrimSpace(targetPasswordEnv),
+		DatabaseSSH:       databaseSSH,
 		DumpFlags:         strings.Fields(strings.TrimSpace(flagsRaw)),
 	}, nil
 }
 
+func promptDatabaseSSHTarget(target ResolvedSyncTarget) (syncSSHTarget, error) {
+	defaultTarget := defaultDatabaseSSHTarget(target)
+
+	host, err := promptText("Database SSH host override (optional)", "", optionalValue, 0)
+	if err != nil {
+		return syncSSHTarget{}, err
+	}
+	if strings.TrimSpace(host) == "" {
+		return defaultTarget, nil
+	}
+
+	username, err := promptText("Database SSH user", defaultTarget.Username, requiredValue, 0)
+	if err != nil {
+		return syncSSHTarget{}, err
+	}
+
+	port, err := promptPort("Database SSH port", strconv.Itoa(defaultTarget.Port))
+	if err != nil {
+		return syncSSHTarget{}, err
+	}
+
+	return syncSSHTarget{
+		Host:     strings.TrimSpace(host),
+		Username: strings.TrimSpace(username),
+		Port:     port,
+	}, nil
+}
+
+func defaultDatabaseSSHTarget(target ResolvedSyncTarget) syncSSHTarget {
+	return syncSSHTarget{
+		Host:     strings.TrimSpace(target.Host),
+		Username: strings.TrimSpace(target.Username),
+		Port:     target.Port,
+	}
+}
+
 func promptPort(label, defaultValue string) (int, error) {
 	value, err := promptText(label, defaultValue, requiredValue, 0)
 	if err != nil {
@@ -230,23 +279,42 @@ func buildRemoteMySQLImportCommand(config mysqlSyncConfig, targetPassword string
 	)
 }
 
+func buildDatabaseSSHArgs(target syncSSHTarget, remoteCommand string) []string {
+	return []string{"-p", strconv.Itoa(target.Port), fmt.Sprintf("%s@%s", target.Username, target.Host), remoteCommand}
+}
+
 func shellQuote(value string) string {
 	escaped := strings.ReplaceAll(value, "'", "'\\''")
 	return "'" + escaped + "'"
 }
 
-func isSameDatabaseTarget(target ResolvedSyncTarget, config mysqlSyncConfig) bool {
-	if !strings.EqualFold(strings.TrimSpace(target.Host), strings.TrimSpace(config.SourceHost)) {
+func isSameDatabaseTarget(config mysqlSyncConfig) bool {
+	if !isSameDatabaseHost(config.SourceHost, config.TargetHost, config.DatabaseSSH.Host) {
 		return false
 	}
 
 	if config.TargetPort != config.SourcePort {
 		return false
 	}
 
-	if !strings.EqualFold(strings.TrimSpace(config.TargetHost), strings.TrimSpace(config.SourceHost)) {
+	return strings.EqualFold(strings.TrimSpace(config.SourceDatabase), strings.TrimSpace(config.TargetDatabase))
+}
+
+func isSameDatabaseHost(sourceHost string, targetHost string, targetSSHHost string) bool {
+	source := strings.TrimSpace(sourceHost)
+	target := strings.TrimSpace(targetHost)
+	if source == "" || target == "" {
 		return false
 	}
 
-	return strings.EqualFold(strings.TrimSpace(config.SourceDatabase), strings.TrimSpace(config.TargetDatabase))
+	if !isLoopbackHost(target) {
+		return strings.EqualFold(source, target)
+	}
+
+	return strings.EqualFold(source, strings.TrimSpace(targetSSHHost))
+}
+
+func isLoopbackHost(host string) bool {
+	normalized := strings.ToLower(strings.TrimSpace(host))
+	return normalized == "localhost" || normalized == "127.0.0.1" || normalized == "::1"
 }
diff --git a/cmd/sync-database_test.go b/cmd/sync-database_test.go
@@ -0,0 +1,73 @@
+package cmd
+
+import (
+	"reflect"
+	"testing"
+)
+
+func TestDefaultDatabaseSSHTargetUsesSelectedSyncTarget(t *testing.T) {
+	target := ResolvedSyncTarget{
+		Host:     "file.example.com",
+		Username: "siteuser",
+		Port:     2222,
+	}
+
+	got := defaultDatabaseSSHTarget(target)
+	want := syncSSHTarget{Host: "file.example.com", Username: "siteuser", Port: 2222}
+
+	if got != want {
+		t.Fatalf("unexpected default database SSH target: got %+v want %+v", got, want)
+	}
+}
+
+func TestBuildDatabaseSSHArgsUsesClusterOverride(t *testing.T) {
+	target := syncSSHTarget{
+		Host:     "sql.example.com",
+		Username: "dbadmin",
+		Port:     2200,
+	}
+
+	got := buildDatabaseSSHArgs(target, "mysql --host=127.0.0.1 shop")
+	want := []string{"-p", "2200", "dbadmin@sql.example.com", "mysql --host=127.0.0.1 shop"}
+
+	if !reflect.DeepEqual(got, want) {
+		t.Fatalf("unexpected ssh args: got %#v want %#v", got, want)
+	}
+}
+
+func TestIsSameDatabaseTargetUsesDatabaseSSHHost(t *testing.T) {
+	config := mysqlSyncConfig{
+		SourceDatabase: "shop",
+		SourceHost:     "file.example.com",
+		SourcePort:     3306,
+		TargetDatabase: "shop",
+		TargetHost:     "127.0.0.1",
+		TargetPort:     3306,
+		DatabaseSSH:    syncSSHTarget{Host: "file.example.com", Username: "siteuser", Port: 22},
+	}
+
+	if !isSameDatabaseTarget(config) {
+		t.Fatalf("expected loopback target to use database SSH host for same-target detection")
+	}
+
+	config.DatabaseSSH.Host = "target-file.example.com"
+	if isSameDatabaseTarget(config) {
+		t.Fatalf("expected different database SSH host to avoid same-target match for loopback target")
+	}
+}
+
+func TestIsSameDatabaseTargetUsesExplicitTargetMySQLHost(t *testing.T) {
+	config := mysqlSyncConfig{
+		SourceDatabase: "shop",
+		SourceHost:     "sql.example.com",
+		SourcePort:     3306,
+		TargetDatabase: "shop",
+		TargetHost:     "sql.example.com",
+		TargetPort:     3306,
+		DatabaseSSH:    syncSSHTarget{Host: "file.example.com", Username: "siteuser", Port: 22},
+	}
+
+	if !isSameDatabaseTarget(config) {
+		t.Fatalf("expected explicit target MySQL host to drive same-target detection")
+	}
+}
