there is no parameter $1

[thedevdavid]

Link to reproduction

No response

Describe the Bug

If Payload v3 is added to an existing app with data in the DB, it fails to initialize payload tables.

error: there is no parameter $1
    at /node_modules/pg-pool/index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async DrizzleORMPgClient.query (/node_modules/drizzle-kit/payload.js:34498:21)
    at async /node_modules/drizzle-kit/payload.js:2259:46

To Reproduce

• set up a nextjs app with a postgres DB
• add any random table to the DB
• install Payload v3
• start dev server and open /admin

Payload Version

3.0.0-beta.18

Adapters and Plugins

No response

[jmikrut]

If there is an existing database with its own schema / data, you would need to write a Payload config to match its shape perfectly before starting up. It’s not expected that Payload will instantly work with any existing database - at least not right now.

[thedevdavid]

@jmikrut could you explain this a bit more, please?

Assuming I have a DB schema with 30+ tables and complex relationships (and it's possibly evolving), how (and why) would I have to add this to the Payload config?

[jmikrut]

Sure thing! So, Payload requires you to define your schema in the Payload config, so that we can surface it in our APIs as well as allow the admin UI to be generated appropriately. It is specific to Payload and is not agnostic regarding existing database shape, by design.

Although you have an existing schema defined in your database, Payload does not introspect your existing DB to dynamically build its own schema from your existing DB at all.

So, if you connect to an existing database, unless your database matched what Payload's schema was defined as, there would be all sorts of things that would blow up.

Right now at least, Payload is not designed to be able to import any Postgres schema into its own configuration. We could allow for this in the future, but as of now, this is not how things work. So you're gonna have problems unless you migrate your data to the shape that Payload would expect.

It's possible that in the future we could certainly introspect your database shape and allow you to map your existing table structures over to what Payload needs, but that will be no small feat, because of the complex field types that Payload supports (blocks, nested relationships, arrays, hasMany select fields, hasMany text fields, etc.)

Payload also supports versions and other complex db-level structures, so if you were to want to have Payload match your existing database shape, you would be missing out on a lot of the features that Payload provides and then would likely have to run DB migrations in order to add additional functionality.

You can, however, import your existing data into Payload using the Payload Local API once you have your new Payload config defined as you want it.

Does this make sense?

Can I ask why you're interested in bringing 30+ pre-existing tables into Payload? What would your best-case scenario look like here? What would you like to have the result look like / function like after having Payload connect to your existing DB?

[thedevdavid]

Does this make sense?

Ah, this makes sense. Is it possible to specify a separate Postgres schema for Payload to use? So, I keep my core app DB in the public schema, and Payload would use something like content? I think this would be an easy solution for the current problem

why you're interested in bringing 30+ pre-existing tables into Payload? What would your best-case scenario look like here? What would you like to have the result look like / function like after having Payload connect to your existing DB?

Sure. So, I have an existing app where I'd like to integrate Payload as a CMS. It would be used mainly to edit a couple of articles a month and handle some files.
The app has already been using Postgres for the core business functionality, so I thought it would make sense to use the DB I already have instead of creating a separate one for Payload.

I also plan to customize Payload admin so the admin user would be able to use some fields to query data from the core app database.
Example: App DB has an 'artists' table and in Payload admin, when editing the artists subpage, the admin would be able to choose "featured artists" from the core app DB.

[jmikrut]

Is it possible to specify a separate Postgres schema for Payload to use?

Yes - pass schemaName to the Postgres adapter and you'll be golden! This defaults to public but can be changed. Payload will then use the schema you set.

https://payloadcms.com/docs/database/postgres#options

Everything else you mentioned should then work perfectly there!

[thedevdavid]

Yes - pass schemaName to the Postgres adapter and you'll be golden! This defaults to public but can be changed. Payload will then use the schema you set.

Hmmm.. I tried this right now but still getting the same error.

[jmikrut]

The error might actually be related to your connection string then, in that case. Have you double-checked that your connection string is valid?

[thedevdavid]

The error might actually be related to your connection string then, in that case. Have you double-checked that your connection string is valid?

Yes, and I tried multiple databases. I tried a local postgres running in Docker, and with 2 Supabase projects. The only time I was able to get through this was when trying a completely new DB.

[thedevdavid]

@jmikrut I just updated to the latest beta (24) and still having the same issue. I think schemaName is not respected. Might be related to #5822