Files
it_cost_dashboard/scripts/migrate-dev-data-to-rds.ts
2026-09-08 17:46:28 +08:00

176 lines
6.1 KiB
TypeScript

/**
* One-time data migration: copy rows from the local SQLite dev DB
* (prisma/dev.db) into the production MySQL/RDS database.
*
* WHY A STANDALONE SCRIPT (not two PrismaClients): the app generates
* @prisma/client to node_modules/.prisma/client from whichever schema was
* generated last (see DEPLOY.md — sqlite vs mysql client collision). Rather
* than juggling two generated clients, this script talks to both databases
* with plain drivers (better-sqlite3 + mysql2), so it can't clobber whatever
* client the app currently has generated.
*
* WHERE TO RUN THIS: the RDS instance is in a private subnet reachable only
* from the app EC2 host (per DEPLOY.md). Run this ON THE EC2 HOST, after
* copying prisma/dev.db there.
*
* PREREQS:
* 1. npm install better-sqlite3 mysql2 --save-dev (one-time)
* 2. Tables must already exist on RDS:
* set -a && source .env.production && set +a
* npm run prod:migrate
* 3. .env.production has the real DATABASE_URL (mysql://...)
*
* USAGE (from project root on the EC2 host):
* set -a && source .env.production && set +a
* npx tsx scripts/migrate-dev-data-to-rds.ts # migrate
* npx tsx scripts/migrate-dev-data-to-rds.ts --dry-run # preview only
* npx tsx scripts/migrate-dev-data-to-rds.ts --wipe # delete existing
* # prod rows first
*
* By default the script refuses to run if the destination tables are
* non-empty, to avoid double-importing. Pass --wipe to clear them first
* (CostItem/SubCostItem/TrendAiPercentage/Year only — nothing else is touched).
*/
import Database from "better-sqlite3";
import mysql from "mysql2/promise";
import path from "node:path";
const DRY_RUN = process.argv.includes("--dry-run");
const WIPE = process.argv.includes("--wipe");
const SQLITE_PATH =
process.env.DEV_SQLITE_PATH ?? path.join(__dirname, "..", "prisma", "dev.db");
async function main() {
const prodUrl = process.env.DATABASE_URL;
if (!prodUrl || !prodUrl.startsWith("mysql://")) {
throw new Error(
"DATABASE_URL is not set to a mysql:// URL. Did you `source .env.production` first?"
);
}
console.log(`Reading dev data from ${SQLITE_PATH}`);
const sqlite = new Database(SQLITE_PATH, { readonly: true });
const years = sqlite.prepare("SELECT year FROM Year").all() as { year: number }[];
const costItems = sqlite
.prepare("SELECT id, name, region, year, createdAt, updatedAt FROM CostItem")
.all() as {
id: number;
name: string;
region: string;
year: number;
createdAt: string;
updatedAt: string;
}[];
const subCostItems = sqlite
.prepare("SELECT id, name, subCost, costItemId FROM SubCostItem")
.all() as { id: number; name: string; subCost: number; costItemId: number }[];
const trendAi = sqlite
.prepare("SELECT id, region, year, percentage FROM TrendAiPercentage")
.all() as { id: number; region: string; year: number; percentage: number }[];
sqlite.close();
console.log(
`Found: Year=${years.length}, CostItem=${costItems.length}, ` +
`SubCostItem=${subCostItems.length}, TrendAiPercentage=${trendAi.length}`
);
if (DRY_RUN) {
console.log("--dry-run set: not connecting to RDS. Done.");
return;
}
const conn = await mysql.createConnection(prodUrl);
try {
const [[{ c: yearCount }]]: any = await conn.query(
"SELECT COUNT(*) as c FROM `Year`"
);
const [[{ c: itemCount }]]: any = await conn.query(
"SELECT COUNT(*) as c FROM `CostItem`"
);
if ((yearCount > 0 || itemCount > 0) && !WIPE) {
throw new Error(
`Destination already has data (Year=${yearCount}, CostItem=${itemCount}). ` +
`Re-run with --wipe to clear existing rows first, or investigate before overwriting.`
);
}
await conn.beginTransaction();
if (WIPE) {
console.log("Wiping existing prod rows...");
await conn.query("SET FOREIGN_KEY_CHECKS=0");
await conn.query("DELETE FROM `SubCostItem`");
await conn.query("DELETE FROM `CostItem`");
await conn.query("DELETE FROM `TrendAiPercentage`");
await conn.query("DELETE FROM `Year`");
await conn.query("SET FOREIGN_KEY_CHECKS=1");
}
console.log("Inserting Year...");
for (const y of years) {
await conn.query("INSERT INTO `Year` (year) VALUES (?)", [y.year]);
}
console.log("Inserting CostItem...");
for (const c of costItems) {
await conn.query(
"INSERT INTO `CostItem` (id, name, region, year, createdAt, updatedAt) VALUES (?, ?, ?, ?, ?, ?)",
[c.id, c.name, c.region, c.year, new Date(c.createdAt), new Date(c.updatedAt)]
);
}
console.log("Inserting SubCostItem...");
for (const s of subCostItems) {
await conn.query(
"INSERT INTO `SubCostItem` (id, name, subCost, costItemId) VALUES (?, ?, ?, ?)",
[s.id, s.name, s.subCost, s.costItemId]
);
}
console.log("Inserting TrendAiPercentage...");
for (const t of trendAi) {
await conn.query(
"INSERT INTO `TrendAiPercentage` (id, region, year, percentage) VALUES (?, ?, ?, ?)",
[t.id, t.region, t.year, t.percentage]
);
}
// Realign AUTO_INCREMENT counters so future inserts don't collide with
// the explicit ids we just wrote.
for (const table of ["CostItem", "SubCostItem", "TrendAiPercentage"]) {
const [[{ maxId }]]: any = await conn.query(
`SELECT COALESCE(MAX(id), 0) as maxId FROM \`${table}\``
);
await conn.query(`ALTER TABLE \`${table}\` AUTO_INCREMENT = ${maxId + 1}`);
}
await conn.commit();
const [[{ c: finalItems }]]: any = await conn.query(
"SELECT COUNT(*) as c FROM `CostItem`"
);
const [[{ c: finalSubs }]]: any = await conn.query(
"SELECT COUNT(*) as c FROM `SubCostItem`"
);
console.log(
`Done. Committed. CostItem=${finalItems}, SubCostItem=${finalSubs} now in RDS.`
);
} catch (err) {
await conn.rollback();
console.error("Migration failed, rolled back:", err);
throw err;
} finally {
await conn.end();
}
}
main().catch((e) => {
console.error(e);
process.exit(1);
});