Node and MySQL tutorial

In this tutorial, you will learn the basics of working with the popular MySQL database with Node.

Introduction to Node.js and MySQL

MySQL is a popular relational database management system, and Node.js is a server-side JavaScript runtime.
You can use the mysql package to connect to MySQL databases from Node.js.

Setting Up MySQL and Node.js

To get started, install the mysql package using npm

npm install mysql

Connecting to MySQL

To connect to a MySQL database, create a connection using the mysql package.

const mysql = require('mysql');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'yourpassword',
    database: 'mydb',
});

connection.connect((err) => {
    if (err) throw err;
    console.log('Connected to MySQL');
});

Performing CRUD Operations

You can perform Create, Read, Update, and Delete (CRUD) operations using SQL queries.

// Creating data
const insertQuery = "INSERT INTO users (name, email) VALUES (?, ?)";
const values = ['John', 'john@example.com'];
connection.query(insertQuery, values, (err, result) => {
    if (err) throw err;
    console.log('Inserted ID:', result.insertId);
});

// Reading data
const selectQuery = 'SELECT * FROM users';
connection.query(selectQuery, (err, rows) => {
    if (err) throw err;
    console.log('Selected data:', rows);
});

// Updating data
const updateQuery = 'UPDATE users SET email = ? WHERE name = ?';
const updateValues = ['newemail@example.com', 'John'];
connection.query(updateQuery, updateValues, (err, result) => {
    if (err) throw err;
    console.log('Updated rows:', result.affectedRows);
});

// Deleting data
const deleteQuery = 'DELETE FROM users WHERE name = ?';
const deleteValue = 'John';
connection.query(deleteQuery, deleteValue, (err, result) => {
    if (err) throw err;
    console.log('Deleted rows:', result.affectedRows);
});

Querying Data

You can run SQL queries to filter and retrieve specific data from your database.

const selectQuery = 'SELECT name, email FROM users WHERE age > 25';
connection.query(selectQuery, (err, rows) => {
    if (err) throw err;
    console.log('Selected data:', rows);
});

Error Handling

Always handle errors when working with MySQL to ensure robust applications.

connection.query('SELECT * FROM nonexistent_table', (err, result) => {
    if (err) {
        console.error('MySQL error:', err);
    } else {
        console.log('Selected data:', result);
    }
});

Using Promises for Asynchronous Operations

To handle asynchronous MySQL operations with promises, you can use the mysql2 package.

const mysql = require('mysql2/promise');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'yourpassword',
    database: 'mydb',
});

(async () => {
    try {
    await connection.connect();
    console.log('Connected to MySQL');
    
    // Perform database operations using await
    // ...
    
    } catch (error) {
    console.error('Error:', error);
    } finally {
    connection.end();
    }
})();

Previous Next