Loading
MindXchange
MindXchange
MindXchange

Quipoin Menu

Learn • Practice • Grow

Tutorial Exercises MCQs Interview Q & A Tests
node-js / Connecting to MySQL
tutorial
ExercisesInterview QsMCQVideosDiscussion

Connecting to MySQL

While MongoDB is great for many applications, sometimes you need a traditional relational database. MySQL is one of the most popular relational databases. Let's learn how to connect Node.js to MySQL.

What is MySQL?

MySQL is a relational database management system (RDBMS) that uses SQL (Structured Query Language). Data is organized into tables with rows and columns, and relationships can be established between tables.

Think of MySQL as a giant spreadsheet with multiple sheets (tables), where each sheet has rows and columns, and you can link data between sheets.

Installing MySQL Driver

The most popular MySQL driver for Node.js is `mysql2`. It's fast, supports promises, and is actively maintained.
npm install mysql2

Setting Up MySQL

You need MySQL installed and running. You can:

Creating a Connection
const mysql = require('mysql2/promise');

async function connectMySQL() {
  try {
    const connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'yourpassword',
      database: 'myapp'
    });
    
    console.log('Connected to MySQL');
    
    <!-- Example query -->
    const [rows, fields] = await connection.execute('SELECT * FROM users');
    console.log('Users:', rows);
    
    <!-- Close connection -->
    await connection.end();
  } catch (err) {
    console.error('Error connecting to MySQL:', err);
  }
}

connectMySQL();

Using Connection Pool (Recommended)

For production applications, you should use a connection pool instead of creating a new connection for each request.
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'yourpassword',
  database: 'myapp',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

async function getUsers() {
  try {
    const [rows] = await pool.execute('SELECT * FROM users');
    console.log(rows);
  } catch (err) {
    console.error(err);
  }
}

Basic CRUD Operations with MySQL

CREATE (INSERT)
const [result] = await pool.execute(
  'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
  ['John', 'john@example.com', 30]
);
console.log('Inserted ID:', result.insertId);

READ (SELECT)
<!-- Get all users -->
const [rows] = await pool.execute('SELECT * FROM users');

<!-- Get user by ID -->
const [user] = await pool.execute(
  'SELECT * FROM users WHERE id = ?',
  [userId]
);

UPDATE
const [result] = await pool.execute(
  'UPDATE users SET age = ? WHERE id = ?',
  [31, 1]
);
console.log('Updated rows:', result.affectedRows);

DELETE
const [result] = await pool.execute(
  'DELETE FROM users WHERE id = ?',
  [userId]
);
console.log('Deleted rows:', result.affectedRows);

Important: SQL Injection Prevention

Always use **parameterized queries** (the `?` placeholders) like we did above. Never concatenate user input directly into SQL strings – that leads to SQL injection attacks.
<!-- ❌ DANGEROUS – never do this! -->
await pool.execute(`SELECT * FROM users WHERE email = '${userInput}'`);

<!-- ✅ SAFE – use parameterized queries -->
await pool.execute('SELECT * FROM users WHERE email = ?', [userInput]);

Two Minute Drill

  • MySQL is a relational database that uses SQL.
  • Install `mysql2` package – it supports promises.
  • Use connection pools for production applications.
  • Always use parameterized queries (`?` placeholders) to prevent SQL injection.
  • `execute()` returns an array with [rows, fields].
  • Result objects contain `insertId`, `affectedRows`, etc.
Practice ExercisesInterview QuestionsTake Quiz

Need more clarification?

Drop us an email at career@quipoinfotech.com