prest/prest
Prest
PostgREST is a tool that automatically transforms a PostgreSQL database schema into a production-ready RESTful API. It serves as a database access layer and query engine that maps HTTP requests directly to SQL queries, providing a low-code interface for executing create, read, update, and delete operations without requiring manual boilerplate code.
The project distinguishes itself by using schema-driven API generation and metadata-based discovery to expose database tables as navigable resources. It extends standard CRUD capabilities through the execution of custom and templated SQL, a plugin-based middleware system for injecting business logic, and the ability to load external shared libraries at runtime.
The system covers a broad range of capabilities including complex data querying with table joins, aggregations, and full-text search. It implements a comprehensive security framework featuring token-based authentication, granular table-level permissions, and CORS management. Additional operational features include local result caching, server health monitoring, and connectivity for distributed SQL and Amazon Redshift.
Installation is supported across multiple environments, including as a standalone binary or via Docker Compose and Heroku automation templates.
Features
- PostgreSQL Integrations - Enables seamless integration between external applications and PostgreSQL or Redshift databases.
- Database API Generators - Automatically transforms database schemas into functional REST HTTP endpoints without manual boilerplate code.
- Database-to-API Generators - Automatically transforms a PostgreSQL database schema into a production-ready RESTful API.
- CRUD Operations - Maps standard HTTP methods to execute create, read, update, and delete operations on database records.
- Query Parameter Filters - Translates URL query parameters into SQL WHERE clauses for server-side filtering, sorting, and pagination.
- Database Access Layers - Acts as a secure database access layer managing authentication, CORS, and fine-grained access control.
- Dynamic SQL Query Executions - Implements a query engine that translates HTTP parameters into dynamic SQL for complex data retrieval.
- Database Metadata Discovery - Automatically exposes database tables and schemas as API resources by inspecting the system catalog.
- CRUD Interfaces - Provides a high-performance interface for executing CRUD operations on PostgreSQL records via HTTP.
- Database Schema Inspection - Provides programmatic retrieval of database schemas and table structures to facilitate API discovery.
- SQL Translation Engines - Translates HTTP requests into complex SQL queries including joins, aggregations, and full-text searches.
- Low-code - Provides a low-code approach to backend development by mapping HTTP requests directly to database operations.
- Database REST APIs - Automatically generates a production-ready RESTful API based on the PostgreSQL database schema.
- API - Validates requests using tokens verified via secret keys, JWKS, or OpenID configurations to secure API endpoints.
- API Authentication - Manages programmatic access to database resources using tokens and credentials verified against PostgreSQL.
- Authorization Logic - Provides comprehensive authentication and authorization logic to secure API endpoints and restrict data access.
- Bearer Token Authentication - Implements a security layer that validates bearer tokens to authorize API requests.
- Granular Access Controls - Defines granular, endpoint-level permissions to limit read, write, and delete operations on a per-table and per-field basis.
- Table-Level Access Controls - Controls which specific database tables are exposed to the REST API using precise matching or regular expressions.
- Data Access Permission Configurators - Implements granular data access permissions using role-based access and row-level filters.
- Token Generation - Generates cryptographically secure tokens after verifying user credentials against the database.
- User-Based Access Restrictions - Restricts API access to authorized users by validating permissions against database records and token-based authentication.
- API Response Caching - Reduces server load and latency by caching API endpoint responses in a local store.
- Custom SQL Execution - Provides capabilities for running hand-written, parameterized SQL statements to handle complex data operations.
- JSONb Path Querying - Implements dynamic filtering and extraction of nested data stored in JSONB columns.
- SQL Templating Engines - Allows fetching and filtering data dynamically by running database queries through customizable URL templates.
- General Record Retrieval - Retrieves records using specific column filters, field selections, sorting, and pagination.
- Database Result Caching - Improves performance by caching raw database query results in a local file.
- Field Selection - Limits returned data to a specified list of columns to optimize API response payloads.
- Full Text Search - Supports natural-language full-text search using Boolean operators and language-specific tokenization.
- Group-By Aggregations - Summarizes information by performing group-by operations and aggregate functions over database views.
- Schema-less Extraction - Processes and retrieves values from schema-less JSON structures using targeted path queries.
- Pagination - Splits large result sets into smaller chunks by specifying page numbers and record limits.
- PostgreSQL-Compatible Databases - Supports connectivity with various PostgreSQL-compatible databases to serve as the API data source.
- Query Aggregates - Calculates sums, averages, and variances by grouping and selecting data points within a single request.
- Filtered and Ordered Queries - Narrows returned datasets by applying criteria, ordering, and distinct constraints via query string parameters.
- Nested Field Querying - Extracts and filters values stored within JSONb columns using targeted path queries.
- Table Joining Operations - Combines data from two tables in one request using specified join types and conditional operators.
- SQL Query Executions - Retrieves filtered or structured data by executing complex SQL queries directly via REST API endpoints.
- Database Access Controls - Enforces granular permission and role management for database objects to restrict how the API interacts with data.
- Security and Access Control - Implements SSL/TLS encryption and granular permissions to secure data operations and prevent unauthorized interactions.
- Plugin-Based Extensibility - Uses a plugin-based architecture to extend API functionality by loading external shared libraries.
- Runtime Library Extensions - Allows injecting custom business logic into the request pipeline by loading external shared libraries at runtime.
- Custom API Endpoints - Allows for the extension of the API surface area through custom routes and middleware.
- Route and Middleware Extensions - Supports the implementation of custom business logic via added routes and middleware extensions.
- API Generation - Serves RESTful APIs from various databases using Go.
- Data Utilities - Golang-based RESTful API server.
- Database Tools - Instant, real-time API for PostgreSQL.
- API Frameworks - Serves RESTful APIs from databases using Go.
- Developer Frameworks - A high-performance REST API generator for PostgreSQL.
- Server Frameworks - Go-based tool for serving RESTful APIs from PostgreSQL databases.
