Connect NodeJS App With MySQL Database Hosted on a Digital Ocean Droplet (Server)
Tags: node, mysql, linux, digitalocean
Published: 2020-02-05 14:35:47

<strong>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).</strong>

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

<pre>
npm init -y                                                          <em>CLI</em>
</pre>

Next, install the mysql package using npm:
<pre>
npm i --save mysql                                                   <em>CLI</em>
</pre>

Create an index.js file in your project root directory. Inside the index.js file,  create a variable named <em>mysql</em> which will be an instance of the mysql package.
<pre>
const mysql = require('mysql');                                      <em>CLI</em></pre>

Now, we need to create a connection object by supplying the following:
* <strong>host</strong>: which will be <em>localhost</em> if both the nodejs app and the database exist on the same server or the <em>ip address</em> of the server where the database is hosted,
* <strong>database</strong>: the name of the database
* <strong>user</strong>: the database username
* <strong>password</strong>: the database password

Here is how it would look like:
<pre>const connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'u53rname',
  password : 'pa55w0rd',
  database : 'food_db'
});</pre>

If all is well, we can now connect to the database:
<pre>connection.connect((err) => {
    if (err) {
        console.log('Connection error message: ' + err.message);
        return;
    }
    console.log('Connected!')
});</pre>

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:
<pre>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('===========================================');
});</pre>

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

Don't forget to close the connection:
<pre>connection.end();</pre>

That should get the work done.

<strong>PROTIP</strong>
* Keep credentials safe using environment variables, check out <a href='https://www.npmjs.com/package/dotenv'>dotenv</a>
* Follow best practices, use newer js syntax (ES6+)

You can get the complete codes from <a href='https://github.com/Ileriayo/node-mysql'>github</a>.

I hope this helps you. Thanks for reading. Do leave your comments below.

<u>Further Resources</u>
- <a href='https://dev.to/ileriayo/setting-up-a-continuous-deployment-workflow-with-bitbucket-and-a-linux-server-1lgj'>Setting up a Continuous Deployment Workflow With Bitbucket And Linux Server</a>

Follow me on [Twitter @Ileriayooo](https://twitter.com/Ileriayooo) for more on Tech and Opportunities.

If you want to be successful in tech, [here is a simple framework](https://twitter.com/Ileriayooo/status/1490416597596614660).