PostGraphile — Instant GraphQL API from PostgreSQL

Introduction

PostGraphile (part of the Graphile suite) introspects your PostgreSQL database and generates a full GraphQL API in seconds. Tables become types, columns become fields, foreign keys become relationships, and functions become custom queries or mutations. You control access via PostgreSQL's built-in role-based security.

What PostGraphile Does

• Introspects PostgreSQL schemas to auto-generate GraphQL types, queries, and mutations
• Maps foreign keys to nested relationships with automatic join optimization
• Exposes PostgreSQL functions as custom GraphQL fields or mutations
• Uses PostgreSQL row-level security (RLS) and grants for authorization
• Supports real-time subscriptions via PostgreSQL LISTEN/NOTIFY

Architecture Overview

PostGraphile reads the pg_catalog system tables to build a complete model of your schema: tables, views, functions, constraints, and comments. It generates a GraphQL schema and uses Grafast, a planning-based execution engine, to translate GraphQL queries into optimized SQL. Instead of resolving fields one at a time, Grafast plans the entire query tree and issues minimal SQL queries with proper joins and batching.

Self-Hosting & Configuration

• Run as a CLI tool, a Node.js middleware (Express, Koa, Fastify), or a standalone server
• Configure via graphile.config.ts for presets, plugins, and schema options
• Use Smart Tags (database comments) to customize names, hide fields, or add constraints
• Enable watch mode for automatic schema rebuilds when you change database objects
• Deploy behind a reverse proxy with connection pooling via PgBouncer for production

Key Features

• Zero-resolver API generation that stays in sync with your database schema
• Grafast execution engine that plans and batches SQL for minimal round-trips
• Plugin system for extending the schema with custom types, fields, and logic
• PostgreSQL-native authorization via RLS policies and database roles
• Real-time subscriptions powered by PostgreSQL LISTEN/NOTIFY

Comparison with Similar Tools

• Hasura — similar auto-generated GraphQL from Postgres; PostGraphile is open-source Node.js with deeper PostgreSQL integration
• Prisma — code-first ORM with GraphQL support; PostGraphile is schema-first from the database
• Apollo Server — requires manually written resolvers; PostGraphile generates them automatically
• Supabase — provides REST and real-time APIs; PostGraphile focuses on GraphQL with advanced query planning
• Postgrest — generates REST APIs from PostgreSQL; PostGraphile generates GraphQL

FAQ

Q: Does PostGraphile support mutations? A: Yes. It auto-generates create, update, and delete mutations for every table, plus custom mutations from PostgreSQL functions.

Q: How does authorization work? A: PostGraphile uses PostgreSQL's native role-based access control and row-level security policies. Each request runs as a specific database role.

Q: Is PostGraphile fast enough for production? A: Yes. The Grafast engine produces highly optimized SQL and avoids the N+1 query problem entirely through query planning.

Q: Can I extend the generated schema? A: Yes. Use plugins or makeExtendSchemaPlugin to add custom types, fields, and resolvers alongside the auto-generated schema.

Sources

• https://github.com/graphile/crystal
• https://postgraphile.org