DEV Community

Cover image for How To Build Rest API With NodeJS, Express, and MySQL

How To Build Rest API With NodeJS, Express, and MySQL

Julia Strichash on September 23, 2020

With knowledge of JavaScript and MySQL, we can build our NodeJS API using Express. I did some research, and I was attempting to develop an API fro...
Collapse
 
darkonecorp profile image
Darkone Corp

Hey Julia, Thanks for the precise guide.

I have the following question, how can I execute a procedure that receives an input parameter?
Try the following way:
const sql = CALL insert_data(?);
const result = await query(sql, [dataJson]);

But I get the following error message:
Error] Error: Incorrect arguments to mysqld_stmt_execute.

Collapse
 
juliest88 profile image
Julia Strichash • Edited

Hey, I guess you used the Create Procedure statement, which I didn't mention in my post.
In any case, if you created a procedure with one parameter, all you need is to use

const result = await query(sql, [value]);
Enter fullscreen mode Exit fullscreen mode
  • The first argument is the SQL query,
  • The second argument is a flat array with all the values in the same order as '?' marks. The value should be simple, like string, int, and so on.

For example:
Create Procedure:

DELIMITER //
CREATE PROCEDURE insert_data(
  IN usrname VARCHAR(25),
  IN pass CHAR(60),
  IN firstname VARCHAR(50),
  IN lastname VARCHAR(50),
  IN useremail VARCHAR(100),
  IN userrole ENUM('Admin', 'SuperUser'),
  IN userage INT(11)
) BEGIN
INSERT INTO
  user (
    username,
    password,
    first_name,
    last_name,
    email,
    role,
    age
  )
VALUES
  (
    usrname,
    pass,
    firstname,
    lastname,
    useremail,
    userrole,
    userage
  );
  END//
DELIMITER ;
Enter fullscreen mode Exit fullscreen mode

Add a method in user.model.js file, which uses the call command:

    create = async ({ username, password, first_name, last_name, email, role = Role.SuperUser, age = 0 }) => {
        const sql = 'CALL insert_data(?,?,?,?,?,?,?)';

        const result = await query(sql, [username, password, first_name, last_name, email, role, age]);
        const affectedRows = result ? result.affectedRows : 0;

        return affectedRows;
    }
Enter fullscreen mode Exit fullscreen mode
Collapse
 
darkonecorp profile image
Darkone Corp

Thanks Julia for your response and explanation, I could solve my problem.

Collapse
 
darkonecorp profile image
Darkone Corp

Hi Julia.
I have to execute a procedure that returns an output parameter. How can I get that value ?

Collapse
 
raviphad profile image
Ravikumar Phd • Edited

HI thanks for the mindblowing Article

but am facing the issue while starting the npm
the line no. 13 in user.controller.js
getAllUsers = async (req, res, next) => {
am using MySQL

Error:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\xampp\htdocs\mysql-node-express>npm start

mysql-node-express@1.0.0 start C:\xampp\htdocs\mysql-node-express
node src/server.js

C:\xampp\htdocs\mysql-node-express\src\controllers\user.controller.js:13
getAllUsers = async (req, res, next) => {
^

SyntaxError: Unexpected token =
at new Script (vm.js:79:7)
at createScript (vm.js:251:10)
at Object.runInThisContext (vm.js:303:10)
at Module._compile (internal/modules/cjs/loader.js:657:28)
at Object.Module._extensions..js (internal/modules/cjs/loader.js:700:10)
at Module.load (internal/modules/cjs/loader.js:599:32)
at tryModuleLoad (internal/modules/cjs/loader.js:538:12)
at Function.Module._load (internal/modules/cjs/loader.js:530:3)
at Module.require (internal/modules/cjs/loader.js:637:17)
at require (internal/modules/cjs/helpers.js:22:18)
npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! mysql-node-express@1.0.0 start: node src/server.js
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the mysql-node-express@1.0.0 start script.
npm ERR! This is probably not a problem with npm. There is likely additional log
ging output above.

npm ERR! A complete log of this run can be found in:
npm ERR! C:\Users\User\AppData\Roaming\npm-cache_logs\2021-02-24T09_31_30_8
14Z-debug.log

C:\xampp\htdocs\mysql-node-express>

please check screenshot

Collapse
 
mauromontrond profile image
mauro-montrond

Hey man,
I think I was running into the same error, check your node version and update to the latest version so it recognizes the latest syntax.
I hope this helped you, cheers.

Collapse
 
meghead profile image
meghead

Hi Julia,

I installed from your Github repository instructions. I then tried to create a user from /api/v1/users using the Postman raw body example which you provided. But I got the following error;

"type": "error",
"status": 400,
"message": "Validation faild",  
Enter fullscreen mode Exit fullscreen mode
Collapse
 
juliest88 profile image
Julia Strichash • Edited

Hi,
Can you please share the entire request body and the response?
As part of the response, you should get an array that includes the relevant errors.

For instance:

{
    "type": "error",
    "status": 400,
    "message": "Validation faild",
    "errors": [
        {
            "msg": "username is required",
            "param": "username",
            "location": "body"
        },
        {
            "msg": "Must be at least 3 chars long",
            "param": "username",
            "location": "body"
        },
        {
            "value": "12345",
            "msg": "Password must contain at least 6 characters",
            "param": "password",
            "location": "body"
        }
    ]
}
Enter fullscreen mode Exit fullscreen mode

Thanks!

Collapse
 
morganmattr profile image
morganmattr

I was getting 400s (every key was returned as an error) problem, and it was just because in the Postman UI was sending the raw data as 'text' instead of 'json'. 'text' seems to be the default in the Postman UI, so make sure you switch it to json and you will stop getting the 400 error.

By the way Julia, love this guide and all your detailed explanations. Thank you for sharing!

Thread Thread
 
juliest88 profile image
Julia Strichash

Hi, I'm glad you were able to solve this, and you're right, we need to make sure it's a JSON format before we send the request.
Thanks a lot! You're very welcome! :)

Collapse
 
Sloan, the sloth mascot
Comment deleted
Collapse
 
juliest88 profile image
Julia Strichash

Hi,
You're welcome :)
Question: did you install myqsl2 or mysql node module?
The error that you described occurs when I try to use mysql node module.

Collapse
 
carlossoto2 profile image
Charles

thanks a lot for your quick response
i think is mysql2

this is my package.json
"cors": "^2.8.5",
"dotenv": "^8.2.0",
"express": "^4.17.1",
"express-validator": "^6.10.0",
"jsonwebtoken": "^8.5.1",
"mysql2": "^2.2.5"

Collapse
 
andre7th profile image
Andre7th

Hi Julia from Greece!
We thank you very much for the perfect guide!!!! I follow it faithfully!!!!
I would like you to answer a question for me about this point:

user.model.js ----->find = async (params = {}) => {.......

The question is why params is empty(={})?
I would like to use your folowing code:

if (!Object.keys(params).length) {
return await query(sql);
}

  const { columnSet, values } = multipleColumnSet(params)
   sql += ` WHERE ${columnSet}`;

but i dont know how to use...
Sorry for my English....
Thank you again!

Collapse
 
ramakanthrapaka profile image
RamakanthRapaka

Hi Julia,
Can you please help me,How can we write query for WHERE IN,
when passing array to query it is giving no records found 404 response
when am passing comma separated ids it is giving syntax error because comma separated ids converting as string '(6,5) '

SELECT farmer.*, farmer_address.country, farmer_address.region, farmer_address.landmark FROM farmer INNER JOIN farmer_address ON farmer.id = farmer_address.farmer_id WHERE farmer.id IN '(6,5)'

for where in query can please share small code snippet

Collapse
 
juliest88 profile image
Julia Strichash

Hi,
You need to use the prepared statement.
Just put placeholders (?) in the IN => IN(?, ?)
and pass the [5,6] array as a parameter to the query async method:

const result = await query(sql, [5, 6]);
Enter fullscreen mode Exit fullscreen mode

In my repository(on GitHub), I added an additional function in the common.utils.js file. this function sets the placeholders(?) instead of the array items, and returns back a string like '?, ?'.

exports.getPlaceholderStringForArray = (arr) => {
    if (!Array.isArray(arr)) {
        throw new Error('Invalid input');
    }

    // if is array, we'll clone the arr 
    // and fill the new array with placeholders
    const placeholders = [...arr];
    return placeholders.fill('?').join(', ').trim();
}
Enter fullscreen mode Exit fullscreen mode

So you can use this function to fill the placeholders as the number of your array something like => IN(getPlaceholderStringForArray([5, 6]))
Just don't forget to import the function.

Collapse
 
ramakanthrapaka profile image
RamakanthRapaka • Edited

Hi Julie,

var farmercropsLists = await UserCropsModel.findCropsByChampsArray({ 'farmer.id': farmar_ids });

if (!Object.keys(params).length) {
return await query(sql);
}
for (const [key, value] of Object.entries(params)) {
var values = value;
sql += WHERE ${key} IN ( + getPlaceholderStringForArray(value) + );
}
console.log(sql);

    return await query(sql, values);
Enter fullscreen mode Exit fullscreen mode

can you please suggest JULIE how can we use where and where IN in single function

Thank you

Collapse
 
krishilmodi profile image
krishil-modi

Hi Julia,
I am backend developer. your api code is working good in post method just like ( Create user and login ) api. my concern is get method. i have issue in get method. i tested with postman, my api end point is ( localhost:3000/api/v1/users/id/1 ) and method is GET but response is below
{
"type": "error",
"status": 401,
"message": "Access denied. No credentials sent!"
}

how to pass parameter in GET method please suggest me.

Thanks
Darshan Modi

Collapse
 
juliest88 profile image
Julia Strichash • Edited

Hi!
As you can see, I used the auth function almost on all of the routes instead of the create user, and login routes.

That means that after creating your user, first, you need to login to get a token.
Then put it in the Authorization in the headers with 'Bearer' before.

And then you'll have access to the rest of the routes (according to your user role).

Collapse
 
krishilmodi profile image
krishil-modi

Thanks Julia,
As per follow your above comment my API is working fine in GET method.

Request: localhost:3000/api/v1/users/id/1 ( Method GET )
Headers: Authorization (Key) : Bearer token

Response:
{
"id": 1,
"username": "krishil",
"first_name": "Darshan",
"last_name": "Modi",
"email": "darshanmodi2010@gmail.com",
"role": "SuperUser",
"age": 37
}

Thanks
Darshan Modi

Thread Thread
 
juliest88 profile image
Julia Strichash

I'm glad it worked!
You're welcome! :)

Collapse
 
Sloan, the sloth mascot
Comment deleted
Collapse
 
juliest88 profile image
Julia Strichash • Edited

Hi,
This post shows just how to combine NodeJS with MYSQL in a simple way.
You can run whatever queries you want using the query async method (await query(sql, []))
Just don't forget the prepared statements if you have user input.

How to do it is another part of the SQL.

If you want to make a query that depends on another query,
all you need to do is write the first query with await query-> get the result,
Then use it on another query.

async-await it's a new feature from ES8, and it's like the newest version of Promise.

promise.then(data => {
    console.log(data);
});
Enter fullscreen mode Exit fullscreen mode

Is like:

const data = await promise();
console.log(data);
Enter fullscreen mode Exit fullscreen mode

The line below the await promise() is like using the .then() method.

You can read more about it to understand how to use it.

The utils.js file is just a file to hold the common functions that we can reuse over and over; that's all.

Collapse
 
riguidix profile image
Riguidix Rodriguez

Wow, pretty good... Will try to do an API that takes care of simplicity and don't have code duplication, I've been working one and it's an entire mess to keep Copy/Pasting the controllers over and over

Collapse
 
patrickcharda profile image
patrickcharda

Thanks for sharing, very useful and formative !

Collapse
 
clintonksang_81 profile image
Clinton • Edited

Hey Julia, Thanks for the precise guide, Im having this error when I try to Get from DB,

I doubt I'm having issues with configuring JWT. Kindly assist
dev-to-uploads.s3.amazonaws.com/i/...

Collapse
 
juliest88 profile image
Julia Strichash • Edited

Hey,
I can't see your error. if you tried to upload an image, you need to copy and paste it to your comment.

Collapse
 
clintonksang_81 profile image
Clinton

I've edited

Thread Thread
 
juliest88 profile image
Julia Strichash • Edited

Thanks!
I think it's something related to an invalid token.

You have to get the token from the login response and then paste it into the header after the "Bearer .." in the Authorization

Collapse
 
gosan profile image
GOsan • Edited

Emm i need support for issue
after post localhost:3331/api/v1/users
with password and confirm_password the same value still not create a new user?
dev-to-uploads.s3.amazonaws.com/i/...

Collapse
 
juliest88 profile image
Julia Strichash • Edited

Hi, I was able to reproduce the error you saw.
Since it's a POST method, all the parameters should be sent as part of the body.
In the request you prepared, all the parameters were part of the headers, which caused the error to occur.

You didn't get errors in all the other parameters because they already existed in the request object (req.headers), but I checked req.body.password for the password validation and therefore you got an error (because the body was an empty object, without the password property).

There really needs to be a verification that all the parameters have been entered in the body parameter and not somewhere else.

In the userValidator.middleware.js file, we can use the body function instead of the check function (from the express-validator library).
This way we're only going to check the req.body.
I updated the code.

In any case, if you put all the parameters in the body, as I described in the image, it won't be a problem to create a user with the parameters you entered.

dev-to-uploads.s3.amazonaws.com/i/...

Collapse
 
carlossoto2 profile image
Charles

Hello Julia

I need a help i'm a novice

i'm having this errors

1.- Class properties must be methods. Expected '(' but instead saw '='. (E054)jshint(E054)

2.- Class properties must be methods. Expected '(' but instead saw 'callback'. (E054)jshint(E054)

query = async(sql, values) => {
return new Promise((resolve, reject) => {
const callback = (error, result) => {
if (error) {
reject(error);
return;
}
resolve(result);
}

any help

Collapse
 
andylink profile image
andylink

Hi Julia great article. Wonder if you could help me with something? How would I alter get CurrentUser in usercontroller.js to only return username and email of the current user?

I'm struggling to work out how userWithoutPassword works.

Thanks

Andy

Collapse
 
andylink profile image
andylink

Ah, typical, posted for help and worked it out shortly afterwards.

Wasn't aware of the es6 spread operator, altering as per below removes those items from being sent.

getCurrentUser = async (req, res, next) => {
const { password, first_name, last_name...userWithLessInfo } = req.currentUser;
res.send(userWithLessInfo);
};

Collapse
 
zifherx profile image
Fernando_R

Hello Julia,
I appreciate your article,
But when consuming the Login I get this error:
[Error] ReferenceError: columnSet is not defined
Maybe I am omitting something but I would appreciate it if you would give me a hand.

Collapse
 
juliest88 profile image
Julia Strichash • Edited

Hi Fernando,
Can you describe the steps you did?
I need more information.
Did you send an email and a password on the body request?

Basically, userLogin method performs the following steps on the user.controller.js file:

  • Check for validation
  • Extract email and password from req.body
  • Use findOne method by email <= (this is where we are using columnSet).
  • If there is no such user with this email => throw HTTP error exception.
  • Checking whether the user input password matches the user password from the DB.
  • If not => throw HTTP error exception.
  • If the user matched => create a token by secretKey
  • Send back the user (without the password) and the token.

Perhaps it's something that needs if block (or something like that),
But I need to see the steps that you have taken.

Thanks! :)

Collapse
 
surya_avi profile image
Avinash Suryawanshi • Edited

This is the Best Article on Node.js, Express and Sql, which explains the right way of creating node and express based project.

Thanks,
Avi

Collapse
 
nyakurilevite profile image
Nyakuri Levite

Your tutorial is too cool!
You saved me

Collapse
 
locnt19 profile image
Nguyễn Thành Lộc

Awesome!

Collapse
 
cimpli2005 profile image
cimpli2005

Thank you. Your tutorial is very useful for me.

Collapse
 
betaprog profile image
BetaProg

Hi Julia, Thanks for the article. I followed step-to-step tutorial, but for the mysql, I've added my MySql-Workbench connection details in .env file.

I'm getting this error at the very first step of npm run dev:

Server running on port 3000!
buffer.js:329
throw new ERR_INVALID_ARG_TYPE(
^

TypeError [ERR_INVALID_ARG_TYPE]: The first argument must be of type string or an instance of Buffer, ArrayBuffer, or Array or an Array-like Object. Received undefined
at Function.from (buffer.js:329:9)

Can you please guide me here. Thanks

Collapse
 
betaprog profile image
BetaProg

Any clue- why am I getting this error?

Server running on port 3000!
buffer.js:329
throw new ERR_INVALID_ARG_TYPE(
^

TypeError [ERR_INVALID_ARG_TYPE]: The first argument must be of type string or an instance of Buffer, ArrayBuffer, or Array or an Array-like Object. Received undefined
at Function.from (buffer.js:329:9)

Collapse
 
cnochx profile image
Rudra Martin Grellmann

so far one of the best examples

Collapse
 
ramakanthrapaka profile image
RamakanthRapaka

Hi Julia,
How Can We Logout, Invalidate JWT Token

Collapse
 
Sloan, the sloth mascot
Comment deleted
Collapse
 
Sloan, the sloth mascot
Comment deleted
Collapse
 
destronic profile image
DestroNic

This is an awesome guide, thank you Julia, I was wondering if you use React, do you still need to use the user validator?

Collapse
 
dominika profile image
Dominika

Hi Julia, great article.

Can you tell me how to add a simple frontend to it?