MySQL is one of the most widely used databases on the web. You can access this database with almost all major programming languages. The connection of Node.js to MySQL can be realized via a special database driver.
MySQL has already proven itself as a database for many years, even in very large applications. Not only does the system have a simple server component, but it also allows you to run a database on multiple servers in a primary-secondary compound structure. The benefit of this is that you can distribute the requests across several systems and thus ensure resilience.
In addition, for very large data volumes, a database can be partitioned and distributed across multiple systems, which offers further opportunities in terms of performance improvements. MySQL has many other useful features, including triggers (functions that are executed when certain operations are performed), and transactions. A transaction is a group of operations that may only be performed in their entirety or not at all.
Another advantage of MySQL is that this database is available on a large number of different operating systems, such as Linux, Windows, and macOS. The availability and widespread use have led to a very active community that you can call on if you have any questions or problems. There are also forks of MySQL, such as MariaDB, which offer similar functionality.
One of the drawbacks of MySQL is that it can’t quite keep up with the big SQL databases such as Oracle or DB2 in terms of its feature set. However, the advanced development of the database is increasingly compensating for this disadvantage.
In general, there are two different approaches to connecting to a MySQL database. For one thing, there is a driver that directly implements the MySQL protocol and is written entirely in JavaScript. No other software is needed for this driver, and it can be used directly to work with a database.
Another variant of MySQL drivers is based on the MySQL client libraries. These have the advantage of being slightly more performant than drivers implemented entirely in JavaScript. However, they have the disadvantage that the MySQL client libraries must be installed on the system running the Node.js application.
MySQL in a Container
You don’t need to install MySQL on your development system. Especially for experimenting, running the database in a container is a viable alternative. But containers are also becoming increasingly popular for day-to-day development operations, as this approach allows both the configuration to be shared between developer systems and the container configuration to be versioned along with the rest of the application’s source code. You can launch the MySQL container using the command shown below.
docker run
--name mysql
-v db-data:/etc/mysql/conf.d
-e MYSQL_ROOT_PASSWORD=topSecret
-e MYSQL_ROOT_HOST=% -p 3306:3306
-d
mysql:latest
The docker run command creates a new container from the image named mysql:latest. The mysql image is officially provided by MySQL on Docker Hub (https://hub.docker.com/), which is downloaded by the command in the specified version—here :latest—that is, the latest version. The --name option assigns a name to the container so that it’s easier to manage and doesn’t need to be addressed by its ID. You can use the -v option to specify that a local directory is mounted as a volume in the container, so that the database files are located on the host system rather than in the container. The subsequent -e option sets environment variables for the container, which MySQL uses in this case to set the root password and allow the root user to log on from outside the container. Finally, you use -d to specify that the container should run in the background. When you execute the command, you’ll get a cryptic-looking character string as a result. This is the ID of the container, which you can also use to manage the container in addition to the name. Once you’ve launched the container, you can use MySQL on your system.
The table below contains an overview of the most important commands for the Docker command line. A comprehensive list of all commands, including descriptions and all available options, can be found in the official documentation at https://docs.docker.com/engine/reference/commandline/cli/.
Installation
The MySQL driver for Node.js is available as an npm module and can be installed from the command line using the npm install mysql2 command. The driver implements the MySQL protocol entirely in JavaScirpt, so it doesn’t require you to install any other external libraries or perform any compilation steps during the installation process.
Once the installation is complete and you have a MySQL database available, you can access the MySQL database from your application.
Database Structure
To reproduce the examples in this chapter, you need a running instance of a MySQL database with a data structure to work on. The listing below contains the required SQL commands to create a database with the two tables, Movies and Ratings, and to fill the database with initial values. Once you’ve executed these statements, you can start implementing the sample application in the next step. To create the structure, you must either use the mysql command locally on the console of your system, or, if you’re running the database in a Docker container, you should use the docker exec -it mysql mysql -p command to execute the mysql command in the container named mysql. Then you can execute the statements shown below.
CREATE DATABASE `movie-db`;
USE `movie-db`;
CREATE TABLE `Movies` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
` year` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `Movies` (`title`, `year`) VALUES
('Iron Man', 2008),
('Thor', 2011),
('Captain America', 2011);
Establishing the Connection
In any application that uses a database, before using this database, a connection must first be established through which the commands are sent to the database system and the information from the database can flow back to the application. The application is based on source code from the book Node.js: The Comprehensive Guide, and it uses the Pug template engine. The database connection is established in the movie/model.js file. Below shows how you can do this in the source code.
import mysql from 'mysql2/promise';
const connection = await mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'topSecret',
database: 'movie-db',
});
await connection.connect();
export async function getAll() {}
export async function get(id) {}
export async function remove(id) {}
export function save(movie) {}
After including the promise interface of the mysql2 module, you can use the createConnection function to configure the connection and then use the connect method of the connection object to connect to the database. Much of the driver’s functionality in this case is based on promises. For the createConnection function, it returns a promise object that you can wait to be resolved using the await keyword. You can also terminate an open connection by calling the end method. The connect method also returns a promise object. You can use the catch method of this object to implement error handling related to the connection establishment. Alternatively, you can use the await keyword and perform error handling with try-catch.
The connection can also be established implicitly by calling the query method on the connection object.
Reading Data Records
Because you’ve already created some data records when initializing the database, you can already read them. To do this, you must first define an appropriate query and pass it to the query method of the connection object. Communication with the database takes place asynchronously and also uses promises. Alternatively, the mysql2 package also provides a callback-based interface, but this is much less convenient to use. The source code below shows how you can read data from a table.
import mysql from 'mysql2/promise';
const connection = await mysql.createConnection({
host: '127.0.0.1',
user: 'root',
password: 'topSecret',
database: 'movie-db',
});
await connection.connect();
export async function getAll() {
const query = 'SELECT * FROM Movies';
const [data] = await connection.query(query);
return data;
}
export async function get(id) {}
export async function remove(id) {}
export function save(movie) {}
The query method of the connection object returns a promise object that is resolved with an array of multiple elements. The first element of this array contains the queried data from the database, which you can extract and return using a destructuring statement. After you mark the getAll function as an async function, you can work inside the function with the await keyword, and the function will automatically return a promise object. If successful, the returned promise object contains the data from the database; if there’s an error, the corresponding error is returned from the database.
Because the signature of the getAll method hasn’t changed due to the connection of the database, you don’t need to adjust anything else in the controller.
Currently, the source code of your application assumes only the success case, which is sufficient for our purpose, namely, connecting the database. However, for a productive application, you also need to take care of error handling and send at least a generic error message to inform your users without revealing too many internal details about your application to a potential attacker.
The MySQL driver for Node.js supports many other features, such as escaping, which is described in more detail in the following sections.
Creating New Data Records
After implementing read access to the database, the next step deals with write access. Basically, the operation process here is similar because you also use the query method of the connection object to send the INSERT query. A special feature here is that you don’t directly compose the values entered by the users into a query via string concatenation, but rather use placeholders and let the database driver do the escaping. This reduces the risk of an SQL injection.
As was the case with the read process, you also use the movie/model.js file as the starting point when creating new records. Below shows the customized source code of the file.
import mysql from 'mysql2/promise';
const connection = await mysql.createConnection({...});
await connection.connect();
export async function getAll() {...}
async function insert(movie) {
const query = 'INSERT INTO Movies (title, year) VALUES (?, ?)';
const [result] = await connection.query(query, [movie.title, movie.year]);
return { ...movie, id: result.insertId };
}
export async function get(id) {}
export async function remove(id) {}
export function save(movie) {
if (!movie.id) {
return insert(movie);
}
}
The central place of the model file that takes care of saving the data is the save function. Based on the presence of the id property of the passed data record, the function decides whether it to create a new record or update an existing record later. In the current case of a new creation, the save function then calls the insert function with the data record to be created.
You can implement the insert function as an async function to be able to use the promise- based interface of the mysql2 driver comfortably. In the first step, you must prepare the INSERT statement by writing the query completely and providing question marks for the values to be inserted. The driver then replaces these question marks with the values when they are called and takes care of escaping the values correctly. The query method of the connection object accepts the SQL statement as a string as the first argument and an array with the values for the placeholders as the second argument. The return value is a promise object with an array whose first element contains a set of metadata about the request. Here you’ll find, among other things, the insertId property, which contains the ID of the newly created data record. You must use this ID together with the information from the originally passed object to create a new object with the spread operator and return it to the calling instance.
You don’t need to adjust the controller and its saveAction because the interface of the model hasn’t changed.
Updating Data Records
To update the data records in your application, you must first connect the model method for reading individual data records to the database to populate the form with the existing data. In the implementation, you should combine the structure of the reading request from before with escaping to safely insert the passed ID into the request. The code below shows the necessary adjustments to the movie/model.js file.
import mysql from 'mysql2/promise';
const connection = await mysql.createConnection({
host: '127.0.0.1',
user: 'root',
password: 'topSecret',
database: 'movie-db',
});
await connection.connect();
export async function getAll() {...}
async function insert(movie) {...}
export async function get(id) {
const query = 'SELECT * from Movies WHERE id = ?';
const [data] = await connection.query(query, [id]);
return data.pop();
}
export async function remove(id) {}
export function save(movie) {...}
Again, no further adjustments to the controller or view are required, so in the final step, you can turn your attention to implementing the functionality for updating the data in the database. For this purpose, you must use an update request to the database.
The adjustments for the update are limited to the changes shown below because the controller passes the requests directly to the model for both updates and new installations.
import mysql from 'mysql2/promise';
const connection = await mysql.createConnection({...});
await connection.connect();
export async function getAll() {...}
async function insert(movie) {...}
async function update(movie) {
const query = 'UPDATE Movies SET title = ?, year = ? WHERE id = ?';
await connection.query(query, [movie.title, movie.year, movie.id]);
return movie;
}
export async function get(id) {...}
export async function remove(id) {}
export function save(movie) {
if (!movie.id) {
return insert(movie);
} else {
return update(movie);
}
}
Removing Data Records
The last operation you’ll learn about here in connection with MySQL is deleting data records from the database. Deleting data records can’t be easily undone. If you work with referential integrity via foreign keys, deleting one record can result in a cascade of deletions of other data records that are based on that record. You should therefore always exercise caution with such operations and, if in doubt, prompt the user for confirmation via a dialog. Below shows how you can remove data records from your database.
import mysql from 'mysql2/promise';
const connection = await mysql.createConnection({...});
await connection.connect();
export async function getAll() {...}
async function insert(movie) {...}
async function update(movie) {...}
export async function get(id) {...}
export async function remove(id) {
const query = 'DELETE FROM Movies WHERE id = ?';
await connection.query(query, [id]);
return;
}
export function save(movie) {...}
With regard to the structure, the remove function is similar to the get function except that you use a DELETE statement instead of a SELECT statement. With these customizations, you can now manage the data records in your application. You can create new records, view existing ones, and modify them. You can delete data records from the database that are no longer needed.
An alternative to the final deletion of data records is the marking of records. In the database, this is represented by an additional field within the table. This field contains the value 0 for active data records and the value 1 for deleted data records. The drawback of this variant is that you have to take care of the referential integrity of your database by yourself; that is, you have to mark dependent data records as deleted yourself.
The fact that you need to install a driver for each respective database access enables you to connect different databases. Besides MySQL, there are numerous other relational databases. A lightweight alternative, for example, is SQLite.
Editor’s note: This post has been adapted from a section of the Node.js: The Comprehensive Guide by Sebastian Springer.
Comments