Pourquoi Sequelize upsert ne fonctionne-t-il pas avec une clé unique composite?

Question

J'utilise cette table dans une base de données PostgreSQL :

 create table if not exists "Service" (
    _id uuid not null primary key,
    service text not null,
    "count" integer not null,
    "date" timestamp with time zone,
    team uuid,
    organisation uuid,
    "createdAt" timestamp with time zone not null,
    "updatedAt" timestamp with time zone not null,
    unique (service, "date", organisation),
    foreign key ("team") references "Team"("_id"),
    foreign key ("organisation") references "Organisation"("_id")
);

Lorsque j'essaie un upsert avec Sequelize avec le code suivant, une erreur est renvoyée :

Service.upsert({ team, date, service, organisation, count }, { returning: true })

L'erreur est :

error: duplicate key value violates unique constraint "Service_service_date_organisation_key"

Key (service, date, organisation)= (xxx, 2022-12-30 01:00:00+01, 12345678-5f63-1bc6-3924-517713f97cc3) already exists.

Mais selon la documentation de Sequelize, cela devrait fonctionner : https://sequelize.org/docs/v6/other-topics/upgrade/#modelupsert

Note pour les utilisateurs de Postgres : Si la charge utile upsert contient le champ PK, alors PK sera utilisé comme cible de conflit. Sinon, la première contrainte unique sera sélectionnée comme clé de conflit.

Comment puis-je trouver cette erreur de clé dupliquée et la faire fonctionner avec la clé unique composite : unique (service, "date", organisation) ?

Answer 1

Il semble que votre problème soit lié à l'issue #13240.

Si vous êtes sur Sequelize 6.12 ou supérieur, vous devriez pouvoir utiliser une liste explicite de conflictFields:

Service.upsert(
    { team, date, service, organisation, count }, 
    { conflictFields: ["service", "date", "organisation"] },
    { returning: true }
)

Answer 2

Références

Des questions similaires ont été posées sur GitHub, voir :

• https://github.com/sequelize/sequelize/issues/13240
• https://github.com/sequelize/sequelize/issues/13412

et elles n'ont pas encore été résolues, donc, à ce jour, ce problème semble être non résolu, vous devrez donc le contourner. Ci-dessous, je fournirai quelques idées pour résoudre cela, mais comme je n'ai jamais travaillé avec Sequelize, il est possible que j'ai une erreur de syntaxe ou de compréhension. Si tel est le cas, veuillez le signaler et je le corrigerai.

Approche 1 : Requêter par votre clé unique et insérer/mettre à jour par celle-ci

Post.findAll({
  where: {
    service: votreservice,
    date: votredate,
    organization: votreorganisation
  }
});

Et ensuite insert si le résultat est vide, mettre à jour sinon.

Approche 2 : Modifier votre schéma

Puisque votre clé unique composite est une clé candidate, une option serait de supprimer votre champ _id et de rendre votre clé unique (service, "date", organization).

Approche 3 : Implémenter un déclencheur d'insertion sur votre table

Vous pourriez simplement appeler insert depuis Sequelize et laisser un déclencheur PostgreSQL gérer l'upserting, voir : Comment écrire un déclencheur upsert dans PostgreSQL?

Exemple de déclencheur :

CREATE OR REPLACE FUNCTION on_before_insert_versions() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF pg_trigger_depth() = 1 THEN
      INSERT INTO versions (key, version) VALUES (NEW.key, NEW.version)
         ON CONFLICT (key)
         DO UPDATE SET version = NEW.version;
      RETURN NULL;
   ELSE
      RETURN NEW;
   END IF;
END;$$;

Vous devrez bien sûr modifier les noms de table et de champ en fonction de votre schéma et de la commande.