Drizzle Team•16mo ago

PostgresError: null value in column "id" of relation "pairs" violates not-null constraint

Hello, I am fairly new to Drizzle and working with databases, so I am running into a problem that I am unsure how to solve and hope that you can help me with it. I use Directus CMS to create all of my tables. Then, I wrote schema.ts (using introspect and making a couple of manual changes). So, one of my tables looks like this:

export const pairs = pgTable("pairs", {
  id: uuid("id").defaultRandom().primaryKey().notNull(),
  person_a: uuid("person_a")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  person_b: uuid("person_b")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  has_liked: boolean("has_liked").default(true).notNull(),
});

export const pairs = pgTable("pairs", {
  id: uuid("id").defaultRandom().primaryKey().notNull(),
  person_a: uuid("person_a")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  person_b: uuid("person_b")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  has_liked: boolean("has_liked").default(true).notNull(),
});

Now whenever i try to insert new pair like this

    const [pair] = await database
        .insert(pairs)
        .values({
          person_a: data.ctx.user.id,
          has_liked: false,
          person_b: data.input.user,
        })
        .returning();

    const [pair] = await database
        .insert(pairs)
        .values({
          person_a: data.ctx.user.id,
          has_liked: false,
          person_b: data.input.user,
        })
        .returning();

I get the following error: PostgresError: null value in column "id" of relation "pairs" violates not-null constraint What confuses me is that in Directus, I have checked 'ON CREATE: Generate and Save UUID,' which is working just fine. Please help me understand how I can get around this error.

10 Replies

Angelelz•16mo ago

After you make changes to the schema, you need to either generate and run the migration or use the push command, so that the dababase is aware of the schema changes you made locally

tonidOP•16mo ago

That makes sense. However, I think that changing the database like that would: - Delete tables that I removed from the schema, which Directus uses internally. - Potentially create a change in some table that Directus doesn't know how to deal with. My idea was to use Directus to create tables, generate schema using introspect with drizzle-kit, cleanup the schema and then manually keep it in sync with database

Angelelz•16mo ago

Can you log the query to the console and pass it here?

tonidOP•16mo ago

Query: insert into "pairs" ("id", "person_a", "person_b", "has_liked") values (default, $1, $2, $3) returning "id", "person_a", "person_b", "has_liked" -- params: ["ff35ae6f-5ea5-4d50-9d7a-7dc9a01cffd3", "8ff4da6e-ad8e-45be-bb7e-dd7a0f7fe637", false]

Query: insert into "pairs" ("id", "person_a", "person_b", "has_liked") values (default, $1, $2, $3) returning "id", "person_a", "person_b", "has_liked" -- params: ["ff35ae6f-5ea5-4d50-9d7a-7dc9a01cffd3", "8ff4da6e-ad8e-45be-bb7e-dd7a0f7fe637", false]

tonidOP•16mo ago

Also this is what my database looks like

tonidOP•16mo ago

And directus schema

Angelelz•16mo ago

Well, I'm not familiar with directus, but you don't have a default defined at the database level. This might be something directus is doing for you You could change your schema to:

export const pairs = pgTable("pairs", {
  id: uuid("id").primaryKey().notNull().$default(() => someFnThatReturnsAnUUIDInJs()),
  person_a: uuid("person_a")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  person_b: uuid("person_b")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  has_liked: boolean("has_liked").default(true).notNull(),
});

export const pairs = pgTable("pairs", {
  id: uuid("id").primaryKey().notNull().$default(() => someFnThatReturnsAnUUIDInJs()),
  person_a: uuid("person_a")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  person_b: uuid("person_b")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  has_liked: boolean("has_liked").default(true).notNull(),
});

That should work with the setup that you have

tonidOP•16mo ago

Thanks! Yes its likely something that directus does on its level. When i try to do it as you showed i get ts error:

Property '$default' does not exist on type 'NotNull<NotNull<PgUUIDBuilderInitial<"id">>>'. Did you mean 'default'?

Property '$default' does not exist on type 'NotNull<NotNull<PgUUIDBuilderInitial<"id">>>'. Did you mean 'default'?

Angelelz•16mo ago

What version of drizzle-orm are you running?

tonidOP•16mo ago

Updated it and it works now 🙂 at least ts , will see for default now Everything works now. Thanks a lot !

Gaming

Programming

PostgresError: null value in column "id" of relation "pairs" violates not-null constraint