[3.0 Beta] Changes not applying to tables when using schemaName about payload

[K-Mistele]

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": {}
  }
}

[DanRibbens]

This might be related to #5473

I am investigating this.

[DanRibbens]

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

[maneike]

@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]

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.