tihao-ai/server/db/init-db.ts

import { readFile } from 'node:fs/promises';
import { Client } from 'pg';

const adminConnectionString = process.env.DATABASE_ADMIN_URL || 'postgres://postgres:postgres@localhost:5432/postgres';
const appDatabase = process.env.PGAPP_DATABASE || 'tihao_ai';
const appUser = process.env.PGAPP_USER || 'tihao_ai_app';
const appPassword = process.env.PGAPP_PASSWORD || 'tihao_ai_app';

async function main(): Promise<void> {
  const admin = new Client({ connectionString: adminConnectionString });
  await admin.connect();

  await admin.query(`DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = '${escapeLiteral(appUser)}') THEN
    CREATE ROLE ${quoteIdent(appUser)} LOGIN PASSWORD '${escapeLiteral(appPassword)}';
  END IF;
END
$$;`);

  const dbExists = await admin.query('SELECT 1 FROM pg_database WHERE datname = $1', [appDatabase]);
  if (dbExists.rowCount === 0) {
    await admin.query(`CREATE DATABASE ${quoteIdent(appDatabase)} OWNER ${quoteIdent(appUser)}`);
  }

  await admin.end();

  const schema = await readFile('server/db/schema.sql', 'utf-8');
  const app = new Client({ connectionString: process.env.DATABASE_URL || `postgres://${appUser}:${appPassword}@localhost:5432/${appDatabase}` });
  await app.connect();
  await app.query(schema);
  await app.end();

  console.log(`Database initialized: ${appDatabase}`);
}

function quoteIdent(value: string): string {
  return `"${value.replace(/"/g, '""')}"`;
}

function escapeLiteral(value: string): string {
  return value.replace(/'/g, "''");
}

main().catch((error) => {
  console.error(error);
  process.exit(1);
});