pg-agent

AI-native PostgreSQL deployment validation CLI — powered by Google Gemini

pg-agent is a CLI tool designed to eliminate "deployment anxiety" for PostgreSQL developers. By leveraging LLMs (Large Language Models), it automatically generates comprehensive pgTAP unit tests for your migration scripts, executes them in an isolated Docker sandbox, and even self-heals failing tests by analyzing root causes. It ensures your schema changes are correct, your triggers fire as expected, and your functions handle edge cases before they ever touch production.

🚀 Quick Demo

$ pg-agent validate migration_v1.sql

🔍 Parsing SQL... Found 3 objects.
🏗 Building context... 
✨ Generating pgTAP tests with Gemini... Done (12 assertions).
🐳 Starting sandbox... Container started on port 5450.
🚀 Running deployment... ✅ All 3 statements executed successfully (45ms).
🧪 Running tests... ❌ 10/12 tests passed (112ms).

🔄 Healing attempt 1/3...
📊 Healing Analysis:
┌────────┬────────────────────┬────────────────────────────────────────┐
│ Test # │ Category           │ Root Cause                             │
├────────┼────────────────────┼────────────────────────────────────────┤
│ 7      │ missing_fixture    │ calculate_order_total needs an order.  │
└────────┴────────────────────┴────────────────────────────────────────┘

✅ All tests healed after 1 attempt.
✅ PASSED
12 assertions → migration_v1.test.sql

🛠 Requirements

• Node.js: 22 or higher
• Docker: Must be running locally (used for sandbox isolation)
• pg_prove: Part of the pgTAP toolset.
  • macOS: brew install pgformatter (includes dependencies) or cpan TAP::Parser::SourceHandler::pgTAP
  • Ubuntu: sudo apt install libtap-parser-sourcehandler-pgtap-perl
• Google Gemini API Key: Get one for free at Google AI Studio

📦 Installation

npm install -g pg-agent

⏱ 60-Second Quickstart

Step 1: Configure

pg-agent config set

Follow the prompts to select Gemini as your provider and paste your API key.

Step 2: Create a Deployment File

Create deployment.sql:

CREATE TABLE users (id UUID PRIMARY KEY, email TEXT UNIQUE);
CREATE OR REPLACE FUNCTION get_user_count() RETURNS INT AS 'SELECT count(*)::int FROM users' LANGUAGE sql;

Step 3: Validate

pg-agent validate deployment.sql

Step 4: Review Results

pg-agent will generate deployment.test.sql, spin up a sandbox, deploy your SQL, run the tests, and report back.

⌨ Commands Overview

| Command | What it does | When to use it | | :--- | :--- | :--- | | pg-agent chat | Interactive AI chat | When you need help debugging or writing SQL | | pg-agent validate | Full pipeline (Gen → Test → Heal) | Before committing a migration or PR | | pg-agent generate | Create pgTAP tests only | When you want to review/edit tests manually | | pg-agent analyze | Parse SQL & dependency graph | To check deployment order or find missing refs | | pg-agent run | Execute tests in sandbox | When you have existing test files | | pg-agent config | Manage settings | Initial setup or switching models |

📖 Full Documentation

• Command Reference
• Configuration Reference
• Technical Architecture
• pgTAP Testing Guide
• CI/CD Integration Guide
• Troubleshooting

⚙ How it works

deployment.sql 
      ↓
  [ Parser ] → Dependency Graph & Object Detection
      ↓
  [ Gemini ] → Context-aware pgTAP Test Generation
      ↓
[ Docker Sandbox ] ← pgTAP Extension & psql
      ↓
  [ pg_prove ] → TAP Output Analysis
      ↓
  [ Healer ] → AI Failure Analysis & Automatic Repair
      ↓
   [ Report ] → Final Status & Saved Test Files

🤝 Contributing

We welcome contributions! Please see our Contributor Guide for setup instructions and project conventions.

📄 License

MIT © 2026 SivanSakthivel