pGenie: SQL-First Tooling for PostgreSQL That Validates, Manages, and Generates Type-Safe APIs

In the evolving landscape of database tooling, pGenie emerges as a compelling solution for teams that prefer SQL's precision and transparency over ORM abstractions. This PostgreSQL-focused tool validates SQL queries, manages indexes, and generates type-safe client SDKs—all derived from the migrations and queries written in plain SQL.

The SQL-First Philosophy

At its core, pGenie embraces a SQL-first workflow that treats plain SQL as the source of truth. Rather than forcing developers to learn domain-specific languages or ORM abstractions, pGenie works directly with the SQL you already write. This approach resonates with teams who value SQL's correctness, performance characteristics, and readability.

The workflow is elegantly simple: write your schema changes in plain PostgreSQL SQL stored in a migrations/ directory, place your queries in a queries/ directory, and run a single command to produce a typed client API, index migrations, and validation reports. The result is a clean, typed API derived from the actual database you run, eliminating manual parameter wiring, guessed nullability, and decoder boilerplate.

Three-Step Workflow

1. Write Migrations

Schema changes are authored in plain PostgreSQL SQL, stored in the migrations/ directory. No DSL. No ORM abstractions. Just the SQL you'd write anyway, but now with the added benefit of systematic validation and type generation.

2. Write Queries

SQL queries live in the queries/ directory, where pGenie analyzes each query against the schema built by your migrations. This analysis catches issues early—before they reach production.

3. Generate

A single command produces a typed client API, index migrations, and validation reports. All outputs are reproducible in CI, creating a deterministic pipeline from SQL to production-ready code.

Built for Real-World Workflows

Generate Your Data Access Layer

Teams can keep hand-written SQL as the source of truth while eliminating the manual work of wiring parameters and decoders. pGenie generates a typed function per query, providing typed parameters and typed result rows while keeping SQL readable and reviewable.

Prevent Production Incidents

Migrations and queries evolve together in pGenie's workflow. By applying migrations and analyzing queries against the resulting schema, breaking changes are caught in CI rather than after deployment. This shift-left approach to database validation represents a significant improvement in reliability.

Make AI-Generated SQL Safe

As AI tools become increasingly capable at generating SQL, pGenie provides the verification layer needed to ship with confidence. The workflow becomes: use an LLM to propose a query or migration, then run pGenie to prove it against the real schema. This combination leverages AI's drafting capabilities while maintaining rigorous validation.

CI/CD Guardrail

Adding pGenie to your pipeline creates a quality gate that fails PRs introducing invalid or unsafe queries. It ensures generated artifacts match committed SQL and transforms "works on my machine" into "works in CI."

Catch Issues at Generation Time

Rather than discovering bugs at runtime or in production, pGenie moves failures to generation time. Wrong types, nullability issues, result shape mismatches, performance problems (like sequential scans), and schema mismatches are all caught before code ships.

The AI Verification Advantage

The tool's positioning around AI-generated SQL is particularly noteworthy. LLMs excel at drafting SQL but lack the deterministic verification capabilities that production systems require. pGenie addresses this gap by providing:

• Verification against a real schema: Migrations execute, and types are inferred from actual database 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
• Full auditability: Outputs are a deterministic function of migrations, queries, generator versions, and config

Best Practice: AI Drafts, pGenie Verifies

The recommended workflow combines the strengths of both approaches: use LLMs to explore and draft new migrations, queries, and rewrites, then use pGenie as the gate before anything ships. This "Generate with AI, Verify with pGenie, Ship with confidence" approach represents a pragmatic synthesis of emerging AI capabilities and established software engineering practices.

Extensibility and Ecosystem

pGenie extends via custom generators as part of a decentralized ecosystem. This design allows teams to adapt the tool to their specific needs while maintaining the core benefits of SQL-first development and rigorous validation.

Getting Started

pGenie is open source and available on GitHub, with installation guides for various platforms. The project invites developers to browse the code, open issues, and contribute to its development. For teams already invested in SQL and PostgreSQL, pGenie offers a compelling path to stronger typing, better validation, and more reliable database-driven applications.

In an era where database tooling often pushes developers toward increasingly abstract ORM layers, pGenie's commitment to SQL-first development while adding modern type safety and validation represents a thoughtful alternative that respects the expertise of database professionals while addressing the challenges of contemporary software development.

Get started with pGenie | View demo | Star on GitHub