Using MySQL with Node.js and the mysql JavaScript Client

Node.js developers frequently utilize NoSQL databases, with MongoDB being a popular choice. However, database selection should align with project needs. NoSQL databases excel with dynamic table creation and real-time inserts, while SQL databases are better suited for complex queries and transactions. This tutorial demonstrates using the mysql module—a Node.js client for MySQL—to connect to a MySQL database and perform CRUD operations, utilize stored procedures, and prevent SQL injection vulnerabilities. This article was updated in 2020 to reflect current best practices. For a deeper dive into MySQL, see Jump Start MySQL.

Key Concepts:

• Employ the mysql module in Node.js for efficient MySQL database interaction and CRUD operations.
• Verify Node.js and npm are installed before using npm to install the mysql module.
• Establish a MySQL database connection using mysql.createConnection, implementing error handling for robust database management.
• Execute SQL queries, retrieve data, and process results using con.query.
• Leverage MySQL stored procedures for efficient SQL code management and call them within your Node.js application via con.query.
• Safeguard against SQL injection attacks using parameterized queries or the mysql.escape method for input sanitization.

Quick Start:

Connect to MySQL in Node.js with these five steps:

1. Create a project: mkdir mysql-test && cd mysql-test.
2. Initialize package.json: npm init -y.
3. Install the mysql module: npm install mysql.
4. Create app.js (modify placeholders):

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'database name'
});
connection.connect((err) => {
  if (err) throw err;
  console.log('Connected!');
});

5. Run: node app.js. Expect a "Connected!" message.

Installing the mysql Module:

The commands mkdir mysql-test, cd mysql-test, npm init -y, and npm install mysql create the project directory, initialize the project, and install the necessary module. This assumes Node.js and npm are already installed. Refer to the SitePoint article "Install Multiple Versions of Node.js using nvm" if needed.

Database Setup:

Before connecting, ensure MySQL is installed and configured. Create a database (e.g., sitepoint) and a table (e.g., authors). The following SQL commands can be used:

CREATE DATABASE sitepoint CHARACTER SET utf8 COLLATE utf8_general_ci;
USE sitepoint;

CREATE TABLE authors (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50),
  city varchar(50),
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO authors (id, name, city) VALUES
(1, 'Michaela Lehr', 'Berlin'),
(2, 'Michael Wanyoike', 'Nairobi'),
(3, 'James Hibbard', 'Munich'),
(4, 'Karolina Gawron', 'Wrocław');

Connecting to the Database:

Create app.js:

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'database name'
});
connection.connect((err) => {
  if (err) throw err;
  console.log('Connected!');
});

Run node app.js (or use nodemon for automatic restarts after file changes).

Executing Queries:

• Reading: con.query('SELECT * FROM authors', (err,rows) => { ... }); retrieves data.
• Creating: con.query('INSERT INTO authors SET ?', author, (err, res) => { ... }); inserts data.
• Updating: con.query('UPDATE authors SET city = ? Where ID = ?', ['Leipzig', 3], (err, result) => { ... }); updates data.
• Deleting: con.query('DELETE FROM authors WHERE id = ?', [5], (err, result) => { ... }); deletes data.

Advanced Usage:

• Stored Procedures: Create stored procedures in MySQL (e.g., sp_get_authors) and call them using con.query('CALL sp_get_authors()',function(err, rows){...});.
• Escaping User Input: Always escape user input using mysql.escape() or parameterized queries to prevent SQL injection.

ORMs: Consider using an ORM like Sequelize for increased productivity, but be aware of potential performance implications.

Conclusion:

This tutorial provides a foundation for using the mysql module with Node.js. Explore the official documentation for more advanced features. Alternatives like node-mysql2 are also available.

FAQs: The provided FAQs are already well-answered within the original text and do not require further modification.

The above is the detailed content of Using MySQL with Node.js and the mysql JavaScript Client. For more information, please follow other related articles on the PHP Chinese website!