MySQL Overview

• MySQL - Structured Query Language database
• Open-source relational database
• Used by Facebook, Twitter, YouTube
• ACID compliant (reliable transactions)

Key Concepts

• Database: Container for tables
• Table: Collection of related data
• Row: Single record (entry)
• Column: Data field (attribute)
• Primary Key: Unique identifier

Example Table Structure

users table:
+----+----------+------------------+-----+
| id | name     | email            | age |
+----+----------+------------------+-----+
| 1  | Alice    | alice@mail.com   | 25  |
| 2  | Bob      | bob@mail.com     | 30  |
| 3  | Charlie  | charlie@mail.com | 28  |
+----+----------+------------------+-----+

Creating Tables

-- Create a database
CREATE DATABASE school;
USE school;

-- Create a table
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT
        CURRENT_TIMESTAMP
);

Data Types

• INT - Integer numbers
• VARCHAR(n) - Variable text (max n chars)
• TEXT - Long text
• DATE, DATETIME, TIMESTAMP
• BOOLEAN - True/False
• DECIMAL(p,s) - Precise numbers

Constraints

• PRIMARY KEY - Unique identifier
• AUTO_INCREMENT - Auto-generate numbers
• NOT NULL - Value required
• UNIQUE - No duplicates allowed
• DEFAULT - Default value
• FOREIGN KEY - Links to another table

CREATE (INSERT)

-- Insert single record
INSERT INTO students (name, email, age)
VALUES ('Alice', 'alice@mail.com', 25);

-- Insert multiple records
INSERT INTO students (name, email, age)
VALUES
    ('Bob', 'bob@mail.com', 30),
    ('Charlie', 'charlie@mail.com', 28);

READ (SELECT)

-- Get all records
SELECT * FROM students;

-- Get specific columns
SELECT name, email FROM students;

-- Filter with WHERE
SELECT * FROM students WHERE age > 25;

-- Sort results
SELECT * FROM students ORDER BY name ASC;

UPDATE

-- Update single record
UPDATE students
SET age = 26
WHERE id = 1;

-- Update multiple fields
UPDATE students
SET name = 'Alice Smith', age = 27
WHERE email = 'alice@mail.com';

DELETE

-- Delete specific record
DELETE FROM students WHERE id = 3;

-- Delete with condition
DELETE FROM students WHERE age < 18;

-- Delete all (be careful!)
DELETE FROM students;

1. Install MySQL Driver

npm init -y
npm install mysql2

Why mysql2?

• Faster than original mysql package
• Promise support built-in
• Prepared statements support
• Better security features

2. Create Connection

const mysql = require('mysql2');

// Create connection
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'school'
});

// Connect
connection.connect((err) => {
    if (err) {
        console.error('Error:', err);
        return;
    }
    console.log('Connected to MySQL!');
});

3. Using Connection Pool

Better for production - reuses connections:

const mysql = require('mysql2');

// Create pool (recommended)
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'school',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// Use promise wrapper
const promisePool = pool.promise();

// Now you can use async/await
async function getUsers() {
    const [rows] = await promisePool
        .query('SELECT * FROM students');
    return rows;
}

CREATE - Insert Data

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

async function createStudent(name, email, age) {
    const pool = mysql.createPool({
        host: 'localhost',
        user: 'root',
        password: 'password',
        database: 'school'
    });

    try {
        const [result] = await pool.execute(
            'INSERT INTO students (name, email, age) VALUES (?, ?, ?)',
            [name, email, age]
        );

        console.log('Inserted ID:', result.insertId);
        return result.insertId;
    } catch (error) {
        console.error('Error:', error.message);
        throw error;
    }
}

// Usage
createStudent('David', 'david@mail.com', 22);

READ - Fetch Data

// Get all students
async function getAllStudents() {
    const [rows] = await pool.execute(
        'SELECT * FROM students'
    );
    return rows;
}

// Get single student by ID
async function getStudentById(id) {
    const [rows] = await pool.execute(
        'SELECT * FROM students WHERE id = ?',
        [id]
    );
    return rows[0]; // First row or undefined
}

// Search students
async function searchStudents(searchTerm) {
    const [rows] = await pool.execute(
        'SELECT * FROM students WHERE name LIKE ?',
        [`%${searchTerm}%`]
    );
    return rows;
}

// Usage
const students = await getAllStudents();
const student = await getStudentById(1);
const results = await searchStudents('Ali');

UPDATE - Modify Data

// Update single field
async function updateStudentEmail(id, newEmail) {
    const [result] = await pool.execute(
        'UPDATE students SET email = ? WHERE id = ?',
        [newEmail, id]
    );

    console.log('Rows affected:', result.affectedRows);
    return result.affectedRows > 0;
}

// Update multiple fields
async function updateStudent(id, data) {
    const [result] = await pool.execute(
        `UPDATE students
         SET name = ?, email = ?, age = ?
         WHERE id = ?`,
        [data.name, data.email, data.age, id]
    );

    return result.affectedRows > 0;
}

// Usage
await updateStudentEmail(1, 'newalice@mail.com');
await updateStudent(2, {
    name: 'Robert',
    email: 'robert@mail.com',
    age: 31
});

DELETE - Remove Data

// Delete by ID
async function deleteStudent(id) {
    const [result] = await pool.execute(
        'DELETE FROM students WHERE id = ?',
        [id]
    );

    return result.affectedRows > 0;
}

// Delete with condition
async function deleteInactiveStudents(days) {
    const [result] = await pool.execute(
        `DELETE FROM students
         WHERE last_login < DATE_SUB(NOW(),
               INTERVAL ? DAY)`,
        [days]
    );

    console.log('Deleted:', result.affectedRows);
    return result.affectedRows;
}

// Usage
const deleted = await deleteStudent(3);
if (deleted) {
    console.log('Student removed');
} else {
    console.log('Student not found');
}

What is SQL Injection?

A security vulnerability where attackers insert malicious SQL code:

// DANGEROUS - User input directly in query
const query = `SELECT * FROM users
    WHERE username = '${username}'
    AND password = '${password}'`;

// Attacker inputs:
// username: admin' --
// password: anything

// Results in:
// SELECT * FROM users
// WHERE username = 'admin' --'
// AND password = 'anything'
// The -- comments out the password check!

Using Prepared Statements

Prepared statements separate SQL from data:

// Safe - Parameters are escaped
const [rows] = await pool.execute(
    'SELECT * FROM users WHERE username = ? AND password = ?',
    [username, password]
);

// The ? placeholders are replaced safely
// Malicious input becomes harmless string

// Alternative: Named placeholders
const [rows] = await pool.execute(
    'SELECT * FROM users WHERE username = :user',
    { user: username }
);

Benefits of Prepared Statements

• Prevents SQL injection attacks
• Better performance (query is pre-compiled)
• Cleaner, more readable code
• Automatic type conversion

PUT - Update Student

app.put('/api/students/:id', async (req, res) => {
    try {
        const { name, email, age } = req.body;
        const [result] = await pool.execute(
            `UPDATE students
             SET name = ?, email = ?, age = ?
             WHERE id = ?`,
            [name, email, age, req.params.id]
        );

        if (result.affectedRows === 0) {
            return res.status(404)
                .json({ error: 'Student not found' });
        }

        res.json({
            id: req.params.id,
            name, email, age
        });
    } catch (error) {
        res.status(500)
            .json({ error: error.message });
    }
});

DELETE - Remove Student

app.delete('/api/students/:id', async (req, res) => {
    try {
        const [result] = await pool.execute(
            'DELETE FROM students WHERE id = ?',
            [req.params.id]
        );

        if (result.affectedRows === 0) {
            return res.status(404)
                .json({ error: 'Student not found' });
        }

        res.json({
            message: 'Student deleted',
            id: req.params.id
        });
    } catch (error) {
        res.status(500)
            .json({ error: error.message });
    }
});

# Using curl
curl -X POST http://localhost:3000/api/students \
  -H "Content-Type: application/json" \
  -d '{"name":"Eve","email":"eve@mail.com","age":24}'

Proper Error Handling

async function safeQuery(pool, query, params) {
    try {
        const [rows] = await pool.execute(query, params);
        return { success: true, data: rows };
    } catch (error) {
        console.error('Database error:', error);

        // Handle specific errors
        if (error.code === 'ER_DUP_ENTRY') {
            return {
                success: false,
                error: 'Duplicate entry'
            };
        }
        if (error.code === 'ER_NO_REFERENCED_ROW') {
            return {
                success: false,
                error: 'Related record not found'
            };
        }

        return {
            success: false,
            error: 'Database error'
        };
    }
}

Transactions

Group multiple operations - all succeed or all fail:

async function transferCredits(fromId, toId, amount) {
    const connection = await pool.getConnection();

    try {
        await connection.beginTransaction();

        // Deduct from sender
        await connection.execute(
            'UPDATE accounts SET credits = credits - ? WHERE id = ?',
            [amount, fromId]
        );

        // Add to receiver
        await connection.execute(
            'UPDATE accounts SET credits = credits + ? WHERE id = ?',
            [amount, toId]
        );

        // Both succeeded - commit
        await connection.commit();
        return true;

    } catch (error) {
        // Something failed - rollback everything
        await connection.rollback();
        throw error;
    } finally {
        connection.release();
    }
}

Key Concepts

• MySQL stores data in tables with rows/columns
• CRUD: Create, Read, Update, Delete
• Use mysql2/promise for async/await
• Connection pools handle multiple requests
• Prepared statements prevent SQL injection
• Transactions ensure data integrity

SQL Commands Learned

• CREATE TABLE - Define structure
• INSERT INTO - Add data
• SELECT - Query data
• UPDATE - Modify data
• DELETE - Remove data

Best Practices

• Always use prepared statements
• Use connection pooling in production
• Handle errors gracefully
• Use transactions for related operations
• Never store plain-text passwords
• Validate input before database operations