d296108f67
- 创建 db/init.ts 脚本，可自动创建数据库和表 - 更新 db/index.ts，应用启动时自动检查并创建表结构 - 添加 bun run init-db 命令用于初始化数据库 - 更新 README.md 添加数据库初始化说明
84 lines
2.4 KiB
TypeScript
84 lines
2.4 KiB
TypeScript
/*
|
|
* 数据库初始化脚本
|
|
* 自动创建数据库和表结构
|
|
*/
|
|
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 getBaseConnection = () => {
|
|
const user = process.env.DB_USER || "postgres";
|
|
const password = process.env.DB_PASSWORD || "postgres";
|
|
const host = process.env.DB_HOST || "localhost";
|
|
const port = process.env.DB_PORT || "5432";
|
|
return `postgresql://${user}:${password}@${host}:${port}`;
|
|
};
|
|
|
|
// 初始化数据库
|
|
export async function initDatabase() {
|
|
const dbName = process.env.DB_NAME || "media";
|
|
const baseUrl = getBaseConnection();
|
|
|
|
try {
|
|
// 连接到 PostgreSQL 服务器（使用默认的 postgres 数据库）
|
|
const adminSql = postgres(`${baseUrl}/postgres`);
|
|
|
|
// 检查数据库是否存在
|
|
const dbExists = await adminSql`
|
|
SELECT 1 FROM pg_database WHERE datname = ${dbName}
|
|
`;
|
|
|
|
// 如果数据库不存在，创建它
|
|
if (dbExists.length === 0) {
|
|
console.log(`Creating database: ${dbName}`);
|
|
await adminSql.unsafe(`CREATE DATABASE ${dbName}`);
|
|
console.log(`Database ${dbName} created successfully`);
|
|
} else {
|
|
console.log(`Database ${dbName} already exists`);
|
|
}
|
|
|
|
await adminSql.end();
|
|
|
|
// 连接到新创建的数据库
|
|
const sql = postgres(`${baseUrl}/${dbName}`);
|
|
|
|
// 读取并执行 schema.sql
|
|
const schemaPath = join(__dirname, "schema.sql");
|
|
const schema = readFileSync(schemaPath, "utf-8");
|
|
|
|
// 执行 schema 中的 SQL 语句
|
|
console.log("Creating tables...");
|
|
await sql.unsafe(schema);
|
|
console.log("Tables created successfully");
|
|
|
|
await sql.end();
|
|
console.log("Database initialization completed");
|
|
} catch (error: any) {
|
|
console.error("Database initialization failed:", error.message);
|
|
throw error;
|
|
}
|
|
}
|
|
|
|
// 如果直接运行此文件，执行初始化
|
|
if (
|
|
import.meta.main ||
|
|
(typeof Bun !== "undefined" && Bun.main === import.meta.path)
|
|
) {
|
|
initDatabase()
|
|
.then(() => {
|
|
console.log("Initialization complete");
|
|
process.exit(0);
|
|
})
|
|
.catch((error) => {
|
|
console.error("Initialization failed:", error);
|
|
process.exit(1);
|
|
});
|
|
}
|