postgresql.test.ts

// Copyright (c) 2026 Azimutt, Inc.
// Licensed under the MIT License. See LICENSES/MIT.txt in the repository root.

import { Client, ClientConfig } from 'pg'
import { ConsoleLogger, DatabaseParams, DatabaseUrl } from '@azimutt/inspector-core'
import {
  buildSchemaFilter,
  cancelQuery,
  connect,
  countTables,
  getChecks,
  getColumns,
  getConnections,
  getCostlyPlans,
  getCostlyQueries,
  getDatabases,
  getDatabasesSchema,
  getForeignKeys,
  getIncomingForeignKeys,
  getIndexes,
  getInstance,
  getLocks,
  getQueriesDatabaseStats,
  getQueryPlan,
  getRunningQueries,
  getSchemas,
  getSequences,
  getSettings,
  getSlowPlans,
  getSlowQueries,
  getTables,
  getTableSchemas,
  killConnection,
  ping,
} from './postgresql'

describe('PostgreSQL', () => {
  const name = 'azimutt/test'
  const conn = DatabaseUrl.parse('postgresql://postgres:postgres@localhost/azimutt_dev')
  // const conn = DatabaseUrl.parse('postgresql://metabase:metabase@localhost:4001/metabase')
  // const conn = DatabaseUrl.parse('postgresql://gitlab:gitlab@localhost:4003/gitlab')
  const logger = new ConsoleLogger()
  it.skip('connects', async () => {
    const conn: DatabaseParams = DatabaseParams.parse({
      host: 'localhost',
      port: 5432,
      user: 'postgres',
      pass: 'postgres',
      db: 'azimutt_dev',
    })
    expect(await connect(name, conn, logger, c => c.all('SELECT 1;'))).toEqual([{ '?column?': 1 }])
  })
  it.skip('connects raw', async () => {
    // see https://node-postgres.com/apis/client and https://node-postgres.com/features/connecting
    // ssl: https://nodejs.org/api/tls.html#tlsconnectoptions-callback
    const config: ClientConfig = {
      host: 'localhost',
      port: 5432,
      user: 'postgres',
      password: 'postgres',
      database: 'azimutt_dev',
    }
    const client = new Client(config)
    await client.connect()
    const res = await client.query('SELECT 1;').finally(() => client.end())
    expect(res.rows).toEqual([{ '?column?': 1 }])
  })
  it.skip('query', async () => {
    expect(await connect(name, conn, logger, c => c.all('SELECT count(*) FROM users;'))).toEqual([{ count: 6 }])
    /*expect(await connect(name, conn, logger, c => c.all('SELECT e.name, u.slug FROM events e JOIN users u ON e.created_by = u.id LIMIT 2;'))).toEqual([
      { name: 'billing_loaded', slug: 'admin' },
      { name: 'organization_loaded', slug: 'loi-c' },
    ])
    expect(await connect(name, conn, logger, c => c.all('SELECT e.name, u.name FROM events e JOIN users u ON e.created_by = u.id LIMIT 2;'))).toEqual([
      { 'e.name': 'billing_loaded', 'u.name': 'Azimutt Admin' },
      { 'e.name': 'organization_loaded', 'u.name': 'Loïc' },
    ])
    expect(await connect(name, conn, logger, c => c.all('SELECT e.name, u.name, u.name FROM events e JOIN users u ON e.created_by = u.id LIMIT 2;'))).toEqual([
      { '1.name': 'billing_loaded', '2.name': 'Azimutt Admin', '3.name': 'Azimutt Admin' },
      { '1.name': 'organization_loaded', '2.name': 'Loïc', '3.name': 'Loïc' },
    ])*/
  })
  it.skip('long query with props', async () => {
    expect(await connect(name, conn, logger, c => c.all(`
    /* application=admin-xxx endpoint=/projects/:project_id/tasks request=xxx query=getTasks source=/app/controllers/tasks.js:42 trace_id=abc123 span_id=def456 */
    SELECT count(*), pg_sleep(20) FROM users;`))).toEqual([{ count: 6, pg_sleep: '' }])
  }, 30 * 1000)
  it.skip('ping', async () => {
    const res = await connect(name, conn, logger, c => ping(c))
    console.log('res', res)
    expect(res.length).toEqual(1)
  })
  it.skip('instance', async () => {
    const instance = await connect(name, conn, logger, c => getInstance(c))
    // console.log('instance', instance)
    expect(instance.current_database).toEqual('azimutt_dev')
  })
  it.skip('settings', async () => {
    const settings = await connect(name, conn, logger, c => getSettings(c))
    // console.log('settings', settings)
    expect(settings.length).toEqual(367)
  })
  it.skip('count tables', async () => {
    const count = await connect(name, conn, logger, c => countTables(c, undefined))
    // console.log('count', count)
    expect(count.tables).toEqual(15)
  })
  it.skip('databases', async () => {
    const databases = await connect(name, conn, logger, c => getDatabases(c, undefined))
    // console.log('databases', databases)
    expect(databases.length).toEqual(7)
  })
  it.skip('schemas', async () => {
    const schemas = await connect(name, conn, logger, c => getSchemas(c, undefined))
    // console.log('schemas', schemas)
    expect(schemas.length).toEqual(4)
  })
  it.skip('tables', async () => {
    const tables = await connect(name, conn, logger, c => getTables(c, { include: ['public'] }, undefined))
    // console.log('tables', tables)
    expect(tables.length).toEqual(27)
  })
  it.skip('columns', async () => {
    const columns = await connect(name, conn, logger, c => getColumns(c, { include: ['public'] }, 'users', undefined))
    // console.log('columns', columns)
    expect(columns.length).toEqual(19)
  })
  it.skip('indexes', async () => {
    const indexes = await connect(name, conn, logger, c => getIndexes(c, { include: ['public'] }, 'users', undefined))
    // console.log('indexes', indexes)
    expect(indexes.length).toEqual(3)
  })
  it.skip('foreign keys', async () => {
    const foreignKeys = await connect(name, conn, logger, c => getForeignKeys(c, { include: ['public'] }, 'projects', undefined))
    // console.log('foreignKeys', foreignKeys)
    expect(foreignKeys.length).toEqual(5)
  })
  it.skip('incoming foreign keys', async () => {
    const foreignKeys = await connect(name, conn, logger, c => getIncomingForeignKeys(c, { include: ['public'] }, 'projects', undefined))
    // console.log('foreignKeys', foreignKeys)
    expect(foreignKeys.length).toEqual(2)
  })
  it.skip('checks', async () => {
    const checks = await connect(name, conn, logger, c => getChecks(c, { include: ['public'] }, undefined, undefined))
    // console.log('checks', checks)
    expect(checks.length).toEqual(0) // 11 with metabase
  })
  it.skip('sequences', async () => {
    const sequences = await connect(name, conn, logger, c => getSequences(c, { include: ['public'] }, undefined, undefined, undefined))
    // console.log('sequences', sequences)
    expect(sequences.length).toEqual(1)
  })
  it.skip('database schema', async () => {
    const database = await connect(name, conn, logger, c => getDatabasesSchema(c))
    // console.log('database', database)
    expect(database.entities?.length).toEqual(27)
  })
  it.skip('table schemas', async () => {
    const tables = await connect(name, conn, logger, c => getTableSchemas(c, 'public', undefined))
    // console.log('table', tables[0])
    // console.log('tables', tables)
    expect(tables.length).toEqual(27)
  })
  it.skip('getLocks', async () => {
    const locks = await connect(name, conn, logger, c => getLocks(c, undefined))
    // console.log('locks', locks)
    expect(locks.length).toEqual(0)
  })
  it.skip('cancelQuery', async () => {
    const canceled = await connect(name, conn, logger, c => cancelQuery(c, 3753607))
    console.log('canceled', canceled)
    expect(canceled).toEqual(true)
  })
  it.skip('killConnection', async () => {
    const killed = await connect(name, conn, logger, c => killConnection(c, 3757366))
    console.log('killed', killed)
    expect(killed).toEqual(true)
  })
  it.skip('query plan', async () => {
    // const query = 'SELECT * FROM users'
    const query = 'SELECT * FROM pg_class WHERE relname = $1;'
    const plan = await connect(name, conn, logger, c => getQueryPlan(c, query, { json: false }))
    // console.log('plan', plan)
    // console.log('plan', plan.plan[0])
    expect(plan.query).toEqual(query)
  })
  it.skip('connections', async () => {
    const connections = await connect(name, conn, logger, c => getConnections(c, undefined))
    // console.log('connections', connections)
    expect(connections.length).toEqual(6)
  })
  it.skip('running queries', async () => {
    const queries = await connect(name, conn, logger, c => getRunningQueries(c))
    // console.log('queries', queries)
    expect(queries.length).toEqual(0)
  })
  it.skip('slow queries', async () => {
    const queries = await connect(name, conn, logger, c => getSlowQueries(c, undefined, 10))
    // console.log('queries', queries)
    expect(queries.length).toEqual(10)
  })
  it.skip('costly queries', async () => {
    const queries = await connect(name, conn, logger, c => getCostlyQueries(c, undefined, 10))
    // console.log('queries', queries)
    expect(queries.length).toEqual(10)
  })
  it.skip('queries database stats', async () => {
    const databaseStats = await connect(name, conn, logger, c => getQueriesDatabaseStats(c, [16388]))
    // console.log('databaseStats', databaseStats)
    expect(databaseStats.length).toEqual(1)
  })
  it.skip('slow plans', async () => {
    const plans = await connect(name, conn, logger, c => getSlowPlans(c, undefined, 10))
    // console.log('plans', plans)
    expect(plans.length).toEqual(10)
  })
  it.skip('costly plans', async () => {
    const plans = await connect(name, conn, logger, c => getCostlyPlans(c, undefined, 10))
    // console.log('plans', plans)
    expect(plans.length).toEqual(10)
  })
  it.skip('abort query', async () => {
    const controller = new AbortController()
    setTimeout(() => controller.abort(), 1000)
    await expect(connect(name, conn, logger, c => c.all('SELECT pg_sleep(2);'), { abort: controller.signal })).rejects.toThrow('Aborted')
  })

  describe('buildSchemaFilter', () => {
    it('build schema filter', () => {
      const filter = buildSchemaFilter('n.nspname', 1, { include: ['public', 'azimutt_%', '.+_prod'], exclude: ['information_schema', 'pg_%', '.+_data'] })
      expect(filter).toEqual({
        fragment: '(n.nspname = $1 OR n.nspname ILIKE $2 OR n.nspname ~* $3) AND n.nspname != $4 AND n.nspname NOT ILIKE $5 AND n.nspname !~* $6',
        params: ['public', 'azimutt_%', '.+_prod', 'information_schema', 'pg_%', '.+_data'],
        index: 7
      })
    })
    it('handles empty filter', () => {
      const expected = { fragment: 'n.nspname NOT IN ($1, $2, $3)', params: ['information_schema', 'pg_catalog', 'pg_toast'], index: 4 }
      expect(buildSchemaFilter('n.nspname', 1, { include: [], exclude: [] })).toEqual(expected)
      expect(buildSchemaFilter('n.nspname', 1, { exclude: [] })).toEqual(expected)
      expect(buildSchemaFilter('n.nspname', 1, { include: [] })).toEqual(expected)
      expect(buildSchemaFilter('n.nspname', 1, {})).toEqual(expected)
      expect(buildSchemaFilter('n.nspname', 1, undefined)).toEqual(expected)
    })
    it('handles schemas only', () => {
      const filter = buildSchemaFilter('n.nspname', 1, { include: ['public'] })
      expect(filter).toEqual({ fragment: 'n.nspname = $1', params: ['public'], index: 2 })
    })
    it('handles exclusions only', () => {
      const filter = buildSchemaFilter('n.nspname', 3, { exclude: ['pg_catalog'] })
      expect(filter).toEqual({ fragment: 'n.nspname != $3', params: ['pg_catalog'], index: 4 })
    })
  })
})