The basics of working with MySQL and Node

MySQL is one of the most popular relational databases in the world.

The Node ecosystem has several packages that let you talk to it. For years the standard was mysqljs/mysql, a callback-based package with over 12.000 GitHub stars. Today the package to use is mysql2, which was built to be compatible with it, is actively maintained, and ships a promise API so you can use async/await.

Installing the mysql2 package

Install it with npm:

npm install mysql2

Connecting to the database

Import the promise API and create a connection:

import mysql from 'mysql2/promise'

const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'the_mysql_user_name',
  password: 'the_mysql_user_password',
  database: 'the_mysql_database_name',
})

If the credentials are wrong, createConnection rejects with an error like ER_ACCESS_DENIED_ERROR. Read the message: it tells you which user and host MySQL tried to match.

The connection options

In the example above I passed 4 options. There are more you can use, including:

• host, the database hostname, defaults to localhost
• port, the MySQL server port number, defaults to 3306
• socketPath, used to specify a unix socket instead of host and port
• ssl, used to set up an SSL connection to the server

In a real application, load these values from environment variables instead of writing them in the code, so credentials stay out of your repository.

If your library takes a connection URL instead of an options object, I built a free connection string builder that assembles and parses MySQL connection strings.

Perform a SELECT query

Run a query with await. The result comes back as an array of rows:

const [todos] = await connection.query('SELECT * FROM todos')
console.log(todos)

Don’t concatenate user input into the SQL string. Pass values separately with ? placeholders, and the driver makes sure they are treated as data, not as SQL:

const id = 223
const [todos] = await connection.query(
  'SELECT * FROM todos WHERE id = ?',
  [id]
)
console.log(todos)

To pass multiple values, just put more elements in the array you pass as the second parameter:

const id = 223
const author = 'Flavio'
const [todos] = await connection.query(
  'SELECT * FROM todos WHERE id = ? AND author = ?',
  [id, author]
)
console.log(todos)

Perform an INSERT query

You can pass an object with the column values, using the SET ? shorthand:

const todo = {
  thing: 'Buy the milk',
  author: 'Flavio',
}
const [results] = await connection.query('INSERT INTO todos SET ?', todo)

If the table has a primary key with auto_increment, the value generated for the new row is returned in results.insertId:

const [results] = await connection.query('INSERT INTO todos SET ?', todo)
console.log(results.insertId) // 5

Use a pool for real applications

A single connection is fine for a script. A web application should create a pool instead, so connections are reused across requests and their number stays bounded:

import mysql from 'mysql2/promise'

const pool = mysql.createPool({
  host: 'localhost',
  user: 'the_mysql_user_name',
  password: 'the_mysql_user_password',
  database: 'the_mysql_database_name',
  connectionLimit: 5,
})

const [todos] = await pool.query('SELECT * FROM todos')

The pool object has the same query interface as a connection.

Close the connection

When you need to terminate the connection to the database you can call the end() method:

await connection.end()

This makes sure any pending query gets sent, and the connection is gracefully terminated. A script that skips this step keeps the Node process alive after the work is done.