93 lines
3.0 KiB
JavaScript
93 lines
3.0 KiB
JavaScript
// server/db.mjs — Postgres pool + schema bootstrap + file seed
|
|
import pg from "pg";
|
|
import fs from "node:fs";
|
|
import path from "node:path";
|
|
import { fileURLToPath } from "node:url";
|
|
|
|
const __dirname = path.dirname(fileURLToPath(import.meta.url));
|
|
export const DATA_DIR = path.resolve(__dirname, "../src/data");
|
|
|
|
export const pool = new pg.Pool({
|
|
connectionString: process.env.DATABASE_URL,
|
|
max: 5,
|
|
connectionTimeoutMillis: 10000,
|
|
});
|
|
|
|
const SLUG_RE = /^[a-z0-9-]+$/;
|
|
|
|
export function isValidSlug(s) {
|
|
return typeof s === "string" && SLUG_RE.test(s);
|
|
}
|
|
|
|
// Read all src/data/<page>/<locale>.json files
|
|
export function readDataFiles() {
|
|
const out = [];
|
|
for (const page of fs.readdirSync(DATA_DIR)) {
|
|
const dir = path.join(DATA_DIR, page);
|
|
if (!fs.statSync(dir).isDirectory()) continue;
|
|
for (const file of fs.readdirSync(dir)) {
|
|
if (!file.endsWith(".json")) continue;
|
|
const locale = file.replace(/\.json$/, "");
|
|
if (!isValidSlug(page) || !isValidSlug(locale)) continue;
|
|
const raw = fs.readFileSync(path.join(dir, file), "utf-8");
|
|
out.push({ page, locale, content: JSON.parse(raw) });
|
|
}
|
|
}
|
|
return out;
|
|
}
|
|
|
|
export async function ensureSchema() {
|
|
await pool.query(`
|
|
CREATE TABLE IF NOT EXISTS page_data (
|
|
id SERIAL PRIMARY KEY,
|
|
page TEXT NOT NULL,
|
|
locale TEXT NOT NULL,
|
|
content JSONB NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (page, locale)
|
|
)
|
|
`);
|
|
}
|
|
|
|
// Seed DB from JSON files. overwrite=false keeps existing DB rows intact.
|
|
export async function seedFromFiles({ overwrite = false } = {}) {
|
|
const files = readDataFiles();
|
|
let inserted = 0;
|
|
for (const { page, locale, content } of files) {
|
|
const res = await pool.query(
|
|
overwrite
|
|
? `INSERT INTO page_data (page, locale, content, updated_at)
|
|
VALUES ($1, $2, $3, now())
|
|
ON CONFLICT (page, locale)
|
|
DO UPDATE SET content = EXCLUDED.content, updated_at = now()`
|
|
: `INSERT INTO page_data (page, locale, content)
|
|
VALUES ($1, $2, $3)
|
|
ON CONFLICT (page, locale) DO NOTHING`,
|
|
[page, locale, JSON.stringify(content)],
|
|
);
|
|
inserted += res.rowCount;
|
|
}
|
|
return { total: files.length, written: inserted };
|
|
}
|
|
|
|
// Write DB rows back to src/data/<page>/<locale>.json
|
|
// Formatted with Prettier so exports match the repo's existing JSON style.
|
|
export async function exportToFiles() {
|
|
const prettier = await import("prettier");
|
|
const { rows } = await pool.query(
|
|
"SELECT page, locale, content FROM page_data ORDER BY page, locale",
|
|
);
|
|
const written = [];
|
|
for (const { page, locale, content } of rows) {
|
|
if (!isValidSlug(page) || !isValidSlug(locale)) continue;
|
|
const dir = path.join(DATA_DIR, page);
|
|
fs.mkdirSync(dir, { recursive: true });
|
|
const formatted = await prettier.format(JSON.stringify(content), {
|
|
parser: "json",
|
|
});
|
|
fs.writeFileSync(path.join(dir, `${locale}.json`), formatted, "utf-8");
|
|
written.push(`${page}/${locale}.json`);
|
|
}
|
|
return written;
|
|
}
|