Metadata-Version: 2.4
Name: db-guardrail
Version: 0.1.2
Summary: A PostgreSQL migration safety analyzer
Author: Qudsiya Siddique
License-Expression: MIT
Project-URL: Homepage, https://github.com/Qudsiya954/Db-Gaurdrail
Project-URL: Repository, https://github.com/Qudsiya954/Db-Gaurdrail
Project-URL: Issues, https://github.com/Qudsiya954/Db-Gaurdrail/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

### PostgreSQL Migration Safety & Performance Analyzer

DB-Guardrail is an open-source Python package and CLI tool that analyzes PostgreSQL SQL migrations before they are executed.

It uses PostgreSQL execution plans, transactions, and system catalogs to identify potential performance and migration risks without permanently changing the database.

---

## Why DB-Guardrail?

SQL migrations can introduce issues such as:

- Sequential scans on large tables
- High estimated planner costs
- Aggressive database locks
- Foreign-key dependencies

DB-Guardrail analyzes these risks before deployment and produces a structured, deterministic report.

---

## Installation

Install the published Python package:

```bash
python -m pip install db-guardrail
```

Verify the installation:

```bash
db-guardrail --help
```

---

## Requirements

- Python 3.11+
- PostgreSQL database

DB-Guardrail requires an external PostgreSQL database because it uses PostgreSQL-specific features such as `EXPLAIN (FORMAT JSON)`, `pg_class`, `pg_locks`, and `pg_constraint`.

---

## Configuration

Set your PostgreSQL connection URL.

### PowerShell

```powershell
$env:DB_GUARDRAIL_DATABASE_URL = "postgresql://postgres:password@localhost:5432/your_database"
```

### Windows CMD

```cmd
set DB_GUARDRAIL_DATABASE_URL=postgresql://postgres:password@localhost:5432/your_database
```

Use URL-encoded credentials when necessary. For example, `@` in a password must be written as `%40`.

---

## Quick Start

Initialize the optional analysis-history tables:

```bash
db-guardrail init-db
```

Analyze a migration without saving the result:

```bash
db-guardrail analyze migration.sql --no-save
```

Analyze and save the result:

```bash
db-guardrail analyze migration.sql
```

View previous analysis results:

```bash
db-guardrail history
```

---

## How It Works

```text
SQL Migration
     ↓
Statement Splitting
     ↓
Statement Classification
     ↓
PostgreSQL Analysis
     ↓
Risk Analysis
     ↓
Structured Report
```

### Query Statements

For query statements such as:

```text
SELECT
INSERT
UPDATE
DELETE
```

DB-Guardrail uses:

```sql
EXPLAIN (FORMAT JSON)
```

The resulting execution plan is recursively parsed to identify:

- Sequential Scan
- Index Scan
- Bitmap Heap Scan
- Sort
- Estimated rows
- Planner cost

### Schema Changes

For schema statements such as:

```text
CREATE
ALTER
DROP
TRUNCATE
```

DB-Guardrail uses a transaction sandbox:

```sql
BEGIN;

-- migration statement

ROLLBACK;
```

It also inspects PostgreSQL metadata including:

- `pg_locks` for lock information
- `pg_constraint` for schema dependencies
- `pg_class` for table statistics

The transaction is rolled back after analysis, so schema changes are not permanently applied.

---

## Example

The repository includes example SQL files:

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

Example finding:

```text
Migration Status: UNSAFE

[CRITICAL] PERF_SEQ_SCAN

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.
```

---

## CLI Commands

| Command | Description |
| --- | --- |
| `db-guardrail analyze <file>` | Analyze a SQL migration |
| `db-guardrail init-db` | Initialize history tables |
| `db-guardrail history` | View saved analysis history |

Useful options:

```bash
db-guardrail analyze migration.sql --no-save
db-guardrail analyze migration.sql --show-sql
db-guardrail analyze migration.sql --fail-on-unsafe
```

---

## Docker PostgreSQL

For local development, the GitHub repository includes a Docker Compose PostgreSQL setup:

```bash
docker compose up -d
```

Then configure the database connection:

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

---

## Design Principles

**PostgreSQL-aware**  
Uses PostgreSQL planner output and system catalogs directly.

**Deterministic**  
The same database state and execution-plan information produce consistent findings.

**Explainable**  
Findings are based on PostgreSQL metadata and planner output rather than opaque predictions.

---

## Current Scope

DB-Guardrail focuses on analysis before execution. It does not:

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

---

## Open Source

DB-Guardrail is open source and released under the MIT License.

Contributions, issues, and suggestions are welcome.
