Getting started with PostGraphile

This is a quick-start tutorial for getting set up for development with a thick database architecture.

What we’ll learn:

  1. Get PostGraphile running on top of a PostgreSQL database on your computer
  2. Set up a simple data schema along with a read-only view schema
  3. Expose the view schema as GraphQL through PostGraphile

Our grand achievement will be posting GraphQL queries using curl and the GraphiQL web UI.

Our Running Example

Starting with this post and throughout the (hopefully) many posts to follow, I aim to build a usable e-commerce platform. There are several reasons for this choice.

First, it’s a non-trivial usecase, which will provide ample opportunity to explore various thick database techniques. Second, it could result in something actually useful, and that prospect will help keep me motivated.

And third, this is a domain I’m fairly familiar with. I have been working with the Solidus e-commerce platform for 5+ years, and more recently I’ve been running my own web shop (dedicated to high-quality Swedish craft honey) on Shopify. Along the way, I’ve come to the conclusion that there aren’t enough really good open source e-commerce platforms around. I don’t presume to become the creator of the next big thing in e-commerce, but if I can at least create something that I feel productive working in, I will consider it a success!

Tech Overview

Here is an extremely brief summary of the technologies involved in this post:

  • PostgreSQL (aka Postgres) - the world’s favorite relational database, providing most of the value in your SaaS startup
  • GraphQL - a flexible, typed query language for APIs
  • PostGraphile - a GraphQL server which translates a PostgreSQL schema into a GraphQL schema and exposes it to clients

Prerequisites

I will provide two different ways to stand up this stack: a) Docker Compose, b) nix-shell. You need to have a recent version of either Docker or the Nix package manager installed. Explaining how to use these tools is beyond the scope of this tutorial, but the project’s README contains some more details and I show the relevant commands below.

Let’s get this party started!

Even if you prefer to run the stack using Docker Compose, the nix shell configuration still contains some useful stuff (like the pgcli command), so you might want to use it as a complement to Docker Compose.

1. Postgres

First of all, we want to run a Postgres server—this will be the heart, brain, and body of our system.

TODO: Tabbed content

docker-compose.yml

version: '3.8'

services:
  db:
    image: postgres:14
    environment:
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_USER_PASSWORD}
      - POSTGRES_DB=${DB_NAME}
      - POSTGRAPHILE_USER=${POSTGRAPHILE_USER}
      - POSTGRAPHILE_USER_PASSWORD=${POSTGRAPHILE_USER_PASSWORD}
      - DB_ANON_ROLE=${DB_ANON_ROLE}
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./db/src:/docker-entrypoint-initdb.d