How to Query A MySQL Database with NodeJS
In this tutorial, you'll learn how to query a MySQL database with NodeJS and the MySQL library from the npm registry.
Install
First, you'll need to download and install NodeJS. The minimum version requirement for this library is v0.6.
Next, you'll need to install the MySQL library via the command line:
npm install mysql
Code Examples
Query a database
var mysql = require("mysql");
var connection = mysql.createConnection({
host: "localhost",
user: "[username]",
password: "[password]",
database: "[database_name]"
});
connection.connect();
connection.query("SELECT FirstName, LastName FROM Employees",
function(error, results, fields) {
if (error) {
throw error;
}
console.log("There are " + results.length + " employees.");
}
);
connection.end();
First, we include the MySQL library and assign it to the mysql
variable. Second, we create a connection to the database and assign it to the connection
variable. Within the mysql.createConnection()
method, replace the database name and credentials in the object passed with the correct values.
Next, we'll connect to our database and run a query against our hypothetical Employees
table. You can run any query you need that matches your table structures. The processing halts when an error is thrown. Otherwise, the console logs the number of employees returned in the record set.
After running a query and processing the results, it's very important to close your existing database connections. Too many concurrent connections can cause further query errors, which can be especially troublesome in production environments. In that case, the MySQL service would either need to be restarted or the previous connections would need to time out before a new connection can be established.
Loop over query results
To loop over the results of the same query, you could use the following code:
connection.query("SELECT FirstName, LastName FROM Employees",
function(error, results, fields) {
if (error) {
throw error;
}
for (var i = 0; i < results.length; i++) {
console.log("Employee: " + results[i].FirstName + " " + results[i].LastName);
}
}
);
Conclusion
This tutorial showed you how to install NodeJS and the MySQL dependency, set up a database connection, query a database, and loop over the results.
Comments
There are no comments yet. Start the conversation!