Postgres Single Quote Escaping Breaking Migrations

[ajhollowayvrm]

Bug description

Prisma Migrate does not handle double single quotes ('') in a schema. This is an issues because Postgres uses double single quotes ('') to escape a single quote (') in a JSONB default.

How to reproduce

1. Create a schema.prisma file or pull from a db with the following schema (this is from an introspection on my specific DB; Notice the two quotes ('')):

birthday_award                        Json?    @default("{\"amount\": 250, \"status\": false, \"message\": \"HAPPY BIRTHDAY, @receiver_username!! Here''s +amount to celebrate!\", \"visibility\": \"Publicly Visible\"}")

2. Run npx prisma migrate dev --name init
3. Notice the error.

Expected behavior

The migration to successfully apply.

Prisma information

The line breaking the migration:

birthday_award                        Json?    @default("{\"amount\": 250, \"status\": false, \"message\": \"HAPPY BIRTHDAY, @receiver_username!! Here''s +amount to celebrate!\", \"visibility\": \"Publicly Visible\"}")

The error given:

Applying migration `20220228230046_init`
Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20220228230046_init

Database error code: 42601

Database error:
ERROR: syntax error at or near "\"

Position:
152     "require_increments_ten" BOOLEAN DEFAULT false,
153     "require_points" BOOLEAN DEFAULT true,
154     "reward_redemption_enabled" BOOLEAN DEFAULT false,
155     "allow_user_points" BOOLEAN DEFAULT true,
156     "email_sending_enabled" BOOLEAN DEFAULT true,
157     "birthday_award" JSONB DEFAULT '{"amount": 250, "status": false, "message": "HAPPY BIRTHDAY, @receiver_username!! Here\'\'s +amount to celebrate!", "visibility": "Publicly Visible"}',

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("42601"), message: "syntax error at or near \"\\\"", detail: None, hint: None, position: Some(Original(5069)), where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("scan.l"), line: Some(1180), routine: Some("scanner_yyerror") }

Environment & setup

• OS: MacOS Monterey 12.2.1
• Database: PostgreSQL accessed with pgAdmin 4
• Node.js version: v14.19.0

Prisma Version

Environment variables loaded from .env
prisma                  : 3.10.0
@prisma/client          : 3.10.0
Current platform        : darwin
Query Engine (Node-API) : libquery-engine 73e60b76d394f8d37d8ebd1f8918c79029f0db86 (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli 73e60b76d394f8d37d8ebd1f8918c79029f0db86 (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine    : introspection-core 73e60b76d394f8d37d8ebd1f8918c79029f0db86 (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary           : prisma-fmt 73e60b76d394f8d37d8ebd1f8918c79029f0db86 (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash    : 73e60b76d394f8d37d8ebd1f8918c79029f0db86
Studio                  : 0.458.0

[ajhollowayvrm]

Wondering about any updates on this?

[janpio]

I can reproduce this:

npm init -y && npm install prisma && npx prisma init --url postgresql://viol...fish and then:

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

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

model Foo {
  id             Int   @id
  bar Json? @default("{\"message\": \"This message includes a quote: Here''s it!\"}")
}

Run npx prisma migrate dev --name init outputs:

PS C:\Users\Jan\Documents\throwaway\12095> npx prisma migrate dev --name init
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": PostgreSQL database "b..h", schema "public" at "db-..."

Applying migration `20220315220148_init`
Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20220315220148_init

Database error code: 42601

Database error:
ERROR: syntax error at or near "\"

Position:
  0
  1 -- CreateTable
  2 CREATE TABLE "Foo" (
  3     "id" INTEGER NOT NULL,
  4     "bar" JSONB DEFAULT '{"message": "This message includes a quote: Here\'\'s it!"}',

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("42601"), message: "syntax error at or near \"\\\"", detail: None, hint: None, position: Some(Original(139)), where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("scan.l"), line: Some(1181), routine: Some("scanner_yyerror") }

The migration that was generated:

-- CreateTable
CREATE TABLE "Foo" (
    "id" INTEGER NOT NULL,
    "bar" JSONB DEFAULT '{"message": "This message includes a quote: Here\'\'s it!"}',

    CONSTRAINT "Foo_pkey" PRIMARY KEY ("id")
);

Can you share the original SQL of the table that includes birthday_award. This can either be a problem in Introspection already which created the schema, or indeed in Migrations that tries to create a SQL query to persist this data and then execute it.

[janpio]

Update: I assumed that the original table should say Here's and modified the generated SQL to match that:

CREATE TABLE "Foo" (
    "id" INTEGER NOT NULL,
    "bar" JSONB DEFAULT '{"message": "This message includes a quote: Here''s it!"}',

    CONSTRAINT "Foo_pkey" PRIMARY KEY ("id")
);

When you create a new row of that table, you get a Here's via the default value.
But when you db pull, it will write the schema we have above, and it will then on migrate dev again create broken SQL instead of the SQL of the original table.

[ajhollowayvrm]

Update: I assumed that the original table should say Here's and modified the generated SQL to match that:

CREATE TABLE "Foo" (
    "id" INTEGER NOT NULL,
    "bar" JSONB DEFAULT '{"message": "This message includes a quote: Here''s it!"}',

    CONSTRAINT "Foo_pkey" PRIMARY KEY ("id")
);

When you create a new row of that table, you get a Here's via the default value. But when you db pull, it will write the schema we have above, and it will then on migrate dev again create broken SQL instead of the SQL of the original table.

Thank you, this is the correct assumption and the correct bug. Thank you for looking into it.

[ajhollowayvrm]

Bump on this.

[ajhollowayvrm]

Another bump here. We could really use this for our migrations. Also, workarounds are acceptable.