bm-sql-inspector

SQL Action Inspector - A CLI tool to extract and analyze MySQL and PostgreSQL actions from action_settings.json

Overview

bm-sql-inspector is a TypeScript-based CLI tool that extracts MySQL and PostgreSQL actions from action_settings.json files and analyzes their SQL queries and parameters.

The action_settings.json file may contain multiple action types (mysql, postgresql, restapi, awss3, etc.), but this tool focuses exclusively on SQL-based actions (class="mysql" or class="postgresql"), simply skipping other action types.

Features

• ✅ Extract MySQL and PostgreSQL actions
• ✅ Extract parameter references from SQL statements ({{ parameter_name }} pattern)
• ✅ Automatically extract table names and column names from SQL queries
• ✅ Filter by multiple conditions (AND logic)
• ✅ JSON output format

Installation

No Installation Required - Use with npx:

# Run directly without installing (once published to npm)
npx @basemachina/bm-sql-inspector --help
npx @basemachina/bm-sql-inspector --action-settings-file=action_settings.json

For Development:

# Clone the repository
git clone <repository-url>
cd bm-sql-inspector

# Install dependencies
npm install

# Build the project
npm run build

# Link globally for local testing
npm link

# Now you can test the command
bm-sql-inspector --help

Usage

Basic Usage

# Extract all SQL actions (default)
npx @basemachina/bm-sql-inspector

# Specify a file
npx @basemachina/bm-sql-inspector --action-settings-file=action_settings.json

Filtering

Filter by Datasource Type

# MySQL actions only
npx @basemachina/bm-sql-inspector --datasource-type=mysql

# PostgreSQL actions only
npx @basemachina/bm-sql-inspector --datasource-type=postgresql

Filter by Data Source Name

npx @basemachina/bm-sql-inspector --data-source-name=service-db

Filter by Table Name

npx @basemachina/bm-sql-inspector --table-name=users

Filter by Column Name

npx @basemachina/bm-sql-inspector --column-name=id

Multiple Filters (AND Condition)

npx @basemachina/bm-sql-inspector \
  --datasource-type=mysql \
  --table-name=products \
  --column-name=category

Display Help

npx @basemachina/bm-sql-inspector --help
# or
npx @basemachina/bm-sql-inspector -h

Options

| Option | Description | Default | |--------|-------------|---------| | --action-settings-file <path> | Path to action_settings.json file | ./action_settings.json | | --datasource-type <type> | Filter by datasource type (mysql or postgresql) | - | | --data-source-name <name> | Filter by data source name (resourceID) | - | | --table-name <name> | Filter by table name | - | | --column-name <name> | Filter by column name | - | | --help, -h | Display help message | - |

Output Format

Results are output in JSON format to stdout.

{
  "totalActions": 4,
  "totalSkipped": 1,
  "actions": [
    {
      "id": "test-mysql-001",
      "name": "Product Search",
      "class": "mysql",
      "resourceID": "resource-001",
      "statements": [
        {
          "statement": "SELECT * FROM products WHERE category = {{ category }} AND price >= {{ min_price }}",
          "method": "QUERY",
          "parametersUsed": ["category", "min_price"]
        }
      ],
      "parameters": [
        {"name": "category", "inputType": "TEXT"},
        {"name": "min_price", "inputType": "NUMBER"}
      ],
      "tablesUsed": ["products"],
      "columnsUsed": ["category", "price"]
    }
  ]
}

Development

Prerequisites

• Node.js 18 or later
• npm

Setup

# Install dependencies
npm install

# Link globally for development (no build required!)
npm link

# Now you can run the command directly
bm-sql-inspector --action-settings-file=test/fixtures/sample_actions.json

# Or use npm run dev
npm run dev -- --action-settings-file=test/fixtures/sample_actions.json

# Run tests
npm test

# Build TypeScript (optional, for distribution)
npm run build

Testing

This project is developed using Test-Driven Development (TDD) with Vitest.

# Run all tests
npm test

# Run specific test file
npm test -- types.test.ts

# Generate coverage report
npm test -- --coverage

Test Coverage:

• Unit tests: 49 tests
• Integration tests: 12 tests
• E2E tests: 13 tests
• Total: 74 tests

Project Structure

bm-sql-inspector/
├── src/
│   ├── types.ts              # Type definitions
│   ├── fileLoader.ts         # File loading
│   ├── sqlActionExtractor.ts # SQL extraction & filtering
│   ├── outputFormatter.ts    # Output formatting
│   ├── cli.ts                # CLI interface
│   ├── index.ts              # Entry point
│   ├── *.test.ts             # Unit tests
│   ├── integration.test.ts   # Integration tests
│   └── e2e.test.ts           # E2E tests
├── test/
│   └── fixtures/
│       └── sample_actions.json
├── dist/                     # Build output
├── package.json
├── tsconfig.json
└── vitest.config.ts

Publishing to npm

To make this package available via npx, publish it to npm:

# 1. Login to npm (first time only)
npm login

# 2. Update version in package.json
npm version patch  # or minor, or major

# 3. Publish to npm (runs build and tests automatically)
npm publish

After publishing, anyone can use it with:

npx @basemachina/bm-sql-inspector --action-settings-file=action_settings.json

License

MIT

Author

BaseMachina