Automate your plain PostgreSQL to a GraphQL schema and custom useQuery() hooks

All of us who have been motivated to use GraphQL in their next application have reached the point of rewriting their database schema as a GraphQL schema.

When using Typescript, the GraphQL types are often rewritten as Typescript types as well.

This is often a high potential for errors and type inference, especially when changes are made to the database schema afterwards.

Recently I discovered this little setup that not only solves the pain-points of type inferences between GraphQL and Typescript, but also provides type safety for the query resolvers and converts native GraphQL query files directly into useQuery hooks.
All you have to create to use data from the database via these useQuery hooks in your frontend is:

• the initial SQL to create the database schema and insert data
• a docker-compose file, which provides the Postgres database and the Hasura engine
• a codegen file, which generates the typescript code through the Hasura schema

Step by Step:

Let's start with the basic setup:
-> Running a Postgres database in a docker container.

docker-compose.yml

version: '3.8'

services:
  postgres:
    build:
      context: ./database
      dockerfile: Dockerfile
    ports:
      - 5432:5432
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=booksdb 
    volumes:
      - db-data:/var/lib/postgresql/data
volumes:
  db-data:

This will bring up our first Docker container, which contains the Postgres database running on port 5432 and set the default database to booksdb as we use it for our example.
For a better structure I have outsourced the Dockerfile to a database folder:

./database/Dockerfile

# pull official base image
FROM postgres:12.2-alpine

# run init.sql
ADD init.sql /docker-entrypoint-initdb.d

We also copy our initial script to docker-entrypoint-initdb.d inside the container, so that it is executed directly when the container is started. Now our database container is ready and can be called via pgadmin.

For our example, we next create a NextJs application and install the following packages first:

npm install graphql @types/graphql apollo-server-micro @apollo/client knex pg

The next steps would be to create the GraphQL schema as well as the Typescript types, as we need them for our resolvers, but let's skip this for now.

Our structure now looks something like this:

-> Next, we add Hasura, which we also want to run in a Docker container.

To do this, we extend our docker-compose as follows:

docker-compose.yml

version: '3.8'

services:
  postgres:
    build:
      context: ./database
      dockerfile: Dockerfile
    ports:
      - 5432:5432
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=booksdb 
    volumes:
      - db-data:/var/lib/postgresql/data
  graphql-engine:
    image: hasura/graphql-engine:v1.3.2.cli-migrations-v2
    ports:
    - "8081:8080"
    depends_on:
    - "postgres"
    environment:
      HASURA_GRAPHQL_DATABASE_URL: postgres://postgres:postgres@postgres:5432/booksdb
      HASURA_GRAPHQL_ENABLE_CONSOLE: "true"
      HASURA_GRAPHQL_DEV_MODE: "true"
      HASURA_GRAPHQL_ENABLED_LOG_TYPES: startup, http-log, webhook-log, websocket-log, query-log
    volumes:
      - ./database/metadata:/hasura-metadata
volumes:
  db-data:

As a 2nd Docker container, we call Hasura here graphql-engine. As image we use the migration image, which I will explain in a second. The port should be other than 8080, because we may still need it for our application.
An important part of our workflow here is to add the postgres-url as HASURA_GRAPHQL_DATABASE_URL.

To start both containers now, all you need to do is run:

docker-compose up -d

After both containers are successfully started, the Hasura Console can be accessed at http://localhost:8081/console. Here you should see your tables from the Postgres database under untracked tables. After a click on select all these can be queried directly in the query tool of the Hasura console. The schema, which is used for this, is what we want to pass to the code generator in the next step.

In order not to always have to open up Hasura console to add new tables to the schema, a metadata file can be imported when starting the container. The import for that is already placed in our docker-compose file.

./database/metadata/tables.yaml

- table:
    schema: public
    name: author
  array_relationships:
  - name: book
    using:
      foreign_key_constraint_on:
        column: authorid
        table:
          schema: public
          name: book
- table:
    schema: public
    name: book
  object_relationships:
  - name: author
    using:
      foreign_key_constraint_on: authorid

-> Let's set up our code generator!

First we need to install the following packages:

npm install @graphql-codegen/cli @graphql-codegen/typescript @graphql-codegen/typescript-operations @graphql-codegen/typescript-react-apollo @graphql-codegen/typescript-resolvers

Next we set up our codegen.yml:

codegen.yml

overwrite: true
schema: 'http://localhost:8081/v1/graphql'
documents: 'src/graphql/**/*.graphql'
generates:
  src/generated/graphql-backend.ts: # Outputs the resolver type-safed
    plugins:
      - 'typescript'
      - 'typescript-resolvers'
    config:
      useIndexSignature: true
  src/generated/graphql-frontend.ts: # Outputs the type definitions for typescript as well as useQuery hooks for all query files
    plugins:
      - 'typescript'
      - 'typescript-operations'
      - 'typescript-react-apollo'
  src/generated/schema.graphql:
    plugins:
      - 'schema-ast'

We just need to specify our Hasura url as the schema and then we can specify which files we want to have generated. In this case we have specified 3, a graphql-backend.ts, a graphql-frontend.ts and the schema.graphql itself. Both generate the typescript types first. Then we generate an interface for the resolver in the backend-file and the useQuery hooks in the frontend using the typescript-react-apollo plugin. Here all queries in GraphQL files are used, which are located in the directory specified after documents. ('src/graphql/*/.graphql').

The schema is generated here again as a file in order to extend it with own query definitions if necessary. Both are then merged in our schema.ts.

schema.ts

import {GraphQLFileLoader} from '@graphql-tools/graphql-file-loader';
import {loadSchemaSync} from '@graphql-tools/load';
import {makeExecutableSchema} from 'apollo-server-micro';
import {printSchema} from 'graphql';

import {resolvers} from './resolvers';
import {typeDefs} from './type-defs';

const generatedSchema = loadSchemaSync('./src/generated/schema.graphql', {
    loaders: [new GraphQLFileLoader()]
});

export const schema = makeExecutableSchema({
    typeDefs: [printSchema(generatedSchema), typeDefs],
    resolvers
});

To run the generator, I added the following script to Package.json:

"graphql-codegen": "graphql-codegen --config codegen.yml"

In the resolver, the generated interfaces and types from the generated backend file can now be used and in our frontend, queries or the useQuery hooks are available.

If we now change the schema in the database, the GraphQL schema of the application as well as all required type definitions can be updated with only one call.

Our structure now looks something like this:

Repo: https://github.com/alexkneis/automated-sql-to-graphql