菜单
Import or refresh a Cloudflare D1 database locally from a remote export when direct SQL dump execution fails because of foreign key ordering, transaction limitations, or wrangler import quirks. Use this skill when syncing production D1 data into the local development database.
用 Codex 或 Claude 帮你安装 复制这段 Prompt，粘贴到 Codex、Claude 或其他助手里，让它检查 Skill 页面并帮你完成安装。
基于 SOC 职业分类
| name | import-d1-database |
| description | Import or refresh a Cloudflare D1 database locally from a remote export when direct SQL dump execution fails because of foreign key ordering, transaction limitations, or wrangler import quirks. Use this skill when syncing production D1 data into the local development database. |
This skill documents the safe import workflow for Cloudflare D1 in this project. It avoids the failure modes observed with raw SQL dumps:
The preferred implementation is the repository script at scripts/import-d1-database.ts, exposed as npm run db:import:local. The script encodes the entire workflow described below — for routine syncs, just run it.
Use this skill when you need to:
If you just want the practical one-command path, use npm run db:import:local instead of performing the steps manually.
Do not use this skill for schema design or schema migrations. Use create-migration for that.
Gather or confirm:
| Data | Required | Example |
|---|---|---|
| Source database name | Yes | townofus-pl |
| Target state directory | Yes | .wrangler/state/v3/d1 (the default the dev server reads) |
| Fresh export file path | Yes | db-backups/townofus-pl-remote.sql |
| Whether the local dev server is running | Yes | stop it first if it locks the state files |
| Cloudflare account selection | Yes if multiple accounts | set CLOUDFLARE_ACCOUNT_ID=d1287f321cc95882fc773ab71241b51a (the project's "Spoko Firma" account, also used by .github/workflows/deploy.yml) before running wrangler d1 export --remote |
Before touching .wrangler/state/, stop:
npm run devworkerd processwrangler process using the same state directoryIf the state files are locked, the import or copy step can fail or leave the database half-updated.
Run (set CLOUDFLARE_ACCOUNT_ID first if you belong to multiple accounts — see Prerequisites):
wrangler d1 export townofus-pl --remote --output db-backups/townofus-pl-remote.sql
Keep the raw export intact. Do not try to import the full file directly into local D1.
Run migrations into a fresh persist path:
npm run db:migrate:apply:local
If you need a clean isolated import state, use a separate --persist-to directory for the import run.
Important: the local schema must come from migrations first. Do not import schema statements from the export file.
Extract only INSERT INTO ... statements from the export.
Rules:
PRAGMA statements from the export fileCREATE TABLE, CREATE INDEX, etc.)INSERT INTO "d1_migrations" ... because migrations already populate this tableThe dump order is not safe for D1 import. Reorder the INSERTs so parent tables are loaded before child tables.
Recommended order for this project (matches TABLE_ORDER in scripts/import-d1-database.ts):
players with currentRankingId temporarily nulledgameslista_cwelidrama_afera_settingsmeetingsplayer_rankingsgame_player_statisticsplayer_rolesplayer_modifiersgame_eventsmeeting_votesmeeting_skip_votesmeeting_jailed_playersmeeting_blackmailed_playersmeeting_no_votessqlite_sequenceTables not listed (e.g. a new model added later) are appended alphabetically at the end of the import by the script's catch-all (scripts/import-d1-database.ts:249-252). This is safe for FK-free or self-contained tables but should be revisited when introducing a new table with relations into another table.
The important circular dependency is:
players.currentRankingId references player_rankings.idplayer_rankings.playerId references players.idTo break the cycle, import players with currentRankingId = NULL first, then restore the original values afterward.
Avoid these approaches:
BEGIN TRANSACTIONCOMMITSAVEPOINTPRAGMA foreign_keys=OFFPRAGMA defer_foreign_keys=TRUEIn this project, wrangler/D1 did not reliably honor them for the dump import workflow.
Run the cleaned import file against a fresh persist directory:
wrangler d1 execute townofus-pl --local --file db-backups/<clean-import-file>.sql --persist-to '.wrangler/state/v3/<fresh-state>'
The import should be done into an empty state directory.
players.currentRankingIdAfter the bulk import succeeds, generate UPDATE statements from the original players rows and apply them to restore the original currentRankingId values.
The dev server reads the default local D1 state. If you imported into a separate persist path, sync the populated SQLite files into the default local D1 state used by npm run dev, or rerun the final import against the state the app actually reads.
If the app still shows no data, verify that the SQLite files under .wrangler/state/v3/d1/... are the populated ones, not the empty bootstrap state.
Note for Prisma 7: prisma.config.ts calls listLocalDatabases() and expects exactly one SQLite file under .wrangler/state/v3/d1/miniflare-D1DatabaseObject/. If you ran the import into a separate --persist-to directory, clean up the stale state directory before running prisma commands (e.g. npm run db:migrate:diff), or the config will throw a "Multiple local D1 databases found" error.
After import, verify:
SELECT COUNT(*) on key tables (players, games, player_rankings, game_player_statistics, meetings, game_events, lista_cweli, drama_afera_settings)PRAGMA foreign_key_check; returns no rowsnpm run db:migrate:diff reports -- This is an empty migration. — confirms the imported DB schema matches prisma/schema.prisma (no drift introduced by the import)npm run dev reads the populated local stateReference counts from a recent prod import (May 2026), for sanity-checking magnitudes:
| Table | Rows |
|---|---|
| players | ~50 |
| games | ~470 |
| meetings | ~1,750 |
| player_rankings | ~18,200 |
| game_player_statistics | ~7,300 |
| game_events | ~12,300 |
| lista_cweli | ~4 |
| drama_afera_settings | 0 (until PR #278 lands and production starts writing) |
no such table: main.player_rankingsThe schema or dump order is wrong. Ensure the schema comes from migrations and the import file only contains INSERTs.
FOREIGN KEY constraint failedThe INSERT order is still wrong, or players.currentRankingId was not nulled before importing.
UNIQUE constraint failed: d1_migrations.idRemove INSERT INTO "d1_migrations" from the import file. Migrations already created those rows.
SQLITE_ERROR: unrecognized token: "\\"The import file was damaged by a bad text transformation or encoding problem. Rebuild the file from a fresh export.
More than one account available but unable to select one in non-interactive mode.Wrangler can't pick between multiple Cloudflare accounts. Export CLOUDFLARE_ACCOUNT_ID=d1287f321cc95882fc773ab71241b51a (this project's account) and rerun.
Multiple local D1 databases found from prisma.config.tslistLocalDatabases() found more than one .sqlite file under .wrangler/state/v3/d1/miniflare-D1DatabaseObject/. Usually a leftover persist directory from a previous import. Remove the stale one and rerun.
The app is reading the default local D1 state, but the import was written to a different --persist-to directory.
CLOUDFLARE_ACCOUNT_ID exported (or single-account environment)d1_migrations INSERTs removedplayers.currentRankingId nulled for the bulk importplayers.currentRankingId restored afterwardPRAGMA foreign_key_check passesnpm run db:migrate:diff reports empty (no drift)npm run dev sees the imported dataPerform the weekly Dramaafera content update — add emperor poll JSON, rotate settings changelog, and add a new playlist entry. Use this skill when asked to do a weekly update, add a new emperor poll, update the playlist, or rotate the settings changelog.
Plan a new user-facing feature for townofus.pl. Produces a human-reviewed PLAN.md and then splits it into self-contained AI-executable task files. Use this skill when asked to plan a new feature, design how to implement something, or create a feature implementation roadmap.
Scaffold a new API route with handler files, route.ts middleware composition, Zod schemas, and OpenAPI registration. Use this skill when asked to create a new API endpoint, add a new route, or build a new REST resource.
Perform a database schema change — edit the Prisma schema, generate migrations, and apply them to the local Cloudflare D1 database. Use this skill when asked to add a column, create a new table, modify the database schema, or run migrations.
Add a new game role or modifier to the Town of Us role wiki. Use this skill when asked to create a new role, add a new modifier, or define a new game character for the Town of Us mod.