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.
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)
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
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
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.
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.
Post this text into your folder and file structure supposed to look like as follows:
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.