Just a few weeks until the 2021 JavaScript Full-Stack Bootcamp opens.
Signup to the waiting list!
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 tolocalhost
port
, the MySQL server port number, defaults to 3306socketPath
, used to specify a unix socket instead of host and portdebug
, by default disabled, can be used for debuggingtrace
, by default enabled, prints stack traces when errors occurssl
, 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.
Download my free Node.js Handbook
The 2021 JavaScript Full-Stack Bootcamp will start at the end of March 2021. Don't miss this opportunity, signup to the waiting list!
More node tutorials:
- An introduction to the npm package manager
- Introduction to Node.js
- HTTP requests using Axios
- Where to host a Node.js app
- Interact with the Google Analytics API using Node.js
- The npx Node Package Runner
- The package.json guide
- Where does npm install the packages?
- How to update Node.js
- How to use or execute a package installed using npm
- The package-lock.json file
- Semantic Versioning using npm
- Should you commit the node_modules folder to Git?
- Update all the Node dependencies to their latest version
- Parsing JSON with Node.js
- Find the installed version of an npm package
- Node.js Streams
- Install an older version of an npm package
- Get the current folder in Node
- How to log an object in Node
- Expose functionality from a Node file using exports
- Differences between Node and the Browser
- Make an HTTP POST request using Node
- Get HTTP request body data using Node
- Node Buffers
- A brief history of Node.js
- How to install Node.js
- How much JavaScript do you need to know to use Node?
- How to use the Node.js REPL
- Node, accept arguments from the command line
- Output to the command line using Node
- Accept input from the command line in Node
- Uninstalling npm packages with `npm uninstall`
- npm global or local packages
- npm dependencies and devDependencies
- The Node.js Event Loop
- Understanding process.nextTick()
- Understanding setImmediate()
- The Node Event emitter
- Build an HTTP Server
- Making HTTP requests with Node
- The Node fs module
- HTTP requests in Node using Axios
- Reading files with Node
- Node File Paths
- Writing files with Node
- Node file stats
- Working with file descriptors in Node
- Working with folders in Node
- The Node path module
- The Node http module
- Using WebSockets with Node.js
- The basics of working with MySQL and Node
- Error handling in Node.js
- The Pug Guide
- How to read environment variables from Node.js
- How to exit from a Node.js program
- The Node os module
- The Node events module
- Node, the difference between development and production
- How to check if a file exists in Node.js
- How to create an empty file in Node.js
- How to remove a file with Node.js
- How to get the last updated date of a file using Node.js
- How to determine if a date is today in JavaScript
- How to write a JSON object to file in Node.js
- Why should you use Node.js in your next project?
- Run a web server from any folder
- How to use MongoDB with Node.js
- Use the Chrome DevTools to debug a Node.js app
- What is pnpm?
- The Node.js Runtime v8 options list
- How to fix the "Missing write access" error when using npm
- How to enable ES Modules in Node.js
- How to spawn a child process with Node.js
- How to get both parsed body and raw body in Express
- How to handle file uploads in Node.js
- What are peer dependencies in a Node module?
- How to write a CSV file with Node.js
- How to read a CSV file with Node.js
- The Node Core Modules
- Incrementing multiple folders numbers at once using Node.js
- How to print a canvas to a data URL
- How to create and save an image with Node.js and Canvas
- How to download an image using Node.js
- How to mass rename files in Node.js
- How to get the names of all the files in a folder in Node
- How to use promises and await with Node.js callback-based functions
- How to test an npm package locally
- How to check the current Node.js version at runtime
- How to use Sequelize to interact with PostgreSQL
- Serve an HTML page using Node.js
- How to solve the `util.pump is not a function` error in Node.js