176 lines
6.1 KiB
TypeScript
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);
|
|
});
|