Welcome to the Ultimate Guide of MySQL2

Are you in search of a powerful and flexible Node.js client for MySQL databases? Look no further than mysql2! It’s a trusted library that helps developers seamlessly interact with MySQL databases in a fluid and efficient manner. In this guide, we introduce mysql2 and dive into various APIs it offers, complete with useful examples and a full-fledged application. Let’s get started!

Getting Started with MySQL2

npm install mysql2

Connecting to Your Database

First, you need to create a connection to your MySQL database.

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

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

Using Promises for Async Operations

You can use promises to perform asynchronous operations with mysql2.

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

async function main() {
    const connection = await mysql.createConnection({host: 'localhost', user: 'root', database: 'test_db'});
    const [rows, fields] = await connection.execute('SELECT * FROM `users` WHERE `age` > ?', [30]);
    console.log(rows);
    await connection.end();
}

main();

Querying the Database

Here’s how you can query the database:

connection.query('SELECT * FROM users', (err, results, fields) => {
    if (err) throw err;
    console.log(results); // results contains rows returned by server
});

Prepared Statements

Use prepared statements to prevent SQL injection:

connection.execute(
    'SELECT * FROM `users` WHERE `name` = ? AND `age` > ?',
    ['Jane', 30],
    (err, results, fields) => {
        if (err) throw err;
        console.log(results);
    }
);

Inserting Data into the Database

Here’s an example of how to insert data:

const user = {name: 'John', age: 25};
connection.query('INSERT INTO users SET ?', user, (err, results, fields) => {
    if (err) throw err;
    console.log(results);
});

Updating Data in the Database

Updating data is simple:

connection.query(
    'UPDATE users SET age = ? WHERE name = ?',
    [26, 'John'],
    (err, results, fields) => {
        if (err) throw err;
        console.log(results);
    }
);

Deleting Data from the Database

Here’s how you can delete data:

connection.query(
    'DELETE FROM users WHERE name = ?',
    ['John'],
    (err, results, fields) => {
        if (err) throw err;
        console.log(results);
    }
);

A Complete Node.js App Example Using mysql2

Here’s a full example of a Node.js application that uses mysql2 to perform CRUD operations:

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

const connectionPromise = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    database: 'test_db'
});

app.use(express.json());

app.get('/users', async (req, res) => {
    const connection = await connectionPromise;
    const [rows] = await connection.query('SELECT * FROM users');
    res.json(rows);
});

app.post('/users', async (req, res) => {
    const connection = await connectionPromise;
    const [result] = await connection.execute('INSERT INTO users (name, age) VALUES (?, ?)', [req.body.name, req.body.age]);
    res.json({ id: result.insertId });
});

app.put('/users/:id', async (req, res) => {
    const connection = await connectionPromise;
    await connection.execute('UPDATE users SET name = ?, age = ? WHERE id = ?', [req.body.name, req.body.age, req.params.id]);
    res.send('User updated');
});

app.delete('/users/:id', async (req, res) => {
    const connection = await connectionPromise;
    await connection.execute('DELETE FROM users WHERE id = ?', [req.params.id]);
    res.send('User deleted');
});

app.listen(3000, () => {
    console.log('Server is running on port 3000');
});

With this knowledge, you should be equipped to leverage mysql2 for various database operations in your Node.js applications efficiently.