Connect NodeJS App With MySQL Database Hosted on a Digital Ocean Droplet (Server)

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                                                          CLI

Next, install the mysql package using npm:

npm i --save mysql                                                   CLI

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

const mysql = require('mysql');                                      CLI

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

• host: which will be localhost if both the nodejs app and the database exist on the same server or the ip address of the server where the database is hosted,
• database: the name of the database
• user: the database username
• password: the database password

Here is how it would look like:

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

If all is well, we can now 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 and displays the error message in the console if there is, otherwise it means the connection was successful and it displays just that on the console.

Go ahead and try out the connection by actually 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. Do leave your comments below.

Further Resources

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

Follow me on Twitter @Ileriayooo for more on Tech and Opportunities.

If you want to be successful in tech, here is a simple framework.

Comments

[Olamide Adeleye]

Great article! Thanks for sharing.

[Ileriayo Adebiyi]

Thanks for reading Olamide Adeleye!

[jupjup]

Can you make a tutorial on how to connect NodeJS App with PostgresSQL database hosted on a Digital Ocean

[Ileriayo Adebiyi]

Hi @jupjup !
Thanks for stopping by.

It follows the same idea for connecting to a PostgreSQL database on DO.
Here's the difference:

• You'll need to install a driver for PostgreSQL rather than for MySQL. "pg" is a good choice. npm install --save pg

Afterwards, you'll create a connection object and and execute queries.
So it will look like this:

const { Client } = require('pg')

const client = new Client({
  user: 'yourdbpassword',
  host: yourhostname.com',
  database: 'yourdbname',
  password: 'yoursupersecretpassword',
  port: 3211,
})

client.connect()

client.query('SELECT NOW()', (err, res) => {
  console.log(err, res)
  client.end()
})

Note: You may want to use a connection pool if your application makes frequent calls. Thus, rather than using Client, you will want to use Pool. Please refer to the node-postgres docs for more information.