Part 1 — Building a full-stack web app with PostGraphile and React: server-side steps

If you’re interested in PostgreSQL as your Database and use GraphQL for your CRUD operations, then this post is for you.

Before we start, let’s lay down some definitions.

PostGraphile automatically detects tables, columns, indexes, relationships, views, types, functions, comments and more. It builds a GraphQL server that is highly intelligent about your data, and that automatically updates itself without restarting when you modify your database.

PostGraphile | Introduction

GraphQL Code Generator is a CLI tool that can generate TypeScript typings out of a GraphQL schema. The codegen allows you to specify scripts it can run for you in certain events. (In our case, we will get hook-based queries and mutations.)

Now that we’re clear on the definitions, let’s look at the technologies we will be using to build our web app architecture.

Database: PostgreSQL
Server: Node, ExpressJS and PostGraphile
Client: React+TypeScript, GraphQL, Apollo-Client
Schema handling: Knex Migrations

Let’s get started!

Step 1: PostgreSQL installation

First, let’s download the installer for PostgreSQL from: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

Once we’ve downloaded the package and completed the installation, we’ll set a password for our postgres user. We will be using that in the backend configuration.

Next, we’ll create a database with the name ‘postgres-graphql’. For detailed steps on how to create the database, you can refer to PostgreSQL documentation: https://www.postgresql.org/docs/12/tutorial-createdb.html.

Step 2: Creating Node-Express server

Express is a prebuilt NodeJS framework. Using it will help us create a server that’s faster and smarter.

For full documentation, check out: https://expressjs.com.

First, let’s go to our root folder. We’ll create a server folder, run the following command in the terminal, and set the main file path to src/index.js

npm init

Next, it’s time to install our dependencies and dev-dependancies. We will install them in two commands:

Dependencies:
npm i body-parser cors express knex knex-migrate postgraphile pg

Dev-dependancies:
npm i nodemon dotenv -D

The above commands will install all packages in node_modules and update package.json. Our package.json will look this:

server/package.json

In order to get our server running, we need to create src/index.js file. If you know how to create a Node-Express server you can skip this step and create your own server config. If not, here’s the code to put into src/index.js :

server/src/index.js

As you can see, we are using .env file for our credentials. Below is the sample .env file for your reference. (Also, make sure you place .env file inside the server folder, and update the details according to your configuration.)

server/.env

We are all set! We can use the following commands to get our server running, based on our package.json file:

yarn start or npm run start : Start our server.
yarn watch or npm run watch : Watch mode for server.

Step 3: Create and execute migrations using Knex-Migrations

In this step, I am covering schema building and inserting data into tables using migrations . If you want to do it another way, using User Interface CLI or schema.sql , you can skip this step.

I have written a separate post for this as this is an optional way to do it. Here’s the link for that: Knex Migration — For schema and seeds with PostgreSQL

Post this text into your folder and file structure supposed to look like as follows:

- Server
  - db
    - migrations
    - seeds
    knex.js
  - src
    - index.js
    - postgraphile.js
  .env
  knexfile.js

Step 4: Integrate PostGraphile to enable graphiql

With our schema uploaded to the database with some data in it, we are all set to enable graphQl for our server using postgraphile. As we have already installed postgraphile package, we don’t need to add again.

Configuring postgraphile is very simple, and can be completed within minutes.

First, we’ll create new file src/postgraphile.js which will consist of connection details to our database and graphQl options. To do this, we just need to copy and paste the following code and update based on requirements.

Here’s the postgraphile configuration file:

server/src/postgraphile.js

Next, we’ll import this to our src/index.js file.
const postgraphile = require(‘./postgraphile’)

The last step is to integrate this with our app:
app.use(postgraphile)

Now our updated src/index.js should look like this:

server/src/index.js

Finally, let’s put our server in running mode and hit the following URL to access graphql : http://localhost:8080/graphiql

http://localhost:8080/graphiql

Now we will see knex_migrations and knex_migrations_lock query and mutation under graphiql. Personally, I like to hide these mutations, as I don’t want to keep this open for the client. To omit these tables from generating graphiql, follow these steps: GraphQL — Omit table from generating under ‘graphiql’. Postgres smart comments.

And that’s it from the server-side! Our Postgres-Node server is ready, with the help of postgraphile.

In Part 2, we will create a client for our full-stack web app. We will be using React+TypeScript, GraphQL, and Apollo to build our client.

Here we go: Part 2 — Building Full-stack web app with PostGraphile and React (Client-side).

P.S : We’re hiring at Heady! 👋 If you would like to come work with us on some really cool apps please check out our careers page.