Express.js - MySQL database connection

In this article, we would like to show you how to connect MySQL database to the Express.js application.

Note:

In this solution mysql server is required. The free mysql compatible version for windows can be downloaded from here: downloads.mariadb.org

Example data

In this article we will be using MySQL database with HeidiSQL preview but you can use any other database administration tool.

Database:

Database data:

We mostly get the example data queries from the following articles:

1. MySQL - Create database

2. MySQL - Create table

Database connection

1. Install mysql client package using npm

npm install mysql

2. Import mysql using require() and create connection

const express = require('express');

// import mysql
const mysql = require('mysql');

const app = express();

// create connection
const connection = mysql.createConnection({
  user: 'root',
  password: 'root',
  database: 'example',
  host: 'localhost',
});

// connect
connection.connect();

app.listen(5000);

3. Now the database connection is set up and you can write your queries.

Note:

For applications that require a large number of database connections it is better to use createPool() method instead of createConnection(). For more details read this article.

Example query

To check if our connection works, we'll write a testing query that should return all the users from our example database.

For this purpose, we will use:

connection.query('SELECT * FROM users AS users', (err, res) => {
  if (err) throw err;

  console.log(JSON.stringify(res));  // prints users in the console as JSON
});

Full example:

const express = require('express');
const mysql = require('mysql');

const app = express();

const connection = mysql.createConnection({
  user: 'root',
  password: 'root',
  database: 'example',
  host: 'localhost',
});

connection.connect();

connection.query('SELECT * FROM users AS users', (err, res) => {
  if (err) throw err;

  console.log(JSON.stringify(res));
});

app.listen(5000);

Result:

[
 {"id":1,"name":"root","role":"root"},
 {"id":2,"name":"admin","role":"admin"},
 {"id":3,"name":"user1","role":"user"},
 {"id":4,"name":"user2","role":"user"}
]

See also

1. Node.js / MySQL - createConnection vs createPool

References

1. Express database integration

2. MySQL - Create database

3. MySQL - Create table