Prisma v4 breaks support for empty dbgenerated() - invalid migration created with no schema change

[andyjy]

Bug description

Using Postgres generated columns via @default(dbgenerated()) with custom Postgres GENERATED AWAYS AS ... STORED SQL in migration worked just fine through Prisma v3; breaks with Prisma 4.0.0 onwards.

Reproduction repo: https://github.com/andyjy/prisma-dbgenerated-bug-repro

Reproduction details:

schema.prisma:

model table {
    id String @id
    hereBeDragons String @default(dbgenerated())
}

..with custom migration to use Postgres generated field:

migration.sql:

CREATE TABLE "table" (
    ...

    -- manually edited migration to make column generated
    "hereBeDragons" TEXT NOT NULL GENERATED ALWAYS AS (
'this row ID is: '::text || "id") STORED,

    ...

Worked just fine through prisma 3.15.2; prisma 4.0.0 onwards produces invalid migration with no schema change
that attempts to DROP DEFAULT (which we don't want to do, and fails since Postgres generated columns require
use of DROP EXPRESSION if we were actually to want to convert the generated column to a normal one).

...invalid_failed_migration/migration.sql:

ALTER TABLE "table" ALTER COLUMN "hereBeDragons" DROP DEFAULT;

(unwanted new migration fails with error:)

Database error code: 42601

Database error:
ERROR: column "hereBeDragons" of relation "table" is a generated column
HINT: Use ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION instead.

How to reproduce

Reproduction repo: https://github.com/andyjy/prisma-dbgenerated-bug-repro

Expected behavior

Prisma 4 continues to support this schema and migration history with no attempt
to generate a new migration dropping the default value for the column.

Prisma information

Schema above + in reproduction repo

Environment & setup

• OS: MacOS
• Database: PostgreSQL
• Node.js version: v16.16.0

Prisma Version

works up to 3.15.2, fails since 4.0.0 (tested up to 4.2.1)

[janpio]

I can confirm that on commit 6c36a0f5565a9759ad887473637980c550ba10a7 I can migrate deploy and then run migrate dev with it reporting Already in sync, no schema change or pending migration was found..

I can unfortunately also confirm that upgrading to the newest Prisma version then leads to migrate dev creating this migration, as described:

-- AlterTable
ALTER TABLE "table" ALTER COLUMN "hereBeDragons" DROP DEFAULT;

[KunJakob]

We are also running into this error which has been a big dealbreaker for us, as it was a perfect stop-gap before computed fields are officially supported in Prisma.

[tomhoule]

Due to CI issues, the version of prisma-engines that was released in 4.3.0 is a bit older and does not contain this fix, sadly. So it is merged on main, it will be on 4.4.0 dev versions and in the 4.4.0 release, but it won't be in 4.3.0.

[1Vlod]

@tomhoule Hi, could you please let us know the expected release date for version 4.4.0?

I just faced with this one and it blocked me. As I understood there are only workarounds: use version lower than 4.x.x or wait for release 4.4.0 version, am I right?

[tomhoule]

4.4.0 is scheduled for next Tuesday (Sept. 27). And yes this is a bad regression. There is another workaround: picking a dev release version on npm. They are built on a rolling basis so the fix is already in.

[andyjy]

@1Vlod the other workaround is to edit the failing migration file to remove the offending lines and reapply using prisma migrate deploy or prisma migrate reset.

[andyjy]

now enjoying the fix in 4.4.0 - thanks @tomhoule 🙏

[jkcorrea]

@andyjy @tomhoule This seems to still not be working for me (tried v4.4 and v4.5-dev). Am I doing something wrong here?

Prisma schema:

model Event {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  name  String
  start DateTime
  end   DateTime
  fts   Unsupported("tsvector")? @default(dbgenerated())

  @@index([fts], type: Gin)
  @@map("event")
}

Modified SQL migration:

ALTER TABLE "event" ADD COLUMN "fts" tsvector GENERATED ALWAYS AS (
  to_tsvector('english',
    id::text -- add the cuid to the vector
    || ' ' || COALESCE("name", '') -- concat more fields (or empty string if null)
  )
) STORED;
CREATE INDEX event_fts_idx ON "event" using gin (fts); -- generate the index

Prisma migrate then generates and tries to run this migration:

-- AlterTable
ALTER TABLE "event" ALTER COLUMN "fts" DROP DEFAULT;

Which will fail since the column is generated

[andyjy]

@jkcorrea you might find it useful to fork my minimum reproducible example repo and verify whether you still get this error if you swap out my example schema for yours: https://github.com/andyjy/prisma-dbgenerated-bug-repro

Did you remember to update the prisma devDependency to v4.4.0 in addition to @prisma/client?

[jkcorrea]

@jkcorrea you might find it useful to fork my minimum reproducible example repo and verify whether you still get this error if you swap out my example schema for yours: https://github.com/andyjy/prisma-dbgenerated-bug-repro

Did you remember to update the prisma devDependency to v4.4.0 in addition to @prisma/client?

That was very helpful, thanks @andyjy. Was able to use your minimum repro to figure out that it's due to using an Unsupported() type column:

https://github.com/jkcorrea/prisma-unsupported-dbgenerated-bug

Happening in v4.4.0 and v4.5 & for both prisma and @prisma/client

[tomhoule]

@jkcorrea thanks for putting the effort in writing a reproduction repo for this. Please open a new issue, since it's a different problem, otherwise it will be forgotten if it's only a comment in a closed issue.

[jkcorrea]

@jkcorrea thanks for putting the effort in writing a reproduction repo for this. Please open a new issue, since it's a different problem, otherwise it will be forgotten if it's only a comment in a closed issue.

Was in the process of doing just that, thanks :)

#15654

[tomhoule]

awesome, thanks!