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
In this article we will be using MySQL database with HeidiSQL preview but you can use any other database administration tool.
Database:
Database data:
| Database name | example |
| User | root |
| Password | root |
| Host | localhost |
We mostly get the example data queries from the following articles:
1. Install mysql client package using npm
xxxxxxxxxxnpm install mysql2. Import mysql using require() and create connection
xxxxxxxxxxconst express = require('express');// import mysqlconst mysql = require('mysql');const app = express();// create connectionconst connection = mysql.createConnection({ user: 'root', password: 'root', database: 'example', host: 'localhost',});// connectconnection.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 ofcreateConnection(). For more details read this article.
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:
xxxxxxxxxxconnection.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:
xxxxxxxxxxconst 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:
xxxxxxxxxx[ {"id":1,"name":"root","role":"root"}, {"id":2,"name":"admin","role":"admin"}, {"id":3,"name":"user1","role":"user"}, {"id":4,"name":"user2","role":"user"}]
