Constraints false failure for unique index

[mrjogo]

Using seed in conjuction with Supabase, when attempting to add two auth.users, seed is failing because of a unique index on a field that is set to the empty string in both users. However, generating the SQL separately for each of the users and then running them sequentially succeeds (plus the empty string is the default value for the fields when users are added through the web interface). Therefore, I suspect it's an issue with the seed constraint checker, not an actual failure.

seed.ts:

import { copycat } from "@snaplet/copycat";
import { createSeedClient } from "@snaplet/seed";
import dotenv from "dotenv";

dotenv.config({ path: ".env.local" });

const main = async () => {
  const seed = await createSeedClient({dryRun: process.env.DRY !== "0"});

  // Truncate all tables in the database
  await seed.$resetDatabase();

  await seed.auth_users([
    {
      instance_id: "00000000-0000-0000-0000-000000000000",
      id: copycat.uuid("user1@example.com"),
      confirmation_token: "",
      confirmation_sent_at: null,
      email: "user1@example.com",
      encrypted_password:
        "xxxx",
    },
    {
      instance_id: "00000000-0000-0000-0000-000000000000",
      id: copycat.uuid("user2@example.com"),
      confirmation_token: "",
      confirmation_sent_at: null,
      email: "user2@example.com",
      encrypted_password:
        "xxxx",
    },
  ]);

  console.log("Database seeded successfully!");

  process.exit();
};

main();

The error:

% npx tsx seed.ts
my_app/node_modules/@snaplet/seed/dist/src/core/plan/constraints.js:155
                throw new Error([
                      ^


Error: Unique constraint "confirmation_token_idx" violated for model "auth_users" on fields (confirmation_token) with values ()
Seed: 0/auth_users/1
Model data: {
  "id": "51a6658d-732d-522f-8015-aad47c97271c",
  "aud": "Es ere desid potius quem, partitia consed allictorum si tamquam.",
  "role": "Eas victi sapiens cum effecta, quos placet quisquam circo ant.",
  "email_confirmed_at": "2020-11-03T10:26:36.000Z",
  "invited_at": "2020-06-06T17:31:39.000Z",
  "recovery_token": "Hoc vult vix dolorat quod susciplinguam sit dolordiamur.",
  "recovery_sent_at": "2020-05-13T04:44:39.000Z",
  "email_change_token_new": "Dolorum per dolore aliosae et.",
  "email_change": "Copulsa meliorum quis es saepe eorumquam inscientiam, te vero cum etiam adquiescerte sapiens desid possimis.",
  "email_change_sent_at": "2020-11-15T22:57:47.000Z",
  "last_sign_in_at": "2020-04-08T15:30:17.000Z",
  "raw_app_meta_data": {
    "Facet": "Abitur et"
  },
  "raw_user_meta_data": {
    "Sensu": "Repellere stoicosopho"
  },
  "is_super_admin": true,
  "created_at": "2020-12-28T12:15:12.000Z",
  "updated_at": "2020-12-28T11:17:59.000Z",
  "phone_confirmed_at": "2020-10-06T10:01:38.000Z",
  "phone_change_sent_at": "2020-07-23T18:47:30.000Z",
  "banned_until": "2020-06-02T17:55:35.000Z",
  "reauthentication_sent_at": "2020-03-27T02:29:07.000Z",
  "deleted_at": "2020-11-19T11:00:25.000Z",
  "instance_id": "00000000-0000-0000-0000-000000000000",
  "confirmation_token": "",
  "confirmation_sent_at": null,
  "email": "user2@example.com",
  "encrypted_password": "xxxx"
}
    at checkConstraints (my_app/node_modules/@snaplet/seed/dist/src/core/plan/constraints.js:155:23)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at Plan.generateModel (my_app/node_modules/@snaplet/seed/dist/src/core/plan/plan.js:219:13)
    at Plan.generate (my_app/node_modules/@snaplet/seed/dist/src/core/plan/plan.js:347:9)
    at Plan.run (my_app/node_modules/@snaplet/seed/dist/src/core/plan/plan.js:355:23)
    at main (my_app/supabase/seed.ts:19:3)

Node.js v20.16.0

The index:

CREATE UNIQUE INDEX confirmation_token_idx ON auth.users USING btree (confirmation_token) WHERE ((confirmation_token)::text !~ '^[0-9 ]*$'::text)

[itoonx]

i got the same error

[mathiasvdb1998]

I also got the same error.
Unique indexes with a WHERE clause doesn't seem to work well with snaplet. I have the same issue with the following index:

CREATE UNIQUE INDEX departments_responsibles_unique_responsible ON public.departments_responsibles(department) where (adjunct = false)

[richardfwashington]

I'm having the same problem.

The issue is that (using Supabase Auth) these fields must be unique if they have a value, or empty or null. All fine.

But snaplet builds a model which is close but not quite the same. They must be unique if they have a value, or null. Note: missing the empty option.

I believe this is a bug with snaplet in the way it generates its constraints.

Two ways to fix I think:

1. The way the model is built should also cater for the option of empty strings being counted as unique
2. The constaint option of nullNotDistinct should count the empty string too.

@snaplet\seed\dist\src\core\plan\constraints.js:45

constraint.fields.some((c) => props.modelData[c] === null || constraint.fields.some((c) => props.modelData[c] === ''

Actually, the easiest change here may be to just allow for any nullish value

 if (!constraint.fields.some((c) => props.modelData[c]) && !constraint.nullNotDistinct) {
            continue;
        }

[richardfwashington]

PR is up #207

@mrjogo would you be able to test maybe? Resolves this for me and I think is the right thing to do.

I'm pretty sure Supabase will want this working with their auth system.

Open to other ideas of course...

[mrjogo]

Unfortunately, I moved off of Seed in favor of sql seed files (one less dependency to keep up to date).

[richardfwashington]

No worries, that's fair.

[maxcan]

@mathiasvdb1998 where you ever able to solve this? I'm having a similar issue with this constraint:

CREATE UNIQUE INDEX one_owner_per_organization ON public.organization_members (organization_id)
WHERE role = 'owner';

[mathiasvdb1998]

@maxcan I actually haven’t looked into it any further — I’m using the package for a small hobby project and haven’t worked on it in a while. Hope you’re able to find a solution though!

[uncvrd]

just ran in to this myself, my current solution is to generate the SQL for the auth.users table separately and then insert it into my seed.sql that is generated by snaplet. would be real nice to have some sort of ability to just pass DEFAULT as the value within the js snaplet config though....