@payloadcms/db-sqlite: migrate runner executes table-rebuild migrations inside FK-ON transaction; localization/relation migrations unapplyable

@payloadcms/db-sqlite: migrate runner executes table-rebuild migrations inside an FK-ON transaction; localization/relation migrations are unapplyable

Versions

• payload 3.83.0
• @payloadcms/db-sqlite 3.83.0
• @payloadcms/drizzle 3.83.0
• @libsql/client 0.14.0 (local file: backend)
• drizzle-orm libsql dialect
• Node 22 / 25, SQLite via libSQL

Summary

Enabling localization on an existing SQLite database that has FK relations
between collections plus the SEO plugin (localized meta group) and the
search plugin produces a migrate:create migration that cannot be applied
by payload migrate. There are three distinct defects — two in the generated
SQL, one structural in the migrate runner.

Reproduction

1. Existing project, @payloadcms/db-sqlite, populated DB, collections with
   FK relations (e.g. cars.model_id → models.id, countries.region_id → regions.id, both effectively ON DELETE SET NULL with the child column
   NOT NULL), @payloadcms/plugin-seo (meta group) and
   @payloadcms/plugin-search installed.
2. Add localization to the Payload config (e.g. locales: [en, ar],
   defaultLocale: 'en').
3. payload migrate:create → a migration that creates *_locales tables and
   rebuilds the base tables.
4. payload migrate.

Defect 1 — duplicate index names (generated SQL)

The generator emits CREATE INDEX <table>_meta_meta_image_idx on the new
*_locales tables while the identically-named indexes still exist on the base
tables (from the baseline migration). SQLite's index namespace is global per
database, so the CREATE INDEX fails:

SQLITE_ERROR: index `models_meta_meta_image_idx` already exists

The generator only drops the old base-table indexes implicitly during the
later table rebuild, which runs after the colliding CREATE INDEX.

Defect 2 — no localized-data backfill (generated SQL)

When the SEO meta group / search title becomes localized, those columns move
from the base table into the new *_locales table. The generated migration
creates the *_locales tables and rebuilds the base tables without the
now-localized columns, but never copies existing row data across. All
pre-existing meta/title content is silently dropped on apply. (Data loss, not
an error — only visible if you diff row data before/after.)

Defect 3 — migrate runner uses an FK-ON transaction (structural)

@payloadcms/drizzle's runMigrationFile wraps every migration in
initTransaction (drizzle.transaction()), and the sqlite adapter's
connect() never disables foreign keys, so the transaction runs with
PRAGMA foreign_keys=ON. The localization migration must rebuild
FK-referenced parent tables (DROP TABLE regions, DROP TABLE models, …).
Under FK-ON, DROP TABLE regions fires countries.region_id's implicit
ON DELETE SET NULL, which then trips its NOT NULL:

SQLITE_CONSTRAINT_NOTNULL: countries.region_id

Workarounds that do not work:

• PRAGMA foreign_keys=OFF inside the migration up() — a no-op while a
  transaction is open (SQLite ignores foreign_keys pragma inside a tx).
• PRAGMA defer_foreign_keys=ON inside up() — insufficient on libSQL: it
  raises the NOT NULL immediately rather than deferring to COMMIT.

The only thing that works is disabling FK enforcement on the connection
before BEGIN — which is exactly what @libsql/client's own client.migrate()
does (node_modules/@libsql/client/lib-esm/sqlite3.js):

async migrate(stmts) {
  ...
  executeStmt(db, "PRAGMA foreign_keys=off", this.#intMode);
  executeStmt(db, transactionModeToBegin("deferred"), this.#intMode);
  // ...statements...
  executeStmt(db, "COMMIT", this.#intMode);
  // finally:
  executeStmt(db, "PRAGMA foreign_keys=on", this.#intMode);
}

But the Payload migrate runner opens its transaction via
drizzle.transaction() → client.transaction() (the non-migrate path), so
it never gets libSQL's migrate() FK-off semantics.

Suggested fix

The drizzle migrate runner should bracket the migration transaction with
connection-level FK disable/restore for SQLite — i.e. mirror
@libsql/client's client.migrate(): PRAGMA foreign_keys=OFF before
BEGIN, run migrations, COMMIT, then PRAGMA foreign_keys=ON (and ideally
a PRAGMA foreign_key_check to surface any integrity violation the rebuild
introduced, instead of silently leaving dangling refs). This matches both
SQLite's documented "table rebuild" recipe and libSQL's own migration path.

Separately, the schema-diff generator should (a) drop the colliding
base-table indexes before creating the *_locales ones, and (b) emit the
localized-data backfill (INSERT … SELECT … <defaultLocale> …) when columns
move into *_locales.

Workaround in use

A dedicated migrate entrypoint sets PRAGMA foreign_keys=OFF on the libSQL
client connection immediately before calling payload.db.migrate() (so the
runner's BEGIN inherits FK-off, mirroring client.migrate()), runs a
post-migrate PRAGMA foreign_key_check gate, then restores foreign_keys=ON.
The generated migration is hand-corrected for defects 1 and 2. No node_modules
patching.

Start by locating @payloadcms/drizzle's runMigrationFile and initTransaction paths, the SQLite adapter's connect(), and the schema-diff generator. Reproduce the localization migration with FK relations and inspect the generated SQL for index collisions, missing localized-data backfill, and transaction ordering. Done means the migration applies without data loss and a foreign-key check passes.