app-261004/scripts/db-verify-schema.mjs

/**
 * 验证 posts 表的约束与索引是否按预期生效。
 *
 * 这里直接对数据库做"坏写入"测试，确认约束真的能拦住非法数据——
 * 只检查迁移文件容易漏掉约束静默失效的情况。
 *
 * 用法：node scripts/db-verify-schema.mjs
 */

import pg from "pg";
import { config } from "dotenv";

config({ path: ".env.local" });

const client = new pg.Client({
	connectionString: process.env.DATABASE_URL,
	ssl: /localhost|127\.0\.0\.1/.test(process.env.DATABASE_URL ?? "")
		? undefined
		: { rejectUnauthorized: false },
});

let pass = 0;
let fail = 0;

function check(name, condition, detail = "") {
	if (condition) {
		pass++;
		console.log(`  [PASS] ${name}`);
	} else {
		fail++;
		console.log(`  [FAIL] ${name} ${detail}`);
	}
}

/** 期望插入失败（被约束拦截），返回错误信息 */
async function expectReject(label, sql, params) {
	try {
		await client.query(sql, params);
		check(label, false, "← 竟然插入成功了，约束未生效");
		// 清理掉误插入的数据
		await client.query("delete from posts where slug like 'zz-test-%'");
	} catch (error) {
		check(label, true);
		return error.message;
	}
}

await client.connect();

console.log("── 列结构 ────────────────────────────");
const cols = await client.query(
	`select column_name, data_type, is_nullable, column_default
	 from information_schema.columns where table_name='posts' order by ordinal_position`,
);
const colNames = cols.rows.map((r) => r.column_name);
console.log(`  列 (${colNames.length}): ${colNames.join(", ")}`);

check("包含 slug 列", colNames.includes("slug"));
check("包含 status 列", colNames.includes("status"));
check("包含 tags 列", colNames.includes("tags"));
check("包含 published_at 列", colNames.includes("published_at"));
check("包含 reading_minutes 列", colNames.includes("reading_minutes"));

const tagsCol = cols.rows.find((r) => r.column_name === "tags");
check("tags 为数组类型", tagsCol?.data_type === "ARRAY", `got ${tagsCol?.data_type}`);

console.log("\n── 索引 ──────────────────────────────");
const idx = await client.query(
	`select indexname, indexdef from pg_indexes where tablename='posts' order by indexname`,
);
for (const row of idx.rows) {
	console.log(`  - ${row.indexname}`);
}
const idxNames = idx.rows.map((r) => r.indexname);
check("slug 唯一索引存在", idxNames.includes("posts_slug_unique_idx"));
check("状态+发布时间索引存在", idxNames.includes("posts_status_published_at_idx"));
check("标签 GIN 索引存在", idxNames.includes("posts_tags_gin_idx"));

const ginDef = idx.rows.find((r) => r.indexname === "posts_tags_gin_idx")?.indexdef ?? "";
check("标签索引使用 gin", /using gin/i.test(ginDef), ginDef);

console.log("\n── 约束 ──────────────────────────────");
const cons = await client.query(
	`select conname, pg_get_constraintdef(oid) as def
	 from pg_constraint where conrelid = 'posts'::regclass order by conname`,
);
for (const row of cons.rows) {
	console.log(`  - ${row.conname}: ${row.def}`);
}
const conNames = cons.rows.map((r) => r.conname);
check("status CHECK 约束存在", conNames.includes("posts_status_check"));
check("slug 格式 CHECK 约束存在", conNames.includes("posts_slug_format_check"));

console.log("\n── 约束实际拦截能力（坏写入测试）──");
await expectReject(
	"拒绝非法 status",
	`insert into posts (slug,title,summary,content,status) values ($1,$2,$3,$4,$5)`,
	["zz-test-badstatus", "t", "s", "c", "archived"],
);
await expectReject(
	"拒绝非法 slug（大写/下划线）",
	`insert into posts (slug,title,summary,content) values ($1,$2,$3,$4)`,
	["ZZ_Test_Slug", "t", "s", "c"],
);

// 唯一约束需要先插入一行，再插入同样的 slug 才能验证
await client.query(
	`insert into posts (slug,title,summary,content) values ($1,$2,$3,$4)`,
	["zz-test-dup", "t", "s", "c"],
);
await expectReject(
	"拒绝重复 slug",
	`insert into posts (slug,title,summary,content) values ($1,$2,$3,$4)`,
	["zz-test-dup", "t", "s", "c"],
);

console.log("\n── 正常写入与数组读写 ───────────────");
try {
	const inserted = await client.query(
		`insert into posts (slug,title,summary,content,tags,status,published_at,reading_minutes)
		 values ($1,$2,$3,$4,$5,$6,now(),$7) returning id, tags, status`,
		[
			"zz-test-ok",
			"正常文章",
			"摘要",
			"正文",
			["Next.js", "数据库"],
			"published",
			3,
		],
	);
	check("合法数据可写入", inserted.rowCount === 1);
	check(
		"tags 数组正确回读",
		Array.isArray(inserted.rows[0].tags) && inserted.rows[0].tags.length === 2,
		JSON.stringify(inserted.rows[0].tags),
	);

	// 验证 GIN 索引可用的标签查询语法
	const byTag = await client.query(
		`select count(*)::int as n from posts where tags @> ARRAY[$1]::text[]`,
		["Next.js"],
	);
	check("按标签包含查询可用", byTag.rows[0].n >= 1);

	const anyTag = await client.query(
		`select count(*)::int as n from posts where $1 = any(tags)`,
		["数据库"],
	);
	check("按标签 ANY 查询可用", anyTag.rows[0].n >= 1);
} finally {
	// 清理测试数据
	await client.query("delete from posts where slug like 'zz-test-%'");
}

const remaining = await client.query("select count(*)::int as n from posts");
console.log(`\n清理后剩余行数: ${remaining.rows[0].n}`);

console.log("\n================================");
console.log(`  通过 ${pass} / ${pass + fail}`);
console.log("================================");

await client.end();
process.exitCode = fail > 0 ? 1 : 0;