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 { GraphQLObjectType, GraphQLID, GraphQLString, GraphQLBoolean } = 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(parentValue, args) {
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(query, values)
.then(res => res)
.catch(err => err);
}
},
editPerson: {
type: PersonType,
args: {
id:{type:GraphQLString},
firstname: { type: GraphQLString },
lastname: { type: GraphQLString },
email: { type: GraphQLString }
},
resolve(parentValue, args) {
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(query, values)
.then(res => {
console.log(JSON.stringify(res));
return res[0]
})
.catch(err => err);
}
},
deletePerson: {
type: PersonType,
args: {
id:{type:GraphQLString},
},
resolve(parentValue, args) {
const query = `Delete from person where id=$1 RETURNING *`;
const values = [
args.id
];
return db
.any(query, values)
.then(res => {
//console.log(JSON.stringify(res));
return res[0]
})
.catch(err => err);
}
}
}
});
exports.mutation = RootMutation;
types.js contains
const graphql = require("graphql");
const { GraphQLObjectType, GraphQLString } = 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 { GraphQLObjectType, GraphQLID,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(parentValue, args) {
const query = `SELECT * FROM person WHERE id=$1`;
const values = [args.id];
return db
.one(query, values)
.then(res => res)
.catch(err => err);
}
},
persons: {
type: GraphQLList(PersonType),
args: {
email: { type: GraphQLString },
firstname: { type: GraphQLString },
lastname: { type: GraphQLString }
},
resolve(parentValue, args) {
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(query, values)
.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.
Whole project can be viewed at https://gitlab.com/sangram.desai/graphql-express-postgres
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
}
}
No comments:
Post a Comment