Basic knowledge of using MySQL and Node

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

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

Of course, the Node ecosystem has several different software packages that allow you to interact with MySQL, store data, retrieve data, etc.

We will usemysqljs/mysql, The package has more than 1.2000 GitHub stars and has been around for many years.

Install Node mysql package

You install using

npm install mysql

Initialize the connection to the database

You first include the package:

const mysql = require('mysql')

Then 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 can start a new connection by calling the following command:

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

Connection options

In the example above,optionsThe object contains 3 options:

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

You can use more features, including:

hostDatabase host name, the default islocalhost
port, the MySQL server port number, defaults to 3306
socketPath, Used to specify the unix socket instead of the host and port
debug, Which is disabled by default and can be used for debugging
trace, Enabled by default, print stack trace when an error occurs
ssl, Used to establish an SSL connection with the server (outside the scope of this tutorial)

Execute SELECT query

Now you can execute SQL queries on the database. Once the query is executed, a callback function will be called, which contains the final error, result and 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 that 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 passed array 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)
})

Execute 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, withauto_increment, The value will be returned inresults.insertIdvalue:

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 with the database, you can callend()method:

connection.end()

This ensures that any pending queries are sent and the connection can be terminated normally.

Download mine for freeNode.js manual