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.
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.
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
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:
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.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 thebefore_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.