Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[3.0 Beta] Changes not applying to tables when using schemaName #5822

Closed
maneike opened this issue Apr 12, 2024 · 16 comments
Closed

[3.0 Beta] Changes not applying to tables when using schemaName #5822

maneike opened this issue Apr 12, 2024 · 16 comments
Assignees
Labels
dependency-issue The issue is in a dependency of payload - not payload itself

Comments

@maneike
Copy link

maneike commented Apr 12, 2024

Link to reproduction

No response

Describe the Bug

I've encountered a bug using payload-3.0-demo, where upon initializing Payload, a custom schema in my database is not being created. I've implemented it like so:

  db: postgresAdapter({
    pool: {
      connectionString: process.env.DATABASE_URI || "",
    },
    schemaName: "payload",
  }),

Here is the error after running simple pnpm dev:

@app/admin:dev:  ⨯ Internal error: error: schema "payload" does not exist
@app/admin:dev:     at /node_modules/pg-pool/index.js:45:11
@app/admin:dev:     at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
@app/admin:dev:     at async DrizzleORMPgClient.query (/node_modules/drizzle-kit/payload.js:34498:21)
@app/admin:dev:     at async apply (/node_modules/drizzle-kit/payload.js:36648:9)
@app/admin:dev:     at async pushDevSchema (/node_modules/@payloadcms/db-postgres/dist/utilities/pushDevSchema.js:47:5)
@app/admin:dev:     at async Object.connect (/node_modules/@payloadcms/db-postgres/dist/connect.js:84:5)
@app/admin:dev:     at async BasePayload.init (/node_modules/payload/dist/index.js:212:13)
@app/admin:dev: digest: "1355805570"

So I've created a schema with the specified name in my database and tables were properly created within it. I have the impression that this is a workaround for the problem, as when modifying the collection (e.g., changing the slug name in pages collection), I've received a similar error.

slug: "title" -> slug: "page_title"

Compiled in 448ms (4012 modules)
 ⨯ Internal error: error: column "page_title" does not exist
    at eval (./node_modules/.pnpm/pg@8.11.3/node_modules/pg/lib/client.js:526:17)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async eval (./node_modules/.pnpm/drizzle-orm@0.29.4_@libsql+client@0.5.6_@types+react@18.2.74_pg@8.11.3_react@18.2.0/node_modules/drizzle-orm/node-postgres/session.js:59:22)
    at async find (./node_modules/.pnpm/@payloadcms+db-postgres@3.0.0-beta.2_@types+react@18.2.74_payload@3.0.0-beta.2_react@18.2.0/node_modules/@payloadcms/db-postgres/dist/find/findMany.js:141:21)
    at async findOperation (./node_modules/.pnpm/payload@3.0.0-beta.2_@swc+core@1.4.12_@swc+types@0.1.6/node_modules/payload/dist/collections/operations/find.js:104:22)
    at async ListView (./node_modules/.pnpm/@payloadcms+next@3.0.0-beta.2_@types+react@18.2.74_http-status@1.6.2_monaco-editor@0.47.0_nex_yfexsn5fqfva4wuhfmx6xps6u4/node_modules/@payloadcms/next/dist/views/List/index.js:77:22)
digest: "2491816213"

To Reproduce

  1. Create a postgres database (I've used Supabase)
  2. Clone https://github.com/payloadcms/payload-3.0-demo and set up config and environment variables accordingly
  3. Add schemaName: "payload" to payload.config.ts
  4. Run pnpm dev and go to localhost:3000/admin

Payload Version

3.0.0-beta.6

Adapters and Plugins

@payloadcms/db-postgres

@maneike maneike added the status: needs-triage Possible bug which hasn't been reproduced yet label Apr 12, 2024
@DanRibbens
Copy link
Contributor

This might be related to #5473

I am investigating this.

@DanRibbens DanRibbens added the dependency-issue The issue is in a dependency of payload - not payload itself label Apr 15, 2024
@DanRibbens
Copy link
Contributor

I think this problem is coming from drizzle-kit. I'm waiting on answers from their team.

@maneike
Copy link
Author

maneike commented Apr 16, 2024

@DanRibbens
I've read that creating anyTable in public schema would fix the Drizzle issue. While it didn't fix it, now it tries to push the tables into the schema, however it's actually pushing to public, even after specifying custom schema in both Payload and Drizzle configs. Then after going through all the db push prompts successfully:

+ users table will be created
+ payload_preferences table will be created
+ payload_preferences_rels table will be created
+ payload_migrations table will be created
--- all table conflicts resolved ---

I'm getting the same error:

 ⨯ Internal error: error: schema "payload" does not exist
    at /node_modules/pg-pool/index.js:45:11
    ...

These are the queries I'm passing to be executed in db.execute(import_drizzle_orm9.sql.raw(query)); and they're being logged just before execution:

// node_modules/drizzle-kit/payload.js

    DrizzleORMPgClient = class extends DrizzleDbClient {
      async query(query, values) {
        console.log("console.log(query):", query)
        const res = await this.db.execute(import_drizzle_orm9.sql.raw(query));
        return res.rows;
      }
      async run(query) {
        console.log("I'm not being logged out due to error. :(")
        const res = await this.db.execute(import_drizzle_orm9.sql.raw(query));
        return res.rows;
      }
    };

Output:

console.log(query): select count(*) as count from "anyTable"
# no CREATE SCHEMA here
console.log(query): CREATE TABLE IF NOT EXISTS "payload"."users" (
        "id" serial PRIMARY KEY NOT NULL,
        "updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL,
        "created_at" timestamp(3) with time zone DEFAULT now() NOT NULL,
        "email" varchar NOT NULL,
        "reset_password_token" varchar,
        "reset_password_expiration" timestamp(3) with time zone,
        "salt" varchar,
        "hash" varchar,
        "login_attempts" numeric,
        "lock_until" timestamp(3) with time zone
);
 ⨯ Internal error: error: schema "payload" does not exist
    at /node_modules/pg-pool/index.js:45:11
    ...

So it looks like Drizzle is pointing to public first and only then looking for other schemas and eventually not finding them.

@K-Mistele
Copy link

I think this problem is coming from drizzle-kit. I'm waiting on answers from their team.

Probably not, I have used drizzle and drizzle-kit with non-default schemas (not the public schema) in numerous other projects without payload. It is either a payload issue, or an issue with how payload is using drizzle.

@K-Mistele
Copy link

I can share a few more helpful pieces of information:

1. The migration that is generated does not include a CREATE SCHEMA statement.

Note that the migration file generated below was created with pnpm run payload migrate:create using the following adapter config, AND that I have truncated the create statements since there are a lot:

    db: postgresAdapter({
      pool: {
        connectionString: process.env.DATABASE_URI || '',
      },
      schemaName: 'template_payload_nfc_app'

    }),
import { MigrateUpArgs, MigrateDownArgs, sql } from '@payloadcms/db-postgres'

export async function up({ payload }: MigrateUpArgs): Promise<void> {
await payload.db.drizzle.execute(sql`

CREATE TABLE IF NOT EXISTS "template_payload_nfc_app"."nfc_tags" (
	"id" serial PRIMARY KEY NOT NULL,
	"xuid" varchar NOT NULL,
	"active" boolean NOT NULL,
	"tag_name" varchar,
	"updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL,
	"created_at" timestamp(3) with time zone DEFAULT now() NOT NULL
);

CREATE TABLE IF NOT EXISTS "template_payload_nfc_app"."nfc_tags_rels" (
	"id" serial PRIMARY KEY NOT NULL,
	"order" integer,
	"parent_id" integer NOT NULL,
	"path" varchar NOT NULL,
	"media_gallery_id" integer
);

CREATE TABLE IF NOT EXISTS "template_payload_nfc_app"."users" (
	"id" serial PRIMARY KEY NOT NULL,
	"admin" boolean,
	"name" varchar,
	"updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL,
	"created_at" timestamp(3) with time zone DEFAULT now() NOT NULL,
	"email" varchar NOT NULL,
	"reset_password_token" varchar,
	"reset_password_expiration" timestamp(3) with time zone,
	"salt" varchar,
	"hash" varchar,
	"login_attempts" numeric,
	"lock_until" timestamp(3) with time zone
);
`);

};

export async function down({ payload }: MigrateDownArgs): Promise<void> {
await payload.db.drizzle.execute(sql`

DROP TABLE "template_payload_nfc_app"."nfc_tags";
DROP TABLE "template_payload_nfc_app"."nfc_tags_rels";
DROP TABLE "template_payload_nfc_app"."users";
DROP TABLE "template_payload_nfc_app"."media_gallery_media_items";
DROP TABLE "template_payload_nfc_app"."media_gallery";
DROP TABLE "template_payload_nfc_app"."media_gallery_rels";
DROP TABLE "template_payload_nfc_app"."uploads";
DROP TABLE "template_payload_nfc_app"."payload_preferences";
DROP TABLE "template_payload_nfc_app"."payload_preferences_rels";
DROP TABLE "template_payload_nfc_app"."payload_migrations";`);

};

2. The generated JSON file does not include entries in the schemas field

Once again, I have not pasted the entire file - just the end of it

{
  "id": "3af332c8-094a-443d-a20e-410d184c2bbb",
  "prevId": "00000000-0000-0000-0000-000000000000",
  "version": "5",
  "dialect": "pg",
  "tables": {...}, // tables are here, correctly. omitted for illustration & brevity
  "enums": {},
  "schemas": {},
  "_meta": {
    "schemas": {}, // there should be stuff here for a schema, but there is not.
    "tables": {},
    "columns": {}
  }
}

@alsherif-khalaf
Copy link

still doesn't respect schemaName

@alsherif-khalaf
Copy link

  db: postgresAdapter({
    pool: {
      connectionString: process.env.DATABASE_URI || '',
    },
    schemaName: "payload",
  }),
⨯ unhandledRejection: error: there is no parameter $1
    at eval (webpack-internal:///(rsc)/./node_modules/pg-pool/index.js:45:11)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async DrizzleORMPgClient.query (/home/alsherif/New_Dev/dr-brand-backend-storefront/node_modules/drizzle-kit/payload.js:34498:21)
    at async /home/alsherif/New_Dev/dr-brand-backend-storefront/node_modules/drizzle-kit/payload.js:2259:46 {
  length: 95,
  severity: 'ERROR',
  code: '42P02',
  detail: undefined,
  hint: undefined,
  position: '175',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_expr.c',
  line: '842',
  routine: 'transformParamRef'
}

@alsherif-khalaf
Copy link

worked when i changed to the postgres databas and i created a new schema

image

@DanRibbens
Copy link
Contributor

The problem right now with schema name is that if you have other tables with the same name in other schemas they will conflict. It only works if each schema is using uniquely named table and enums. We're waiting on a fix from drizzle.

@DanRibbens DanRibbens removed the status: needs-triage Possible bug which hasn't been reproduced yet label Jun 10, 2024
@nechita
Copy link

nechita commented Jun 26, 2024

@DanRibbens do you know if there is any update for this issue? I'm experiencing the same issue and it would be nice to be able to add payload with an existing project to accelerate the content creation via the CMS.

@hansfuchs
Copy link

I have also encountered this bug. @DanRibbens might be right. This obviously prevents any attempts at trying to make two separate Payload CMS instances with collections with duplicate names access the same database, which is what I was trying to do. If for example you have a schema "public" and "test", both with the collection "users", only the Payload CMS instance that tries to access the "public" schema will work, and the other will throw something like the following when attempting to run it locally or anywhere else:

⨯ Internal error: error: relation "users" already exists in schema "test"

I guess this could be resolved if there was a way to add a prefix/suffix to all tables that payload accesses inside the postgresAdapter options, but that would be quite an ugly hack.

@maneike
Copy link
Author

maneike commented Jul 3, 2024

@hansfuchs
I believe there is already a fix in progress:
drizzle-team/drizzle-orm#2363 - Fix RQB behavior for tables with same names in different schemas

@DanRibbens
Copy link
Contributor

I pinged our drizzle friends about this, waiting on a fix.

@hansfuchs
Copy link

any update on this? I'd love to use Payload as CMS for our customers but this is kind of a deal breaker...

@DanRibbens
Copy link
Contributor

This was fixed here: #7453

Copy link
Contributor

github-actions bot commented Sep 6, 2024

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

@github-actions github-actions bot locked as resolved and limited conversation to collaborators Sep 6, 2024
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
dependency-issue The issue is in a dependency of payload - not payload itself
Projects
None yet
Development

No branches or pull requests

7 participants