Typescript cli file to execute sql against supabase returning results as json

supabase-sql.ts

#!/usr/bin/env tsx

// Usage: $0 your-sql-statement

// Prerequisites:  npx, npm package `pg`, SUPABASE_DB_URL env var

import { Client } from 'pg'

const SUPABASE_DB_URL: string = process.env.SUPABASE_DB_URL || ''

async function runQuery(): Promise<void> {
  const query = process.argv[2] || 'select 1 as "1"'

  if (!SUPABASE_DB_URL) {
    console.error(
      JSON.stringify(
        { error: 'SUPABASE_DB_URL environment variable must be set.' },
        null,
        2,
      ),
    )
    process.exit(1)
  }

  const client = new Client({
    connectionString: SUPABASE_DB_URL,
  })

  try {
    await client.connect()

    const res = await client.query(query)

    if (res.rows && res.rows.length > 0) {
      console.log(JSON.stringify(res.rows, null, 2))
    } else {
      const metaData = {
        command: res.command,
        rowCount: res.rowCount,
      }
      console.log(JSON.stringify(metaData, null, 2))
    }
  } catch (err: any) {
    console.error(
      JSON.stringify({ error: err.message, stack: err.stack }, null, 2),
    )
    process.exit(1)
  } finally {
    await client.end()
  }
}

runQuery()
  .then()
  .catch((e) => {
    console.error(e.message)
    process.exit(1)
  })