prisma: ConnectorError Some(WasNull()) on `String[]` field with `NULL` entries

Bug description

When running Prisma in production I occasionally get this error:

Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: FromSql(6), cause: Some(WasNull) }) })

How to reproduce

I cannot reproduce this reliably, but can provide DM a Sentry error for diagnosis.

Expected behavior

No error should be thrown.

Prisma information

Prisma Query:

  const user = await prisma.user.findUnique({
    where: { id: data.userId },
    rejectOnNotFound: true,
  });

Relevant schema:

model User {
  id     Int     @id @default(autoincrement())
}

Environment & setup

OS:
Database:
Node.js version:

Running on Heroku with PostgreSQL 13 against Node.js v16.13.1.

Prisma Version

prisma                  : 3.4.0
@prisma/client          : 3.4.0
Current platform        : debian-openssl-1.1.x
Query Engine (Node-API) : libquery-engine 1c9fdaa9e2319b814822d6dbfd0a69e1fcc13a85 (at node_modules/@prisma/engines/libquery_engine-debian-openssl-1.1.x.so.node)
Migration Engine        : migration-engine-cli 1c9fdaa9e2319b814822d6dbfd0a69e1fcc13a85 (at node_modules/@prisma/engines/migration-engine-debian-openssl-1.1.x)
Introspection Engine    : introspection-core 1c9fdaa9e2319b814822d6dbfd0a69e1fcc13a85 (at node_modules/@prisma/engines/introspection-engine-debian-openssl-1.1.x)
Format Binary           : prisma-fmt 1c9fdaa9e2319b814822d6dbfd0a69e1fcc13a85 (at node_modules/@prisma/engines/prisma-fmt-debian-openssl-1.1.x)
Default Engines Hash    : 1c9fdaa9e2319b814822d6dbfd0a69e1fcc13a85
Studio                  : 0.438.0

About this issue

Original URL
State: closed
Created 2 years ago
Comments: 16 (9 by maintainers)

Most upvoted comments

Thanks for the concise reproduction @westmark. I was able to reproduce the error.

pantharshit00 on Mar 22, 2022

Hey,

Given the clear error message we now have, is it acceptable to close this?

To be clear, Prisma unfortunately doesn’t support nullable scalar lists at the moment but we already have a feature request here.

Weakky on Nov 29, 2022

@casey-chow Would this fit what you observed? https://github.com/westmark/prisma-error-repro/blob/main/seed.sql#L1

Yes!

casey-chow on Mar 28, 2022

@janpio Gladly. You can have a full repro:

https://github.com/westmark/prisma-error-repro

westmark on Mar 22, 2022

Okay, I figured out what was causing it! I had a field that was of type String[] but it had some NULL values in some results. When Prisma read those null values, it would throw this error. To fix, I ran the following migration to add a check constraint and ensure nulls don’t make their way back in:

BEGIN;

UPDATE "user"
SET "emails" = array_remove("emails", NULL)
WHERE array_position("emails", NULL) IS NOT NULL;

ALTER TABLE "user"
ADD CONSTRAINT "user_email_check"
  CHECK (array_position("emails", NULL) IS NULL);

COMMIT;

I can confirm deploying this migration has stopped this error from appearing at least for now.

casey-chow on Mar 16, 2022

I’ll go ahead and email you the Sentry error!

Do all these queries have something in common somehow? Similar data structure? Can you log these in cases of error?

All instances of the error are of this query. I’ve looked at the retry in a couple instances and it seems retrying works just fine.

Would it be possible for you to update to the most recent one?

Yep! I’m on 3.10.0 now as of 4 days ago.

casey-chow on Mar 11, 2022