a first look at postgraphile with railway

PostGraphile builds a GraphQL API from a PostgreSQL schema that automatically detects tables, columns, indexes, relationships, views, types, functions, and comments. It combines PostgreSQL's role-based grant system and row-level security policies with Graphile Engine's GraphQL look-ahead and plugin expansion technologies.

Outline

Provision a PostgreSQL database with Railway

Railway Dashboard
Railway CLI
Check Railway CLI version
Login with railway login
Initialize project with railway init
Provision PostgreSQL with railway add
Connect to database
Seed database
List tables in database
Describe table
Quit psql
Copy database connection string to clipboard

Introspect Database with PostGraphile

Introspect Railway Database
Test the endpoint
Connect to endpoint with ngrok

Provision a PostgreSQL database with Railway

There are two ways to setup a PostgreSQL database with Railway, through the dashboard or through the CLI.

Railway Dashboard

Click dev.new and choose "Provision PostgreSQL" After the database is setup click "PostgreSQL" on the left and then choose "Connect". Copy and paste the PostgreSQL client command.

Railway CLI

First you need to install the Railway CLI.

Check Railway CLI version

railway version

railway version 0.2.40

If you do not have a Railway account you will be prompted to create one.

railway login

Initialize project with railway init

Run the following command, select “Empty Project,” and give your project a name.

railway init

Provision PostgreSQL with railway add

Run the following command and select PostgreSQL to add a plugin to your Railway project.

railway add

Connect to database

railway connect postgresql

psql (13.3, server 13.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

railway=#

Seed database

Run the following SQL commands to create a test table with seed data.

CREATE TABLE Post (title text, body text);
INSERT INTO Post VALUES ('This is a blog post', 'Wooooooo');
INSERT INTO Post VALUES ('Another blog post', 'Even better than the other!');

List tables in database

\d

List of relations
 Schema | Name | Type  |  Owner   
-------------+------+-------+----------
 public | post | table | postgres
(1 row)

Describe table

\d post

Table "public.post"
 Column | Type | Collation | Nullable | Default 
-------------+------+-----------+----------+---------
 title  | text |           |          | 
 body   | text |           |          |

Quit psql

\q

Copy database connection string to clipboard

echo `railway variables get DATABASE_URL` | pbcopy

Introspect Database with PostGraphile

It is easy to install PostGraphile with npm, although the PostGraphile documentation does not recommend installing PostGraphile globally if you want to use plugins.

npm install -g postgraphile

If you do not globally install you will need to add npx the beginning of all postgraphile commands in this tutorial.

Introspect Railway Database

railway run postgraphile --watch --enhance-graphiql --dynamic-json --port 5001

Open localhost:5001/graphiql and send the following query.

Test the endpoint

curl \
  --request POST \
    --url "http://localhost:5001/graphql" \
    --header "Content-Type: application/json" \
    --data '{"query":"{ query { allPosts { totalCount nodes { body title } } } }"}'

{
  "data":{
    "query":{
      "allPosts":{
        "totalCount":2,
        "nodes":[
          {
            "body":"Wooooooo",
            "title":"This is a blog post"
          },
          {
            "body":"Even better than the other!",
            "title":"Another blog post"
          }
        ]
      }
    }
  }
}

Connect to endpoint with ngrok

./ngrok http 5001

Session Status                online
Account                       Anthony Campolo (Plan: Free)
Version                       2.3.40
Region                        United States (us)
Web Interface                 http://127.0.0.1:4040
Forwarding                    http://363ef1ef5cf3.ngrok.io -> http://localhost:5001
Forwarding                    https://363ef1ef5cf3.ngrok.io -> http://localhost:5001

Connections                   ttl     opn     rt1     rt5     p50     p90
                              2       0       0.00    0.00    5.11    5.21

Send the same query with your API tool of choice.