mysql.test.ts

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

import * as mysql from 'mysql2/promise'
import { Connection, FieldPacket, RowDataPacket } from 'mysql2/promise'
import { ConsoleLogger, DatabaseParams, DatabaseUrl } from '@azimutt/inspector-core'
import {
  cancelQuery,
  connect,
  getChecks,
  getColumns,
  getDatabasesSchema,
  getForeignKeys,
  getIndexes,
  getInfos,
  getQueryPlan,
  getRunningQueries,
  getSchemas,
  getSettings,
  getTables,
  killConnection,
  ping,
} from './mysql'

describe('MySQL', () => {
  // run `docker compose up` in `demo/dbs/mysql`
  const conn = DatabaseUrl.parse('mysql://mysql:mysql@localhost:3308/test_db')
  const logger = new ConsoleLogger()
  it.skip('connects', async () => {
    const conn = DatabaseParams.parse({
      host: 'localhost',
      port: 3308,
      user: 'mysql',
      pass: 'mysql',
      db: 'test_db',
    })
    expect(await connect(conn, logger, c => c.all('SELECT 1;'))).toEqual([{ '1': 1 }])
  })
  it.skip('connects raw', async () => {
    const config: mysql.ConnectionOptions = {
      host: 'localhost',
      port: 3308,
      user: 'mysql',
      password: 'mysql',
      database: 'test_db',
      insecureAuth: true,
    }
    const connection: Connection = await mysql.createConnection(config)
    try {
      const [rows, fields]: [RowDataPacket[], FieldPacket[]] = await connection.query<RowDataPacket[]>('SELECT 1;')
      console.log('rows', rows)
      console.log('fields', fields)
    } finally {
      await connection.end()
    }
  })
  it.skip('query', async () => {
    expect(await connect(conn, logger, c => c.all('SELECT count(*) FROM information_schema.TABLES;'))).toEqual([{ 'count(*)': 124 }])
  })
  it.skip('ping', async () => {
    const res = await connect(conn, logger, c => ping(c))
    console.log('res', res)
    expect(res.length).toEqual(1)
  })
  it.skip('infos', async () => {
    const infos = await connect(conn, logger, c => getInfos(c))
    console.log('infos', infos)
    expect(infos.database).toEqual('test_db')
  })
  it.skip('settings', async () => {
    const settings = await connect(conn, logger, c => getSettings(c))
    console.log('settings', settings)
    expect(settings.length).toEqual(622)
  })
  it.skip('schemas', async () => {
    const schemas = await connect(conn, logger, c => getSchemas(c, undefined))
    console.log('schemas', schemas)
    expect(schemas.length).toEqual(1)
  })
  it.skip('tables', async () => {
    const tables = await connect(conn, logger, c => getTables(c, { include: ['test_db'] }, undefined))
    console.log('tables', tables)
    expect(tables.length).toEqual(9)
  })
  it.skip('columns', async () => {
    const columns = await connect(conn, logger, c => getColumns(c, { include: ['test_db'] }, 'users', undefined))
    console.log('columns', columns)
    expect(columns.length).toEqual(8)
  })
  it.skip('indexes', async () => {
    const indexes = await connect(conn, logger, c => getIndexes(c, { include: ['test_db'] }, 'users', undefined))
    console.log('indexes', indexes)
    expect(indexes.length).toEqual(6)
  })
  it.skip('foreign keys', async () => {
    const foreignKeys = await connect(conn, logger, c => getForeignKeys(c, { include: ['test_db'] }, undefined))
    console.log('foreignKeys', foreignKeys)
    expect(foreignKeys.length).toEqual(7)
  })
  it.skip('checks', async () => {
    const checks = await connect(conn, logger, c => getChecks(c, { include: ['test_db'] }, undefined))
    console.log('checks', checks)
    expect(checks.length).toEqual(3)
  })
  it.skip('database schema', async () => {
    const database = await connect(conn, logger, c => getDatabasesSchema(c, { include: ['test_db'] }))
    console.log('database', JSON.stringify(database, null, 2))
    expect(database.entities).toBeDefined()
    expect(database.entities!.length).toEqual(9)
  })
  it.skip('running queries', async () => {
    const queries = await connect(conn, logger, c => getRunningQueries(c, { database: 'test_db' }))
    console.log('running queries', queries)
    expect(queries).toEqual([])
  })
  it.skip('query plan', async () => {
    const query = 'SELECT u.username, COUNT(t.id) FROM users u LEFT JOIN tasks t ON t.assigned_to = u.id GROUP BY u.id'
    const plan = await connect(conn, logger, c => getQueryPlan(c, query, { analyze: true }))
    // console.log('plan', JSON.stringify(plan.plan, null, 2))
    console.log('plan', plan.plan)
    expect(plan.query).toEqual(query)
  })
  it.skip('cancel query', async () => {
    const canceled = await connect(conn, logger, c => cancelQuery(c, 12))
    console.log('canceled', canceled)
    expect(canceled).toEqual(true)
  })
  it.skip('kill connection', async () => {
    const killed = await connect(conn, logger, c => killConnection(c, 12))
    console.log('killed', killed)
    expect(killed).toEqual(true)
  })
})