Database Migration and PostgreSQL Cutover

Database Migration & PostgreSQL 16 Cutover

---

1. Executive Summary

As part of the v2.0.0 major platform release, StudentAppBackend migrated its primary persistence engine from MySQL 8.x to PostgreSQL 16. This document outlines the rationale, architectural adjustments, data canonicalization scripts, ETL procedures, and rollback contingencies.

---

2. Motivation & Architectural Rationale

Dimension	MySQL 8.x (Legacy)	PostgreSQL 16 (Production)
Collation & Case	Case-insensitive (utf8mb4_0900_ai_ci) by default; unique constraints ignore case unless binary collation specified.	Strict case-sensitivity (text / varchar); requires explicit lowercasing for case-insensitive unique constraints.
Schema Dialect	Backtick identifier quoting (`column`).	ANSI double-quote identifier quoting ("column"), strictly required for camelCase columns like "contactNumber".
Sequences & Types	AUTO_INCREMENT integer primary keys; native TINYINT(1) for boolean.	Native SERIAL / BIGSERIAL sequences; native BOOLEAN and UUID primitives.
Cloud Target	Complex TLS handshake configurations on newer cloud proxies (e.g. caching_sha2_password issues).	First-class native connection pooling, SSL negotiation, and high cloud-native compatibility (e.g., Render, AWS RDS).

---

3. Step-by-Step Implementation

Step 1: Driver Integration (Package.swift)

Replaced FluentMySQLDriver with FluentPostgresDriver as the default database dependency:

// Package.swift
.package(url: "https://github.com/vapor/fluent-postgres-driver.git", from: "2.8.0"),
// Pin postgres-nio < 1.33.0 for Swift 6.0 compatibility on Linux toolchains
.package(url: "https://github.com/vapor/postgres-nio.git", "1.0.0"..<"1.33.0"),
// Kept for rollback safety net
.package(url: "https://github.com/vapor/fluent-mysql-driver.git", from: "4.4.0"),

---

Step 2: Dynamic Fail-Loudly Configuration (configure.swift)

Configured dynamic driver resolution via DB_DRIVER:

• Default driver: postgres.
• Enforces strict presence of DATABASE_HOST, DATABASE_NAME, DATABASE_USER, and DATABASE_PASSWORD.
• Explicitly flags isDefault: true on the active driver so Fluent resolves app.db without ambiguity.
• Emits diagnostic warnings if port 3306 is specified while DB_DRIVER=postgres.

let driver = (Environment.get("DB_DRIVER") ?? "postgres").lowercased()
switch driver {
case "postgres", "psql", "postgresql":
    let postgresConfig = SQLPostgresConfiguration(
        hostname: host,
        port: port,
        username: user,
        password: password,
        database: database,
        tls: tlsConfig
    )
    app.databases.use(.postgres(configuration: postgresConfig), as: .psql, isDefault: true)
case "mysql":
    // Rollback fallback path
    app.databases.use(.mysql(...), as: .mysql, isDefault: true)
}

---

Step 3: Migration Audits & Identifier Quoting

PostgreSQL treats unquoted identifiers as lowercase. To support camelCase properties (e.g., contactNumber) in raw SQL migrations (AddRoleStatusPhoneToStudents.swift), dialect-aware identifier quoting was implemented:

let isPostgres = sql.dialect.name.lowercased().contains("postgres")
let contactColumn = isPostgres ? "\"contactNumber\"" : "`contactNumber`"

try await sql.raw("""
    CREATE UNIQUE INDEX \(isPostgres ? "uq_students_contact_number" : "students_contactNumber_unique")
    ON students (\(raw: contactColumn));
""").run()

---

4. The Migration Toolkit (scripts/migration/)

To guarantee data safety and zero corruption during cutover, four production-grade automation scripts were created in scripts/migration/:

1. 01-canonicalize.sql

Executed against MySQL before extraction. Cleans data by trimming leading/trailing whitespace and forcing email lowercasing to prevent unique constraint failures when moving to PostgreSQL's strict collation.

-- Normalize emails in MySQL source
UPDATE students SET email = LOWER(TRIM(email));
UPDATE students SET contactNumber = TRIM(contactNumber) WHERE contactNumber IS NOT NULL;

2. pgloader.load

ETL specification file for pgloader to stream data directly from MySQL to PostgreSQL:

• Maps MySQL tinyint(1) to PostgreSQL boolean.
• Maps MySQL datetime to PostgreSQL timestamptz.
• Resets sequence counters post-import (reset sequences).

3. 02-verification.sh

Automated validation script querying both databases:

• Compares table row counts between MySQL and PostgreSQL.
• Verifies MD5 checksums of concatenated, ordered email digests.
• Validates sequence values against maximum IDs.

4. runbook.md

Chronological execution checklist covering maintenance windows, traffic draining, cutover commands, and verification gates.

---

5. Rollback Contingency & Baselining

1. Pre-Migration Snapshot:
   • Tag v1.0.0-mysql was created and pushed to GitHub, marking the exact commit on main before the PostgreSQL changes landed.
2. Runtime Driver Rollback:
   • If an immediate operational issue occurs, setting DB_DRIVER=mysql, DATABASE_PORT=3306, and pointing to the MySQL backup allows the application to boot back into MySQL mode without recompiling code.
3. Cutover Milestone Tag:
   • The PostgreSQL cutover was finalized under tag v2.0.0.