prisma: Attempted "create" with related record "connect"s fails with "Null constraint violation on the fields: (`id`)"

Bug description

Attempting to “create” a record with related models using the “connect” syntax results in a null constraint violation in postgres.

How to reproduce

  1. Create a schema with related models
  2. Attempt to create a new record that connects to existing records (as described)
  3. The prisma query fails with Null constraint violation on the fields: ('id'), postgres fails with ERROR: null value in column "id" of relation "Team" violates not-null constraint

Expected behavior

I would expect the generated INSERT to not attempt to insert a null value for id

i.e. instead of

 INSERT INTO "public"."Team" ("id","name","balance","addressLine1","addressLine2","city","country","countryLongName","postalCode","region","onboardingStep","ownerId","createdAt","updatedAt") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14) RETURNING "public"."Team"."id"

I would expect something like

 INSERT INTO "public"."Team" ("name","balance","addressLine1","addressLine2","city","country","countryLongName","postalCode","region","onboardingStep","ownerId","createdAt","updatedAt") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) RETURNING "public"."Team"."id"

Prisma information

schema:

datasource db {
    provider = "postgresql"
    url      = env("DATABASE_URL")
}

generator client {
    provider = "prisma-client-js"
}

model Card {
    id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid

    cardType           String @db.VarChar(20)
    cardholderId       String @db.Uuid
    name               String @db.VarChar(64)
    nickname           String @db.VarChar(64)
    spendingLimits     Json[]
    status             String @db.VarChar(20)
    stripeCardId       String @unique @db.VarChar(64)
    stripeCardholderId String @db.VarChar(64)
    teamId             String @db.Uuid
    userId             String @db.Uuid

    createdAt DateTime @default(now()) @db.Timestamp(6)
    updatedAt DateTime @default(now()) @updatedAt @db.Timestamp(6)

    cardholder  Cardholder    @relation(fields: [cardholderId], references: [id])
    team        Team          @relation(fields: [teamId], references: [id])
    user        User          @relation(fields: [userId], references: [id])
    transaction Transaction[]
}

model Cardholder {
    id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid

    name               String  @db.VarChar(64)
    email              String  @db.VarChar(254)
    stripeCardholderId String  @db.VarChar(64)
    teamId             String  @db.Uuid
    userId             String? @unique @db.Uuid


    createdAt DateTime @default(now()) @db.Timestamp(6)
    updatedAt DateTime @default(now()) @updatedAt @db.Timestamp(6)

    team Team   @relation(fields: [teamId], references: [id])
    user User?  @relation(fields: [userId], references: [id])
    card Card[]
}

model Invite {
    id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid

    code   String  @unique @db.VarChar(24)
    teamId String? @unique @db.Uuid

    createdAt DateTime @default(now()) @db.Timestamp(6)
    updatedAt DateTime @default(now()) @updatedAt @db.Timestamp(6)

    team Team?
}

model MemberInvite {
    id     String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
    email  String @unique @db.VarChar(254)
    role   String @db.VarChar(24)
    teamId String @db.Uuid

    createdAt DateTime @default(now()) @db.Timestamp(6)
    updatedAt DateTime @default(now()) @updatedAt @db.Timestamp(6)

    team Team @relation(fields: [teamId], references: [id])
}

model PendingTransaction {
    id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid

    amount          Int    @db.Integer
    transactionHash String @db.VarChar(66)
    address         String @db.VarChar(66)
    status          String @db.VarChar(20)
    userId          String @db.Uuid
    teamId          String @db.Uuid

    createdAt DateTime @default(now()) @db.Timestamp(6)
    updatedAt DateTime @default(now()) @updatedAt @db.Timestamp(6)

    team Team @relation(fields: [teamId], references: [id])
    user User @relation(fields: [userId], references: [id])
}

model Team {
    id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid

    name            String  @db.VarChar(64)
    balance         Int     @default(0) @db.Integer
    addressLine1    String  @db.VarChar(44)
    addressLine2    String  @db.VarChar(44)
    city            String  @db.VarChar(64)
    country         String  @db.VarChar(64)
    countryLongName String  @db.VarChar(64)
    postalCode      String  @db.VarChar(64)
    region          String  @db.VarChar(64)
    taxId           String? @db.VarChar(255)
    onboardingStep  Int     @default(0) @db.Integer
    ownerId         String  @unique @db.Uuid

    createdAt DateTime @default(now()) @db.Timestamp(6)
    updatedAt DateTime @default(now()) @updatedAt @db.Timestamp(6)

    invite              Invite               @relation(fields: [id], references: [teamId])
    owner               User                 @relation("Team_ownerIdToUser", fields: [ownerId], references: [id])
    cards               Card[]
    cardholders         Cardholder[]
    memberInvites       MemberInvite[]
    pendingTransactions PendingTransaction[]
    transactions        Transaction[]
    users               User[]               @relation("TeamToUser_teamId")
    wallets             Wallet[]
}

model Token {
    id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid

    key       String   @unique @db.VarChar(66)
    expiresAt DateTime @db.Timestamp(6)
    userId    String   @db.Uuid

    createdAt DateTime @default(now()) @db.Timestamp(6)
    updatedAt DateTime @default(now()) @updatedAt @db.Timestamp(6)

    user User @relation(fields: [userId], references: [id])
}

model Transaction {
    id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid

    amount                  Int
    type                    String  @db.VarChar(20)
    memo                    String? @db.VarChar(255)
    cardType                String? @db.VarChar(20)
    cardId                  String? @db.Uuid
    cardholderEmail         String? @db.VarChar(254)
    cardholderName          String? @db.VarChar(64)
    cardholderUserId        String? @db.Uuid
    category                String? @db.VarChar(64)
    categoryCode            String? @db.VarChar(12)
    city                    String? @db.VarChar(64)
    country                 String? @db.VarChar(64)
    currency                String? @db.VarChar(3)
    merchantName            String? @db.VarChar(64)
    state                   String? @db.VarChar(64)
    stripeCardId            String? @db.VarChar(64)
    zip                     String? @db.VarChar(64)
    authorizationAmount     Int?
    authorizationStatus     String? @db.VarChar(64)
    authorizationId         String? @db.VarChar(64)
    stripeTransactionId     String? @db.VarChar(64)
    stripeTransactionAmount Int?
    chain                   String? @db.VarChar(64)
    transactionHash         String? @db.VarChar(66)
    walletAddress           String? @db.VarChar(66)
    userId                  String? @db.Uuid
    teamId                  String  @db.Uuid

    createdAt DateTime @default(now()) @db.Timestamp(6)
    updatedAt DateTime @default(now()) @updatedAt @db.Timestamp(6)

    card           Card? @relation(fields: [cardId], references: [id])
    cardholderUser User? @relation("Transaction_cardholderUserIdToUser", fields: [cardholderUserId], references: [id])
    team           Team  @relation(fields: [teamId], references: [id])
    user           User? @relation("Transaction_userIdToUser", fields: [userId], references: [id])
}

model User {
    id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid

    address                  String  @unique @db.VarChar(66)
    name                     String  @db.VarChar(24)
    email                    String  @db.VarChar(254)
    inviteCode               String  @db.VarChar(24)
    role                     String  @db.VarChar(24)
    isActive                 Boolean @default(true)
    isAdmin                  Boolean @default(false)
    isTermsOfServiceAccepted Boolean @default(false)
    teamId                   String? @db.Uuid

    createdAt DateTime @default(now()) @db.Timestamp(6)
    updatedAt DateTime @default(now()) @updatedAt @db.Timestamp(6)

    team                   Team?                @relation("TeamToUser_teamId", fields: [teamId], references: [id])
    cards                  Card[]
    cardholder             Cardholder?
    pendingTransactions    PendingTransaction[]
    ownedTeam              Team?                @relation("Team_ownerIdToUser")
    tokens                 Token[]
    spendTransactions      Transaction[]        @relation("Transaction_cardholderUserIdToUser")
    collateralTransactions Transaction[]        @relation("Transaction_userIdToUser")
    wallets                Wallet[]

    @@index([id])
    @@index([address])
    @@index([teamId])
}

model Wallet {
    id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid

    chain         Int
    walletAddress String  @unique @db.VarChar(66)
    teamId        String? @db.Uuid
    userId        String  @db.Uuid

    createdAt DateTime @default(now()) @db.Timestamp(6)
    updatedAt DateTime @default(now()) @updatedAt @db.Timestamp(6)

    team Team? @relation(fields: [teamId], references: [id])
    user User  @relation(fields: [userId], references: [id])
}

query:

...
    const team = {
      name: data.name,
      addressLine1: data.addressLine1,
      addressLine2: data.addressLine2,
      city: data.city,
      region: data.region,
      postalCode: data.postalCode,
      country: data.country,
      countryLongName: data.countryLongName,
      owner: { connect: { id: user.id } },
      invite: { connect: { code: user.inviteCode } },
      wallets: { connect: [{ walletAddress: user.address }] },
      users: { connect: [{ id: user.id }] },
      balance: 0,
    };

    const createdTeam = await fastify.repo.team.create(team);

debug output (note the id in the Generated request):

[1] 2022-06-17T19:15:49.525Z prisma:client  Prisma Client call:
[1] 2022-06-17T19:15:49.526Z prisma:client  prisma.team.create({
[1]   data: {
[1]     name: 'tt',
[1]     addressLine1: '100 Centre St',
[1]     addressLine2: '',
[1]     city: 'New York',
[1]     region: 'NY',
[1]     postalCode: '10013',
[1]     country: 'US',
[1]     countryLongName: 'United States',
[1]     owner: {
[1]       connect: {
[1]         id: '018b9981-fae0-45ca-8da2-efe7a2d66a12'
[1]       }
[1]     },
[1]     invite: {
[1]       connect: {
[1]         code: 'test'
[1]       }
[1]     },
[1]     wallets: {
[1]       connect: [
[1]         {
[1]           walletAddress: '0x7C3cf25108a28Bf70dBC52aD2A125f8f64cad90C'
[1]         }
[1]       ]
[1]     },
[1]     users: {
[1]       connect: [
[1]         {
[1]           id: '018b9981-fae0-45ca-8da2-efe7a2d66a12'                                                                                                                                                              [1]         }
[1]       ]                                                                                                                                                                                                           [1]     },
[1]     balance: 0
[1]   }
[1] })
[1] 2022-06-17T19:15:49.526Z prisma:client  Generated request:
[1] 2022-06-17T19:15:49.526Z prisma:client  mutation {
[1]   createOneTeam(data: {
[1]     name: "tt"
[1]     addressLine1: "100 Centre St"
[1]     addressLine2: ""
[1]     city: "New York"                                                                                                                                                                                              [1]     region: "NY"                                                                                                                                                                                                  [1]     postalCode: "10013"                                                                                                                                                                                           [1]     country: "US"                                                                                                                                                                                                 [1]     countryLongName: "United States"                                                                                                                                                                              [1]     owner: {
[1]       connect: {                                                                                                                                                                                                  [1]         id: "018b9981-fae0-45ca-8da2-efe7a2d66a12"
[1]       }
[1]     }
[1]     invite: {
[1]       connect: {
[1]         code: "test"
[1]       }
[1]     }
[1]     wallets: {
[1]       connect: [
[1]         {
[1]           walletAddress: "0x7C3cf25108a28Bf70dBC52aD2A125f8f64cad90C"
[1]         }
[1]       ]
[1]     }
[1]     users: {
[1]       connect: [
[1]         {
[1]           id: "018b9981-fae0-45ca-8da2-efe7a2d66a12"
[1]         }
[1]       ]
[1]     }
[1]     balance: 0
[1]   }) {
[1]     id
[1]     name
[1]     balance
[1]     addressLine1
[1]     addressLine2
[1]     city
[1]     country
[1]     countryLongName
[1]     postalCode
[1]     region
[1]     taxId
[1]     onboardingStep
[1]     ownerId
[1]     createdAt
[1]     updatedAt
[1]   }
[1] }

postgres output:

[2] rain-api-db-1  | 2022-06-17 19:15:49.571 UTC [29] ERROR:  null value in column "id" of relation "Team" violates not-null constraint
[2] rain-api-db-1  | 2022-06-17 19:15:49.571 UTC [29] DETAIL:  Failing row contains (null, tt, 0, 100 Centre St, , New York, US, United States, 10013, NY, null, 0, 018b9981-fae0-45ca-8da2-efe7a2d66a12, 2022-06-17 19:15:49.526, 2022-06-17 19:15:49.526).
[2] rain-api-db-1  | 2022-06-17 19:15:49.571 UTC [29] STATEMENT:  INSERT INTO "public"."Team" ("id","name","balance","addressLine1","addressLine2","city","country","countryLongName","postalCode","region","onboardingStep","ownerId","createdAt","updatedAt") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14) RETURNING "public"."Team"."id"

Environment & setup

  • OS: Mac OS
  • Database: Postgresql
  • Node.js version: 16.14.2

Prisma Version

prisma                  : 3.15.1
@prisma/client          : 3.15.1
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine 461d6a05159055555eb7dfb337c9fb271cbd4d7e (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli 461d6a05159055555eb7dfb337c9fb271cbd4d7e (at node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine    : introspection-core 461d6a05159055555eb7dfb337c9fb271cbd4d7e (at node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary           : prisma-fmt 461d6a05159055555eb7dfb337c9fb271cbd4d7e (at node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Default Engines Hash    : 461d6a05159055555eb7dfb337c9fb271cbd4d7e
Studio                  : 0.462.0

About this issue

  • Original URL
  • State: open
  • Created 2 years ago
  • Reactions: 4
  • Comments: 16 (5 by maintainers)

Most upvoted comments

Don’t know if this is related. But I experience similar behavior today with 5.1.1 with explicit many-to-many relationship.

Schema

model Player {
  id               String   @id @default(cuid())
  name             String   @db.VarChar(100)
  createdAt        DateTime @default(now())
  updatedAt        DateTime @updatedAt

  matches            MatchesOnPlayers[]
}

model Match {
  id                 String              @id @default(cuid())
  type               String              @db.VarChar(20)
  players            MatchesOnPlayers[]
  date               DateTime            @default(now())
  createdAt          DateTime            @default(now())
  updatedAt          DateTime            @updatedAt
}

model MatchesOnPlayers {
  id        String   @id @default(cuid())
  player    Player   @relation(fields: [playerId], references: [id])
  playerId  String   
  match     Match    @relation(fields: [matchId], references: [id])
  matchId   String   
  team      Int      @db.SmallInt
  result    String   @db.VarChar(20)
  score     Int      @db.SmallInt
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Notice how MatchesOnPlayers is an explicit table modal to connect Player and Match table with additional metadata. And also I declared an id field as primary key instead of using @@id([playerId, matchId]) I don’t know if this is the problem but when I call create on match with connect on players as follow

  await prisma.match.create({
    data: {
      type: MatchType.Doubles,
      date: new Date(),https://github.com/prisma/prisma/issues/13885#top
      players: {
        create: [
          ...team1PlayerIds.map((id) => ({
            team: 1,
            score: score1,
            result: score1 > score2 ? MatchResult.Win : MatchResult.Loss,
            player: {
              connect: { id },
            },
          })),
          ...team2PlayerIds.map((id) => ({
            team: 2,
            score: score2,
            result: score2 > score1 ? MatchResult.Win : MatchResult.Loss,
            player: {
              connect: { id },
            },
          })),
        ],
      },
    },
  });

I got same Null constraint violation on the fields: (id) error and with query

INTO "public"."MatchesOnPlayers" ("playerId","matchId","team","result","score","createdAt","updatedAt") VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING "public"."MatchesOnPlayers"."playerId", "public"."MatchesOnPlayers"."matchId"

Notice how Id is not even provided here.

+1
WangHansen on Aug 9, 2023

I was using TypegraphQL Prisma at the time, so hard for me to give an exact log. It is easily replicated from the schema above, I would think. We followed @solumos 's suggestion and it solved the problem for us.

+1
mattkindy on Aug 3, 2022

@mattkindy part of my realization in working around this was that a model can’t enforce a required foreign key if that foreign key does not exist on the model’s table.

So if QuoteBidEdits must have a QuoteBid, there must be a QuoteBid ID persisted on the QuoteBidEdit table.

That’s to say: there’s not a way for postgres to require that a QuoteBidEdit be associated with a QuoteBid as you’ve designed. You might consider moving the foreign key to QuoteBidEdit if possible.

After working around this, my primary issue is the resulting error message — it’s particularly unclear that the root issue is the underlying data modeling.

e.g. something like

model QuoteBid {
  id                        String         @default(cuid()) @id @db.VarChar(30)
  createdAt                 DateTime       @default(now())
  updatedAt                 DateTime       @updatedAt
  ...
  pendingEdit               QuoteBidEdit? @relation(fields: [id], references: [originalBidId], onUpdate: NoAction)
}

model QuoteBidEdit {
  id                String    @default(cuid()) @id @db.VarChar(30)
  createdAt         DateTime  @default(now())
  updatedAt         DateTime  @updatedAt
  ...
  originalBidId   String       @map("originalBid") @db.VarChar(30)
  originalBid       QuoteBid 
}
+1
solumos on Jul 19, 2022
Read more comments on GitHub
← prisma: API calls failing with `Error querying the database: db error: FATAL: remaining connection slots are reserved for non-replication superuser connections`
prisma: [aws rds mysql] Error: P1001 Can't reach database server using ssl connect →