Error when using Local API to persist data during migration

[jefferyto]

Describe the Bug

Following the reproduction steps, this is the error thrown when running the last migration command:

$ npm run payload migrate

> local-api-migration@1.0.0 payload
> cross-env NODE_OPTIONS=--no-deprecation payload migrate

[08:13:37] WARN: No email adapter provided. Email will be written to console. More info at https://payloadcms.com/docs/email/overview.
[08:13:37] INFO: Reading migration files from /home/jeff/Code/personal/payload/issues/local-api-migration/src/migrations
[08:13:37] INFO: Migrating: 20251103_235227
[08:13:37] ERROR: Error running migration 20251103_235227 Failed query: select "id", "updated_at", "created_at", (select coalesce(json_group_array(json_array("title", "_locale")), json_array()) as "data" from "posts_locales" "posts__locales" where "posts__locales"."_parent_id" = "posts"."id") as "_locales" from "posts" "posts" order by "posts"."created_at" desc
params: .
    err: {
      "type": "DrizzleQueryError",
      "message": "Failed query: select \"id\", \"updated_at\", \"created_at\", (select coalesce(json_group_array(json_array(\"title\", \"_locale\")), json_array()) as \"data\" from \"posts_locales\" \"posts__locales\" where \"posts__locales\".\"_parent_id\" = \"posts\".\"id\") as \"_locales\" from \"posts\" \"posts\" order by \"posts\".\"created_at\" desc\nparams: : SQLITE_ERROR: no such table: posts_locales: no such table: posts_locales",
      "stack":
          Error: Failed query: select "id", "updated_at", "created_at", (select coalesce(json_group_array(json_array("title", "_locale")), json_array()) as "data" from "posts_locales" "posts__locales" where "posts__locales"."_parent_id" = "posts"."id") as "_locales" from "posts" "posts" order by "posts"."created_at" desc
          params:
              at LibSQLPreparedQuery.queryWithCache (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/src/sqlite-core/session.ts:80:11)
              at async LibSQLPreparedQuery.values (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/src/libsql/session.ts:286:10)
              at async LibSQLPreparedQuery.all (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/src/libsql/session.ts:214:16)
              at async find (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/@payloadcms/drizzle/src/find/findMany.ts:162:19)
              at async findOperation (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/payload/src/collections/operations/find.ts:206:16)
              at async Object.up (/home/jeff/Code/personal/payload/issues/local-api-migration/src/migrations/20251103_235227.ts:4:20)
              at async runMigrationFile (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/@payloadcms/drizzle/src/migrate.ts:99:5)
              at async Object.migrate (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/@payloadcms/drizzle/src/migrate.ts:86:5)
              at async migrate (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/payload/src/bin/migrate.ts:87:7)
              at async runBinScript (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/payload/dist/bin/index.js:112:9)
          caused by: LibsqlError: SQLITE_ERROR: no such table: posts_locales
              at mapSqliteError (file:///home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/@libsql/client/lib-esm/sqlite3.js?tsx-namespace=1762215211201:372:16)
              at executeStmt (file:///home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/@libsql/client/lib-esm/sqlite3.js?tsx-namespace=1762215211201:277:15)
              at Sqlite3Client.execute (file:///home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/@libsql/client/lib-esm/sqlite3.js?tsx-namespace=1762215211201:79:16)
              at <anonymous> (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/src/libsql/session.ts:288:58)
              at LibSQLPreparedQuery.queryWithCache (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/src/sqlite-core/session.ts:78:18)
              at LibSQLPreparedQuery.values (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/src/libsql/session.ts:286:21)
              at LibSQLPreparedQuery.all (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/src/libsql/session.ts:214:27)
              at QueryPromise.executeRaw (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/src/sqlite-core/query-builders/query.ts:193:31)
              at QueryPromise.execute (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/src/sqlite-core/query-builders/query.ts:197:15)
              at QueryPromise.then (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/src/query-promise.ts:31:15)
          caused by: SqliteError: no such table: posts_locales
              at convertError (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/libsql/index.js:51:12)
              at Database.prepare (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/libsql/index.js:119:13)
              at executeStmt (file:///home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/@libsql/client/lib-esm/sqlite3.js?tsx-namespace=1762215211201:248:28)
              at Sqlite3Client.execute (file:///home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/@libsql/client/lib-esm/sqlite3.js?tsx-namespace=1762215211201:79:16)
              at <anonymous> (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/src/libsql/session.ts:288:58)
              at LibSQLPreparedQuery.queryWithCache (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/src/sqlite-core/session.ts:78:18)
              at LibSQLPreparedQuery.values (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/src/libsql/session.ts:286:21)
              at LibSQLPreparedQuery.all (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/src/libsql/session.ts:214:27)
              at QueryPromise.executeRaw (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/src/sqlite-core/query-builders/query.ts:193:31)
              at QueryPromise.execute (/home/jeff/Code/personal/payload/issues/local-api-migration/node_modules/src/sqlite-core/query-builders/query.ts:197:15)
      "query": "select \"id\", \"updated_at\", \"created_at\", (select coalesce(json_group_array(json_array(\"title\", \"_locale\")), json_array()) as \"data\" from \"posts_locales\" \"posts__locales\" where \"posts__locales\".\"_parent_id\" = \"posts\".\"id\") as \"_locales\" from \"posts\" \"posts\" order by \"posts\".\"created_at\" desc",
      "params": []
    }

The last commit changes the post title field to be localized, and the last migration script attempts to use the Local API to retrieve and restore the title field to the 'en' locale.

I tested this with SQLite only but I believe using Postgres would result in a similar error.

While this is one specific error, I believe the larger issue is that since the Payload config will be aligned with the database schema either before or after the schema change (but not both), using the Local API in a migration script involving a schema change will almost always result in an error (either when applying migrations or rolling back migrations).

Link to the code that reproduces this issue

https://github.com/jefferyto/payload-local-api-migration

Reproduction Steps

1. Checkout the second-to-last commit (jefferyto/payload-local-api-migration@d16ffb3) of the reproduction repo
2. Set up .env with a SQLite database, e.g. DATABASE_URI=file:./local-api-migration.db
3. Run initial migration: npm run payload migrate
4. Run seed script to add test post: npm run payload seed
5. Checkout the last commit of the reproduction repo
6. Run migration: npm run payload migrate

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

Not sure

Environment Info

Binaries:
  Node: 22.21.1
  npm: 11.6.2
  Yarn: N/A
  pnpm: N/A
Relevant Packages:
  payload: 3.62.1
  next: 15.4.4
  @payloadcms/db-sqlite: 3.62.1
  @payloadcms/drizzle: 3.62.1
  @payloadcms/email-nodemailer: 3.62.1
  @payloadcms/graphql: 3.62.1
  @payloadcms/next/utilities: 3.62.1
  @payloadcms/payload-cloud: 3.62.1
  @payloadcms/richtext-lexical: 3.62.1
  @payloadcms/translations: 3.62.1
  @payloadcms/ui/shared: 3.62.1
  react: 19.1.0
  react-dom: 19.1.0
Operating System:
  Platform: linux
  Arch: x64
  Version: #35-Ubuntu SMP PREEMPT_DYNAMIC Sat Oct 11 10:06:31 UTC 2025
  Available memory (MB): 7225
  Available CPU cores: 4