Postgres manual table creation

[gentle anchor]

Is it normal practice to create separate tables in the Postgres database directly that are not Payloadcms models and are not directly represented in the admin? Will there not be any conflicts? Or is it better not to do this?

Should I use migrations?

I need to store data that will be updated by the worker and that will be used by the frontend. The data will be linked to one of the Payloadcms tables

Thanks in advance!

[wispy spoke]

New Community-Help Thread Created!

Help is on the way! To mark it as solved, use the /solve command. In the meantime, here are some existing threads that may help you:

Documentation:

• Building Your Own Plugin - Best practices - Publish your finished plugin to NPM:
• Adding your own Queries and Mutations - Best practices
• Building Your Own Plugin - Best practices
• Building Your Own Plugin - Best practices - Providing an enable / disable option:
• Building Your Own Plugin - Best practices - Include tests in your GitHub CI workflow:

[gentle anchor]

Please give a short answer 🙏

[kindred vapor]

Hi @gentle anchor,

Have a look at the following PR (from Payload team member @shadow nest) as I believe this is precisely the use-case for it.

https://github.com/payloadcms/payload/pull/8196 Hope it helps

[gentle anchor]

I've done it this way: ```afterSchemaInit: [
// @ts-ignore
({ schema }) => {
const { items } = schema.tables

    const salesHistory = pgTable(
      'steam_sales_history',
      {
        itemId: integer('item_id')
          .notNull()
          .references(() => items.id),
        date: timestamp('date').notNull(),
        price: numeric('price', { precision: 10, scale: 2 }).notNull(),
        salesCount: integer('sales_count').notNull(),
      },
      (table) => {
        return {
          primaryKey: primaryKey({
            columns: [table.itemId, table.date],
          }),
        }
      },
    )

    return {
      ...schema,
      tables: {
        ...schema.tables,
        salesHistory,
      },
    }
  },
]```

And created this migration: ```export async function up({ payload, req }: MigrateUpArgs): Promise<void> {
await payload.db.drizzle.execute(sql `
CREATE TABLE IF NOT EXISTS "steam_sales_history" (
"item_id" integer NOT NULL,
"date" timestamp NOT NULL,
"price" numeric(10, 2) NOT NULL,
"sales_count" integer NOT NULL,
CONSTRAINT "steam_sales_history_item_id_date_pk" PRIMARY KEY("item_id","date")
);

DO $$ BEGIN
ALTER TABLE "steam_sales_history" ADD CONSTRAINT "steam_sales_history_item_id_items_id_fk" FOREIGN KEY ("item_id") REFERENCES "public"."items"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
WHEN duplicate_object THEN null;
END $$;

SELECT create_hypertable('steam_sales_history', 'date', if_not_exists => TRUE);
`)
}```

But it seems to be impossible to run it via payload. I'm getting: [13:14:57] ERROR: Error running migration 20241004_090914 The following field is invalid: id. err: { "type": "ValidationError", "message": "The following field is invalid: id", "stack": ValidationError: The following field is invalid: id

Maybe there are some options to bypass the payloadcms adapter? The thing is that I can't set a unique id on this table, because timeScaleDB requires other fields to be unique