Home » Enterprise Java » sql » Postgres and Graphql

About Yatin

Avatar photo
An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).

Postgres and Graphql

Hello readers. In this tutorial, we will bring in the postgres (running as a Docker container) to integrate with graphql and implement the CRUD operations. The application will expose different endpoints with the help of the apollo server.

1. Introduction

GraphQL is an API syntax that defines how to fetch data from one or more databases. It was developed by Facebook to optimize the RESTful api calls.

  • It is a data query and manipulation language for api’s. It is faster, simple, and easier to implement
  • It follows the client-driven architecture and gives a declarative way to fetch and update the data
  • It has a graphical structure where objects are represented by nodes and edges represent the relationship between the nodes
  • Provides high consistency across all platforms
  • It doesn’t have any automatic caching system

1.1 Application components in GraphQL

In graphql, there are two types of application components.

  • Server-side components
  • Client-side components

1.1.1 Service-side components

The server-side component allows parsing the queries coming from the graphql client applications and consists of 3 components i.e. query, resolver, and schema. Apollo is the most popular graphql server.

ComponentDescription
QueryA query is a client request made by the graphql client for the graphql server. It is used to fetch values and can support arguments and points to arrays. field and arguments are two important parts of a query
ResolverHelps to provide directions for converting graphql operation into data. Users define the resolver functions to resolve the query to the data. They help to separate the db and api schema thus making it easy to modify the content obtained from the db
SchemaIt is the center of any graphql server implementation. The core block in a schema is known as a type

1.1.2 Client-side components

The client-side components represent the client which is a code or a javascript library that makes the post request to the graphql server. It is of two types i.e.

  • GraphiQL – Browser-based interface used for editing and testing graphql queries and mutations
  • Apollo client – State management library for javascript that enables local and remote data management with graphql. Supports pagination, prefetching data, and connecting the data to the view layer

1.2 Setting up Node.js

To set up Node.js on windows you will need to download the installer from this link. Click on the installer (also include the NPM package manager) for your platform and run the installer to start with the Node.js setup wizard. Follow the wizard steps and click on Finish when it is done. If everything goes well you can navigate to the command prompt to verify if the installation was successful as shown in Fig. 1.

Fig. 1: Verifying node and npm installation

2. Postgres and Graphql

To set up the application, we will need to navigate to a path where our project will reside and I will be using Visual Studio Code as my preferred IDE. Let a take a quick peek at the project structure.

Fig. 2: Project structure

2.1 Pre-requirement – Postgres Setup

Usually, setting up the database is a tedious step but with technological advancements, this process has become simple with the help of Docker. Readers can watch the video available at this link to understand the Docker installation on Windows OS. Open the terminal and trigger the following commands to get the postgres up and running on the local machine.

Docker commands

-- command to run postgres on docker --
-- remember to change the password --
docker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres

-- command to stop the Postgres docker container --
docker stop postgres

-- command to remove the Postgres docker container --
docker rm postgres

Remember to enter the password of your choice. If everything goes well the postgresql database server would be up and running on a port number – 5433 and you can connect with the pgAdmin gui tool for connecting to the server.

Fig. 3. Postgres on Docker

2.1.1 Setting up the mock data

To play with the application we need to create mock data. Once the connection with postgresql is made I will be using the below sql script to create the required data.

users.sql

drop database if exists "learning";

create database "learning";

DROP TABLE IF EXISTS "users";

CREATE TABLE "users" (
  id SERIAL PRIMARY KEY,
  name varchar(255) default NULL,
  email varchar(255) default NULL,
  age integer NULL
);

INSERT INTO users (name,email,age) values ('Uma Montoya','rutrum.justo@hotmail.edu',35);
INSERT INTO users (name,email,age) values ('Burton Tran','urna.nec.luctus@outlook.couk',37);
INSERT INTO users (name,email,age) values ('Catherine Allen','risus.nulla.eget@google.org',24);
INSERT INTO users (name,email,age) values ('Willow Hoffman','facilisis@protonmail.edu',36);
INSERT INTO users (name,email,age) values ('Marny Bishop','consectetuer@protonmail.org',40);
INSERT INTO users (name,email,age) values ('Jane doe','jandoe@protonmail.org',27);

select * from "users";

If everything works fine the sql select statement will show the data.

Fig. 4: Mock data in a table

2.2 Setting up project dependencies

Navigate to the project directory and run npm init -y to create a package.json file. This file holds the metadata relevant to the project and is used for managing the project dependencies, script, version, etc. Replace the generated file with the code given below –

package.json

{
  "name": "graphql-postgresql",
  "version": "1.0.0",
  "description": "understanding graphql and apollo server with postgresql",
  "main": "index.js",
  "scripts": {
    "dev": "nodemon index.js",
    "start": "node index.js",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "express",
    "apollo-server",
    "apollo-server-express",
    "graphql",
    "nodemon",
    "learning",
    "postgresql",
    "database",
    "knex",
    "pg"
  ],
  "author": "geek",
  "license": "MIT",
  "dependencies": {
    "apollo-server-express": "^3.8.1",
    "config": "^3.3.7",
    "express": "^4.18.1",
    "knex": "^2.1.0",
    "pg": "^8.7.3"
  },
  "devDependencies": {
    "nodemon": "^2.0.16"
  }
}

Once the file is replaced trigger the below npm command in the terminal window to download the different packages required for this tutorial.

Downloading dependencies

npm install

2.3 Setting up the type definition

typeDefs represents the graphql schema language string and the following file present in the schema directory represents the same. You’re free to modify it as per your needs.

typedefs.js

const { gql } = require("apollo-server-express");

const typeDefs = gql`
  type User {
    id: Int!
    name: String!
    email: String!
    age: Int!
  }

  type Link {
    status: String!
  }

  # Queries
  type Query {
    getAll: [User!]!

    find(id: Int!): [User!]!
  }

  # Mutation
  type Mutation {
    create(name: String!, email: String!, age: Int!): Link!

    delete(id: Int!): Link!

    deleteAll: Link!
  }
`;

module.exports = { typeDefs };

2.4 Setting up config for resolver

The below file created in the config will hold the values required to connect to the database. For security reasons, the database connection details of this tutorial have been replaced with the mock details in the below file. You are required to add the details as per your confirmation.

config.json

{
  "database": {
    "dev": {
      "host": "ENDPOINT",
      "user": "USERNAME",
      "password": "PASSWORD",
      "database": "DATABASE_NAME",
      "table": "TABLE_NAME"
    },
    "driver": "pg"
  }
}

2.5 Setting up the resolver

resolvers in graphql are the functions that resolve a value for the type or a field in the schema and the following file present in the schema directory represents the same. You’re free to modify it as per your needs. The file makes a connection to the database with the help of the knex library and exposes methods to connect with the database and share the result from the sql table users.

resolvers.js

const config = require("config");

const table = config.get("database.dev.table");

const knex = require("knex")({
  client: config.get("database.driver"),
  debug: false,
  connection: {
    host: config.get("database.dev.host"),
    user: config.get("database.dev.user"),
    password: config.get("database.dev.password"),
    database: config.get("database.dev.database")
  }
});

const resolvers = {
  Query: {
    getAll: () => {
      console.log("fetching all users");
      return knex(table).select("*");
    },

    find: (parent, args) => {
      let key = args.id;
      console.log("fetching %s", key);
      return knex(table).where("id", key);
    }
  },

  Mutation: {
    create: (parent, args) => {
      console.log("inserting new user");
      knex(table)
        .insert({ name: args.name, email: args.email, age: args.age })
        .returning("id")
        .then(([res]) => {
          console.log("%s inserted", res.id);
        });

      return { status: `inserted` };
    },

    delete: (parent, args) => {
      let key = args.id;
      console.log("deleting %s", key);
      return knex(table)
        .del()
        .where({ id: key })
        .then(() => {
          return { status: `${key} deleted` };
        });
    },

    deleteAll: () => {
      console.log("deleting all");
      return knex(table)
        .del()
        .then(() => {
          return { status: "all records deleted" };
        });
    }
  }
};

module.exports = { resolvers };

2.6 Setting up implementation file

Create a file in the root directory that acts as an entry point for the application. The file contains the configuration required to start the apollo server on port number 3005 and the server can be accessed on the url http://localhost:3005/graphql

index.js

const { ApolloServer } = require("apollo-server-express");
const { typeDefs } = require("./schema/typedefs");
const { resolvers } = require("./schema/resolvers");

const server = new ApolloServer({ typeDefs, resolvers });

const express = require("express");
const app = express();

// browser url - http://localhost:3005/graphql

const app_port = 3005;
server.start().then(() => {
  server.applyMiddleware({ app });
  app.listen({ port: app_port }, () => {
    console.log(`Service endpoint :: http://localhost:${app_port}/graphql`);
  });
});

3. Run the Application

To run the application navigate to the project directory and enter the following command as shown below in the terminal. The application will be started successfully on the port number – 3005.

Run command

$ npm run start

Once the application is started successfully open the browser and hit the below endpoint to view the query explorer. If you will be a first-time user welcome page will be shown otherwise the query explorer.

Application endpoint

http://localhost:3005/graphql
Fig. 5: Application demo

You can download the sample queries and mutations specified in the resolver from the Downloads section. That is all for this tutorial and I hope the article served you with whatever you were looking for. Happy Learning and do not forget to share!

4. Summary

In this tutorial, we saw a brief introduction to graphql and integrated it with the database. You can download the source code from the Downloads section.

5. Download the Project

This was a tutorial to perform crud operations in graphql with the help of the postgresql database.

Download
You can download the full source code of this example here: Postgres and Graphql

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

I have read and agree to the terms & conditions

 

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments