SQL-first PostgreSQL tooling

Write SQL.
Ship confidence.

pGenie validates SQL, manages indexes, and generates type-safe client SDKs
— all derived from the migrations and queries in plain SQL.

Get started View demo
Validates SQL
Checks correctness and compatibility against your real schema.
Manages indexes
Generates migrations adding missing indexes and dropping the redundant ones.
Generates SDKs
Generates type-safe client APIs in Haskell (Rust & Java coming soon).
Extends
Via custom generators as part of a decentralised ecosystem.
Workflow

How it works

Three steps from plain SQL to a typed, production-ready API.

Write migrations in the migrations/ folder
1
Write migrations

Author schema changes in plain PostgreSQL SQL, stored in migrations/. No DSL. No ORM abstractions.

migrations/
Write queries in the queries/ folder
2
Write queries

Place your SQL queries in queries/. pGenie analyzes each query against the schema built by your migrations.

queries/
Generate with pgn generate
3
Generate

Run one command to produce a typed client API, index migrations, and validation reports — all reproducible in CI.

pgn generate
✨
Result: a clean, typed API derived from the database you actually run

No manual parameter wiring. No guessed nullability. No decoder boilerplate. Just a correct-by-construction client API you can call directly from application code — with types inferred from your real PostgreSQL schema.

Use cases

Built for real-world SQL workflows

From preventing production incidents to accelerating AI-assisted development.

🏗️
Generate your data access layer from SQL

Keep hand-written SQL as the source of truth, but stop wiring parameters and decoders manually.

  • Generate a typed function per query
  • Get typed parameters and typed result rows
  • Keep SQL readable and reviewable, not buried in code
🛡️
Prevent shipping migrations that break production

Migrations and queries evolve together — until they don't. pGenie applies migrations and analyzes queries in the resulting schema, so you catch breaking changes in CI instead of after deploy.

🤖
Make AI-generated SQL safe to ship

LLMs are great at drafting SQL. pGenie is how you verify it. Use an LLM to propose a query, migration, or refactor — then run pGenie to prove it against the real schema.

🚦
CI/CD guardrail for database changes

Add pGenie to your pipeline as a quality gate.

  • Fail PRs that introduce invalid or unsafe queries
  • Enforce that generated artifacts match committed SQL
  • Ensure "works on my machine" becomes "works in CI"
🔬
Catch SQL issues at generation time

Stop discovering bugs at runtime or in production. Move failures to generation time — wrong types, nullability, result shapes, performance issues (sequential scans), and schema mismatches are caught before code ships.

💡
SQL-first workflow for teams that know better than ORMs

If your team prefers SQL for correctness, performance, and transparency, pGenie adds the missing pieces.

  • Strong typing across the entire stack
  • Consistency checks between schema and queries
  • Automated client API generation
In the age of LLMs

AI writes. pGenie verifies.

LLMs are excellent authors. They are not a proof system.

LLM-only
Impressive output, costly failures
  • ✗
    Non-deterministic — the same prompt can produce different SQL across runs.
  • ✗
    Hard to reproduce — model, version, prompt, and temperature become build inputs.
  • ✗
    Unverifiable assumptions — column types, nullability, and result shapes can be subtly wrong.
  • ✗
    Schema drift — a later migration can silently invalidate earlier AI-generated queries.
  • ✗
    Operational risk — errors surface at runtime or during deploy, not at PR time.
pGenie
Deterministic verification against reality
  • ✓
    Verified against a real schema — migrations execute; types are inferred from the actual DB state.
  • ✓
    Reproducible builds — generator inputs can be frozen and artifacts regenerated identically in CI.
  • ✓
    Correct-by-construction APIs — typed parameters and results derived from analysis, not guesses.
  • ✓
    Regression detection — if a migration breaks a query, generation fails early in the PR.
  • ✓
    Fully auditable — outputs are a deterministic function of migrations, queries, generator versions, and config.

Best practice: use both

Use LLMs to explore and draft new migrations, queries, and rewrites.
Then use pGenie as the gate before anything ships.

Generate with AI.  Verify with pGenie.  Ship with confidence.

Start using pGenie today

Install from source
pGenie is open source
Browse the code, open issues, and follow the project on GitHub
Star on GitHub