Firebird migrations: make ALTER TABLE ADD idempotent to prevent deployment failures

[andrevanzuydam]

Problem

When running migrations against Firebird databases, ALTER TABLE ... ADD <column> fails if the column already exists. Unlike MySQL/PostgreSQL/SQLite, Firebird has no IF NOT EXISTS syntax for ALTER TABLE.

This causes a real-world deployment problem: if a migration partially ran (e.g. on staging), or the column was added manually, the migration fails on the next deploy and — because tina4 correctly refuses to deploy with a failed migration — the application cannot start.

Example failure

Migration 008_simplepay_config.sql:

ALTER TABLE SUBCODE ADD PDF_PASSWORD VARCHAR(255);

Error when column already exists:

EXECUTE ERROR: ALTER TABLE SUBCODE ADD PDF_PASSWORD VARCHAR(255)
unsuccessful metadata update
-ALTER TABLE SUBCODE failed
-violation of PRIMARY or UNIQUE KEY constraint "RDB$INDEX_15" on table "RDB$RELATION_FIELDS"
-Problematic key value is ("RDB$FIELD_NAME" = 'PDF_PASSWORD', "RDB$RELATION_NAME" = 'SUBCODE')

This is especially problematic in multi-environment setups (staging + production) where both environments share the same migration files but databases may be at different states.

Proposed Solutions

These all respect tina4's paradigm of failing the deployment when a migration has a genuine error.

Option 1: Firebird-safe DDL helpers (recommended)

Provide helper functions or documented patterns for writing idempotent Firebird migrations. For example, a Python-level helper:

# Could live in tina4_python's migration module
def safe_add_column(dba, table, column, datatype):
    """Add column only if it doesn't already exist (Firebird-safe)."""
    exists = dba.fetch_one(
        "SELECT RDB$FIELD_NAME AS col FROM RDB$RELATION_FIELDS "
        "WHERE RDB$RELATION_NAME = ? AND TRIM(RDB$FIELD_NAME) = ?",
        [table, column]
    )
    if not exists:
        dba.execute(f"ALTER TABLE {table} ADD {column} {datatype}")

Option 2: Pre-flight schema validation

Before executing a migration, parse the SQL for ALTER TABLE ... ADD statements, check against Firebird's RDB$RELATION_FIELDS system table, and skip statements where the column already exists. Log as "already applied" rather than "failed". The migration still gets marked as passed.

Option 3: Document EXECUTE BLOCK pattern

At minimum, document the Firebird-safe pattern for migration authors:

EXECUTE BLOCK AS BEGIN
  IF (NOT EXISTS(
    SELECT 1 FROM RDB$RELATION_FIELDS
    WHERE RDB$RELATION_NAME = 'SUBCODE'
    AND TRIM(RDB$FIELD_NAME) = 'PDF_PASSWORD'
  )) THEN
    EXECUTE STATEMENT 'ALTER TABLE SUBCODE ADD PDF_PASSWORD VARCHAR(255)';
END

Key Principle

Idempotent does not mean ignoring errors — it means not treating already-applied changes as failures. A genuinely broken migration (bad syntax, wrong data type, constraint violation on real data) should still fail and block deployment. But a column that already exists is a no-op, not an error.

Environment

• tina4_python with Firebird (fdb / firebird-driver)
• Multi-environment GitOps deployment (staging + production)
• Firebird 3.x / 4.x