PayloadCMS: type "serial" does not exist

[j-mcgregor]

Describe the bug

First off, love Xata! We want to adapt it into our full code base and it works for everything except for PayloadCMS. I've left comments on relevant PayloadCMS issues.

After creating a new Postgres-enabled database and storing the DATABASE_URI with fresh API key, I try and spin up a new payload (beta) app. 2 things then happen:

• Error 1: Resolved by allowing Xata to make the necessary table changes by following the Xata UI

 ⨯ Internal error: error: unknown statement [<nil>] on regular level, see https://xata.io/sql-support for the support matrix
    at /root/node_modules/pg-pool/index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async DrizzleORMPgClient.query (/root/node_modules/drizzle-kit/payload.js:34498:21)
    at async apply (/root/node_modules/drizzle-kit/payload.js:36648:9)
    at async pushDevSchema (../../node_modules/@payloadcms/db-postgres/dist/utilities/pushDevSchema.js:47:5)
    at async Object.connect (../../node_modules/@payloadcms/db-postgres/dist/connect.js:84:5)
    at async BasePayload.init (../../node_modules/payload/dist/index.js:215:13)

• Error 2: Unresolved

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

I'm guessing its a version / pg flavour mismatch but I don't know

To Reproduce

1. Create a postgres-enabled Xata database and copy the API key-populated Database URI
2. Install beta Payload app: npx create-payload-app@beta and paste the URI into the terminal when prompted
3. Run the app
4. In the Xata UI, you should see an option pop up (in the schema panel or individual tables) to allow Xata to make the necessary changes
5. Bug 2 should appear in the terminal

Expected behavior

I should be able to use Xata Postgres for my PayloadCMS database

Software version

// package json
"dependencies": {
    "@payloadcms/db-postgres": "beta",
    "@payloadcms/next": "beta",
    "@payloadcms/plugin-cloud": "beta",
    "@payloadcms/richtext-lexical": "beta",
    "cross-env": "^7.0.3",
    "next": "^14.3.0-canary.7",
    "payload": "beta",
    "react": "^18.2.0",
    "react-dom": "^18.2.0",
    "sharp": "0.32.6"
  },
  "devDependencies": {
    "@types/node": "^20.11.25",
    "@types/react": "^18.2.64",
    "@types/react-dom": "^18.2.21",
    "dotenv": "^16.4.5",
    "tsx": "^4.7.1",
    "typescript": "^5.4.2"
  },

Additional context

I'm following up enquiries with Payload too. Since both Xata and Payload features right now are in beta, I know its a longshot but you lot seem like wizards so thought I'd ask