Introduction

MySQL is a great database to use to get familiar with relational databases and SQL syntax. Our focus in this walk-through will be integrating a running SQL database with your Node application.

Integrating a MySQL DB to Node

The first step to being able to use a MySQL database in a Node app is to have some way to connect to it. For that, we're going to use a library that connects for us. Let's install it:

npm install mysql2

This will install the mysql2 library which is an API that will connect to a running instance of MySQL based on a configuration object that we will write next.

DB Connection

If you don't already have a util directory in your src directory, create one now. Inside of it we'll add a configuration file for our database.

// util/db.js

const mysql = require('mysql2')

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'library', // replace this with whatever your database is actually named
  password: 'password' // replace this with the password to your database (hopefully it isn't 'password')
})

module.exports = pool.promise()

Here we're using the mysql library to create a pool. This pool will query our database using an open connection which will give us a fast response.

From the docs:

Connection pools help reduce the time spent connecting to the MySQL server by reusing a previous connection, leaving them open instead of closing when you are done with them. This improves the latency of queries as you avoid all of the overhead that comes with establishing a new connection.

Then we're exporting pool.promise(). We could export just pool but adding .promise() to it allows us to work with promises instead of callbacks. Which, in my opinion is a lot cleaner. Let's take a look at how we can use this now.

Making Requests to our Database

Now we can import this module anywhere in our application and start making requests to our database.

// app.js

db.execute('SELECT * FROM books')
  .then(([rows, fieldData]) => {
    rows.forEach(row => {
      console.log(`${rows.id}: ${rows.title} ($${rows.price})`)
    })
  })
  .catch(console.error)

using the execute method on our db, we can actually retrieve rows and populate components on our page! In the previous example we're only printing to the console but passing this to a template would have the same logic.

Important to note is the response that we're getting from the db. Notice we're using array destructuring to get the rows as well as field data (metadata) corresponding to our request. Since our request (SELECT * FROM books) is getting every available row in the form of an Array, we're able to iterate over it and log each row to the console in the format we've specified. We could just as easily pass this as a model element to a template.

POST requests

One important note in dealing with SQL databases. Never inject data directly into a post request. If you do, you can fall victim to what are known as SQL Injection attacks. This is a very specific type of hacking threat in which some nefarious entity types actual SQL code into input fields hoping to gain access to your Data Layer. So, this...

db.execute(`INSERT INTO \`books\` (\`id\`,\`title\`,\`description\`) VALUES (DEFAULT, ${req.body.title},${req.body.description})`)

IS VERY BAD

But this...

db.execute(`INSERT INTO \`books\` (\`id\`,\`title\`,\`description\`) VALUES (DEFAULT,?,?)`, [req.body.title, req.body.description])

IS GOOD

In the second example, SQL will parse the values for SQL keywords and escape them before putting anything in your db. the first example will not.

Conslusion

Being able to connect to a database makes working with our data so much more efficient. What's also nice is now our data is decoupled from our actual application so we could potentially use it with any number of applications running on the same server.

Be sure to get some practice with SQL syntax.