prisma migrate dev will not allow for db level default on scalar list

[nicklloyd]

Bug description

Similar to #8330, we have a field that we want to store in our Postgres db as a scalar list.

As stated here:

To work around this issue, you can set the default value of array fields to {} at a database level

When we set the default in pg however, any subsequent run drops the default.

How to reproduce

1. Declare field as an array.

model Lead {
  requestedServices String[]
}

2. Ensure db and schema are aligned

prisma migrate dev

3. Set default field value in pg

ALTER TABLE "Lead" ALTER COLUMN "requestedServices" SET DEFAULT '{}';

4. Run migrate again

prisma migrate dev

CLI Output:

Datasource "db": PostgreSQL database "prisma_dev", schema "public" at "localhost:5432"

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

[*] Changed the `Lead` table
  [*] Altered column `requestedServices` (default changed from `None` to `Some(DbGenerated("'{}'::text[]"))`)

? We need to reset the PostgreSQL database "prisma_dev" at "localhost:5432".
Do you want to continue? All data will be lost. › (y/N)

Answering y to the prompt then drops data and default on field.
Answering N cancels migration and does not affect db, but prompt returns on next run.

As an aside, if the steps above are repeated, except a new migration is generated for altering a different table/field, the new migration file is generated, but contains only intended changes:

-- AlterTable
ALTER TABLE "PartnerRequest" ALTER COLUMN "status" SET DEFAULT E'UNCONFIRMED';

However the default on the other field is also dropped.

Expected behavior

If prisma cannot support setting a DEFAULT on a scalar list (pity...), I should at least be able to set one on a db level and not have it reverted or cause issues with continuing workflow.

Prisma information

model Lead {
  id                  String      @id @unique @default(cuid()) @db.VarChar(30)
  name                String?     @db.Text
  firstName           String?     @db.Text
  lastName            String?     @db.Text
  email               String?     @db.Text
  telephone           String?     @db.Text
  role                String?     @db.Text
  companyName         String?     @db.Text
  website             String?     @db.Text
  notes               String?     @db.Text
  referer             String?     @db.Text
  srcUrl              String?     @db.Text
  // add default '{}' when prisma is ready to support
  requestedServices   String[]
  createdAt           DateTime    @default(now()) @db.Timestamp
  updatedAt           DateTime    @default(now()) @db.Timestamp
}

Environment & setup

• OS: Mac 12.1 (21C52)
• Database: postgres:14.1-alpine
• Node.js version: v14.17.6

Prisma Version

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

[nicklloyd]

@janpio: hoping the above helps get this one some attention

[janpio]

Super wild idea: Can you try adding a @default(dbgenerated("{}")) to your schema for that field to see if this makes a difference?

[nicklloyd]

Any attempt to set default on list is not happy...

Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "prisma_dev", schema "public" at "localhost:5432"

Error: Schema parsing
error: Error parsing attribute "@default": Cannot set a default value on list field.
  -->  schema.prisma:123
   | 
122 |   installationRequest Boolean?    @default(false)
123 |   requestedServices   String[]    @default(dbgenerated("{}"))
   | 

[flesler]

This would be a nice change and doesn't seem huge. Just allow the current syntax to be used without an artificial limitation
@default(dbgenerated("{}")) 🙏

[janpio]

Is DEFAULT {} the correct PostgreSQL syntax to define an empty list as default?

[flesler]

No, rather DEFAULT '{}' with apostrophes. It automatically casts to the real type.
For example: String[] -> DEFAULT '{}'::text[]

[janpio]

Then the dbgenerated value probably would need to look like this:

@default(dbgenerated("'{}'"))

With this is should generate proper SQL then.

Buuuut, I know we are actually planning to add proper @default([]) (or @default([1, 2]) with values) support soon, which should remove the artificial limitation at the same time.

[flesler]

Buuuut, I know we are actually planning to add proper @default([]) (or @default([1, 2]) with values) support soon, which should remove the artificial limitation at the same time.

That would be way better. Was just trying to get the fastest solution out even if not ideal

[EvanDarwin]

Buuuut, I know we are actually planning to add proper @default([]) (or @default([1, 2]) with values) support soon, which should remove the artificial limitation at the same time.

Wonderful! This issue is a bit of a blocker for me as well, so I've been watching this issue closely.

[janpio]

This will soon be solved via #8330