Graphql-Mysql database connection using Sequelize and Express in Node.js #3

Mysql database connection

Hi friends, in this tutorial I will show you how to establish a database connection using Sequelize and Express in Node.js. The database can be any Relational Database like MySQL, Along with setting up a database connection using Sequelize, also I will show you how to fetch data from a table by a simple example of graphql.

Required Tools :

• Express — This is a framework for Node.js. This will help you to handle all HTTP-related jobs.
• Node.js — To run JavaScript code on the server we will be going to use Node.js
• NPM — Node Package Manager. By using NPM you can install any dependencies or modules globally or in your project.
• MySQL — MySQL is a strong most popular Relational Database Management System (RDBMS). In this tutorial, we will use MySQL as RDBMS.
• Sequelize ORM — This is an ORM (Object Relational Mapping). It will act as an Interface between our application and the MySQL database.
• Apollo-server-express — This is the Express integration of Apollo Server. Apollo Server is a community-maintained open-source GraphQL server that works with many Node.js HTTP server frameworks.

Let’s Start

Step 1: Create a database UserDB in the MySQL database.

MySQL database Query

Step 2: Create the project directory and change the directory path to it in the command prompt.

mkdir -p database_connection && cd database_connection

Step 3:Once you enter your directory then run the following command to create the package.json file in your project folder.

npm init –yes

Step 4: It is time to install our dependencies.

Run the following command, this will install all the dependencies. Since I am using the MySQL database in this project so, I am going to install the mysql2 module. If you are using a different database, then install the appropriate package.

• pg pg-hstore — Postgres
• mysql2 — MySQL
• MariaDB — MariaDB
• sqlite3 — SQLite
• tedious — Microsoft SQL Server

npm install --save express mysql2 sequelize

All necessary project setup is done. It is time to develop the code.

Start Coding for Database Connection using Sequelize.

Step 5: Create a folder and name its models in the root folder.

Now create a model file student.js within it and paste the following code. It is the table structure of the student_student table that will be created soon automatically.

module.exports = (sequelize, DataTypes) => {
return sequelize.define('student', {
student_id: {
type: DataTypes.INTEGER(11),
primaryKey: true,
autoIncrement: true
},
name: {
type: DataTypes.STRING(255),
allowNull: false
},
classes: {
type: DataTypes.STRING(50),
allowNull: false
},
section: {
type: DataTypes.STRING(50),
allowNull:false
},
roll_number: {
type: DataTypes.STRING(10),
allowNull: false,
unique:true
},
},
{
tableName: "student_student"
});
}

Step 6: Now create another js file sequelize.js in the root folder.

It will be responsible for our database connection, model creation, and database table creation

//Import sequelize node module by using require() directive
const Sequelize = require('sequelize');
//Establishing MySQL database connection using Sequelize
const sequelize = new Sequelize('UserDB', 'root', 'root', {
host: 'localhost',
dialect: 'mysql',
});
//Importing the student model
const StudentModel = require('./models/student');
const Student = StudentModel(sequelize, Sequelize);
/*Generating the database table. If we set force:true then each and every
*time when we start our application all tables will be drop from the
*database and regenerate new. So beaware of it before using it.
*/
sequelize.sync({ force: false })
.then(() => {
console.log('\n you are connected to database successfully...!!');
});
module.exports = {
Student
}

read the comments

Step 7: Now create gql folder inside the root folder.

folder structure

Step 8: Now create typedefs and resolver folder inside the gql folder.

under gql folder

Step 9: Now inside the typedefs folder create a typeDefs.js file.

Here in this file, we will define our types of Mutations related to the Student model. paste the following code inside it.

const { gql} =require('apollo-server-express')
const typeDefs =gql`
type Query{
hello :String
getAllStudent:[Student]!
getStudentByID(student_id:Int):Student
}
type Student {
student_id:Int!
name:String!
classes:String!
section:String!
roll_number:Int!
}
type Mutation{
createStudent(name:String,
classes:String,
section:String,
roll_number:Int):Student!
deleteStudentById(student_id:Int):String
updateStudentById(student_id:Int,name:String,
classes:String,
section:String,roll_number:Int):String
}
`;
module.exports = {
typeDefs
}

if you are new to typeDefs and Query, Mutation Type please follow my graphql articles from the beginning to know more you can click here. To know more about Mutation type click here.

Step 10: Now inside the resolver folder create the resolvers.js file.

here we have defined a basic model structure for resolvers the basic definition of resolvers I can say that has a logic to handle defined Queries and Mutations.

Step 11: So let’s resolve our query First

Query:{
hello:()=>'hello shruti from resolvers folder connected',
async  getAllStudent(root ,args, {Student}){
return await Student.findAll()
},
async getStudentByID(root ,args, {Student}){
return await Student.findByPk(args.student_id)
}
},

here findAll and findByPk are both sequelize Query used to Simple SELECT queries just as SELECT * FROM …

to know more about this please follow sequlize document by clicking here

Step 12: Now we will resolve our Mutations

async createStudent(root,{ name, classes, section, roll_number},{Student}){
return await Student.create({name, classes, section, roll_number})
},

here you can see we have resolved createStudent Mutation by taking attributes from user like (name, classes, section,roll_number) and we’ll add this to Student model

here create is method is used for to create this attribute. With the help of this data got inserted into database

Step 13:Now we will update student by its id

async updateStudentById(root,args,{Student}){
await Student.update(
args,
{where:{student_id: args.student_id}})
return 'your data updated successfully'}

here update is a method that is used for updating particular data, where is a clause which specifies particular data.

Step 14:Now we will delete student by its id

async deleteStudentById(root,args,{Student}){
await Student.destroy({
where: {student_id : args.student_id}})
return "your data has been deleted"},

here destroy is a method that is used to delete particular data, where is a clause which specifies particular data.

Here return type is a string as we defined type in typesDefs.js file

Step 15: now create the app.js file in the root directory which is the entry point of any Node.js application.

const  express =  require("express");
const { resolvers } = require("./gql/resolvers/resolvers");
const { typeDefs } = require("./gql/typeDefs/typeDefs");
const { ApolloServer } = require("apollo-server-express");
// dependencies
const { Student } = require("./sequelize");
const server = new ApolloServer({ typeDefs, resolvers, context: { Student } });
//Create an Express app.
const app = express();
// API ENDPOINTS
const port = 4000;
server.start().then((res) => {
server.applyMiddleware({ app, path: "/student-list" });
app.listen({ port }, () =>
console.log(`Gateway API running at port: ${port}`)
);
});

here we have to establish a server connection and it will listen at port 4000 with path /student-list

Step 16: Now is the time to test out the Node.js application. In the command prompt or terminal run the following command —

node  app.js

you can see messages like this

Now go to Database and check student_student table is created or not

you can see the table got created.

Step 17:now go to graphql playground.

http://localhost:4000/student-list

by hitting this you can see graphql playground

here we will see mutations

Step 18: we will perform create student mutation on the playground

here we will see mutations

data got added to DB

opration

mutation Mutation($name: String, $classes: String, $section: String, $rollNumber: Int) {
createStudent(name: $name, classes: $classes, section: $section, roll_number: $rollNumber) {
student_id
name
classes
section
roll_number
}
}

Variables

{
"name": "advik",
"classes": "Kg",
"section": "A",
"rollNumber": 22
}

Step 19: we will perform update student mutation on the playground

student-id 10 got updated

opration

mutation UpdateStudentById($studentId: Int, $name: String, $classes: String, $section: String, $rollNumber: Int) {
updateStudentById(student_id: $studentId, name: $name, classes: $classes, section: $section, roll_number: $rollNumber)
}

variables

{
"studentId": 10,
"name": "ADVIK",
"classes": "KG",
"section": "A",
"rollNumber": 20
}

step 20: now we will perform delete student by its id mutation

student-id 10 data got deleted

operation

mutation DeleteStudentById($studentId: Int) {
deleteStudentById(student_id: $studentId)
}

variables

{
"studentId": 10
}

Wrapping Up

That’s all my friend. I hope this tutorial helped you a lot to understand how to establish database connections using graphql and Sequelize, Express in Node.js. Please friends like and share this tutorial with others.