MySQL is an excellent choice for a database. It’s easy to use, has widespread support, and is one of the most popular relational databases.
Before You Begin
Since you’ll be testing out the connection code on a local MySQL server, you’ll need to install a MySQL server first. Once you’ve done so, create an empty database and make sure you have the username and password to hand.
Create a Node Application
In your terminal, run the following command to create a new folder for your node application:
Navigate to the node-mysql folder and initialize it using npm:
This will generate a package.json file which, amongst other configuration, will hold the dependencies of your project.
Connect Node to MySQL
There are several ways you can connect your node application to MySQL. The mysql2 package is an excellent choice, which you can install with a command like this:
To make the connection, you need several values namely the host, port, user, database, and password. Here is an example that connects to a database called nodedb on a MySQL server installed locally.
This is the config object you’ll pass to the connection method as in the program below.
When you pass the database details to the mysql.createConnection() method, you create a connection object. The connection object will let you perform transactions like creating, deleting, or reading tables.
For example, the following code creates a table in the database you connect to.
When you run it, this query will create a new table called users in the database. The query uses common syntax that you can adapt to create a MySQL database schema that suits your needs.
The connection program above works fine if you only want to make a single connection. However, when making concurrent connection requests, it is best to use a connection pool.
Connect Node to MySQL Using a Connection Pool
Opening and closing connections can become expensive, especially when there are many. Database connection pooling helps reduce this cost by maintaining a pool of open connections. When a request is made, the pool can provide an open connection on demand.
By doing this, you save time because you do not have to open a new connection every time.
Use the following code to create a connection pool in Node:
Remember to change the configuration details to match your own environment.
Securely Connecting to MySQL
The mysql2 library simplifies the process of connecting a Node application to a MySQL database. You can use either a single client or a connection pool. However, If you have to make multiple requests, it is best to use a connection pool since it is less costly.
Connecting to the database is only the first step. You should also ensure that the data you store in the database is secure. After all, MySQL is a popular target for attackers. There are some basic steps you can take to secure your MySQL database server. Set a strong user password, restrict remote logins, and don’t grant unnecessary privileges.
