This lesson shows you how to connect to a MySQL
database using the Express mysql module.
You'll also learn how to perform and process the results
of SELECT queries.
Pre-Requisites
Before doing this lesson, make sure you have an
understanding of Basic
Database Concepts and that you have access to a
MySQL database server. If you need to install one
locally, I have instructions for
Installing
MySQL Server on your local machine.
Express comes with a module you can import to
your programs so you can access MySQL databases
with your Node.js code. This module will need
to be installed along with Express and anything
else your project needs:
npm install express mysql -s
Installing the "mysql" package also installs the
MySQL database driver that's needed to communicate
with the database server. Your code is written in JavaScript.
But the database server speaks in a different language.
A database driver takes your JavaScript commands and
translates them into something the database server can
understand. Regardless of what type of database you are
using, you need a driver for that specifyc kind of
database. For example, if you were using Mongoose/MongoDb,
you'd use the "mongodb" driver, instead.
You can use the MySQL module and its objects/functions
by importing it:
const mysql = require("mysql");
To use MySQL in Express, you need to perform the following
steps:
Make a connection to the database server.
Perform a query.
Disconnect from the database server.
You should always disconnect from the databas server
as soon as you're done. Connections cost resources, and
too many connections can slow things down for other users.
You should never leave your connections open while
they're not being used.
Connecting to the Database
We connect to the database using the
mysql.createConnection() function.
This function accepts an object that contains properties
that configure the database connection. These properties
include:
host: typically this is "localhost", it's the
database server host name
user: the username you want to use to
connect. Note that this should NEVER be your database
root/admin account! You should have a separate user
set up for accessing the database via a web page, and
this account should have limited permissions.
password: this is the password for the account
you are using to connect to the database
database: the database you want to "use"
while this connection exists.
Once you create the connection object with
.createConnection(), you can open
the connection with the connection.connect()
function. The connect() function
accepts a callback that executes when the connection
is being made. The callback contains an error
parameter that only exists if there was an error
while opening the connection. For example:
A single connection is a blocking
operation: when you perform a query, the program waits
until it receives the results. The connection remains open until
it is closed (either programmatically or by the server). The
program can't perform other queries on the connection while the
current query is being executed. It's similar to a situation a
friend of mine was in once: she was driving on a highway and
had just passed the exit before the one she wanted, which was
still several kilometers away. An accident occurred before she
could get to her exit and the accident blocked all traffic on
the highway. There was no way to turn back and no way to move
forward: her single route to her destination was blocked,
and she was unable to proceed to her destination until the
traffic accident was cleared (which took several hours during
which she was stuck where she was). Database operations are
similar: since they are blocking operations, no other
operations can proceed until the first operation has been
finished and cleared away.
Connection Pooling
Connection pooling solves the blocking issue: A
connection pool is a collection
of connections. When the database needs a connection, it requests
one from the pool and uses it. While the program is using
that connection, it might need to perform another query, so it
grabs an available connection and uses that one, while the
other connection is busy. When a connection is finished with
its operation, it is returned to the connection pool and becomes
available for the next database operation. If a connection is requested and
none are available, the program can create new connections as
long as the connection limit has not been reached.
The program can use multiple connections
in this way without blocking the rest of the program execution.
It's like having multiple routes to a destination that you could
choose from. If there were some side roads between exits on the
highway, my friend and the other drivers could have taken any
one of those to get away from the blocked traffic, using
these alternate routes to get to their destinations.
Connection pooling provides better performance in applications
that have many users using the application at one time, and
many database operations being performed simultaneously.
To create a connection pool instead of a single connection,
you use the mysql.createPool() function. This
function takes a single object with properties that configure
the connection pool. This object is the same as the one you
would pass to createConnection() - you provide the
hostname, user credentials, and database name - but you can
also specify options such as the connectionLimit,
which is the maximum number of connections allowed in the
connection pool. There are other properties you can explore
in the connectionPool()
documentation.
Once you have an open connection, you can perform queries
and then process the results of those queries.
Queries on a Single Connection
You can perform queries on a single connection object or on a
connection pool object. To execute a query on a single
connection, use the connection.query() function.
This function takes an SQL statement as a string and a callback.
The query() function will execute the SQL query,
and then execute the callback after the query has been executed.
We'll go over the details of the query()
function and it's arguments/callback in the next
section, Processing the Results of
A SELECT Query.
Example:
const host = "localhost", dbname = "mynodedb";
const connection = mysql.createConnection({
host: host,
user: 'guest',
password: 'tunafish',
database: mynodedb
});
connection.connect((error) => {
if (error) {
console.error(`error connecting to db: ${error)`);
} else {
console.log(`successfully connected to ${host}/${dbname}`);
}
});
connection.query("SELECT * FROM things;", (error, results, fields) => {
// process results of query or error
});
connection.end();
Queries Using a Connection Pool
You can perform queries with a connection pool
using the connection pool's query() function.
This query() function works in a similar
way to the same function you use on a single connection,
except that it operates on a connection that is chosen from
the connection pool.
First, after you create your connection pool, you have to
get a connection object from the pool. Then, after you perform
the query, you must release the connection back into the
connection pool.
We obtain a connection from the pool using the
pool.getConnection() function.
This function accepts a callback that executes the
query and processes the results. The callback
function accepts 2 arguments:
error - the error object, if an error occurred
trying to obtain a connection from the pool
connection - the connection object on which you
perform a query. This connection object is the one that
is chosen from the connection pool, so it's automatically
populated with the chosen connection object.
When you're done with the connection, the callback should call
connection.release() to release the connection back
into the pool so that it's available for a different database
operation.
Example:
let pool = mysql.createPool(...);
pool.getConnection((connectionError, connection) => {
if (connectionError) { // not connected
console.log(connectionError);
} else {
// Use the connection
connection.query('SELECT something FROM sometable', (queryError, results, fields) => {
// process the query results here
// When done with the connection, release it.
connection.release();
// Handle query error after the release.
if (queryError) {
console.log(queryError);
}
// Don't use the connection here, it has been returned to the pool.
});
}
});
pool.end();
In the example, I named the error paramters connectionError
and queryError so that it's easier to tell the
difference.
If your operation contains only one single query, you can
use the shortcut pool.query() function:
This is a shortcut for the whole
pool.getConnection()/connection.query()/connection.release()
process. pool.query() will handle the process of
obtaining a connection, executing your query, and then
releasing the connection after the query has executed:
pool.query('SELECT something FROM sometable', (error, results, fields) => {
if (error) {
console.log(error);
}
... code to process results of query
});
However, if you want to execute multiple queries in order
on the same connection (e.g. insert a record, then retrieve
an updated set of records), use the first technique.
We'll talk about more details of the query()
function and how to process the query results in the
next section, Processing Results of a
SELECT Query.
Closing the Connection
When you have finished with a connection, you should close it.
An open connection takes up resources, so if you're not using
it, an open connection might slow down your application.
Closing a Single Connection
The connection.end() function will close a
single connection. It can take a callback that executes when
the connection closes. There is an error parameter
that contains an error object if
there is an error closing the connection.
Closing a Connection Pool
The pool.end() function will close a
connection pool. It can take a callback that executes when
the connection closes. There is an error parameter
that contains an error object if
there is an error closing the connection.
With a connection pool, you would generally only close it
when the application is finished or shut down: individual connections
are released either programmatically or automatically,
so there is no need to programmatically close or end a pool
after queries are executed.
In both cases, the end() function will
make sure all pending queries have finished executing.
Processing Results of SELECT Queries
Earlier we saw that you can use the query()
function to execute a basic SELECT query on a database.
The query() function has 3 arguments:
The query() callback has 3 parameters:
error - an error object, if an error occured
results - the results of the query
fields - information about the fields of the rows
returned, if any rows/records were returned in the query
results
The results parameter contains the actual
results of the query that was executed, and it has some
useful properties:
insertId: the primary key of a row that
was just inserted. This only works for a primary key field that is an
auto increment field.
affectedRows: the number of rows affected by an
INSERT, UPDATE, or DELETE query.
changedRows: the number of actual rows that were
changed after an UPDATE query. For example, an update may update
a record where nothing was changed, so that wouldn't be counted.
The results parameter also contains an
object array with the actual rows that were returned
in a SELECT query. You could iterate through this array
and process the results however you needed to:
results.forEach( (row) => {
console.log(`${row.name} lives in ${row.city}`);
});
This example assumes that the query results included fields
for the "name" column and the "city" column.
Paramterized Queries
Parameterized queries are used to prevent SQL Injection attacks. These malicious attacks
happen when a hacker tries to insert harmful SQL code inside
an input field on an HTML form. For example, they coudl add
SQL code to delete records or even a SELECT statement to
retrive all the records in a table. They might do this to
destroy data, or to view contents of a table with sensitive information
such as logins, email addresses, and password hashes.
You should use a parameterized query whenever any part
of your query is replaced with data that comes from an
unreliable source (e.g. a user). For example, imagine you
had a form that allowed a user to search for records in a
table. You would have a form with a search input box.
You then might write an SQL query such as:
let sql = "SELECT * FROM tableName WHERE fieldName LIKE %" + searchInput + "%;"
where "searchInput" is the value captured from the
search input field on the form. You should NEVER build your
SQL queries in this way. What happens if a hacker types into
the search field a value like "'';DELETE FROM tableName;"
This will cause your sql variable to contain the value: SELECT * FROM tableName WHERE fieldName LIKE %'';DELETE FROM tableName;%;
The database server will see 3 queries: the first one, which
will find all the records in the table, the second one, which
will delete all the records from the table, and the last one which
would just be invalid. But the first two will actually execute,
resulting in the destruction of all the table's records!
Parameterized queries allow you to use placeholders
where input values (or other values from unreliable sources) would go.
The program will then send the values for those paramters separate
from the query, and it will automatically escape any special SQL
characters (like the semi-colon, quotes, etc) before the final
query is assembled using the parameter values. This prevents
SQL Injection attacks.
To use a paramterized query, you use the query()
function with 3 arguments:
sql: your SQL statement, containing parameter
markers where you want the input values to go. In
express-mysql, the parameter marker is the question mark (?) symbol.
values: an array of the values you want to use
to replace the parameter markers. These should be in the same order
as the markers. For example, array item #0 will replace the first marker
(?) that appears in the SQL statement; item #1 will replace the second
marker (?) that appears in the SQL statement; etc.
callback: the callback function that processes
the query results - this is the same callback used in the
regular query() function.
For example, if you wanted to perform a SELECT query that searched
a books table for books with a specific author that were
greater than a certain price, you could use this code:
connection.query(
'SELECT * FROM `books` WHERE `author` = ? AND price > ?;',
['Don Keigh', 100],
(error, results, fields) => {
// process query results
}
);
If your query has only one parameter, you can pass the single
value for the parameter without using an array:
connection.query(
'SELECT * FROM `books` WHERE `author` = ?',
'Don Keigh',
(error, results, fields) => {
// process query results
}
);
Of course, if your SQL query has no need for parameters,
then you can just execute the query normally as shown in the
previous section: there's no need for a parameterized query
if your query doesn't require any paramters.
Basic Example
Let's create a very basic example that retrieves the records
from the Country table of the World database (this database comes
with MySQL if you selected to include it during installation)
and displays them in a table. Start up a new project (I'm calling
mine /basicdb) and add sub-directories for /controllers, /views,
and /public/css, along with an app.js file that uses Strict Mode.
In app.js, add the standard basic stuff to create an express
application using EJS and handle static files:
Add a CSS file to your /public/css directory. You'll be using
this in your view.
Add a view to the /views directory called countries.ejs.
Add the minimal HTML, an appropriate <header> and <footer>,
and a <main> element. The <main> element will contain
a table that displays the name, country code, continent, and
region for each country in the country table of the World database,
so you can add the code to start your table with four
columns:
Now add a dbController.js file to your
/controllers directory. Add Stict Mode and import the "mysql"
module:
"use strict";
const mysql = require("mysql");
Now we'll create and expose a function that
performs a query to select all countries from the
World database's Country table and send the
results to the countries.ejs view.
First, let's create a database connection pool, using the
guest account you added to your World database (if you
haven't done this, go back to the MySQL
Installation instructions and add a guest account.
You should NEVER use the root/admin account for your web
application).
Notice that we create our connection pool only once when
the script loads. In reality, you would put that block
of code inside a separate module and then import it
into this controller module. But for now this is fine.
Also, we don't need to worry about closing/ending
the pool with pool.end() - the connection pool will
manage the opening and closing of connections for us!
The only reason why you would close the pool is if
the application were to shut down.
Now we can make a connection and perform our query. Add
some code to do this inside the getCountries() function:
Here we are using pool.query() since we only have one
query to execute. This is easier than pool.getConnection,
connection.query(), and connection.release(). All of that
is handled for us with pool.query().
Once the query successfully executes, we store the query
results in a local variable called "countries". This local
will be available in the countries.ejs file that will be
rendered.
Note that normally you would only use the database controller
for the database operations. The page rendering should be done
by a different controller. But for that, you need to use
sessions to pass the data
from the database controller to the rendering controller.
I'll post a version of the files for the Sessions version
of this program at the end of this section.
Now all that's left is to update the app.js and the countries.ejs.
Add the handler to the app.js that routes any GET-requests
to "/countries" to our getCountries() function in the dbController:
app.get("/countries", dbController.getCountries);
Now update the countries.ejs. Under the heading row, add the
EJS and HTML code to render a row with four cells for
each country:
You should now be able to run your program and see it working!
Make sure you npm init, then make sure you
install all the necessary modules: npm install express ejs mysql -s
Then you can run the application.
Test it in your browser: localhost:3000/countries
Output from the application
You should see your page with a table of all the country
data. Feel free to experiment with different SELECT statements.
Perhaps you'd like to sort the data, or display different columns?
Or display only countries in a certain region or with a certain
characteristic (you can explore the World database in the
MySQL Command Line Tool).
Add an index.ejs page to the Countries example: it should include
a form with a single drop-down list that is populated with
only the continents from the Country table. Add a function
to the database controller that performs a query to retrieve
the continents (SELECT DISTINCT continent FROM country order by continent;)
Use the same countries.ejs view for the output. You'll need to
add code to the database controller for this: if there is a
list of continents, perform a paramterized query that selects
only countries for that specific continent. Otherwise, perform
the regular query to get all countries.
For a challenge, include the number of records below the
table.
Tips: Remember that app.use(), app.get(), and app.post() can take
multiple callbacks: they will execute in order as long as you remember
to include next/next() where appropriate. Also, remember that the
/countries endpoint will execute for both GET and POST requests,
so don't make things inefficient: you can just use app.use(),
since that will handle any request type.
Lastly, remember that when you retrieve the continent records,
they're still in an array of rows: each row has only one column,
"Continent". So you'll still have to treat it like an array of
rows in your EJS, and access each row's "Continent" property.
The form for selecting a continentThe output after submitting the form (top part of table)The output after submitting the form (bottom part of table)
