How to connect NodeJS app with MySQL database on Digital Ocean

Note: This article assumes that you are familiar with Nodejs, that you have or are planning to host your application/database in a Linux environment, and that you have a fair knowledge of the command-line interface (bash/terminal/cmd).

For starters, let us create a new project and initialize it with npm:

npm init -y

Next, install the mysql package using npm:

npm i --save mysql

Create an index.js file in your project root directory. Inside the index.js file, create a variable named mysql. This will be an instance of the mysql package:

const mysql = require('mysql');

Now, we need to create a connection object by supplying the following:

• host: will be localhost if both the NodeJS app and the database exist on the same server; if not, the ip address of the server is where the database is hosted.
• database: the name of the database
• user: the database username
• password: the database password

This is how it will look:

const connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'u53rname',
  password : 'pa55w0rd',
  database : 'food_db'
});

If all is well, we can connect to the database:

connection.connect((err) => {
    if (err) {
        console.log('Connection error message: ' + err.message);
        return;
    }
    console.log('Connected!')
});

The above code block checks if there is an error when connecting to the database on the specified host. If there is, it displays an error message in the console. If there is not, it means the connection was successful, and it displays just that on the console.

Go ahead and try out the connection by querying a table in the database:

const queryString = 'select * from tbl_nig_dishes';

connection.query(queryString, (err, res, fields) => {
  if (err) {
    console.log('Error: ' + err);
    return;
  }
  console.log('Here is the result of the query:');
  console.log('===========================================');
  console.log(res);
  console.log('===========================================');
});

You should see the result of the query in your console.

Don’t forget to close the connection:

connection.end();

That should get the work done.

PROTIP

• Keep credentials safe using environment variables, check out dotenv
• Follow best practices, use newer JS syntax (ES6+)

You can get the complete codes from github.

I hope this helps you. Thanks for reading.

Further Resources

• Setting up a Continuous Deployment Workflow With Bitbucket And Linux Server