Launching an MVP is the only way to get real user feedback on your startup. Rather than build out a custom backend, PostgREST exposes your Postgres DB tables as a REST API. Put those first months of engineering focus into the frontend and unique business logic - save the custom backend for when you need to scale.

Using PostgREST is an alternative to manual CRUD programming. Custom API servers suffer problems. Writing business logic often duplicates, ignores or hobbles database structure. Object-relational mapping is a leaky abstraction leading to slow imperative code. The PostgREST philosophy establishes a single declarative source of truth: the data itself. - https://postgrest.org/

AWS Fargate is an ideal compute platform for PostgREST. Being serverless, it is very efficient in terms of cost and operations. Yet Fargate containers are relatively long-lived compared to AWS Lambda - this allows PostgREST's caching mechanisms to remain effective.

Prerequisites

  • You'll need an AWS account and its associated access/secret keys.
  • Install the AWS CLI [directions here], and configure your AWS keys [directions here].
  • The Fargate CLI is the easiest way to deploy a container on AWS. You'll want to install it [download here].
  • A Postgres database. Your Fargate task(s) will need network access to this database. If you are using RDS, be sure to specify subnets and security groups for your load balancer and tasks (see below) so they get deployed in the same VPC as your database.

Ship it

1. Setup a test DB, API, role, and table in Postgres:

Connect to Postgres, then create a database for this exercise:

postgres=> create database startup;
CREATE DATABASE
postgres=> \c startup;
psql ...
You are now connected to database "startup" as user "root".
startup=>

PostgREST uses a "naked schema" to identify which DB tables should be exposed in the API. We can create one, and add a sample table:

startup=> create schema api;
CREATE SCHEMA

startup=> create table api.trees (id serial primary key, name text not null, species text not null);
CREATE TABLE

startup=> insert into api.trees (name, species) values ('Banyan', 'Ficus benghalensis'), ('Quaking Aspen', 'Populus tremula'), ('American Elm', 'Ulmus americana'), ('Red Maple', 'Acer rubrum');
INSERT 0 4

Finally, we need to set up two roles. The first controls anonymous access with Postgres' standard grants and (optionally) row-level security. PostgREST uses the authenticator role to connect to the database. Once connected, PostgREST assumes the web_anon role. Please pick a better password than 'secret1'.

create role web_anon nologin;

grant usage on schema api to web_anon;
grant select on api.trees to web_anon;

create role authenticator noinherit login password 'secret1';
grant web_anon to authenticator;

This structure ensures you are not using the root user for PostgREST connections and makes it easy to add JWT-authenticated roles in the future.

A more detailed PostgREST setup tutorial is available here: https://postgrest.org/en/v7.0.0/tutorials/tut0.html

2. Deploy an Application Load Balancer to route traffic to your Fargate task(s):

fargate lb create postgrest --port HTTP:80
 ℹ️  Created load balancer postgrest

Let's break down this command:

  • fargate lb create postgrest: create an ALB named postgrest.
  • --port HTTP:80: the load balancer will accept internet requests on port 80.
  • If you've deleted your default VPC, or wish to use a different VPC, be sure to specify the appropriate subnets (this flag can be used multiple times):
    --subnet-id subnet-0ae7XXX
  • You can also optionally specify multiple security groups with the --security-group-id sg-02ddXXX flag.

3. Deploy a PostgREST task:

This command will deploy the PostgREST image to a Fargate task. It automatically creates a new ECS cluster and a default IAM role for task execution.

fargate service create postgrest \
  --lb postgrest --port HTTP:3000 \
  --image 'registry.hub.docker.com/postgrest/postgrest' \
  --num 1 \
  --env PGRST_DB_URI='postgres://authenticator:secret1@HOST/startup' \
  --env PGRST_DB_SCHEMA='api' \
  --env PGRST_DB_ANON_ROLE='web_anon'
  ℹ️ Created service postgrest

A quick review of the flags we used:

  • fargate service create postgrest: create a postgrest Fargate service in a new ECS cluster.
  • --lb postgrest --port HTTP:3000: connect this service to your load balancer.
  • --image 'registry.hub.docker.com/postgrest/postgrest': deploy the postgrest image from dockerhub.
  • --env PGRST_DB_URI='postgres://authenticator:secret1@HOST/startup': set URI for access to your database. Note this is the username/password/db you setup in step 1.
  • --env PGRST_DB_SCHEMA='api': name the API you created in step 1.
  • --env PGRST_DB_ANON_ROLE='web_anon': name the role you created in step 1.

4. Test

If things go as planned, you'll be able to find your load balancer hostname with:

$ fargate lb list | grep postgrest
postgrest	Application	Active	
postgrest-132447124.us-east-1.elb.amazonaws.com	HTTP:80

Now, you can query your new API:

curl postgrest-132447124.us-east-1.elb.amazonaws.com/trees
[{"id":1,"name":"Banyan","species":"Ficus benghalensis"},
 {"id":2,"name":"Quaking Aspen","species":"Populus tremula"},
 {"id":3,"name":"American Elm","species":"Ulmus americana"},
 {"id":4,"name":"Red Maple","species":"Acer rubrum"}]

Cost

AWS's "free tier" doesn't include Fargate, so here’s the bottom line: it will cost you $2.83 to run PostgREST on a Fargate for one month, while easily serving 500 requests/second. This is with the smallest single instance available - there is tons of room to scale both vertically and horizontally with this architecture.

Caution: the load balancer costs are more significant (~$16+/mo), but they are included in the "free tier" and can be shared between many Fargate (and Lambda/EC2) services.

Next Steps

  • Looking for more than a read-only API? You can enable authenticated access to read and write private information in your database. PostgREST supports JWT authentication, which maps to Postgres roles you configure. You can even enable row-level security. Tutorial is here, with further details here.
  • Please enable TLS (SSL) on your load balancer, and require encrypted connections to PostgREST. We don't want your credentials leaking!
  • In production, you might use an infrastructure-as-code tool like Terraform or CDK to deploy your Fargate tasks. These tools offer more control and customization than the Fargate CLI.
  • Want more content like this? Follow me on Twitter at @nedmcclain. Struggling to get PostgREST deployed on Fargate? DM me!

Thanks Nalin!

Hat tip to my dear friend @nalin, for encouraging me to write this post!

Cover photo by Kevin Bree on Unsplash.