Graphql-Mysql database connection using Sequelize and Express in Node.js #3
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.
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() directiveconst Sequelize = require('sequelize');//Establishing MySQL database connection using Sequelizeconst sequelize = new Sequelize('UserDB', 'root', 'root', {host: 'localhost',dialect: 'mysql',});//Importing the student modelconst 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}
Step 7: Now create gql folder inside the root folder.
Step 8: Now create typedefs and resolver folder inside the 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 :StringgetAllStudent:[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):StringupdateStudentById(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");// dependenciesconst { Student } = require("./sequelize");const server = new ApolloServer({ typeDefs, resolvers, context: { Student } });//Create an Express app.const app = express();// API ENDPOINTSconst 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_idnameclassessectionroll_number}}
Variables
{"name": "advik","classes": "Kg","section": "A","rollNumber": 22}
Step 19: we will perform update student mutation on the playground
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
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.