CockroachDB: addPrimaryKeyOptions uses SERIAL (unsupported)

[bpamiri]

Summary

CockroachDBMigrator inherits PostgreSQL's addPrimaryKeyOptions() which replaces INTEGER with SERIAL. CockroachDB doesn't support SERIAL — it uses INT DEFAULT unique_rowid() instead.

Details

• PostgreSQLMigrator.addPrimaryKeyOptions() does ReplaceNoCase(sql, "INTEGER", "SERIAL") (line 28-29)
• CockroachDB will error on SERIAL type in DDL
• Need CockroachDB-specific override: INT DEFAULT unique_rowid() PRIMARY KEY
• Alternative: INT DEFAULT gen_random_uuid() for UUID-style PKs

Files

• vendor/wheels/databaseAdapters/CockroachDB/CockroachDBMigrator.cfc — needs addPrimaryKeyOptions() override
• vendor/wheels/databaseAdapters/PostgreSQL/PostgreSQLMigrator.cfc:26-32 — inherited method

[bpamiri]

Analysis

Synopsis

CockroachDBMigrator inherits PostgreSQL's addPrimaryKeyOptions() which replaces INTEGER with SERIAL — a type CockroachDB does not support. Auto-incrementing primary keys should use INT DEFAULT unique_rowid() instead.

Merit Assessment

Recommendation: Proceed

• This is a confirmed blocker — any migration that creates a table with an auto-incrementing primary key will fail on CockroachDB with a DDL error
• The fix is small and self-contained: a single method override in CockroachDBMigrator.cfc
• The inherited addPrimaryKeyOptions() at PostgreSQLMigrator.cfc:26-32 does ReplaceNoCase(sql, "INTEGER", "SERIAL", "all"), which is PostgreSQL-specific
• CockroachDB's unique_rowid() is the documented default PK strategy and is functionally equivalent

Implementation Plan

1. Override addPrimaryKeyOptions() in vendor/wheels/databaseAdapters/CockroachDB/CockroachDBMigrator.cfc
   • When autoIncrement is true, replace INTEGER with INT DEFAULT unique_rowid() instead of SERIAL
   • Append PRIMARY KEY (same as PostgreSQL adapter does at line 30)
2. Estimated complexity: Low — single method, ~8 lines of code

public string function addPrimaryKeyOptions(required string sql, struct options = {}) {
    if (StructKeyExists(arguments.options, "autoIncrement") && arguments.options.autoIncrement) {
        arguments.sql = ReplaceNoCase(arguments.sql, "INTEGER", "INT DEFAULT unique_rowid()", "all");
    }
    arguments.sql = arguments.sql & " PRIMARY KEY";
    return arguments.sql;
}

Dependencies & Risks

• Prerequisite: None — can be implemented independently
• Related: CockroachDB: define adapter-specific sqlTypes in migrator #1972 (sqlTypes should map integer to INT to stay consistent with this override), CockroachDB: add to CI test matrix and Docker Compose #1973 (CI testing needed to validate)
• Breaking change risk: None — this is a new adapter with no existing users on SERIAL

---

Automated analysis by @claude — review and adjust as needed.