The basics of working with MySQL and Node

MySQL is one of the most popular relational databases in the world. Find out how to make it work with Node.js

⭐️ 👀 2023 WEB DEVELOPMENT BOOTCAMP starting in days! Join the waiting list to reserve your spot in my 10-weeks cohort course and learn the fundamentals, HTML, CSS, JS, Tailwind, React, Next.js and much much more! 👀 ⭐️

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

The Node ecosystem of course has several different packages that allow you to interface with MySQL, store data, retrieve data, and so on.

We’ll use mysqljs/mysql, a package that has over 12.000 GitHub stars and has been around for years.

Installing the Node mysql package

You install it using

npm install mysql

Initializing the connection to the database

You first include the package:

const mysql = require('mysql')

and you create a connection:

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

You initiate a new connection by calling:

connection.connect(err => {
  if (err) {
    console.error('An error occurred while connecting to the DB')
    throw err
  }
})

The connection options

In the above example, the options object contained 3 options:

const options = {
  user: 'the_mysql_user_name',
  password: 'the_mysql_user_password',
  database: 'the_mysql_database_name'
}

There are many 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
  • debug, by default disabled, can be used for debugging
  • trace, by default enabled, prints stack traces when errors occur
  • ssl, used to setup an SSL connection to the server (out of the scope of this tutorial)

Perform a SELECT query

Now you are ready to perform a SQL query on the database. The query once executed will invoke a callback function which contains an eventual error, the results and the fields.

connection.query('SELECT * FROM todos', (error, todos, fields) => {
  if (error) {
    console.error('An error occurred while executing the query')
    throw error
  }
  console.log(todos)
})

You can pass in values which will be automatically escaped:

const id = 223
connection.query('SELECT * FROM todos WHERE id = ?', [id], (error, todos, fields) => {
  if (error) {
    console.error('An error occurred while executing the query')
    throw error
  }
  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'
connection.query('SELECT * FROM todos WHERE id = ? AND author = ?', [id, author], (error, todos, fields) => {
  if (error) {
    console.error('An error occurred while executing the query')
    throw error
  }
  console.log(todos)
})

Perform an INSERT query

You can pass an object

const todo = {
  thing: 'Buy the milk'
  author: 'Flavio'
}
connection.query('INSERT INTO todos SET ?', todo, (error, results, fields) => {
  if (error) {
    console.error('An error occurred while executing the query')
    throw error
  }
})

If the table has a primary key with auto_increment, the value of that will be returned in the results.insertId value:

const todo = {
  thing: 'Buy the milk'
  author: 'Flavio'
}
connection.query('INSERT INTO todos SET ?', todo, (error, results, fields) => {
  if (error) {
    console.error('An error occurred while executing the query')
    throw error
  }}
  const id = results.resultId
  console.log(id)
)

Close the connection

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

connection.end()

This makes sure any pending query gets sent, and the connection is gracefully terminated.

One more thing! ⚠️ ✋

At the end of January I will organize the Web Development Bootcamp.

It's a 10-weeks long cohort online course where I will guide you to becoming a Web Developer.

It's not "just a course". It's a big event I organize once a year.

We'll start from zero, learn the fundamentals of Web Development, HTML CSS, JavaScript, Tailwind, Git, using the command line, VS Code, GitHub, Node.js, we'll then learn React, JSX, how to use PostgreSQL, Astro, Next.js, Prisma, deploying on Netlify/DigitalOcean/Fly/Vercel and much more! 

At the end of the first 10 weeks you'll know how to create web sites and web applications and I'll unlock you the 2nd phase of the Bootcamp: you will get access to a large number of projects exclusive to the Bootcamp graduates, so you can follow my instructions to build things like private areas with authentication, clones of popular sites like Twitter YouTube Reddit, create e-commerce sites, and much much more.

Because once you got the fundamentals, you only learn by working on real, exciting projects.

To find out more, visit bootcamp.dev