DEV Community

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...

Read full post

Darkone Corp • Feb 13 '21

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.

Julia Strichash • Feb 13 '21 • 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]);

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 ;

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;
    }

Darkone Corp • Feb 14 '21

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

Darkone Corp • Feb 14 '21

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

Ravikumar Phd • Feb 24 '21 • 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:

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

mauro-montrond • Mar 27 '21

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.

meghead • Feb 6 '21

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",

Julia Strichash • Feb 8 '21 • 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"
        }
    ]
}

Thanks!

morganmattr • Feb 10 '21

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!

Julia Strichash • Feb 10 '21

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! :)

Comment deleted

Julia Strichash • Jan 19 '21

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.

Charles • Apr 21 '21

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"

Andre7th • Sep 1 '21

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!

RamakanthRapaka • Mar 24 '21

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

Julia Strichash • Mar 25 '21

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]);

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();
}

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.

RamakanthRapaka • Mar 25 '21 • 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);

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

Thank you

krishil-modi • Mar 25 '21

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

Julia Strichash • Mar 25 '21 • 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).

krishil-modi • Mar 25 '21

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

Julia Strichash • Mar 25 '21

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

Comment deleted

Julia Strichash • Feb 2 '21 • 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);
});

Is like:

const data = await promise();
console.log(data);

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.

Riguidix Rodriguez • Feb 24 '21

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

patrickcharda • Jan 27 '21

Thanks for sharing, very useful and formative !

Clinton • Feb 12 '21 • 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/...

Julia Strichash • Feb 12 '21 • 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.

Clinton • Feb 12 '21

I've edited

Julia Strichash • Feb 12 '21 • 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

GOsan • Jan 15 '21 • 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/...

Julia Strichash • Jan 15 '21 • 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/...

Charles • Apr 21 '21

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

andylink • Apr 9 '21

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

andylink • Apr 9 '21

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);
};

Fernando_R • Feb 3 '21

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.

Julia Strichash • Feb 4 '21 • 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! :)

Avinash Suryawanshi • Dec 29 '20 • 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

Nyakuri Levite • Dec 28 '20

Your tutorial is too cool!
You saved me

Nguyễn Thành Lộc • Dec 23 '20

Awesome!

cimpli2005 • Nov 5 '21

Thank you. Your tutorial is very useful for me.

BetaProg • Feb 20 '21

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