<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=490755098261949&amp;ev=PageView&amp;noscript=1">

:tada:2024 WEBBY WINNER: The Crate & Barrel mobile app won a Webby Award!

Part 1 — Building Full-stack web app with PostGraphile and React (Server-side)

Pratik Agashe
Author Pratik Agashe
Published On Aug 25, 2020


If you are interested in PostgreSQL as your Database and use GraphQL for your CRUD operations then this post is for you. I was very curious about this combination. PostGraphile is specially developed for this. Before we start, let’s touch down some definitions.

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

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. We will cover the details under the client section.

Here’s the details about technologies we will be using to build our architecture.

Database: PostgreSQL

Server: Node, ExpressJS and PostGraphile

Client: React+TypeScript, GraphQL, Apollo-Client

Schema handling: Knex Migrations.

I have divided this in multiple parts those are as follows:

Part 1 — Building Full-stack web app with PostGraphile and React (Server-side)

Part 2 — Building Full-stack web app with PostGraphile and React (Client-side)

Git Repository: https://github.com/pratikagashe/postgres-graphql


Step 1

PostgreSQL installation

First let’s visit following link to download installer for PostgreSQL:

As per operating system download the package and start installation. Let’s set password for our postgres user. We will be using that in the backend configuration.

Let’s create a database with the name ‘postgres-graphql’. For detailed steps, you can refer to PostgreSQL documentation



Step 2

Creating Node-Express server

I believe while building full-stack app architecture we should with our server setup. We will be creating the Node-Express server. Express is a prebuilt NodeJS framework. That will help us to create a fast and smarter server.

If you want go through full documentation here’s the link for your reference:https://expressjs.com.

So let’s go to our root folder and create server folder and run following command in terminal and set your 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:

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

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:


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 :


As you can see, we are using .envfile 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.)


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
- src
- index.js
- postgraphile.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:


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:

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


Finally, let’s put our server in running mode and hit the following URL to access graphql : 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).


Interested in a career at Heady?

Excellent! We are always looking for great talent.


Interested in a career at Heady?

Excellent! We are always looking for great talent.


Figuring out your next step? We're here for you.

Or drop us a line.