--- name: strapi-pg-to-mysql-migration description: > Migrate a Strapi application's data from PostgreSQL to MySQL and verify it landed correctly. Load this whenever the user asks to migrate/move/transfer a Strapi site or its database from Postgres to MySQL, or to fix "mixed up" / "wrong" media, images, or relations after such a migration. Covers Strapi v5 (validated end-to-end on a real migration) and v4 (best-effort, not yet validated). Includes the exact process for the two things a raw SQL dump/restore reliably breaks: every relation table's foreign keys, and — much less obviously — media attached to nested components. Ends with a live verification playbook and a path to push the fix to the real target server. --- # Strapi: PostgreSQL → MySQL migration ## Why this is needed at all A raw `pg_dump` → convert → `mysql` import (or any dump/restore that doesn't go through Strapi itself) does **not** preserve auto-increment primary keys. Every table gets fresh sequential IDs in MySQL, in whatever order the import happened to process rows — but every relation/join table (`*_lnk`, `*_cmps`, `*_mph`/morph tables, plain FK columns like `created_by_id`) still contains the **old PostgreSQL IDs** verbatim. The result: every relation in the database points at the wrong row (or nothing), not just images — categories, authorship, team assignments, everything. It reads as "images are mixed up" because that's the most visually obvious symptom, but it is never actually limited to media. **Why not just write UPDATE statements to remap every foreign key?** This is the intuitive first approach — build an old-id → new-id map per table (joining on something stable like `document_id`) and rewrite every relation table's foreign key columns to point at the new IDs. It works for content types, which have a stable `document_id` to join old and new rows on. It falls apart for **components** (repeatable fields, dynamic zone entries, anything nested) — these have no `document_id`, only a raw auto-increment id, so there's no reliable key to build the old→new map from in the first place. And the sheer number of relation tables in a real Strapi app (~180+ in the validated migration) makes hand-verifying each one impractical. Don't go down this path — it's the wrong tool for this problem, even though it's the obvious first instinct. The actual fix is Strapi's own `strapi export` / `strapi import` (the Data Transfer engine). It moves data through the entity/document layer instead of raw SQL, so IDs can differ freely between source and target — relations get rebuilt correctly regardless, because Transfer resolves them via Strapi's own understanding of the schema, not by comparing raw numbers. This sidesteps the whole class of bug above — for content types. It does **not** fully sidestep it for components (Phase 3 below) — components have no `document_id` either way, so even Transfer's own relation-resolution has nothing stable to anchor a component's *media* relation to, which is exactly the gap Phase 3 exists to close. **The one thing Transfer does NOT reliably get right**: media (or other polymorphic/morph-table relations) attached to a *component instance* that isn't directly reachable from a document root in an obvious way — repeatable components, components nested inside other components, components inside dynamic zones. We found this the hard way: after a clean, fully-verified Transfer import, 52% of one component type's media galleries were silently cross-contaminated with images from unrelated component instances. Row counts looked fine; spot-checking a couple of examples looked fine; only a systematic per-component comparison caught it. **Do not skip Phase 3 of this skill.** Content, relations between content types, and i18n locale records were reliable in our validated v5 migration. **Admin RBAC has its own narrower gap, found in a second real migration**: role *names and codes* transfer correctly (`admin_roles`), and each stock role (Super Admin, Editor, Author) gets re-seeded with Strapi's own *default* permission set by the post-import bootstrap. But **any permission grant beyond that default — e.g. someone manually gave the Editor role access to Webhooks in the source's admin UI — does not survive either Transfer or bootstrap**, and this is easy to miss because checking "do role names match" looks clean. It was only caught because a user noticed, after migration, that an Editor-role account could reach Webhooks settings in production but not on the freshly migrated target. See Phase 2 and the "Admin role permission customizations" section of `references/02-transfer-and-admin-data.md` for the diff-and-fix method — don't rely on role name/code matching alone to call RBAC verified. ## Checklist — what you need before starting Confirm every item below before touching anything. Ask the user directly for whatever you don't already have; do not guess or assume. - [ ] **The target Strapi app's codebase is available locally**, with dependencies installed (`node_modules` present). This is required — `strapi export`/`import` runs *from* the app, and the component-relation repair in Phase 3 reads the app's own `schema.json` files. If dependencies aren't installed yet: if `bun.lock` (or `bun.lockb`) is present, use `bun install`. **Otherwise, always use yarn** — run `nvm use 22` first (Strapi 5 targets Node 22), then `yarn install`. If the repo already has a committed `package-lock.json`, **do not delete it** — leave it in place (yarn will ignore it and generate `yarn.lock` alongside it); the point is to never destroy a committed file, not to avoid running yarn. - [ ] **Ask for the URL of the current, live Strapi admin panel** — production, or whatever environment is authoritative right now — even though you'll also have direct DB access to the Postgres source. Phase 4's live verification compares the migrated local/staging copy against this URL side by side via browser automation (Playwright or Chrome DevTools), not against the Postgres source directly — a live admin panel and a Postgres dump of it can diverge (someone edits content after the dump is taken), and the live panel is always the more current ground truth. Get this URL up front so Phase 4 isn't blocked later hunting for it. - [ ] **PostgreSQL source is reachable** — either an already-running Postgres instance with the correct/original data restored into it, or the raw `.sql` dump file plus a Postgres server to restore it into (local via Homebrew/Postgres.app/Docker, doesn't matter which). Ask the user which situation applies, and get connection details: host, port, database name, username, password (or confirm trust/peer auth with no password, common for local Postgres.app setups). - [ ] **MySQL target is reachable.** Ask the user explicitly: - Connection details: host, port, database name, username, password. - **Is this a local/staging copy or the real production database?** Strongly prefer fixing a local or staging MySQL copy first, verifying it fully, and only then loading the corrected dump onto the real target. `strapi import` is destructive to whatever's already in the target — never run it against a database with content you can't afford to lose without an explicit go-ahead and ideally a backup. - [ ] **If this is a repair of an already-migrated (broken) target, not a fresh migration: ask whether any content has been created or edited directly on the target since the bad migration happened**, that doesn't exist in the Postgres source. A full `strapi import` wipes and replaces the target's content-type data — if someone's been fixing typos, adding new blog posts, or uploading new media directly in the broken target's admin panel since it went live, a straight re-import silently discards all of that. If the answer is yes, this needs a different plan (e.g. reconciling the two afterward, or scoping the re-import to leave certain content types alone) — don't default to "just reimport everything" without asking this. - [ ] **Ask about the physical media files** (the actual image/video binaries, not just DB rows). Are they already present on the target server / in this local checkout's `public/uploads` (or already migrated to whatever cloud storage provider is configured)? `strapi export` bundles assets from wherever the *local* provider config points at export time — if this checkout's `public/uploads` doesn't already have the right files, exporting from here won't produce a correct asset bundle. - [ ] **Ask the user for the CURRENT Strapi environment's secrets** — specifically `APP_KEYS`, `API_TOKEN_SALT`, `ADMIN_JWT_SECRET`, `TRANSFER_TOKEN_SALT`, `JWT_SECRET` (from the source server's real `.env`, or wherever it's stored — a secrets manager, hosting platform's env var settings, etc.). **These need to be set to the exact same values on the target**, not freshly generated — see Phase 0 for why. If the user genuinely can't retrieve them, that's workable, but say plainly up front what breaks as a result (existing API/transfer tokens won't authenticate, active admin/user sessions will invalidate) rather than discovering it later. - [ ] **Ask which Strapi major version** the app is on (`package.json` → `@strapi/strapi` version). This skill's Phase 3 repair tooling is validated for v5. If v4, read `references/v4-notes.md` before proceeding — the schema differs meaningfully and this path is unvalidated in practice. - [ ] **Check whether the app's codebase has changed since the Postgres data was created.** `strapi export`/`import` runs against the codebase's *current* `schema.json` files — if a content type gained a new required field, a component was renamed, or a content type was added/removed after the source data was originally written, the export/import can error out partway through, or silently produce nulls for fields that didn't exist yet in the source data. Look at recent git history for schema-affecting changes (`git log --oneline -- src/api src/components`) around the time the Postgres dump was taken, and flag anything that looks like it could conflict before running the transfer, rather than debugging a partial-import failure after the fact. - [ ] **Confirm downtime/timing constraints** — any freeze windows, whether this needs to happen live or can be staged, etc. If any of the above is unknown or ambiguous, ask the user before proceeding — do not silently pick a default for anything that could be destructive (importing into a real target, overwriting existing tokens, etc.). ## Phase 0 — Environment prep 1. Confirm/install `pg` as a dependency in the Strapi app (needed to connect to Postgres; the app normally only ships `mysql2`), using whichever package manager the checklist settled on — `bun add pg` if the repo has `bun.lock`, otherwise `nvm use 22 && yarn add pg` (yes, even if a `package-lock.json` is also present — don't delete it, just don't use it to install). This is temporary tooling, not a real app dependency — remove it again in Phase 5 (but leave `mysql2` in place; that one's a real, ongoing dependency once the app targets MySQL). 2. If no `.env` exists yet, set one up — but **use the source server's real `APP_KEYS`, `API_TOKEN_SALT`, `ADMIN_JWT_SECRET`, `TRANSFER_TOKEN_SALT`, and `JWT_SECRET` values, not freshly generated ones**, if the user was able to retrieve them per the checklist above. Set the target's `.env` to these exact same values. **Why this matters, and why it's easy to get wrong**: it's tempting to just generate fresh random secrets for a "new" environment — that's the normal thing to do when setting up a Strapi app from scratch. But this isn't a fresh app, it's the *same* app's data landing on a new database, and several things you're carrying over are cryptographically tied to these specific secret values: - `API_TOKEN_SALT` / `TRANSFER_TOKEN_SALT`: the `access_key` column you copy over in Phase 2 is an HMAC of the real token string, keyed by these salts. Generate a new salt and every copied token row becomes permanently unverifiable — the original token strings will never authenticate again, even though the row data looks identical to source. There's no way to "fix" this after the fact short of reissuing every token; the salt has to match from the start. - `ADMIN_JWT_SECRET` / `JWT_SECRET`: sign the admin panel's and the Users & Permissions plugin's JWTs respectively. A new secret invalidates every existing session immediately — usually tolerable (people just log back in), but not if there are long-lived API-consuming JWTs elsewhere that assume continuity. - `APP_KEYS`: signs admin panel session cookies. Same story as above. If the user truly cannot retrieve the originals, generating fresh ones is the fallback — just be explicit that this means every copied API/transfer token in Phase 2 will need reissuing (the copy will still run without error, it just won't produce a *working* token), and don't let that surprise come up only once someone reports a token failing in production. To generate a fresh one if needed: `node -e "console.log(require('crypto').randomBytes(16).toString('base64'))"`. Also set `DATABASE_*` variables per the connection details gathered in the checklist. 3. Verify you can actually connect to Postgres before running anything else. If `psql` isn't on PATH, ask the user where their Postgres client lives (e.g. Postgres.app installs it at `/Applications/Postgres.app/Contents/Versions/latest/bin/psql`) rather than assuming/searching broadly — this was a case where guessing wasted a round trip; just ask. ## Phase 1 — Transfer via Strapi export/import Full detail: `references/02-transfer-and-admin-data.md`. 1. Point `.env` at Postgres (`DATABASE_CLIENT=postgres`, source credentials). Run `npx strapi export --no-encrypt -f --verbose` and read the summary table — this is your baseline: entity counts, asset counts, link counts per content type. Sanity check these numbers look plausible for the app (nothing wildly off) before moving on. 2. Point `.env` at MySQL (`DATABASE_CLIENT=mysql`, target credentials — the local/staging one per the checklist above). Confirm the target is actually empty (or that the user has explicitly accepted it'll be wiped) before running `npx strapi import -f .tar.gz --force --verbose`. This can take a long time for large media libraries (ours took ~90 minutes for ~400MB of assets) — see `references/06-troubleshooting.md` for how to monitor progress without wasting turns on naive polling. 3. Verify counts match the export summary. Boot the app once (`npx strapi start`, then stop it) — this matters: webhooks and `strapi_core_store_settings` (transferred as part of Transfer's "configuration" category) only get *written* to the target database during the app's own bootstrap sequence, not during `strapi import` itself. Don't panic if those tables look empty immediately after `strapi import` finishes — recheck after the boot. **Admin RBAC permissions are different and don't self-heal from a boot**: bootstrap only seeds each default role with Strapi's own *stock* permission set. It does not restore a custom grant made in the source's admin UI beyond that stock set. That gap needs its own diff-and-copy step — see Phase 2. ## Phase 2 — Fill in what Transfer deliberately excludes Full detail: `references/02-transfer-and-admin-data.md`. Summary of what needs manual handling: - **Admin users** (accounts, passwords, role assignments) — not transferred at all. Copy directly from source to target once the target's `admin_roles` exist (they do, post-import) — this is a straightforward direct copy since target starts empty, no ID remapping needed. - **API tokens / transfer tokens** — same story, direct copy. Note the accessKey is an HMAC keyed by `API_TOKEN_SALT`/`TRANSFER_TOKEN_SALT` — copying the hash only makes the *original* token string work if the target ends up with the *same* salt the source used to create it. - **`created_by_id`/`updated_by_id`** on ordinary content — nulled out by the import (since admin users aren't part of the transferred entity graph). Backfill via a `document_id` join from source once admin users are copied over. - **Admin role permission customizations** — any grant beyond Strapi's own default set for a stock role (e.g. an Editor role manually given access to Webhooks) is silently dropped by both Transfer and the post-import bootstrap. Role *names/codes* match fine, which is what makes this easy to miss if that's all you check. Diff `admin_permissions` (joined through `admin_permissions_role_lnk`/`admin_roles`) by `(role_code, action, subject)` between source and target, and copy over whatever's missing. See `references/02-transfer-and-admin-data.md`. **Don't stop at row presence** — a row that matches by `(role_code, action, subject)` on both sides can still have different `properties` JSON. Bootstrap's default seed for `content-manager.explorer.*` on a localized content type omits the `properties.locales` key entirely; the i18n plugin's permission engine treats that as **deny all locales** (`locale: { $in: [] }`), not "unrestricted" — only an explicit `properties.locales: null` means unrestricted. A role edited even once through the source's admin UI gets `locales` written explicitly; the target's fresh bootstrap seed never does. Symptom: the affected role sees the content-type in the nav (the `read` permission exists) but every list view renders empty. Diff full `properties` equality, not just row existence — see "Admin role permission customizations" in `references/02-transfer-and-admin-data.md`. - **Embedded media URLs in rich-text fields** (CKEditor, Blocks, Markdown) — an image pasted into body text bakes in the *fully resolved source URL* at authoring time (e.g. a Strapi Cloud `.media.strapiapp.com` host). Transfer copies that string verbatim; it doesn't parse HTML/Blocks JSON to rewrite embedded media references the way it rewrites the actual file-upload relation. `SELECT`/`UPDATE ... REPLACE(...)` every rich-text field for the source's media host on **every** content type that has one, not just the one a user happens to notice first. Then **take a fresh dump of the fixed target and `grep` the whole file for the source's domain** — the source's URL can leak into places that aren't content at all (e.g. `strapi_core_store_settings` plugin config copied verbatim by Transfer). See "Embedded media URLs in rich-text fields" in `references/02-transfer-and-admin-data.md`. ## Phase 3 — Fix component-level media relations (the critical, easy-to-miss step) Full detail and the actual repair tooling: `references/03-component-relation-repair.md`. **Do not skip this or consider the migration done without running it.** This is the part that silently corrupts media on nested/repeatable/dynamic-zone components — the actual images/data visible on top-level content (a blog's own cover, a project's own main image, a team member's own avatar) transfers correctly, since those have a stable `document_id` Transfer can anchor to. Anything nested inside a component that itself has no `document_id` is the risk zone. **Why read schema.json instead of just querying the database for patterns?** The first working version of this repair (during the validated migration) was built by hand: manually querying each join table for its distinct `component_type` values and hand-writing a config mapping each component to its parent chain. It worked, but it's per-project busywork that has to be redone from scratch for every different Strapi app's collections/components — exactly what won't scale across "different strapis with different collections and types." `schema.json` is Strapi's own authoritative source for this same information (which fields are components, what they nest, which fields hold media) — reading it directly means the discovery step is generic and doesn't need re-deriving by hand each time. The reference doc walks through: 1. Reading the app's own `schema.json` files to build a generic map of every content type and component, which fields hold media, and how everything nests (no hand-written per-project config needed). 2. Auditing every affected relation by correlating source ↔ target through document_id + locale + position-in-parent (never through raw numeric IDs, which differ between the databases). 3. Rebuilding exactly the relations that don't match, and re-auditing until it's 100%. 4. Distinguishing real bugs from a benign false alarm: pre-existing orphaned relation rows in the *source* database (pointing at components that don't exist anymore) will show up as "missing" in your rebuilt target — that's correct behavior, not data loss. The reference doc has the exact check to tell the two apart. This audit script is schema-driven and untested against a second real project — treat its first run on any new Strapi app as something to sanity-check, not blindly trust. Run it in audit (read-only) mode first, manually verify a few of its findings look right via the admin UI, and only then run the fix. **Scope check we actually did**: it's natural to wonder whether *ordinary* relation fields on components (e.g. a component with a `relation` field pointing at another content type, via its own dedicated `_lnk` table — not the shared polymorphic media table) suffer the same corruption. We checked this directly on the validated migration (a `oneToOne` relation field on a component, 450 instances) and found **zero mismatches** — ordinary component relations transferred correctly. The corruption we found is specific to the shared *polymorphic* media table, not components generally. Still worth a light spot-check on a new project (Phase 4, item 5) rather than assuming this holds universally — we've only verified one relation cardinality (`oneToOne`); a `manyToMany` relation field on a component is unverified and structurally more complex (more rows, different order semantics). ## Phase 4 — Verify live, not just in the database Full detail: `references/04-verification.md`. A DB-level audit showing "100% match" caught real bugs in this project's migration, but a *visual* check on the actual admin UI is what first surfaced the component-relation problem — the DB summary alone (row counts, spot-checking one or two joins) looked fine. Compare the migrated local/staging target against the **current, live Strapi instance** — the URL gathered in the pre-start checklist. That's deliberately not the same thing as "the Postgres source": a Postgres dump is a snapshot, and the live admin panel is the current ground truth if anyone has touched content since the dump was taken. Use a browser via Playwright or Chrome DevTools (or ask the user to check manually) with the local/staging and live admin panels open side by side, log into both, and compare specific entries — not just "does it look populated," but "does this specific image match this specific entry," across every content type and every previously-broken component type. If you don't have admin login credentials for one or both sides, say so plainly rather than quietly skipping this phase — ask the user to log in themselves within the automated browser session (you drive navigation and comparison once they're authenticated), or ask them to spot-check manually and report back. Note that copied `admin_users` rows (Phase 2) carry over password *hashes*, not plaintext, so even after that phase you generally can't log in as a migrated user yourself — you still need the user (or a fresh temporary admin account, with their sign-off) to authenticate the local/staging side. ## Phase 5 — Push to the real target, then clean up 1. **Before touching the real target, back it up**, even if its current state is the known-broken one — a backup costs almost nothing and is the only way back if something goes wrong during the real cutover that wasn't caught in the staging rehearsal: ```bash mysqldump -h -u -p > pre-migration-backup-$(date +%Y%m%d-%H%M%S).sql ``` 2. Get explicit confirmation from the user before running anything against the real target — this overwrites it. Two ways to actually do the push, either is fine: - Dump the verified local/staging MySQL copy and load it onto the real target: ```bash mysqldump -h 127.0.0.1 -u root > verified-migration.sql mysql -h -u -p < verified-migration.sql ``` - Or repoint `.env` at the real target's credentials and re-run `strapi export` (from Postgres) → `strapi import` (into the real target) directly, skipping the intermediate local copy — reasonable once the process has been fully rehearsed against staging and you trust it, but repeats the entire multi-hour import for a large media library rather than reusing the already-verified staging result. - **If the real target Strapi app is running, stop it (or otherwise pause writes) before reloading its database.** A write happening on the live app during the reload could be lost or leave the database in an inconsistent state relative to what just got loaded. 3. After reloading a live server, admin sessions invalidate — tell the user to expect to log back in, this is expected, not a bug. 4. Re-run the Phase 4 visual spot-checks — *and*, if there's a live public-facing website reading from this Strapi instance, load a few actual site pages too, not just the admin panel — against the real live target after the reload, not just the local copy. The admin panel and the public site/API don't necessarily share every code path (see the note on Public role permissions in `references/04-verification.md`), so admin-panel-correct doesn't automatically prove the public site is correct. 5. Revert temporary tooling: uninstall just `pg` (`bun remove pg` if the repo uses bun, otherwise `yarn remove pg` — matching whichever package manager Phase 0 used). Don't blanket `git checkout package.json`/lockfile to do this — that also strips `mysql2`, which must stay as a real, ongoing dependency now that the app runs on MySQL, and don't delete a pre-existing `package-lock.json` either, even though yarn/bun didn't touch it. Remove any one-off scripts written during the migration (schema discovery, audit/fix, admin-data copy, RBAC diff/fix, authorship backfill). Don't leave migration scratch scripts committed to the app's repo. ## A note on how to work through this Treat each phase's completion as something to report back on before moving to the next, especially before Phase 5's real-target push — this involves genuinely destructive operations (wiping a database, overwriting tokens) and the user should be steering those decisions, not just receiving a final "done." When something looks off during verification, dig into it — the component-relation bug in this project was found by not accepting a merely-plausible-looking result as good enough.