259 lines
8.1 KiB
TypeScript
259 lines
8.1 KiB
TypeScript
/**
|
|
* Database initialization script
|
|
* Creates database and all required tables in PostgreSQL
|
|
*/
|
|
|
|
import postgres from "postgres";
|
|
import { readFileSync } from "fs";
|
|
import { join } from "path";
|
|
|
|
/**
|
|
* Parse DATABASE_URL and extract components
|
|
*/
|
|
function parseDatabaseUrl(url: string): {
|
|
protocol: string;
|
|
user: string;
|
|
password: string;
|
|
host: string;
|
|
port: string;
|
|
database: string;
|
|
} {
|
|
const match = url.match(
|
|
/^postgresql:\/\/([^:]+):([^@]+)@([^:]+):(\d+)\/(.+)$/
|
|
);
|
|
if (!match) {
|
|
throw new Error(
|
|
"Invalid DATABASE_URL format. Expected: postgresql://user:password@host:port/database"
|
|
);
|
|
}
|
|
return {
|
|
protocol: "postgresql",
|
|
user: match[1],
|
|
password: match[2],
|
|
host: match[3],
|
|
port: match[4],
|
|
database: match[5],
|
|
};
|
|
}
|
|
|
|
async function initDatabase() {
|
|
const dbUrl = process.env.DATABASE_URL;
|
|
|
|
if (!dbUrl) {
|
|
console.error("Error: DATABASE_URL environment variable is required");
|
|
console.error("Please set it in your .env file or export it:");
|
|
console.error(
|
|
" export DATABASE_URL=postgresql://user:password@host:port/database"
|
|
);
|
|
process.exit(1);
|
|
}
|
|
|
|
const dbConfig = parseDatabaseUrl(dbUrl);
|
|
const targetDatabase = dbConfig.database;
|
|
|
|
console.log(`Target database: ${targetDatabase}`);
|
|
console.log(
|
|
`Connecting to PostgreSQL server (${dbConfig.host}:${dbConfig.port})...`
|
|
);
|
|
|
|
// Connect to postgres database (default database) to create target database
|
|
const adminUrl = `postgresql://${dbConfig.user}:${dbConfig.password}@${dbConfig.host}:${dbConfig.port}/postgres`;
|
|
const adminSql = postgres(adminUrl);
|
|
|
|
try {
|
|
// Check if database exists
|
|
const dbExists = await adminSql`
|
|
SELECT 1 FROM pg_database WHERE datname = ${targetDatabase}
|
|
`;
|
|
|
|
if (dbExists.length === 0) {
|
|
console.log(`Database "${targetDatabase}" does not exist. Creating...`);
|
|
// Create database (escape database name to prevent SQL injection)
|
|
// PostgreSQL identifiers are case-insensitive unless quoted, so we quote it
|
|
const escapedDbName = `"${targetDatabase.replace(/"/g, '""')}"`;
|
|
await (adminSql as any).unsafe(`CREATE DATABASE ${escapedDbName}`);
|
|
console.log(`✓ Database "${targetDatabase}" created successfully`);
|
|
} else {
|
|
console.log(`✓ Database "${targetDatabase}" already exists`);
|
|
}
|
|
} catch (error) {
|
|
const errorMessage = (error as Error).message;
|
|
if (errorMessage.includes("already exists")) {
|
|
console.log(`✓ Database "${targetDatabase}" already exists`);
|
|
} else {
|
|
console.error(`✗ Failed to create database: ${errorMessage}`);
|
|
throw error;
|
|
}
|
|
} finally {
|
|
await adminSql.end();
|
|
}
|
|
|
|
// Now connect to target database and create tables
|
|
console.log(`\nConnecting to database "${targetDatabase}"...`);
|
|
const sql = postgres(dbUrl);
|
|
|
|
try {
|
|
// Test connection
|
|
await sql`SELECT 1`;
|
|
console.log("✓ Connected to database");
|
|
|
|
// Read schema file
|
|
const schemaPath = join(process.cwd(), "src", "storage", "schema.sql");
|
|
console.log(`Reading schema from ${schemaPath}...`);
|
|
const schema = readFileSync(schemaPath, "utf-8");
|
|
|
|
// Remove comments
|
|
const cleanedSchema = schema
|
|
.split("\n")
|
|
.map((line) => {
|
|
const trimmed = line.trim();
|
|
if (trimmed.startsWith("--")) {
|
|
return "";
|
|
}
|
|
const commentIndex = line.indexOf("--");
|
|
if (commentIndex >= 0) {
|
|
return line.substring(0, commentIndex);
|
|
}
|
|
return line;
|
|
})
|
|
.join("\n");
|
|
|
|
// Split by semicolon and reconstruct multi-line statements
|
|
const parts = cleanedSchema.split(";");
|
|
const allStatements: string[] = [];
|
|
let currentStatement = "";
|
|
|
|
for (let i = 0; i < parts.length; i++) {
|
|
const part = parts[i].trim();
|
|
if (part.length === 0) {
|
|
// If we have a current statement and hit an empty part, it's the end of a statement
|
|
if (currentStatement.trim().length > 0) {
|
|
allStatements.push(currentStatement.trim());
|
|
currentStatement = "";
|
|
}
|
|
continue;
|
|
}
|
|
|
|
currentStatement += (currentStatement ? " " : "") + part;
|
|
|
|
// Check if parentheses are balanced (for CREATE TABLE) or if it's a simple statement (CREATE INDEX)
|
|
const openCount = (currentStatement.match(/\(/g) || []).length;
|
|
const closeCount = (currentStatement.match(/\)/g) || []).length;
|
|
const isIndex = currentStatement
|
|
.toUpperCase()
|
|
.trim()
|
|
.startsWith("CREATE INDEX");
|
|
|
|
// Statement is complete if:
|
|
// 1. It's an INDEX (usually single line)
|
|
// 2. Parentheses are balanced
|
|
// 3. Or it's the last part
|
|
if (isIndex || openCount === closeCount || i === parts.length - 1) {
|
|
if (currentStatement.trim().length > 0) {
|
|
allStatements.push(currentStatement.trim());
|
|
currentStatement = "";
|
|
}
|
|
}
|
|
}
|
|
|
|
// Separate CREATE TABLE and CREATE INDEX statements
|
|
const tableStatements: string[] = [];
|
|
const indexStatements: string[] = [];
|
|
|
|
for (const statement of allStatements) {
|
|
const upperStatement = statement.toUpperCase().trim();
|
|
if (upperStatement.startsWith("CREATE TABLE")) {
|
|
tableStatements.push(statement);
|
|
} else if (upperStatement.startsWith("CREATE INDEX")) {
|
|
indexStatements.push(statement);
|
|
}
|
|
}
|
|
|
|
console.log(
|
|
`Found ${tableStatements.length} table(s) and ${indexStatements.length} index(es) to create`
|
|
);
|
|
|
|
// First, create all tables
|
|
console.log("\nCreating tables...");
|
|
for (const statement of tableStatements) {
|
|
if (statement) {
|
|
try {
|
|
await (sql as any).unsafe(statement + ";");
|
|
const tableName =
|
|
statement.match(
|
|
/CREATE TABLE\s+(?:IF NOT EXISTS\s+)?(\w+)/i
|
|
)?.[1] || "unknown";
|
|
console.log(`✓ Created table: ${tableName}`);
|
|
} catch (error) {
|
|
const errorMessage = (error as Error).message;
|
|
if (
|
|
errorMessage.includes("already exists") ||
|
|
errorMessage.includes("duplicate")
|
|
) {
|
|
const tableName =
|
|
statement.match(
|
|
/CREATE TABLE\s+(?:IF NOT EXISTS\s+)?(\w+)/i
|
|
)?.[1] || "unknown";
|
|
console.log(`⚠ Table already exists: ${tableName}`);
|
|
} else {
|
|
console.error(`✗ Error creating table: ${errorMessage}`);
|
|
console.error(` Statement: ${statement.substring(0, 100)}...`);
|
|
throw error;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
// Then, create all indexes
|
|
console.log("\nCreating indexes...");
|
|
for (const statement of indexStatements) {
|
|
if (statement) {
|
|
try {
|
|
await (sql as any).unsafe(statement + ";");
|
|
const indexName =
|
|
statement.match(
|
|
/CREATE INDEX\s+(?:IF NOT EXISTS\s+)?(\w+)/i
|
|
)?.[1] || "unknown";
|
|
console.log(`✓ Created index: ${indexName}`);
|
|
} catch (error) {
|
|
const errorMessage = (error as Error).message;
|
|
if (
|
|
errorMessage.includes("already exists") ||
|
|
errorMessage.includes("duplicate")
|
|
) {
|
|
const indexName =
|
|
statement.match(
|
|
/CREATE INDEX\s+(?:IF NOT EXISTS\s+)?(\w+)/i
|
|
)?.[1] || "unknown";
|
|
console.log(`⚠ Index already exists: ${indexName}`);
|
|
} else {
|
|
console.error(`✗ Error creating index: ${errorMessage}`);
|
|
console.error(` Statement: ${statement.substring(0, 100)}...`);
|
|
throw error;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
console.log("\n✓ Database initialization completed successfully!");
|
|
console.log("\nTables created:");
|
|
console.log(" - code_snippets");
|
|
console.log(" - notes");
|
|
console.log(" - tasks");
|
|
console.log(" - baby_milestones");
|
|
console.log(" - math_resources");
|
|
console.log(" - game_wishlist");
|
|
} catch (error) {
|
|
console.error("\n✗ Database initialization failed:", error);
|
|
process.exit(1);
|
|
} finally {
|
|
await sql.end();
|
|
}
|
|
}
|
|
|
|
// Run initialization
|
|
initDatabase().catch((error) => {
|
|
console.error("Fatal error:", error);
|
|
process.exit(1);
|
|
});
|