For this lesson, we need to have a complete MySQL Server installation so that we can run examples for the NodeJS application we create. Please go through\u00a0the MySQL Server Tutorial<\/a> lesson to understand how installation can be done with some simple commands to get started with the MySQL operations in a NodeJS application.<\/p>\n In this section, we will start by setting up a simple\u00a0NodeJS application\u00a0with important techniques to establish and close the connection with the server along with database connection pooling techniques which is very important to have in any production-grade application. In this lesson, we will make use of MySQL driver for NodeJS for communication between the two. Let us start by creating a new directory for our project:<\/p>\n New Directory<\/em><\/span><\/p>\n Once we have made this new directory, we can move inside it with the following command:<\/p>\n Change Directory<\/em><\/span><\/p>\n Now, we can make this directory a NodeJS project by creating a Initialise NodeJS<\/em><\/span><\/p>\n Once we run the above command, we will be presented with a series of questions which we can answer to finalise the creation of the JSON file. Here is what we get when we run the above command (we selected all the default options presented to us):<\/p>\n Now, here is the package.json<\/em><\/span><\/p>\n If your Installed MySQL Driver for NodeJS<\/em><\/span><\/p>\n Here is what we get when we run the above command:<\/p>\n Now, package.json<\/em><\/span><\/p>\n There is just one more thing which we need to do to start working on our app. We need to create a new database which will be used in the NodeJS application we create. Open MySQL shell and run the following command to create a new database Create Database<\/em><\/span><\/p>\n Once this is done, we can start connecting to the MySQL Server<\/a>.<\/p>\n There are multiple ways with which we can connect our application to the MySQL server on the local machine. For this, we create a new file known as Import MySQL<\/em><\/span><\/p>\n Now, once we have imported the MySQL into our file, we can connect to it by providing the connection parameters:<\/p>\n Connecting to MySQL<\/em><\/span><\/p>\n We only created a simple JSON and passed it to the Connecting to MySQL<\/em><\/span><\/p>\n If the user credentials are correct, the appropriate message will be printed to the console. Let us check the same by running the following command: Run Node app<\/em><\/span><\/p>\n We will see the following output once our application is able to connect to the MySQL server:<\/p>\n Now that we have an open MySQL connection, we will look at another simple NodeJS which can be used to close the MySQL connection in our app. We just need to call a single destroy function in our app to close the database connection, as shown in the following code snippet:<\/p>\n Run Node app<\/em><\/span><\/p>\n Let us run our NodeJS app again to see what happens:<\/p>\n Please note that once a connection the MySQL server is closed, it cannot be used again for any queries or prepared statements. If we try to do so, we will get an error.<\/p>\n In any application, once we focus on database connection pooling for productionisation of our application, it makes a huge difference because database connections are expensive<\/strong> to make and keep. The implementation for database connection pooling is very simple and we just need to pass one more parameter when we provide details for connection to be made:<\/p>\n Connecting to MySQL<\/em><\/span><\/p>\n The above code snippet makes sure that the database connection count never exceeds 10 in the application. Whenever we need to execute a query, we can call a single function to get a connection:<\/p>\n Get connection from pool<\/em><\/span><\/p>\n This way, we get a connection from the pool and simply release the connection once we are done executing any query so that connection can be made available in the pool again:<\/p>\n Release connection to the pool<\/em><\/span><\/p>\n Once we call the Finally, note that the connection pool will create connections lazily, which means that even when we set the connection limit to be 10, if we are using only 2 connections at a time, the pool will not create other 8 connections. Each connection will be made only when it is actually needed in the app.<\/p>\n Now we are ready to create some tables from our application directly once we have made a connection to the MySQL server in the NodeJS app we created in the last section.<\/p>\n For this, we need to create an SQL statement which will be executed each time the application is run. Please note that we don’t want to create a new table each time we run the app because that will raise an error. We only want to create a new table if it doesn’t exist already. Here is the SQL statement we will make use for the same:<\/p>\n Make new Table<\/em><\/span><\/p>\n Let us embed this SQL statement in the code so that is executed once a connection is open to the database:<\/p>\n Create new Table in code<\/em><\/span><\/p>\n With the above code, we are creating a new table where our data will be stored. The query function takes input as one parameters which are explained here and also provide a single callback function whose parameters are also described here:<\/p>\n Now that our code is ready, we can execute it again. Once we run the code, we will see the following output when we check what all tables exist in our database:<\/p>\n In the final section of our tutorial, we will show how we can insert data into our table using the NodeJS application we created. We will follow 3 steps to do:<\/p>\n Let’s get started with these steps. We will create a new file and call it Connection to the MySQL<\/em><\/span><\/p>\n Now, we need to write an SQL statement which will be an Defining INSERT statement<\/em><\/span><\/p>\n Finally, we can insert the data and close the database connection:<\/p>\n Connection to the MySQL<\/em><\/span><\/p>\n We can now run the file now:<\/p>\n Run insert file<\/em><\/span><\/p>\n We will see the following data in our table once we have run the above file:<\/p>\n We were able to insert data into our table using the NodeJS application. Please note that we can perform any database operation just like what we did above, may it be a Delete or Update operation. We can even work on Express view which will help us to view all the data we have in our table into a nice UI view.<\/p>\n Now that we have some data in our database after the previous section code is complete, we can move to perform some update operations on that data. To make this more interesting, we inserted some more data in our Posts table and now, the data looks like:<\/p>\n We will follow 3 steps to update data in the Posts table:<\/p>\n Let’s get started with these steps. We will create a new file and call it Now, we need to write an SQL statement which will be an Defining UPDATE statement<\/em><\/span><\/p>\n We see that we are not setting the update and where values in the query directly because that is a security vulnerability we can avoid just by using Prepared statements. We can define what data needs to be substituted with the following definition:<\/p>\n Preparing statements<\/em><\/span><\/p>\n Note that the variables will be substituted in the order they appear in the SQL statement. Finally, we can update the data and close the database connection:<\/p>\n Connection to the MySQL<\/em><\/span><\/p>\n We can now run the file now:<\/p>\n Run update file<\/em><\/span><\/p>\n We will see the following data in our table once we have run the above file:<\/p>\n Please note that SQL Injection<\/a> is a pretty common vulnerability we need to avoid in our application. Finally, if you are sill interested in exploring more, read Using SQL Injection Vulnerabilities to Dump Your Database<\/a> for much more information and knowledge.<\/p>\n One of the most important operation in SQL is DELETE operation. This is what we will be demonstrating in this section by executing one of the DELETE statement in the application. Note that all of the other steps remains the same like making a connection, executing the query and closing the connection after use, so we won’t be redundant here and show only the queries we execute here:<\/p>\n Run update file<\/em><\/span><\/p>\n This code will also print the number of rows which were deleted once this query is executed and it is really helpful to understand if any data was affected with the provided query.<\/p>\n It is possible to run a stored procedure from inside of a NodeJS application. A stored procedure is a very good way to execute a number of SQL statements when you want to do a complex operation which needs more than a couple of statements needed to be executed sequentially. We can consider a stored procedure as a program written in SQL.<\/p>\n Again, the procedure to run a stored procedure is same as well, make an SQL connection (or pick one from the pool), prepare and execute the stored procedure and finally, close the connection (or release it into the pool). Read more about stored procedures in MySQL Stored Procedure Tutorial<\/a> and also how to use them in a Java application with Java Stored Procedures in Java DB<\/a>.<\/p>\n We can define a simple SQL statement to call a stored procedure which you have defined in the SQL DB as:<\/p>\n Call Stored procedure SQL Statement<\/em><\/span><\/p>\n Here, we define a stored procedure defined as In this lesson, we looked at a very simple yet effective example of setting up a connection to a MySQL server from a NodeJS application. We started with important techniques to establish and close the connection with the server along with database connection pooling techniques which is very important to have in any production-grade application based on any framework and not just NodeJS. We encourage you to study more about NodeJS and MySQL to run more complex examples which involve Prepared statements and result sets. One of the more important things which can be done inside a NodeJS application is calling stored procedures, define triggers<\/a> and adding full-text search capabilities<\/a> to the application.<\/p>\n MySQL Stored procedures are a very powerful tool with very high performance (usually) but the bad thing is that they can grow very large very easily and quickly once your application starts to grow. The main database consistency should be managed at the application level wherever and whenever possible. Still, MySQL Stored procedures make a very good companion if you are a database administrator and just want to make things consistent without relying on too much code because after all, the performance of a Stored procedures is very high because database operations are very fast on the database server itself. Don\u2019t forget to check the examples of using Java with\u00a0SQL: JDBC Best Practices Tutorial<\/a>\u00a0and\u00a0Java JDBC ResultSet Example<\/a>. These lessons explain how to effectively use SQL commands with Java &\u00a0JDBC drivers<\/a>. There are many more examples present on MySQL which can be studied for a deeper understanding of the database. Finally, do read about some of the most popular MySQL functions<\/a> which you will find interesting and useful in your daily life when you deal with MySQL. There are many more examples present on MySQL which can be studied for a deeper understanding of the database.<\/p>\n This was an example of NodeJS Framework and MySQL database.<\/p>\nTable Of Contents<\/h3>\n
\n
\n
2. Setting up NodeJS application<\/h2>\n
mkdir jcg-node-mysql\r\n<\/pre>\n
cd jcg-node-mysql\r\n<\/pre>\n
package.json<\/code> file inside it. If you want to understand the NodeJS installation process and how it works, deep dive into NodeJS, I recommend you download the NodeJS minibook by JavaCodeGeeks<\/a>. Run the following command so that the directory is initialised as a NodeJS app and the corresponding package.json<\/code> file is created in it:<\/p>\nnpm init\r\n<\/pre>\n
![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/jcg-nodejs-init-1.png\")
<\/a>package.json<\/code> file which we got once we created the above command:<\/p>\n{\r\n \"name\": \"jcg-node-mysql\",\r\n \"version\": \"1.0.0\",\r\n \"description\": \"NodeJS app for SystemCodeGeeks\",\r\n \"main\": \"index.js\",\r\n \"scripts\": {\r\n \"start\": \"node server.js\"\r\n },\r\n \"dependencies\": {\r\n \"express\": \"^4.16.1\"\r\n },\r\n \"author\": \"Shubham Aggarwal <sbmaggarwal@gmail.com>\",\r\n \"license\": \"ISC\"\r\n}\r\n<\/pre>\npackage.json<\/code> file doesn’t look like above, do not worry, you can copy from above and paste it in your package.json<\/code> file. Now we can install mysql driver for NodeJS so that we can use MySQL packages in the app as well. Run the following command so that MySQL driver is installed:<\/p>\nnpm install mysql\r\n<\/pre>\n
![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/jcg-mysql-driver-nodejs.png\")
<\/a>package.json<\/code> file would have changed to the following with an extra MySQL dependency:<\/p>\n{\r\n \"name\": \"jcg-node-mysql\",\r\n \"version\": \"1.0.0\",\r\n \"description\": \"NodeJS app for SystemCodeGeeks\",\r\n \"main\": \"index.js\",\r\n \"scripts\": {\r\n \"start\": \"node server.js\"\r\n },\r\n \"dependencies\": {\r\n \"express\": \"^4.16.1\",\r\n \"mysql\": \"^2.16.0\"\r\n },\r\n \"author\": \"Shubham Aggarwal <sbmaggarwal@gmail.com>\",\r\n \"license\": \"ISC\"\r\n}\r\n<\/pre>\njcg_schema_nodejs<\/code> which we will use:<\/p>\nCREATE SCHEMA jcg_schema_nodejs;\r\n<\/pre>\n
2.1 Connecting to MySQL from NodeJS<\/h3>\n
database.js<\/code> in the project home directory and add the following code:<\/p>\nlet mysql = require('mysql');\r\n<\/pre>\nlet connection = mysql.createConnection({\r\n host: 'localhost',\r\n user: 'root',\r\n password: 'qweRTY1!',\r\n database: 'jcg_schema_nodejs'\r\n});\r\n<\/pre>\ncreateConnection<\/code> function which accept credentials for connection and returns a brand new connection object for MySQL which we can use later to execute queries. Now, even though we have a connection object but it doesn’t actually represent a connection unless we call connect<\/code> on it which will actually create a connection which will be persisted across the lifecycle of our program:<\/p>\nconnection.connect(function(err) {\r\n if (err) {\r\n return console.error('error: ' + err.message);\r\n }\r\n \r\n console.log('Connected to the SystemCodeGeeks MySQL server.');\r\n});\r\n<\/pre>\nnode database.js\r\n<\/pre>\n
![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/mysql-connect-nodejs.png\")
<\/a>2.2 Closing connection to MySQL<\/h3>\n
connection.end(function(err) {\r\n if (err) {\r\n return console.log('error:' + err.message);\r\n }\r\n console.log('Database connection is closing.');\r\n connection.destroy();\r\n console.log('Database connection is closed.');\r\n });\r\n<\/pre>\n![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/mysql-connect-nodejs-close.png\")
<\/a>2.3 Connection Pooling<\/h3>\n
let connection = mysql.createConnection({\r\n connectionLimit: 10,\r\n host: 'localhost',\r\n user: 'root',\r\n password: 'qweRTY1!',\r\n database: 'jcg_schema_nodejs'\r\n});\r\n<\/pre>\npool.getConnection(function(err, connection) {\r\n \/\/ execute query here\r\n \r\n});\r\n<\/pre>\npool.getConnection(function(err, connection) {\r\n \/\/ execute query here\r\n connnection.release();\r\n});\r\n<\/pre>\nrelease<\/code> function, we release the connection to be made available in the pool again.<\/p>\n3. Creating Table in NodeJS<\/h2>\n
CREATE TABLE IF NOT EXISTS Posts (\r\n id INT PRIMARY KEY,\r\n title VARCHAR(64) NOT NULL,\r\n author_id INT NOT NULL\r\n);\r\n<\/pre>\n
\/\/ connect to the MySQL server\r\nconnection.connect(function(err) {\r\n if (err) {\r\n return console.error('error: ' + err.message);\r\n }\r\n \r\n let createPosts = `CREATE TABLE IF NOT EXISTS Posts (\r\n id INT PRIMARY KEY,\r\n title VARCHAR(64) NOT NULL,\r\n author_id INT NOT NULL\r\n )`;\r\n \r\n connection.query(createPosts, function(err, results, fields) {\r\n if (err) {\r\n console.log(err.message);\r\n }\r\n });\r\n \r\n connection.end(function(err) {\r\n if (err) {\r\n return console.log(err.message);\r\n }\r\n });\r\n });\r\n<\/pre>\n\n
![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/nodejs-create-table-1.png\")
<\/a>4. Inserting Data in Table<\/h2>\n
\n
insert.js<\/code>. This file will contain code used to insert new data in our table we created in the last section. We need to start with our first step and define the connection parameters in this file as well:<\/p>\nlet mysql = require('mysql');\r\n\r\nlet connection = mysql.createConnection({\r\n connectionLimit: 10,\r\n host: 'localhost',\r\n user: 'root',\r\n password: 'root',\r\n database: 'jcg_schema'\r\n});\r\n<\/pre>\nINSERT<\/code> statement for the table Posts<\/code> we defined:<\/p>\nlet insertQuery = \"INSERT INTO Posts VALUES (1, 'Introduction to Python', 23)\";\r\n<\/pre>\n
\/\/ execute the insert query\r\nconnection.query(insertQuery);\r\nconnection.end();\r\n<\/pre>\n
node insert.js\r\n<\/pre>\n
![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/nodejs-insert-statement.png\")
<\/a>5. Update Data in Table<\/h2>\n
![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/mysql-current-data.png\")
<\/a>\n
update.js<\/code>. This file will contain code used to update existing data in our table we created in the last section. We need to start with our first step and define the connection parameters in this file as well:
\nConnection to the MySQL<\/em><\/span><\/p>\nlet mysql = require('mysql');\r\n\r\nlet connection = mysql.createConnection({\r\n connectionLimit: 10,\r\n host: 'localhost',\r\n user: 'root',\r\n password: 'root',\r\n database: 'jcg_schema'\r\n});\r\n<\/pre>\nUPDATE<\/code> statement for the table Posts<\/code> we defined. We will update the Author ID for posts whose author ID was previously 1 and change the same to 19 (or any integer value):<\/p>\nlet updateQuery = \"UPDATE Posts SET author_id = ? WHERE author_id = ?\";\r\n<\/pre>\n
let author_ids = [19, 1];\r\n<\/pre>\n
connection.query(updateQuery, author_ids, (error, results, fields) => {\r\n if (error){\r\n return console.error(error.message);\r\n }\r\n console.log('Rows affected:', results.affectedRows);\r\n});\r\n<\/pre>\nnode update.js\r\n<\/pre>\n
![\"MySQL](\"http:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2018\/11\/mysql-updated-data.png\")
<\/a>6. Delete Data from Table<\/h2>\n
\/\/ DELETE statement\r\nlet deleteQuery = `DELETE FROM Posts WHERE author_id = ?`;\r\n \r\n\/\/ delete the post with id 19\r\nconnection.query(deleteQuery, 1, (error, results, fields) => {\r\n if (error)\r\n return console.error(error.message);\r\n \r\n console.log('Deleted Post(s):', results.affectedRows);\r\n});\r\n \r\nconnection.end();\r\n<\/pre>\n7. Calling a Stored Procedure from NodeJS application<\/h2>\n
let sql = `CALL findPosts(?)`;\r\n<\/pre>\n
findPosts<\/code> in SQL DB. Please note that this query can be executed just like any other SQL query in which we could have passed parameters and this will be treated as prepared statements. I do not highly recommend using SQL procedures personally due to some of the limitations they present:<\/p>\n\n
8. MySQL NodeJS Example – Conclusion<\/h2>\n
9. Download the Source Code<\/h2>\n