René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Support automatic translation of unique=True / UniqueConstraint to unique indexes in migrations","articleBody":"## **Is your feature request related to a problem? Please describe.**\n\nYes.  And relates to #228 \n\nThe 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__`.\n\nWhen 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.  \n\nThis 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.\n\n---\n\n## **Describe the solution you'd like**\n\nPlease add native support to automatically **translate `UNIQUE` constraints into unique indexes** for Spanner.  \n\nConcretely:\n\n- When Alembic autogenerates migrations for Spanner, emit `op.create_index(..., unique=True)` instead of `op.create_unique_constraint(...)`.\n- When running migrations containing `CreateUniqueConstraintOp` / `AddConstraintOp(UniqueConstraint)` / `DropConstraintOp(unique)`, transparently execute `CREATE UNIQUE INDEX` / `DROP INDEX`.\n- Optionally, add a flag like `spanner_enforce_unique_via_index=True` to control this behavior.\n\nThis would let models using `unique=True` or `UniqueConstraint` work on Spanner without any schema or migration rewrites.\n\n---\n\n## **Describe alternatives you've considered**\n\n1. **Custom Alembic hooks / dialect patches:**  \n   - Developers can use `process_revision_directives` or custom Alembic `renderers` to rewrite unique constraints into unique indexes manually.  \n   - This works but requires significant boilerplate (that should be in this repository to be reused).\n\n2. **Dialect-specific model changes:**  \n   - Removing `unique=True` or duplicating with explicit `Index(..., unique=True)` definitions.  \n   - This breaks cross-dialect compatibility and influences peoples models too much to be generic.\n\n3. **Ignoring uniqueness enforcement on Spanner:**  \n   - Skipping constraints entirely is unsafe, as uniqueness violations go undetected.\n   - This is probably the simplest and better than just exploding people's migrations so long as a warning is emitted.\n\n---\n\n## **Additional context**\n\nSpanner supports **unique indexes** but not **unique constraints**.  \nSupporting 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.\n\n### Minimal (Vibed from my code pasted in #228) Proof of Concept\n\nBelow is a minimal example using Alembic’s renderer dispatch to transparently rewrite unique constraints into unique indexes **only for Spanner**.\n\n```python\n# spanner_renderers.py\nfrom alembic.autogenerate import renderers\nfrom alembic.autogenerate.api import AutogenContext\nfrom alembic.operations import ops\nfrom sqlalchemy.sql.schema import UniqueConstraint\n\ndef _mk_idx_name(table: str, cols, name: str | None) -\u003e str:\n    return name or f\"uq_{table}_{'_'.join(cols)}\"\n\ndef _render_create_idx(table: str, cols, name: str | None, schema: str | None) -\u003e str:\n    idx_name = _mk_idx_name(table, cols, name)\n    parts = [repr(idx_name), repr(table), repr(cols), \"unique=True\"]\n    if schema:\n        parts.append(f\"schema={schema!r}\")\n    return f\"op.create_index({', '.join(parts)})\"\n\ndef _render_drop_idx(table: str, name: str, schema: str | None) -\u003e str:\n    parts = [repr(name), f\"table_name={table!r}\"]\n    if schema:\n        parts.append(f\"schema={schema!r}\")\n    return f\"op.drop_index({', '.join(parts)})\"\n\n# CreateUniqueConstraintOp → create unique index (Spanner)\n@renderers.dispatch_for(ops.CreateUniqueConstraintOp, \"spanner\")\ndef _render_create_uc_spanner(autogen_context: AutogenContext, op: ops.CreateUniqueConstraintOp) -\u003e str:\n    return _render_create_idx(op.table_name, list(op.columns), op.constraint_name, op.schema)\n\n# AddConstraintOp(UniqueConstraint(...)) → create unique index (Spanner)\n@renderers.dispatch_for(ops.AddConstraintOp, \"spanner\")\ndef _render_add_uc_spanner(autogen_context: AutogenContext, op: ops.AddConstraintOp) -\u003e str:\n    cons = op.constraint\n    if isinstance(cons, UniqueConstraint):\n        cols = [c.name for c in cons.columns]\n        return _render_create_idx(cons.table.name, cols, cons.name, cons.table.schema)\n    raise NotImplementedError\n\n# DropConstraintOp(unique) → drop index (Spanner)\n@renderers.dispatch_for(ops.DropConstraintOp, \"spanner\")\ndef _render_drop_uc_spanner(autogen_context: AutogenContext, op: ops.DropConstraintOp) -\u003e str:\n    if op.constraint_type == \"unique\":\n        return _render_drop_idx(op.table_name, op.constraint_name, op.schema)\n    raise NotImplementedError","author":{"url":"https://github.com/MattOates","@type":"Person","name":"MattOates"},"datePublished":"2025-10-06T16:46:16.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":1},"url":"https://github.com/765/python-spanner-sqlalchemy/issues/765"}