How to Use Node.js MySQL Events

Nearly every major programming language, from PHP to Java, performs operations using MySQL. The database is a natural companion for Node.js — when used together, developers can achieve levels of efficiency that were previously inaccessible.

Throughout this tutorial, you will learn to use technologies in tandem, along with events. If you are enrolled in a Node.js class in San Francisco or another major tech hub, you can use this as a reference when you need a refresher on the source material.

Getting Started: The Connection Between Node.js and MySQL

Node.js has registered a wide variety of popular packages, or NPM, that can be relied on when coding for production. One popular driver, node-mysql, lets you use Node.js and MySQL in conjunction.

In this article, we will give you some sample code to help you hit the ground running and show you code for production. 

Practicing With Code

To start, use this example code:

Install dependencies by using npm install.

Then, use this example code to perform an SQL query:

Double-check that you started MySQL on the default port and ensure that the parameters were changed in the code. Then, run this code using node file_name.js.

Code for Production

While the above code helps you get your feet wet, it is not really suited for production. In production scenarios, you may have thousands of users operating simultaneously, which can easily turn into an overwhelming number of MySQL queries in which the above code simply will not work. For example, if you change the code to add Express routes, you get:

Next, install Siege. If you are using Ubuntu, you can use apt-get install siege. Run the node and server with the following:

With the example code, you are running it for a standalone connection — there is only one user at a time. However, real-life coding with Node.js and MySQL is very different because you can receive hundreds or thousands of simultaneous connections in real-time environments, which requires a server capable of handling that kind of load. If your server is not able to keep up with the number of concurrent users, it needs to put them in a queue.

Connection Pooling With Node.js and MySQL

Use connection pooling to manage and reuse multiple connections by simply inputting the code to see whether it can handle more than one connection. Try it:

Then, use Siege to run the app by using node test.js and firing 10 concurrent users for one minute.

From there, your code should give a stable output. You can also use pool.query() to internally acquire the connection and release it whenever queries are executed, however, it is easy for web developers to forget to release acquired connections and accidentally create a bottleneck or database overload. You can see this in the following code:

Use this function for production since it can handle heavy payloads without a problem.

Executing Node.js MySQL Queries

Using Node.js with MySQL to execute queries is relatively simple.

Putting Rows into Tables

To add new rows to a table, use the following code:

Performing Query Escapes

Use the mysql.format function to perform a query escape. Next, query data in the table using the following:

Adding More Rows to Queries

Pass an array in the values if you want to add more than one row to a single query. For example:

Updating Data in Tables

To update the data in your table, use:

Deleting Rows in Tables

To delete a row from a table, use this code: 

Using Node.js to Call MySQL Stored Procedures

When using Node.js with MySQL, you also have the option of calling a stored procedure. Use the code below if you do not have a stored procedure in MySQL.

If you need to call the stored procedure from the code:

Next Steps: Using Siege to Test Your Server

If you want to test your server under some simulated real-world pressure, Siege is a great option. The above code creates a 100 connection limit, it’s natural to wonder whether anything above 100 concurrent connections will cause the code to break. To find out, run this test:

As you can see, firing 1,000 concurrent users for a minute does not break the code. As long as your MySQL server is configured to handle that amount of traffic in one socket, the code will continue to manage concurrent connections. Your server will only serve 100 connections at a time, but the remainder will be managed in the queue, allowing the code continue running without a problem.

Getting Comfortable Using Node.js and MySQL in Conjunction

Becoming proficient using Node.js with MySQL usually takes time and practice for most developers, which is why many people choose to enroll in a Node.js class or coding bootcamp. 

*Please note, these articles are for educational purposes and the topics covered may not be representative of the curriculum covered in our boot camp. Explore our curriculum to see what you’ll learn in our program.