147 lines
4.5 KiB
TypeScript
147 lines
4.5 KiB
TypeScript
/*
|
||
* @Date: 2025-06-12 16:48:44
|
||
* @LastEditors: 陈子健
|
||
* @LastEditTime: 2025-06-13 14:23:03
|
||
* @FilePath: /my-score/honoback/db/index.ts
|
||
*/
|
||
import postgres from "postgres";
|
||
import { readFileSync } from "fs";
|
||
import { join, dirname } from "path";
|
||
|
||
// 获取当前文件所在目录
|
||
const __dirname =
|
||
typeof import.meta.dir !== "undefined"
|
||
? import.meta.dir
|
||
: dirname(new URL(import.meta.url).pathname);
|
||
|
||
// 从环境变量获取数据库连接信息
|
||
const DATABASE_URL =
|
||
process.env.DATABASE_URL ||
|
||
`postgresql://${process.env.DB_USER || "postgres"}:${
|
||
process.env.DB_PASSWORD || "postgres"
|
||
}@${process.env.DB_HOST || "localhost"}:${process.env.DB_PORT || "5432"}/${
|
||
process.env.DB_NAME || "media"
|
||
}`;
|
||
|
||
// 初始化 PostgreSQL 连接
|
||
const sql = postgres(DATABASE_URL);
|
||
|
||
// 自动初始化表结构（如果表不存在）
|
||
async function initTables() {
|
||
try {
|
||
// 检查 media 表是否存在
|
||
const tableExists = await sql`
|
||
SELECT EXISTS (
|
||
SELECT FROM information_schema.tables
|
||
WHERE table_schema = 'public'
|
||
AND table_name = 'media'
|
||
)
|
||
`;
|
||
|
||
// 如果表不存在，创建表
|
||
if (!tableExists[0].exists) {
|
||
console.log("Initializing database tables...");
|
||
const schemaPath = join(__dirname, "schema.sql");
|
||
const schema = readFileSync(schemaPath, "utf-8");
|
||
await sql.unsafe(schema);
|
||
console.log("Database tables initialized successfully");
|
||
}
|
||
} catch (error: any) {
|
||
// 如果是因为数据库不存在，尝试创建数据库
|
||
if (
|
||
error.message?.includes("database") &&
|
||
error.message?.includes("does not exist")
|
||
) {
|
||
console.error("Database does not exist. Please create it first:");
|
||
console.error(" createdb media");
|
||
console.error(" or: psql -U postgres -c 'CREATE DATABASE media;'");
|
||
throw error;
|
||
}
|
||
// 其他错误也抛出
|
||
throw error;
|
||
}
|
||
}
|
||
|
||
// 在模块加载时初始化表（但不阻塞）
|
||
initTables().catch((error) => {
|
||
console.error("Failed to initialize tables:", error.message);
|
||
// 不抛出错误，让应用继续运行，但会在第一次查询时失败
|
||
});
|
||
|
||
// 将 SQLite 的 ? 占位符转换为 PostgreSQL 的 $1, $2, $3...
|
||
function convertQuery(query: string, params: any[]): [string, any[]] {
|
||
let pgQuery = query;
|
||
let paramIndex = 1;
|
||
const pgParams: any[] = [];
|
||
|
||
// 替换 ? 为 $1, $2, $3...
|
||
pgQuery = pgQuery.replace(/\?/g, () => {
|
||
if (paramIndex <= params.length) {
|
||
pgParams.push(params[paramIndex - 1]);
|
||
}
|
||
return `$${paramIndex++}`;
|
||
});
|
||
|
||
return [pgQuery, pgParams];
|
||
}
|
||
|
||
// 创建一个兼容的数据库接口，模拟 SQLite 的 prepare 方法
|
||
class DatabaseAdapter {
|
||
private sql: ReturnType<typeof postgres>;
|
||
|
||
constructor(sql: ReturnType<typeof postgres>) {
|
||
this.sql = sql;
|
||
}
|
||
|
||
prepare(query: string) {
|
||
return {
|
||
// all() 方法用于查询多条记录
|
||
all: async (...params: any[]) => {
|
||
const [pgQuery, pgParams] = convertQuery(query, params);
|
||
const result = await this.sql.unsafe(pgQuery, pgParams);
|
||
return result;
|
||
},
|
||
// get() 方法用于查询单条记录
|
||
get: async (...params: any[]) => {
|
||
const [pgQuery, pgParams] = convertQuery(query, params);
|
||
const result = await this.sql.unsafe(pgQuery, pgParams);
|
||
return result[0] || null;
|
||
},
|
||
// run() 方法用于执行 INSERT/UPDATE/DELETE
|
||
run: async (...params: any[]) => {
|
||
const [pgQuery, pgParams] = convertQuery(query, params);
|
||
const result = await this.sql.unsafe(pgQuery, pgParams);
|
||
|
||
// 如果是 INSERT 语句，返回类似 SQLite 的 lastInsertRowid
|
||
if (query.trim().toUpperCase().startsWith("INSERT")) {
|
||
// PostgreSQL 使用 RETURNING 子句获取插入的 ID
|
||
if (query.includes("RETURNING")) {
|
||
return {
|
||
lastInsertRowid: result[0]?.id || null,
|
||
changes: result.length,
|
||
};
|
||
} else {
|
||
// 如果没有 RETURNING，需要查询最后插入的 ID
|
||
// 注意：这需要表有 id 列且是 SERIAL 类型
|
||
const lastIdResult = await this.sql.unsafe(
|
||
"SELECT lastval() as id"
|
||
);
|
||
return {
|
||
lastInsertRowid: lastIdResult[0]?.id || null,
|
||
changes: result.length || 1,
|
||
};
|
||
}
|
||
}
|
||
|
||
return {
|
||
changes: result.length || 0,
|
||
};
|
||
},
|
||
};
|
||
}
|
||
}
|
||
|
||
const db = new DatabaseAdapter(sql);
|
||
|
||
export { db, sql };
|