How to Connect a Node Application to a MySQL Database

Most real-world applications keep data in databases. It is therefore important to know how to connect your application to a database and retrieve data from it.

MySQL is an excellent choice for a database. It’s easy to use, has wide support, and is one of the most popular relational databases.

Before you start

Since you will be testing the login code on a local MySQL server, you will need to install a MySQL server first. Once you’ve done that, create an empty database and make sure you have the username and password handy.

USE VIDEO OF THE DAY

Create a node application

In your terminal, run the following command to create a new folder for your node app:

mkdir node-mysql

Navigate to the node-mysql folder and initialize it using npm:

cd node-mysql
npm init -y

This will generate a package.json file which, among other configurations, will contain your project’s dependencies.

Connect node to MySQL

There are several ways to connect your node application to MySQL. The mysql2 package is a great choice, which you can install with a command like this:

npm install mysql2

To establish the connection, you need several values, namely host, port, user, database and password. Here is an example that connects to a database called nodedb on a locally installed MySQL server.

const config = {
host: "localhost",
port: 3306,
database: "nodedb",
user: "root",
password: "********",
}

It’s the configuration object that you will pass to the connection method as in the program below.

const mysql = require("mysql2")

const config = {
host: "localhost",
database: "db_name",
user: "mysql_username",
password: "mysql_password",
connectionLimit: 100,
}

const connection = mysql.createConnection(config)

connection.connect(function(err) {
if (err) throw(err);
console.log("Connected!");
});

When you pass the database details to the mysql.createConnection() method, you create a connection object. The connection object will allow you to perform transactions such as creating, deleting or reading tables.

For example, the following code creates a table in the database to which you are connecting.

connection.connect(function(err) {
if (err) throw err;
console.log("Connected!");

let createUser = `CREATE TABLE users (user_id INT(100), user_name VARCHAR(255), email VARCHAR(255))`;

connection.query(createUser, function(err, results, fields) {
if (err) {
console.log(err.message);
}
});
});

When you run it, this query will create a new table called users in the database. The query uses a common syntax that you can adapt to create a MySQL database schema that suits your needs.


The login program above works fine if you only want to establish a single login. However, when making concurrent connection requests, it is best to use a connection pool.

Connect a Node to MySQL Using a Connection Pool

Opening and closing connections can get expensive, especially when there are a lot of them. 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 so, you save time because you don’t have to open a new connection each time.

Use the following code to create a connection pool in Node:

const mysql = require("mysql2");

const config = {
host: "localhost",
database: "db_name",
user: "mysql_username",
password: "mysql_password",
connectionLimit: 100,
}

const pool = mysql.createPool(config);

pool.query("SELECT * FROM some_table", (err, data) => {
if (err) throw(err)
console.log(data);
});

Remember to modify the configuration details to match your own environment.

Secure connection 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 need to perform multiple queries, it is better to use a connection pool because it is less expensive.

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. You can follow some basic steps to secure your MySQL database server. Set a strong user password, limit remote logins, and don’t grant unnecessary privileges.


Source link

Comments are closed.