72 lines
3.3 KiB
JavaScript
72 lines
3.3 KiB
JavaScript
|
// Migration runner สำหรับ container start (docker/web.Dockerfile)
|
||
|
|
// - สร้าง database จาก DATABASE_URL ถ้ายังไม่มี (เชื่อมผ่าน db "postgres" ก่อน)
|
||
|
|
// - apply ไฟล์ SQL ใน drizzle/*.sql ที่ยังไม่อยู่ในตาราง _migrations (ทีละไฟล์ใน transaction)
|
||
|
|
// ไฟล์ .sql มาจาก `npx drizzle-kit generate` — แยก statement ด้วย --> statement-breakpoint
|
||
|
|
import fs from "node:fs";
|
||
|
|
import path from "node:path";
|
||
|
|
import postgres from "postgres";
|
||
|
|
|
||
|
|
const MIGRATIONS_DIR = path.join(process.cwd(), "drizzle");
|
||
|
|
// 42P07 duplicate table/index · 42710 duplicate object (type/constraint) · 42701 duplicate column
|
||
|
|
const ALREADY_EXISTS = new Set(["42P07", "42710", "42701"]);
|
||
|
|
|
||
|
|
const dbUrl = process.env.DATABASE_URL;
|
||
|
|
if (!dbUrl) throw new Error("ต้องตั้ง DATABASE_URL ก่อนรัน migrate");
|
||
|
|
const dbName = new URL(dbUrl).pathname.replace(/^\//, "");
|
||
|
|
if (!/^[a-zA-Z0-9_-]+$/.test(dbName)) throw new Error(`ชื่อ database ใช้ไม่ได้: ${dbName}`);
|
||
|
|
|
||
|
|
async function ensureDatabase() {
|
||
|
|
const adminUrl = new URL(dbUrl);
|
||
|
|
adminUrl.pathname = "/postgres";
|
||
|
|
const sql = postgres(adminUrl.toString(), { prepare: false, max: 1 });
|
||
|
|
try {
|
||
|
|
const found = await sql`select 1 from pg_database where datname = ${dbName}`;
|
||
|
|
if (found.length === 0) {
|
||
|
|
console.log(`[migrate] สร้าง database "${dbName}"`);
|
||
|
|
await sql.unsafe(`CREATE DATABASE "${dbName}"`);
|
||
|
|
} else {
|
||
|
|
console.log(`[migrate] database "${dbName}" มีอยู่แล้ว`);
|
||
|
|
}
|
||
|
|
} finally {
|
||
|
|
await sql.end();
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
async function migrate() {
|
||
|
|
const sql = postgres(dbUrl, { prepare: false, max: 1 });
|
||
|
|
try {
|
||
|
|
await sql`create table if not exists "_migrations" ("name" text primary key, "applied_at" timestamptz not null default now())`;
|
||
|
|
const applied = new Set((await sql`select name from "_migrations"`).map((r) => r.name));
|
||
|
|
const files = fs.readdirSync(MIGRATIONS_DIR).filter((f) => f.endsWith(".sql")).sort();
|
||
|
|
for (const file of files) {
|
||
|
|
if (applied.has(file)) continue;
|
||
|
|
const statements = fs
|
||
|
|
.readFileSync(path.join(MIGRATIONS_DIR, file), "utf8")
|
||
|
|
.split("--> statement-breakpoint")
|
||
|
|
.map((s) => s.trim())
|
||
|
|
.filter(Boolean);
|
||
|
|
console.log(`[migrate] apply ${file} (${statements.length} statements)`);
|
||
|
|
try {
|
||
|
|
await sql.begin(async (tx) => {
|
||
|
|
for (const stmt of statements) await tx.unsafe(stmt);
|
||
|
|
await tx`insert into "_migrations" ("name") values (${file})`;
|
||
|
|
});
|
||
|
|
} catch (err) {
|
||
|
|
if (ALREADY_EXISTS.has(err.code)) {
|
||
|
|
// schema ถูกสร้างไปแล้วด้วยวิธีอื่น (เช่น มือ) — ถือว่าไฟล์นี้ applied แล้ว
|
||
|
|
console.warn(`[migrate] ${file}: มีอยู่แล้ว (สร้างไว้ก่อนหน้า) — บันทึกเป็น applied`);
|
||
|
|
await sql`insert into "_migrations" ("name") values (${file}) on conflict do nothing`;
|
||
|
|
} else {
|
||
|
|
throw err;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
console.log("[migrate] schema พร้อมแล้ว");
|
||
|
|
} finally {
|
||
|
|
await sql.end();
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
await ensureDatabase();
|
||
|
|
await migrate();
|