Metadata-Version: 2.4
Name: db-guardrail
Version: 0.1.0
Summary: A PostgreSQL migration safety analyzer
Author: Qudsiya Siddique
License-Expression: MIT
Project-URL: Homepage, https://github.com/Qudsiya954/Db-Gaurdrail.git
Project-URL: Repository, https://github.com/Qudsiya954/Db-Gaurdrail.git
Project-URL: Issues, https://github.com/Qudsiya954/Db-Gaurdrail.git/issues
Keywords: postgresql,sql,migration,database,cli
Requires-Python: >=3.11
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: psycopg[binary]>=3.2.0
Requires-Dist: sqlparse>=0.5.0
Provides-Extra: dev
Requires-Dist: pytest>=8.0.0; extra == "dev"
Requires-Dist: build>=1.2.2; extra == "dev"
Requires-Dist: twine>=6.0.0; extra == "dev"
Dynamic: license-file

# DB-Guardrail

DB-Guardrail is a Python CLI tool for analyzing PostgreSQL SQL migrations before they are run against a database.

It executes migration statements inside a rollback-safe PostgreSQL transaction, reads planner output from `EXPLAIN (FORMAT JSON)`, inspects table scale and locks from PostgreSQL system catalogs, and produces deterministic risk reports.

No AI. No ML. No guessing. Every finding comes from PostgreSQL metadata or planner output.

## Why This Project Exists

Production SQL migrations can be risky. A migration may scan a large table, acquire an aggressive lock, or touch a table that is referenced by foreign keys.

DB-Guardrail helps answer questions like:

- Will this query use a sequential scan?
- Is PostgreSQL estimating a high execution cost?
- Is the table small enough that a sequential scan is acceptable?
- Does a schema change acquire an `AccessExclusiveLock`?
- Is the table involved in foreign key relationships?

## Features

- Multi-statement SQL migration analysis
- Transaction sandbox using `BEGIN` and `ROLLBACK`
- PostgreSQL `EXPLAIN (FORMAT JSON)` parser
- Recursive execution-plan flattening
- Rule-based performance analysis
- Table scale analysis using `pg_class.reltuples`
- Lock analysis using `pg_locks`
- Foreign key dependency analysis using PostgreSQL catalogs
- CLI commands for `analyze`, `init-db`, and `history`
- Optional PostgreSQL-backed report history
- Docker Compose setup for local PostgreSQL
- Unit tests for parser and rule logic

## Architecture

```mermaid
flowchart TD
    A["SQL migration file"] --> B["SQL splitter"]
    B --> C["Statement analyzer"]
    C --> D["EXPLAIN JSON analyzer"]
    C --> E["Lock analyzer"]
    C --> F["Dependency analyzer"]
    D --> G["Rule engine"]
    E --> H["Migration report"]
    F --> H
    G --> H
    H --> I["Console output"]
    H --> J["PostgreSQL JSONB history"]
```

## Project Structure

```text
src/db_guardrail/
  analysis/
    explain.py                 Runs EXPLAIN (FORMAT JSON)
    plan_parser.py             Converts PostgreSQL plan JSON into PlanNode objects
    rule_engine.py             Applies deterministic performance rules
    table_size.py              Reads estimated row counts from pg_class
    lock_analyzer.py           Reads current locks from pg_locks
    dependency_analyzer.py     Reads foreign key relationships from pg_constraint
    migration_analyzer.py      Coordinates multi-statement migration analysis
  database/
    connection.py              Opens psycopg connections
    schema.sql                 Bundled history-table schema for init-db
    history_repository.py      Saves analysis results
  reports/
    migration_console_report.py
  cli.py                       CLI entry point

examples/                      Example migrations
sql/                           Test-data setup
tests/                         Unit tests
```

## Requirements

- Python 3.11+
- An external PostgreSQL database (local PostgreSQL or Docker Compose)
- `psycopg3`
- `sqlparse`

PostgreSQL is not bundled inside the Python package because DB-Guardrail uses
PostgreSQL-specific functionality such as `EXPLAIN (FORMAT JSON)`, `pg_class`,
`pg_locks`, and `pg_constraint`.

## Install From Source

Create and activate a virtual environment:

```powershell
python -m venv .venv
.venv\Scripts\Activate.ps1
```

Install the CLI:

```powershell
python -m pip install .
```

For contributor tooling, including tests and package-build checks:

```powershell
python -m pip install -e ".[dev]"
```

## PostgreSQL With Docker Compose

Start PostgreSQL:

```powershell
docker compose up -d
```

If your Docker installation uses the older Compose command:

```powershell
docker-compose up -d
```

Set the database URL:

```powershell
$env:DB_GUARDRAIL_DATABASE_URL = "postgresql://postgres:postgres@localhost:55432/db_guardrail_dev"
```

If you use CMD instead of PowerShell:

```cmd
set DB_GUARDRAIL_DATABASE_URL=postgresql://postgres:postgres@localhost:55432/db_guardrail_dev
```

If your password contains `@`, encode it as `%40` inside the URL.

## Initialize Database Tables

DB-Guardrail can save analysis history in PostgreSQL:

```powershell
db-guardrail init-db
```

The command uses the schema bundled with the installed package.

This creates:

- `checked_scripts`
- `performance_metrics`
- `lint_reports`

## Load Demo Data

```powershell
psql -d "postgresql://postgres:postgres@localhost:55432/db_guardrail_dev" -f sql/test_data.sql
psql -d "postgresql://postgres:postgres@localhost:55432/db_guardrail_dev" -f sql/dependency_test_data.sql
```

## Usage

Analyze a migration without saving:

```powershell
db-guardrail analyze examples/large_table_seq_scan.sql --no-save
```

Analyze and save the result:

```powershell
db-guardrail analyze examples/multi_statement_migration.sql
```

Show SQL before analysis:

```powershell
db-guardrail analyze examples/multi_statement_migration.sql --show-sql --no-save
```

Fail with exit code `1` when the migration is unsafe or has errors:

```powershell
db-guardrail analyze examples/large_table_seq_scan.sql --fail-on-unsafe
```

Show saved analysis history:

```powershell
db-guardrail history
```

## Example Output

```text
DB-Guardrail
============
Loaded SQL file: examples\large_table_seq_scan.sql
Migration Status: UNSAFE

Statement 1
-----------
Type: SELECT
Status: UNSAFE
SQL: SELECT * FROM large_orders WHERE status = 'paid'

Execution Plan:
- Seq Scan on large_orders | cost=3029.0 | rows=49605

Issues:
[CRITICAL] PERF_SEQ_SCAN
Message: Sequential Scan detected on large table large_orders.
Estimated table rows: 150000
Table size: large
Recommendation: Consider adding an index for the filtered column before running this query.
```

## Example Migrations

| File | Purpose |
| --- | --- |
| `examples/small_table_seq_scan.sql` | Shows an informational sequential scan on a small table |
| `examples/indexed_lookup.sql` | Shows PostgreSQL using an index-backed plan |
| `examples/large_table_seq_scan.sql` | Shows a critical sequential scan on a large table |
| `examples/high_cost_sort.sql` | Shows high estimated sort cost |
| `examples/multi_statement_migration.sql` | Shows per-statement reporting and error capture |
| `examples/lock_migration.sql` | Shows lock severity detection |
| `examples/dependency_migration.sql` | Shows foreign key dependency detection |

## Testing

Run unit tests:

```powershell
python -m pytest
```

The tests cover:

- SQL statement splitting
- Plan JSON parsing
- Table name extraction
- Rule severity decisions
- Migration status aggregation

## Interview Talking Points

- The tool uses PostgreSQL planner output as the source of truth.
- The analyzer is deterministic and rule-based.
- `ROLLBACK` protects the database after sandboxed execution.
- `pg_class`, `pg_locks`, and `pg_constraint` demonstrate PostgreSQL internals knowledge.
- Multi-statement analysis makes the project realistic for migration files.
- JSONB history storage gives the CLI a backend-style persistence layer without requiring a web app.

## Current Scope

DB-Guardrail intentionally does not:

- Rewrite SQL automatically
- Create indexes automatically
- Predict deadlocks
- Simulate production traffic
- Use machine learning or AI

The goal is explainable migration risk analysis, not automatic optimization.
