Javascript and mysql query

Node.js and MySQL Complete Tutorial

Node.js and MySQL are some of the necessary binding needed for any web application. MySQL is one of the most popular open-source databases in the world and is efficient as well. Almost every popular programming language like Java and PHP provides drivers to access and perform operations with MySQL.

In this Node js and MySQL tutorial, we are going to learn how to connect the Node js server with a MySQL database. We will also learn how to pool connections to improve performance, query the tables, and call stored procedures.

To be able to follow up with the code examples in this Node.js and MySQL tutorial, you should have MySQL installed on your computer, click here to download MySQL.

Quick Start: How to Use MySQL in Node

Assuming you have Node and MySQL installed on your computer. Let’s quickly use MySQL in Node in three easy steps:

Step 1: Create a new Node.js project

Create a new directory and initialize a Node project using the NPM.

$ mkdir mysqlexperiment && cd mysqlexperiment $ npm init --y

Step 2: Install mysql node module

Install the mysql node module using the NPM.

Step 3: Connect with MySQL

Create an app.js file and copy/paste the code shown below. Change the MySQL credentials accordingly with your system.

const mysql = require('mysql'); const connection = mysql.createConnection(< host: 'localhost', user: 'user', password: 'password', database: 'databasename' >); connection.connect((err) => < if (err) throw err; console.log('Connected to MySQL Server!'); >);

Run the code using the following command.

Observe the ‘Connected to MySQL Server!’ message in the terminal.

If you have the latest MySQL server installed, you might end up getting an error saying the following.

To tackle this issue, create a new user in your MySQL server with ‘mysql_native_password’ authentication mechanisum.

Here is how you can do it quickly. First, log in to the MySQL server using root access.

Then run these commands one by one.

CREATE USER 'newuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'newpassword'; GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost'; FLUSH PRIVILEGES;

In the code, pass the new credentials to connect to the MySQL server. Let’s proceed further.

Pooling MySQL Connections

The code shown earlier is not meant for production use. It’s merely to get you started with Node and MySQL. In a production scenario, we must use connection pooling to improve the performance of MySQL and not overload the MySQL server with too many connections.

Let’s explain it with a simple example.

Consider the code shown below.

const express = require("express"); const app = express(); const mysql = require('mysql'); const connection = mysql.createConnection(< host: 'localhost', user: 'username', password: 'password', database: 'databasename' >); connection.connect((err) => < if (err) throw err; console.log('Connected to MySQL Server!'); >); app.get("/", (req, res) => < connection.query('SELECT * from users LIMIT 1', (err, rows) =>< if (err) throw err; console.log('The data from users table are: \n', rows); connection.end(); >); >); app.listen(3000, () => < console.log('Server is running at port 3000'); >);

We are integrating express module to create a web server. Install the module using the following command.

We are creating a MySQL connection on every request coming from the user. Soon after getting multiple concurrent requests, the MySQL server will get overloaded and throw an error.

To simulate the concurrent connection scenario, we are going to use a tool called siege.

Use this command to install it in Ubuntu system.

sudo apt-get install siege

Let’s simulate the concurrent requests.

siege -c10 -t1M http://localhost:3000

Assuming you are running the Node server on Port 3000.

As you can see from the output above, our server crashed while handling concurrent requests. To tackle this scenario, we use the Pooling mechanism.

Connection Pooling is a mechanism to maintain a cache of database connection so that the connection can be reused after releasing it.

Let’s rewrite our code to support connection pooling.

const express = require("express"); const app = express(); const mysql = require('mysql'); const pool = mysql.createPool(< host: 'localhost', user: 'username', password: 'password', database: 'databasename' >); app.get("/", (req, res) => < pool.getConnection((err, connection) => < if (err) throw err; console.log('connected as id ' + connection.threadId); connection.query('SELECT * from users LIMIT 1', (err, rows) =>< connection.release(); // return the connection to pool if (err) throw err; console.log('The data from users table are: \n', rows); >); >); >); app.listen(3000, () => < console.log('Server is running at port 3000'); >);

Run the app using the following command.

Let’s fire up 10 concurrent users for 1 minute using siege by using this command.

siege -c10 -t1M http://localhost:3000

Our server is effectively handling multiple requests with ease. I have used this approach in multiple production software solutions with heavy payload and it works like charm.

Let’s learn how to execute various MySQL queries using Node.

Executing Queries

Let’s learn how to execute queries using Node.js.

Inserting data into Table

Here is the code to add new rows to the table.

const mysql = require('mysql'); const pool = mysql.createPool(< connectionLimit: 100, //important host: 'localhost', user: 'root', password: '', database: 'todolist', debug: false >); // add rows in the table function addRow(data) < let insertQuery = 'INSERT INTO ?? (. ) VALUES (. )'; let query = mysql.format(insertQuery, ["todo", "user", "notes", data.user, data.value]); pool.query(query, (err, response) => < if (err) < console.error(err); return; >// rows added console.log(response.insertId); >); > // timeout just to avoid firing query before connection happens setTimeout(() => < // call the function addRow(< "user": "Shahid", "value": "Just adding a note" >); >, 5000);

The mysql.format function will perform the query escape.

Selecting data in Table

Here is the code to query rows in the table.

const mysql = require('mysql'); const pool = mysql.createPool(< connectionLimit: 100, //important host: 'localhost', user: 'root', password: '', database: 'todolist', debug: false >); // query rows in the table function queryRow(userName) < let selectQuery = 'SELECT * FROM ?? WHERE ?? = ?'; let query = mysql.format(selectQuery, ["todo", "user", userName]); // query = SELECT * FROM `todo` where `user` = 'shahid' pool.query(query, (err, data) => < if (err) < console.error(err); return; >// rows fetch console.log(data); >); > // timeout just to avoid firing query before connection happens setTimeout(() => < // call the function // select rows queryRow('shahid'); >, 5000);

If you would like to add multiple rows in a single query, you can pass an array in the values. Like this.

let insertQuery = 'INSERT INTO ?? (. ) VALUES (. ); let values = [["shahid","hello"],["Rohit","Hi"]]; // each array is one row let query = mysql.format(insertQuery,["todo","user","notes",values]);

Updating data in Table

Here is the code to update the data in the table.

const mysql = require('mysql'); const pool = mysql.createPool(< connectionLimit: 100, //important host: 'localhost', user: 'root', password: '', database: 'todolist', debug: false >); // update rows function updateRow(data) < let updateQuery = "UPDATE ?? SET ?? = ? WHERE ?? = ?"; let query = mysql.format(updateQuery, ["todo", "notes", data.value, "user", data.user]); // query = UPDATE `todo` SET `notes`='Hello' WHERE `name`='shahid' pool.query(query, (err, response) => < if (err) < console.error(err); return; >// rows updated console.log(response.affectedRows); >); > // timeout just to avoid firing query before connection happens setTimeout(() => < // call the function // update row updateRow(< "user": "Shahid", "value": "Just updating a note" >); >, 5000);

Deleting Rows in the table

Here is the code to delete a row from the table.

const mysql = require('mysql'); const pool = mysql.createPool(< connectionLimit: 100, //important host: 'localhost', user: 'root', password: '', database: 'todolist', debug: false >); function deleteRow(userName) < let deleteQuery = "DELETE from ?? where ?? = ?"; let query = mysql.format(deleteQuery, ["todo", "user", userName]); // query = DELETE from `todo` where `user`='shahid'; pool.query(query, (err, response) => < if (err) < console.error(err); return; >// rows deleted console.log(response.affectedRows); >); > // timeout just to avoid firing query before connection happens setTimeout(() => < // call the function // delete row deleteRow('shahid'); >, 5000);

Calling MySQL Stored Procedure Using Node

When a SQL query run in order to retrieve some data from the MySQL database, MySQL executes that query and returns the requested data, and if our system requires querying the same data regularly we have to write over and over again multiple times, so to solve that problem stored procedure comes into existence. A store procedure can store SQL statements in the MySQL server which can be directly run by calling that stored procedure.

You can also call a stored procedure directly using Node.js. If you don’t have stored procedures created in MySQL, you can refer to the code below to do the same.

CREATE PROCEDURE `getAllTodo`() BEGIN SELECT * FROM todo; END$$

Here is the code to call this stored procedure from the Node.js code:

const mysql = require('mysql'); const pool = mysql.createPool(< connectionLimit: 100, //important host: 'localhost', user: 'root', password: '', database: 'todolist', debug: false >); function callSP(spName) < let spQuery = 'CALL ??'; let query = mysql.format(spQuery, [spName]); // CALL `getAllTodo` pool.query(query, (err, result) => < if (err) < console.error(err); return; >// rows from SP console.log(result); >); > // timeout just to avoid firing query before connection happens setTimeout(() => < // call the function // call sp callSP('getAllTodo') >, 5000);

Conclusion

In this tutorial we have learned how to use MySQL in Node.js which we have done using the npm mysql module, we have also learned connection pooling and execute SQL queries directly from Node.js to insert data into tables, query data in tables, update and delete data in the table and finally, we have learned to call MySQL stored procedure using Node.js. Node.js supports all types of databases but it works very well with MySQL, so don’t forget to try it after reading this tutorial.

Источник

MySQL examples in Node.js

If you’re integrating your Node.js service with MySQL, you probably want to execute queries.

I’ve seen a fair bit of ugly code to do this, often using callbacks. I thought I would share some of my own patterns here. Maybe this is useful to others to read.

I don’t typically use ORM’s. If you’re already happy with sequalize, this article is probably not for you.

Prequisites

The most popular NPM package for MySQL is mysql, but I use the mysql2 package instead.

The mysql2 package is also very popular, but has a few advantages. The advantage I care about is support for promises. The authors of this package have actually teamed up with the authors of the mysql package to reduce double efforts, and was made to be compatible with mysql so for many people it’s a drop-in replacement.

Creating a pool

const mysql = require('mysql2/promise'); const pool = mysql.createPool( host: 'localhost', user: 'root', database: 'test', waitForConnections: true, connectionLimit: 10, queueLimit: 0 >); 

A pool manages multiple connections. I create a pool once per node.js server.

Note: if you are migrating from the mysql to mysql2 package, you probably don’t want to require mysql2/promise . You can instead require mysql2 to get full backwards compatibility and use the promise() functions to get access to promisified versions of the APIs.

Note2: mysql2 uses the utf8mb4 charset by default. If you don’t know what this means trust that it’s the right choice. If you do know what this is, rejoice that somebody finally picked a sane default.

Executing a SELECT query

Generally I don’t need transactions for SELECT queries, because of this I can simply ask the pool to execute the query.

async function getBlogPost(id)  const result = await pool.query('SELECT * from posts WHERE >', [id]); if (result[0].length  1)  throw new Error('Post with this id was not found'); > return result[0][0]; > 

result[0][0] looks a bit weird, this is because the result of the query returns a tuple (array) with 2 items. The first item has the result of the query, and the second has the meta data. This subtle decision is my #1 complaint about the library because it makes a lot of things slightly uglier than they need to be.

So if we want just the first record of the result, you can access it with result[0][0] .

Whenever I write a function that should return exactly 1 item, I will either return an item or throw an error. I don’t return undefined or null .

A SELECT query that returns multiple records is more elegant:

async function getAllBlogPost()  const result = await pool.query('SELECT * from posts'); return result[0]; > 

Whenever I write a function that returns 0 or more items, this function always returns an array with 0 or more items. If the collection is empty, I return an empty array.

Note: sometimes there is a distinction between an empty collection or a collection not existing. If that distinction exists, I do throw an error in the latter case.

Executing an INSERT query

Generally when doing INSERT queries, most people use the following syntax:

INSERT INTO posts (title, body) VALUES (?, ?) 

MySQL also has a second system for inserting that is less popular, and looks more like an UPDATE statement:

INSERT INTO posts SET title = ?, body = ? 

The second syntax is the one I use. A big advantage is that the ‘value’ is close to the name of the field. If you ever had to count questionmarks, you know why this is nice.

Naively you can execute this query as follows:

async function insertPost(title, body)  await pool.query( 'INSERT INTO posts SET title = ?, body = ?', [ title, body ] ); > 

But there is a nicer way to do this:

async function insertPost(title, body)  await pool.query( 'INSERT INTO posts SET ?',  title, body > ); > 

If you are used to MySQL prepared statements, you might wonder why does this work?

The reason is that the placeholder ? gets special treatement when you pass objects or arrays to it.

Specifically, if you pass an array like this:

Источник