Awesome Open Source
Awesome Open Source

PostgREST Starter Kit

Base project and tooling for authoring REST API backends with PostgREST.

PostgREST Starter Kit


PostgREST enables a different way of building data driven API backends. It does "one thing well" and that is to provide you with a REST api over your database, however to build a complex production system that does things like talk to 3rd party systems, sends emails, implements real time updates for browsers, write integration tests, implement authentication, you need additional components. For this reason, some developers either submit feature requests that are not the scope of PostgREST or think of it just as a prototyping utility and not a powerful/flexible production component with excellent performance. This repository aims to be a starting point for all PostgREST based projects and bring all components together under a well defined structure. We also provide tooling that will aid you with iterating on your project and tools/scripts to enable a build pipeline to push everything to production. There are quite a few components in the stack but you can safely comment out pg_amqp_bridge/rabbitmq (or even openresty) instances in docker-compose.yml if you don't need the features/functionality they provide.

PostgREST+ as a service

Run your PostgREST instance in subZero cloud and get additional features to the OS version ( free plan available).

Alternatively, deploy an enhanced version of PostgREST on your infrastructure using binary and docker distributions.

Fully Managed — subZero automates every part of setup, running and scaling of PostgREST. Let your team focus on what they do best - building your product. Leave PostgREST management and monitoring to the experts.
Faster Queries — Run an enhanced PostgREST version that uses prepared statements instead of inline queries. This results in up to 30% faster response times.
Custom Relations — Define custom relations when automatic detection does not work. This allows you to use the powerful embedding feature even with the most complicated views
GraphQL API — In addition to the REST API you get a GraphQL api with no additional coding. Leverage all the powerful tooling, libraries and integrations for GraphQL in your frontend.
Preconfigured Authentication — Authenticate users with local email/password or using 3rd party OAuth 2.0 providers (google/facebook/github preconfigured)
Analytical queries — Use GROUP BY, aggregate and window functions in your queries


✓ Cross-platform development on macOS, Windows or Linux inside Docker
PostgreSQL database schema boilerplate with authentication and authorization flow
OpenResty configuration files for the reverse proxy
RabbitMQ integration through pg-amqp-bridge
Lua functions to hook into each stage of the HTTP request and add custom logic (integrate 3rd party systems)
✓ Debugging and live code reloading (sql/configs/lua) functionality using subzero-cli
✓ Full migration management (migration files are automatically created) through subzero-cli/sqitch/apgdiff
✓ SQL unit test using pgTAP
✓ Integration tests with SuperTest / Mocha
✓ Docker files for building production images
✓ Community support on Slack
✓ Compatible with subZero Starter Kit if you need a GraphQL API and a few more features with no additional work

Directory Layout

├── db                        # Database schema source files and tests
│   └── src                   # Schema definition
│       ├── api               # Api entities avaiable as REST endpoints
│       ├── data              # Definition of source tables that hold the data
│       ├── libs              # A collection modules of used throughout the code
│       ├── authorization     # Application level roles and their privileges
│       ├── sample_data       # A few sample rows
│       └── init.sql          # Schema definition entry point
├── openresty                 # Reverse proxy configurations and Lua code
│   ├── lua                   # Application Lua code
│   ├── nginx                 # Nginx configuration files
│   ├── html                  # Static frontend files
│   └── Dockerfile            # Dockerfile definition for building production images
├── tests                     # Tests for all the components
│   ├── db                    # pgTap tests for the db
│   └── rest                  # REST interface tests
├── docker-compose.yml        # Defines Docker services, networks and volumes
└── .env                      # Project configurations



Create a New Project

Click [Use this template] (green) button. Choose the name of your new repository, description and public/private state then click [Create repository from template] button. Check out the step by step guide if you encounter any problems.

After this, clone the newly created repository to your computer. In the root folder of application, run the docker-compose command

docker-compose up -d

The API server will become available at the following endpoints:

Try a simple request

curl http://localhost:8080/rest/todos?select=id,todo

Development workflow and debugging

Execute subzero dashboard in the root of your project.
After this step you can view the logs of all the stack components (SQL queries will also be logged) and if you edit a sql/conf/lua file in your project, the changes will immediately be applied.


The starter kit comes with a testing infrastructure setup. You can write pgTAP tests that run directly in your database, useful for testing the logic that resides in your database (user privileges, Row Level Security, stored procedures). Integration tests are written in JavaScript.

Here is how you run them

npm install                     # Install test dependencies
npm test                        # Run all tests (db, rest)
npm run test_db                 # Run pgTAP tests
npm run test_rest               # Run integration tests


Anyone and everyone is welcome to contribute.

Support and Documentation


Copyright © 2017-present subZero Cloud, LLC.
This source code is licensed under MIT license
The documentation to the project is licensed under the CC BY-SA 4.0 license.

Related Awesome Lists
Top Programming Languages
Top Projects

Get A Weekly Email With Trending Projects For These Topics
No Spam. Unsubscribe easily at any time.
Docker (98,195
Rest (29,239
Postgresql (23,961
Rest Api (16,874
Graphql (15,981
Kit (10,208
Rabbitmq (6,290
Starter Kit (4,021
Plpgsql (2,121
Openresty (933
Postgrest (57
Automatic Api (48