Wednesday, October 16, 2019

creating graphql api using express.js & postgressql



first we will create a table as follows

create table person(
id serial,
firstname character varying(50),
lastname character varying(50),
email character varying(150),
joined timestamp,
lastloggedin timestamp
)

add some records to newly created table

insert into person(firstname,lastname,email,joined,lastloggedin) values('sangram','desai','ssd1110@gmail.com',current_timestamp,current_timestamp)

insert into person(firstname,lastname,email,joined,lastloggedin) values('sagar','desai','ssd1778@gmail.com',current_timestamp,current_timestamp)


we need following packages in our project
pg-promise express graphql express-graphql dotenv

also we are using nodemon as developer dependency.


index.js

"use strict";
const graphql = require("graphql");
const express = require("express");
const expressGraphQl = require("express-graphql");
const { GraphQLSchema } = graphql;
const { query } = require("./schemas/queries");
const { mutation } = require("./schemas/mutations");

const schema = new GraphQLSchema({
  query,
  mutation
});

var app = express();
app.use('/',  expressGraphQl({
    schema: schema,
    graphiql: true
  })
);




app.listen(3000, () =>
  console.log('GraphQL server running on localhost:3000')
);


.env file at root location contains

POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DB=playground
POSTGRES_USER=sangram
POSTGRES_PASSWORD=sangram


.gitingore file contains

node_modules/

create a folder called schemas in this folder create three files
mutation.js ,types.js & queries.js.



Mutation.js file contains

const graphql = require("graphql");
const db = require("../pgAdaptor").db;
const { GraphQLObjectTypeGraphQLIDGraphQLStringGraphQLBoolean } = graphql;
const { PersonType } = require("./types");

const RootMutation = new GraphQLObjectType({
  name: "RootMutationType",
  type: "Mutation",
  fields: {
    addPerson: {
      type: PersonType,
      args: {
        firstname: { type: GraphQLString },
        lastname: { type: GraphQLString },
        email: { type: GraphQLString }
      },
      resolve(parentValueargs) {
        const query = `insert into person(firstname,lastname,email,joined,lastloggedin) VALUES ($1, $2, $3, current_timestamp,current_timestamp) RETURNING *`;
        const values = [
          args.firstname,
          args.lastname,
          args.email
        ];

        return db
          .one(queryvalues)
          .then(res => res)
          .catch(err => err);
      }
    },
    editPerson: {
      type: PersonType,
      args: {
        id:{type:GraphQLString},
        firstname: { type: GraphQLString },
        lastname: { type: GraphQLString },
        email: { type: GraphQLString }
      },
      resolve(parentValueargs) {
        const query = `Update person set firstname=$1,lastname=$2,email=$3 where id=$4 RETURNING *`;
        const values = [
          args.firstname,
          args.lastname,
          args.email,
          args.id
        ];

        return db
          .any(queryvalues)
          .then(res => {
            console.log(JSON.stringify(res));
            return res[0]
          })
          .catch(err => err);
      }
    },
    deletePerson: {
      type: PersonType,
      args: {
        id:{type:GraphQLString},
      },
      resolve(parentValueargs) {
        const query = `Delete from person where id=$1 RETURNING *`;
        const values = [
          args.id
        ];

        return db
          .any(queryvalues)
          .then(res => {
            //console.log(JSON.stringify(res));
            return res[0]
          })
          .catch(err => err);
      }
    }
  }
});

exports.mutation = RootMutation;

types.js contains

const graphql = require("graphql");
const { GraphQLObjectTypeGraphQLString } = graphql;

const PersonType = new GraphQLObjectType({
  name: "Person",
  type: "Query",
  fields: {
    id: { type: GraphQLString },
    firstname: { type: GraphQLString },
    lastname: { type: GraphQLString },
    email: { type: GraphQLString },
    joined: { type: GraphQLString },
    lastloggedin: { type: GraphQLString }
  }
});

exports.PersonType = PersonType;

queries.js contain

const { db } = require("../pgAdaptor");
const { GraphQLObjectTypeGraphQLID,GraphQLList,GraphQLNonNull,GraphQLString,GraphQLDate } = require("graphql");
const { PersonType } = require("./types");

const RootQuery = new GraphQLObjectType({
  name: "RootQueryType",
  type: "Query",
  fields: {
    person: {
      type: PersonType,
      args: { id: { type: GraphQLNonNull(GraphQLID) }},
      resolve(parentValueargs) {
        const query = `SELECT * FROM person WHERE id=$1`;
        const values = [args.id];
        return db
        .one(queryvalues)
        .then(res => res)
        .catch(err => err);
    
      }
    },
    persons: {
      type: GraphQLList(PersonType),
      args: { 
        email: { type: GraphQLString },
        firstname: { type: GraphQLString },
        lastname: { type: GraphQLString }      
      },
      resolve(parentValueargs) {
        let query = `SELECT * FROM person where 1=1 `;
        let values=[];

        if(args.email){
          query = query + ` and email='` + args.email + `'`;
        }

        if(args.firstname){
          query = query + ` and firstname='` + args.firstname + `'`;
        }

        if(args.lastname){
          query = query + ` and lastname='` + args.lastname + `'`;
        }

        return db
          .any(queryvalues)
          .then(res => {
            //console.log(JSON.stringify(res));
            return res;
          })
          .catch(err => err);

      }
    },
  }
});




exports.query = RootQuery;

in root location create pgAdapter.js

pgAdapter.js contains

require('dotenv').config()
const pgPromise = require('pg-promise');

const pgp = pgPromise({}); // Empty object means no additional config required

const config = {
    host: process.env.POSTGRES_HOST,
    port: process.env.POSTGRES_PORT,
    database: process.env.POSTGRES_DB,
    user: process.env.POSTGRES_USER,
    password: process.env.POSTGRES_PASSWORD
};

const db = pgp(config);


/*db.any('select * from person')
    .then(res => {
        console.log(res);
    });
*/

exports.db = db;



start application using npm start.

On browser hit http://localhost:3000 graphiql interface will be shown.



Inside you can run following queries.

GraphQL Queries:

1) inserting record into table using grapql API

mutation {
addPerson(firstname: "sachin", lastname: "desai", email: "ssd2109@gmail.com") {
id
}
}
2) getting record whose id is 3
{
person (id:3){
firstname,
lastname,
email,
lastloggedin,
joined
}
}

3) get all records in table
{
persons{
firstname,
lastname,
email,
lastloggedin,
joined
}
}

4) editing record whose is is 4
mutation {
editPerson(firstname: "swapnil", lastname: "sardeshmukh", email: "ssd2109@gmail.com",id:"4") {
id,
lastname,
firstname,
email,
lastloggedin,
joined
}
}

5) deleteing record whose id is 4
mutation {
deletePerson(id:"4") {
id,
lastname,
firstname,
email,
lastloggedin,
joined
}
}

6) listing all records whose firstname is sangram ,email is ssd1110@gmail.com & lastname is desai.

{
persons(firstname:"sangram",email:"ssd1110@gmail.com",lastname:"desai"){
firstname,
lastname,
email,
lastloggedin,
joined
}
}