SQLAlchemy: Polyfill for UNIQUE constraints

[amotl]

About

CrateDB does not support the notion of UNIQUE constraints, because it is a distributed database. For supporting certain applications, specific workarounds have been showing a successful outcome. Hereby, we would like to evaluate how a corresponding patch could be generalized, to be optionally enabled on the CrateDB SQLAlchemy dialect.

Reference

• Unique Index crate#10279

Details

While working on mlflow-cratedb, we had the need to support for UNIQUE constraints, as defined by the application ORM model, so we added a corresponding monkeypatch, which also has a remark:

TODO: Submit patch to crate-python, to be enabled by a dialect parameter crate_polyfill_unique or such.

Currently, the check_uniqueness_factory function is invoked on a single model which needed to be patched. That is still a bit tied to application logic, and would need to be generalized.

from mlflow.store.tracking.dbmodels.models import SqlExperiment

listen(SqlExperiment, "before_insert", check_uniqueness_factory(SqlExperiment, "name"))

Proposal

So, the idea here is to add a dialect parameter crate_polyfill_unique, which will, under the hood, transparently augment the SQLAlchemy model setup process to establish an event listener on the before_insert event, to check for uniqueness by querying the database table.

Surely, this causes overhead, and needs to be strongly emphasized in the documentation. But in general, the kinds of applications we are building this for do not need that much throughput on this end.

[amotl]

There is a patch now, which includes the corresponding improvement.

• Database: Add autoincrement, uniqueness, and sync-writes polyfills #28