Support automatic translation of unique=True / UniqueConstraint to unique indexes in migrations

[MattOates]

Is your feature request related to a problem? Please describe.

Yes. And relates to #228

The Cloud Spanner SQLAlchemy dialect (python-spanner-sqlalchemy) does not support table-level UNIQUE constraints, while the idiomatic SQLAlchemy/SQLModel way to express uniqueness is unique=True on a Column or a UniqueConstraint in __table_args__.

When running Alembic autogenerate or migrations against Spanner, these become CREATE UNIQUE CONSTRAINT or ADD CONSTRAINT ... UNIQUE operations that fail, since Spanner does not support table-level unique constraints.

This is common for teams sharing models across Postgres/SQLite/Spanner or migrating to Spanner. The result is friction, boilerplate workarounds, and dialect-specific migration hacks.

---

Describe the solution you'd like

Please add native support to automatically translate UNIQUE constraints into unique indexes for Spanner.

Concretely:

• When Alembic autogenerates migrations for Spanner, emit op.create_index(..., unique=True) instead of op.create_unique_constraint(...).
• When running migrations containing CreateUniqueConstraintOp / AddConstraintOp(UniqueConstraint) / DropConstraintOp(unique), transparently execute CREATE UNIQUE INDEX / DROP INDEX.
• Optionally, add a flag like spanner_enforce_unique_via_index=True to control this behavior.

This would let models using unique=True or UniqueConstraint work on Spanner without any schema or migration rewrites.

---

Describe alternatives you've considered

1. Custom Alembic hooks / dialect patches:

   • Developers can use process_revision_directives or custom Alembic renderers to rewrite unique constraints into unique indexes manually.
   • This works but requires significant boilerplate (that should be in this repository to be reused).

2. Dialect-specific model changes:

   • Removing unique=True or duplicating with explicit Index(..., unique=True) definitions.
   • This breaks cross-dialect compatibility and influences peoples models too much to be generic.

3. Ignoring uniqueness enforcement on Spanner:

   • Skipping constraints entirely is unsafe, as uniqueness violations go undetected.
   • This is probably the simplest and better than just exploding people's migrations so long as a warning is emitted.

---

Additional context

Spanner supports unique indexes but not unique constraints.
Supporting this translation natively would make cross-dialect ORM models compatible without user intervention and would align Spanner’s dialect with the expectations of the broader SQLAlchemy ecosystem.

Minimal (Vibed from my code pasted in #228) Proof of Concept

Below is a minimal example using Alembic’s renderer dispatch to transparently rewrite unique constraints into unique indexes only for Spanner.

# spanner_renderers.py
from alembic.autogenerate import renderers
from alembic.autogenerate.api import AutogenContext
from alembic.operations import ops
from sqlalchemy.sql.schema import UniqueConstraint

def _mk_idx_name(table: str, cols, name: str | None) -> str:
    return name or f"uq_{table}_{'_'.join(cols)}"

def _render_create_idx(table: str, cols, name: str | None, schema: str | None) -> str:
    idx_name = _mk_idx_name(table, cols, name)
    parts = [repr(idx_name), repr(table), repr(cols), "unique=True"]
    if schema:
        parts.append(f"schema={schema!r}")
    return f"op.create_index({', '.join(parts)})"

def _render_drop_idx(table: str, name: str, schema: str | None) -> str:
    parts = [repr(name), f"table_name={table!r}"]
    if schema:
        parts.append(f"schema={schema!r}")
    return f"op.drop_index({', '.join(parts)})"

# CreateUniqueConstraintOp → create unique index (Spanner)
@renderers.dispatch_for(ops.CreateUniqueConstraintOp, "spanner")
def _render_create_uc_spanner(autogen_context: AutogenContext, op: ops.CreateUniqueConstraintOp) -> str:
    return _render_create_idx(op.table_name, list(op.columns), op.constraint_name, op.schema)

# AddConstraintOp(UniqueConstraint(...)) → create unique index (Spanner)
@renderers.dispatch_for(ops.AddConstraintOp, "spanner")
def _render_add_uc_spanner(autogen_context: AutogenContext, op: ops.AddConstraintOp) -> str:
    cons = op.constraint
    if isinstance(cons, UniqueConstraint):
        cols = [c.name for c in cons.columns]
        return _render_create_idx(cons.table.name, cols, cons.name, cons.table.schema)
    raise NotImplementedError

# DropConstraintOp(unique) → drop index (Spanner)
@renderers.dispatch_for(ops.DropConstraintOp, "spanner")
def _render_drop_uc_spanner(autogen_context: AutogenContext, op: ops.DropConstraintOp) -> str:
    if op.constraint_type == "unique":
        return _render_drop_idx(op.table_name, op.constraint_name, op.schema)
    raise NotImplementedError

[MattOates]

The reason I have suggested this feature, is this was quite literally the only thing blocking me on generating alembic migrations from a relatively complex set of existing models that are already in use in PostgreSQL and BigQuery. By blocking I mean it fully errors and alembic will always render the wrong thing. This just isn't good as it blocks the use of alembic for managing models where you have limited choice in how you express unique constraints for other databases. The minimal feature would be just to not emit UniqueConstraint into the migrations and warn, but at least produce migrations that do not error. The previous reporter in #228 was assumedly satisfied because they were targeting just Spanner for the first time with their setup. But I think its quite common for companies to wish to migrate schemas to Spanner from elsewhere.