Function introspectPostgres

packages/core/src/scripts/db/schema.ts:74–219

(
  url: string,
  parsed: Record<string, string>,
)

// ---------------------------------------------------------------------------

async function introspectPostgres(
  url: string,
  parsed: Record<string, string>,
): Promise<void> {
  const { default: pg } = await import("postgres");
  const sql = pg(url);

  try {
    // List tables
    const tables: { name: string }[] = await sql`
      SELECT table_name as name
      FROM information_schema.tables
      WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
      ORDER BY table_name
    `;

    const tableInfos: TableInfo[] = [];

    for (const t of tables) {
      // Columns
      const cols: any[] = await sql`
        SELECT
          column_name as name,
          data_type as type,
          CASE WHEN is_nullable = 'NO' THEN 1 ELSE 0 END as notnull,
          column_default as dflt_value
        FROM information_schema.columns
        WHERE table_name = ${t.name}
        ORDER BY ordinal_position
      `;

      // Primary keys
      const pks: any[] = await sql`
        SELECT kcu.column_name
        FROM information_schema.table_constraints tc
        JOIN information_schema.key_column_usage kcu
          ON tc.constraint_name = kcu.constraint_name
          AND tc.table_schema = kcu.table_schema
        WHERE tc.table_name = ${t.name}
          AND tc.constraint_type = 'PRIMARY KEY'
      `;
      const pkSet = new Set(pks.map((p) => p.column_name));

      // Foreign keys
      const fks: any[] = await sql`
        SELECT
          kcu.column_name as "from",
          ccu.table_name as "table",
          ccu.column_name as "to"
        FROM information_schema.table_constraints tc
        JOIN information_schema.key_column_usage kcu
          ON tc.constraint_name = kcu.constraint_name
        JOIN information_schema.constraint_column_usage ccu
          ON tc.constraint_name = ccu.constraint_name
        WHERE tc.table_name = ${t.name}
          AND tc.constraint_type = 'FOREIGN KEY'
      `;