Local API in migrations doesn't use the current schema

[jsardev]

Describe the Bug

The migrations API exposes Payload Local API in the migration files. The problem is that when you do a payload.create() in a migration file, it won't work unless the schema didn't change overtime. It breaks the idempotency of migrations (you can't do migrate:fresh)

https://github.com/jsardev/payload-migration-issue/blob/518548b1d7988a27ee38307c9c30e2edbe85938b/src/migrations/20250512_140919.ts#L150-L162

{
  "type": "LibsqlError",
  "message": "SQLITE_ERROR: table tag has no column named type: table tag has no column named type",
  "stack": "",
  "code": "SQLITE_ERROR",
  "rawCode": 1,
  "name": "LibsqlError"
}

Link to the code that reproduces this issue

https://github.com/jsardev/payload-migration-issue

Reproduction Steps

1. Create a tags collection with name text field
2. Create a new migration payload migrate:create
3. Inside the migration, create pre-defined tags via payload.create()
4. Modify the tags collection
5. Create a new migration payload migrate:create
6. Run payload migrate:fresh
7. Error

Which area(s) are affected? (Select all that apply)

db-sqlite

Environment Info

Binaries:
  Node: 18.20.5
  npm: 10.8.2
  Yarn: 1.22.22
  pnpm: 9.15.2
Relevant Packages:
  payload: 3.33.0
  next: 15.3.0
  @payloadcms/email-nodemailer: 3.33.0
  @payloadcms/graphql: 3.33.0
  @payloadcms/next/utilities: 3.33.0
  @payloadcms/payload-cloud: 3.33.0
  @payloadcms/richtext-lexical: 3.33.0
  @payloadcms/translations: 3.33.0
  @payloadcms/ui/shared: 3.33.0
  react: 19.1.0
  react-dom: 19.1.0
Operating System:
  Platform: darwin
  Arch: arm64
  Version: Darwin Kernel Version 24.4.0: Fri Apr 11 18:33:47 PDT 2025; root:xnu-11417.101.15~117/RELEASE_ARM64_T6030
  Available memory (MB): 18432
  Available CPU cores: 11

[r1tsuu]

Hello @jsardev! I checked your reproduction, thanks for providing it!
However, I don't think it's an issue, but I can explain what you're experiencing and how to make it work.

When executing a migration, Payload starts a transaction and all the schema changes are done only in that transaction, until Payload will commit the transaction (which won't happen in your migration file, that's why you got SQLITE_ERROR: table tag has no column named type: table tag has no column named type). https://payloadcms.com/docs/database/migrations#using-transactions
For Local API calls, in order to perform changes within the current transaction, you need to pass the req object which you receive from the arguments in both up and down functions, for example:

From here:
https://github.com/jsardev/payload-migration-issue/blob/518548b1d7988a27ee38307c9c30e2edbe85938b/src/migrations/20250512_140919.ts#L150-L161

await Promise.all(
    ['first', 'second'].map((name) =>
      payload.create({
        collection: 'tag',
        data: {
          name,
        },
      }),
    ),
  )

You need to pass the req like this:

await Promise.all(
    ['first', 'second'].map((name) =>
      payload.create({
        req,
        collection: 'tag',
        data: {
          name,
        },
      }),
    ),
  )

And I think this should work for you! I'm converting this to a discussion so we can keep the conversation.

[jsardev]

@r1tsuu Hey! Thank you for a quick answer :) Unfortunately I already tried that and unfortunately it doesn't work as well: jsardev/payload-migration-issue@e4d9b40

I'm getting the exact same error that I mentioned in the initial post.

[r1tsuu]

Thanks for following up, I just cloned your repo and I can see the error.
But if I just move the create Local API call into this file instead https://github.com/jsardev/payload-migration-issue/blob/main/src/migrations/20250512_141209.ts, it works -

Can you try to do this? Alternatively, you can also just merge those 2 migrations into a single migration.

[r1tsuu]

I think the reason is that Payload at the runtime expects all the columns to exist, however, the type column is created in the second file. If you don't want to do the above, you'd have to use the database directly via the db argument to skip Payload's layer. (via raw SQL or Drizzle)

[jsardev]

Yeah I get your point and I know how to workaround the issue. The thing is that from an engineering perspective you shouldn't ever modify past migrations, just create new ones with whatever changes you have.

The initial payload.create() that I want to do is related to the initial migration, not any other. Going your way I'd always need to move this seeding logic to the latest migration that changes the tags collection, which smells bad practice 😅

I could just use raw SQL as you mentioned, but this also is problematic because it will omit any hooks etc. related to that collection.

Anyways, just wanted to give you my perspective on the problem and maybe inspire the Payload team to plan some changes so that this won't be a problem – but I searched through discussions and issues and seems like I'm the only one encountering this 😅 So please don't bother, I'll do the workaround 😄 I just hoped there's some magic switch that would make this work 😄

Thank you for your support!