search

payloadcms / payload

Payload is the open-source, fullstack Next.js framework, giving you instant backend superpowers. Get a full TypeScript backend and admin panel instantly. Use Payload as a headless CMS or for building powerful applications.
https://payloadcms.com
MIT License
23.57k stars 1.5k forks source link

Migration unable to run (Postgresql) #8361

Closed Thijmen closed 1 week ago

Thijmen commented 1 week ago

Link to reproduction

No response

Environment Info

Binaries:
  Node: 20.12.1
  npm: 10.5.0
  Yarn: 1.22.22
  pnpm: 8.15.6
Relevant Packages:
  payload: 3.0.0-beta.108
  next: 15.0.0-canary.160
  @payloadcms/db-postgres: 3.0.0-beta.108
  @payloadcms/email-nodemailer: 3.0.0-beta.108
  @payloadcms/graphql: 3.0.0-beta.108
  @payloadcms/live-preview: 3.0.0-beta.108
  @payloadcms/live-preview-react: 3.0.0-beta.108
  @payloadcms/next/utilities: 3.0.0-beta.108
  @payloadcms/plugin-cloud: 3.0.0-beta.108
  @payloadcms/plugin-cloud-storage: 3.0.0-beta.108
  @payloadcms/plugin-form-builder: 3.0.0-beta.108
  @payloadcms/plugin-nested-docs: 3.0.0-beta.108
  @payloadcms/plugin-redirects: 3.0.0-beta.108
  @payloadcms/plugin-seo: 3.0.0-beta.108
  @payloadcms/richtext-lexical: 3.0.0-beta.108
  @payloadcms/translations: 3.0.0-beta.108
  @payloadcms/ui/shared: 3.0.0-beta.108
  react: 19.0.0-rc-5dcb0097-20240918
  react-dom: 19.0.0-rc-5dcb0097-20240918
Operating System:
  Platform: darwin
  Arch: arm64
  Version: Darwin Kernel Version 23.6.0: Mon Jul 29 21:14:30 PDT 2024; root:xnu-10063.141.2~1/RELEASE_ARM64_T6000
  Available memory (MB): 32768
  Available CPU cores: 10

Describe the Bug

So, I have a global that looks like this:

import type { GlobalConfig, Payload } from 'payload'

export const Nav: GlobalConfig = {
    slug: 'nav',
    label: 'Navigation',
    fields: [
        {
            name: 'links',
            label: 'Links',
            type: 'array',
            minRows: 1,
            fields: [
                {
                    name: 'label',
                    label: 'Label',
                    type: 'text',
                },
                {
                    name: 'icon',
                    label: 'Icon',
                    type: 'text',
                },
                {
                    name: 'page',
                    type: 'relationship',
                    relationTo: ['pages', 'projects'], // "pages" is the slug of an existing collection
                    required: false,
                },
                {
                    name: 'url',
                    type: 'text',
                    label: 'URL',
                    required: false,
                },
            ],
        },
    ],
}

I create a migration for Postgresql, and it looks like this:

  CREATE TABLE IF NOT EXISTS "nav_links" (
    "_order" integer NOT NULL,
    "_parent_id" integer NOT NULL,
    "id" varchar PRIMARY KEY NOT NULL,
    "label" varchar,
    "icon" varchar,
    "url" varchar
  );

Then I've decided to make the icon a list item:

...
                {
                    name: 'icon',
                    label: 'Icon',
                    type: 'select',
                    options: [
                        'home',
                        'project',
                        'dashboard',
                        'blog',
                        'profile',
                        'analytics',
                    ],
                    required: true,
                },
...

That will result in a migration that looks like this:

export async function up({ payload, req }: MigrateUpArgs): Promise<void> {
  await payload.db.drizzle.execute(sql`
   DO $$ BEGIN
   CREATE TYPE "public"."enum_nav_links_icon" AS ENUM('home', 'project', 'dashboard', 'blog', 'profile', 'analytics');
  EXCEPTION
   WHEN duplicate_object THEN null;
  END $$;
  ALTER TABLE "nav_links" ALTER COLUMN "icon" SET DATA TYPE enum_nav_links_icon;
  ALTER TABLE "nav_links" ALTER COLUMN "icon" SET NOT NULL;`)
}

Now, this will not be able to run because of the following error:

[15:52:14] ERROR: Error running migration 20240922_125434 column "icon" cannot be cast automatically to type enum_nav_links_icon. You might need to specify "USING icon::enum_nav_links_icon"..
    err: {
      "type": "DatabaseError",
      "message": "column \"icon\" cannot be cast automatically to type enum_nav_links_icon",
      "stack":
          error: column "icon" cannot be cast automatically to type enum_nav_links_icon
              at /Users/thijmen/code/private/thijmen.dev/thijmen.dev/node_modules/.pnpm/pg-pool@3.7.0_pg@8.11.3/node_modules/pg-pool/index.js:45:11
              at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
              at Object.up (/Users/thijmen/code/private/thijmen.dev/thijmen.dev/src/migrations/20240922_125434.ts:4:3)
              at runMigrationFile (/Users/thijmen/code/private/thijmen.dev/thijmen.dev/node_modules/.pnpm/@payloadcms+drizzle@3.0.0-beta.108_payload@3.0.0-beta.108_pg@8.11.3_react@19.0.0-rc-5dcb0097-_75l4mfnhvwz4wqxr3anacz55wi/node_modules/@payloadcms/drizzle/dist/migrate.js:67:9)
              at Object.migrate (/Users/thijmen/code/private/thijmen.dev/thijmen.dev/node_modules/.pnpm/@payloadcms+drizzle@3.0.0-beta.108_payload@3.0.0-beta.108_pg@8.11.3_react@19.0.0-rc-5dcb0097-_75l4mfnhvwz4wqxr3anacz55wi/node_modules/@payloadcms/drizzle/dist/migrate.js:52:9)
              at migrate (/Users/thijmen/code/private/thijmen.dev/thijmen.dev/node_modules/.pnpm/payload@3.0.0-beta.108_graphql@16.9.0_monaco-editor@0.49.0_react-dom@19.0.0-rc-5dcb0097-20240_dp7j6tjxxpvglpocm23nnvqgz4/node_modules/payload/src/bin/migrate.ts:66:7)
              at async start (file:///Users/thijmen/code/private/thijmen.dev/thijmen.dev/node_modules/.pnpm/payload@3.0.0-beta.108_graphql@16.9.0_monaco-editor@0.49.0_react-dom@19.0.0-rc-5dcb0097-20240_dp7j6tjxxpvglpocm23nnvqgz4/node_modules/payload/bin.js:30:7)
      "length": 203,
      "name": "error",
      "severity": "ERROR",
      "code": "42804",
      "hint": "You might need to specify \"USING icon::enum_nav_links_icon\".",
      "file": "tablecmds.c",
      "line": "12391",
      "routine": "ATPrepAlterColumnType"
    }

I am no postgresql guru at all, so I thought: okay, maybe there's some value in the database which does not meet the values allowed. So, I added a little sql part:

export async function up({ payload, req }: MigrateUpArgs): Promise<void> {
  await payload.db.drizzle.execute(sql`
   DO $$ BEGIN
   CREATE TYPE "public"."enum_nav_links_icon" AS ENUM('home', 'project', 'dashboard', 'blog', 'profile', 'analytics');
  EXCEPTION
   WHEN duplicate_object THEN null;
  END $$;
  UPDATE "nav_links" SET "icon" = 'home';
  ALTER TABLE "nav_links" ALTER COLUMN "icon" SET DATA TYPE enum_nav_links_icon;
  ALTER TABLE "nav_links" ALTER COLUMN "icon" SET NOT NULL;`)
}

Unfortunately this does not work either.

I am unsure if this is a bug or expected behaviour.

Reproduction Steps

See steps above

Adapters and Plugins

db-postgres

r1tsuu commented 1 week ago

Hey @Thijmen, there are 2 ways It seems like on the migration create prompt you choosed "rename the column", which looks like can't be done with Drizzle for now. You can choose to drop the previous column and add new, you'll lose the existing data in this column though.

Another way is to modify the SQL script in the migration accordingly so that it properly adds this column without losing the data. This could work:

 ALTER TABLE "nav_links" ALTER COLUMN "icon" SET DATA TYPE enum_nav_links_icon USING "icon"::enum_nav_links_icon;

But you need to ensure that all your current icons values are within the enum values range.

Thijmen commented 1 week ago

Hi @r1tsuu,

This is the full script now;

export async function up({ payload, req }: MigrateUpArgs): Promise<void> {
  await payload.db.drizzle.execute(sql`
   DO $$ BEGIN
   CREATE TYPE "public"."enum_nav_links_icon" AS ENUM('home', 'project', 'dashboard', 'blog', 'profile', 'analytics');
  EXCEPTION
   WHEN duplicate_object THEN null;
  END $$;
  UPDATE "nav_links" SET "icon" = 'home';
  ALTER TABLE "nav_links" ALTER COLUMN "icon" SET DATA TYPE enum_nav_links_icon USING "icon"::enum_nav_links_icon;
  ALTER TABLE "nav_links" ALTER COLUMN "icon" SET NOT NULL;`)
}

I cant recall that I've used 'rename the column`, but this is fine now! :) Just wanted to make sure if this was expected or not.

github-actions[bot] commented 6 days ago

This issue has been automatically locked. Please open a new issue if this issue persists with any additional detail.