DEV Community

Cover image for Connect NodeJS App With MySQL Database Hosted on a Digital Ocean Droplet (Server)
Ileriayo Adebiyi
Ileriayo Adebiyi

Posted on • Edited on

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

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

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

Top comments (4)

Collapse
 
itz_larmie profile image
Olamide Adeleye

Great article! Thanks for sharing.

Collapse
 
ileriayo profile image
Ileriayo Adebiyi

Thanks for reading Olamide Adeleye!

Collapse
 
jupjup profile image
jupjup

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

Collapse
 
ileriayo profile image
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()
})
Enter fullscreen mode Exit fullscreen mode

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.