MySQL connection with mysql2 in Node.js - Step-by-Step Execution

Concept Flow - MySQL connection with mysql2

Import mysql2 module

↓

Create connection config

↓

Call mysql.createConnection()

↓

Connect to MySQL server

↓

Check for connection error

↓

Handle error

↓

Use connection for queries

↓

Close connection when done

This flow shows how to import mysql2, create a connection, handle errors, and use the connection for queries.

Execution Sample

Node.js

import mysql from 'mysql2';

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

connection.connect(err => {
  if (err) {
    console.error('Connection error:', err);
    return;
  }
  console.log('Connected!');
  connection.end();
});

This code connects to a MySQL database using mysql2, logs success or error, then closes the connection.

Execution Table

Step	Action	Evaluation	Result
1	Import mysql2 module	Module imported	mysql object available
2	Create connection config	Config object created	{host:'localhost', user:'root', password:'pass', database:'testdb'}
3	Call mysql.createConnection()	Connection object created	connection object ready
4	Call connection.connect()	Attempt to connect to MySQL server	Connecting...
5	Check for error in callback	Error? (err is null or error object)	No error, err=null
6	Log 'Connected!'	Console output	Connected!
7	Call connection.end()	Close connection	Connection closed

💡 Connection established successfully, then closed.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	Final
mysql	undefined	mysql module imported	mysql module imported	mysql module imported	mysql module imported	mysql module imported
connection	undefined	undefined	connection object created	connection object connecting	connection object connected	connection object closed
err	undefined	undefined	undefined	undefined	null (no error)	null

Key Moments - 3 Insights

Why do we check if 'err' exists in the connect callback?

Because the connection might fail. Checking 'err' (see step 5 in execution_table) lets us handle connection errors gracefully instead of crashing.

What happens if we forget to call connection.end()?

The connection stays open, which can cause resource leaks. Step 7 shows closing the connection to free resources.

Is the connection synchronous or asynchronous?

Connecting is asynchronous. The connect() method takes a callback that runs after connection attempt (step 4 and 5).

Visual Quiz - 3 Questions

Look at the execution_table at step 5, what is the value of 'err' if connection succeeds?

A An error object

B null

C undefined

D true

💡 Hint

Check the 'Evaluation' and 'Result' columns at step 5 in execution_table.

At which step is the connection object created?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Look at the 'Action' column for when mysql.createConnection() is called.

If the password is wrong, what changes in the execution_table?

A Step 5 'err' will be an error object instead of null

B Step 3 connection object is not created

C Step 7 connection.end() is called earlier

D Step 1 import fails

💡 Hint

Think about what happens if connection fails at step 5.

Concept Snapshot

Import mysql2 module
Create connection with mysql.createConnection(config)
Call connection.connect(callback) to connect asynchronously
Check error in callback to handle failures
Use connection for queries
Call connection.end() to close connection

Full Transcript

This example shows how to connect to a MySQL database using the mysql2 library in Node.js. First, we import the mysql2 module. Then, we create a connection object with the database details like host, user, password, and database name. We call connection.connect() with a callback to start connecting asynchronously. Inside the callback, we check if there is an error. If no error, we log 'Connected!'. Finally, we close the connection with connection.end() to free resources. This flow ensures safe and proper connection handling.

Practice

1. What is the main purpose of the mysql2 package in Node.js?

easy

A. To create web servers

B. To connect and interact with MySQL databases

C. To style HTML pages

D. To manage file uploads

Solution

1. Step 1: Understand the role of mysql2

   The mysql2 package is designed to help Node.js applications connect to and communicate with MySQL databases.

2. Step 2: Compare with other options

   Options B, C, and D relate to other tasks like web servers, styling, or file uploads, which are not the purpose of mysql2.

3. Final Answer:

   To connect and interact with MySQL databases -> Option B

4. Quick Check:

   mysql2 = MySQL connection tool [OK]

Hint: Remember mysql2 is for database connection only [OK]

Common Mistakes:

• Confusing mysql2 with web server packages
• Thinking mysql2 styles web pages
• Assuming mysql2 handles file uploads

2. Which of the following is the correct way to create a connection using mysql2 in Node.js?

easy

A. const connection = mysql2.newConnection('localhost', 'root', 'pass');

B. const connection = mysql2.connect({host: 'localhost', user: 'root'});

C. const connection = mysql2.createConnection({host: 'localhost', user: 'root', password: 'pass', database: 'test'});

D. const connection = mysql2.open({host: 'localhost', user: 'root', password: 'pass'});

Solution

1. Step 1: Recall mysql2 connection syntax

   The correct method to create a connection is createConnection with an object containing host, user, password, and database.

2. Step 2: Check each option

   const connection = mysql2.createConnection({host: 'localhost', user: 'root', password: 'pass', database: 'test'}); uses createConnection with all required fields. Options A, B, and D use incorrect method names or missing parameters.

3. Final Answer:

   const connection = mysql2.createConnection({host: 'localhost', user: 'root', password: 'pass', database: 'test'}); -> Option C

4. Quick Check:

   Use createConnection with config object [OK]

Hint: Use createConnection with full config object [OK]

Common Mistakes:

• Using wrong method names like connect or open
• Omitting required fields like password or database
• Passing parameters as separate arguments instead of an object

3. Given the code below, what will be logged if the connection is successful?

const mysql = require('mysql2');
const connection = mysql.createConnection({host: 'localhost', user: 'root', password: 'pass', database: 'test'});
connection.connect(err => {
  if (err) {
    console.log('Connection failed');
  } else {
    console.log('Connected successfully');
  }
});

medium

A. No output

B. Connection failed

C. SyntaxError

D. Connected successfully

Solution

1. Step 1: Understand the connect callback

   The callback checks if there is an error. If no error, it logs 'Connected successfully'.

2. Step 2: Given connection is successful

   Since the connection is successful, err is null, so the else block runs.

3. Final Answer:

   Connected successfully -> Option D

4. Quick Check:

   Successful connection logs success message [OK]

Hint: No error means success message logs [OK]

Common Mistakes:

• Assuming error logs even on success
• Confusing callback parameters
• Expecting no output without callback

4. Identify the error in this code snippet for connecting to MySQL using mysql2:

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

medium

A. Using connection.end() before connect callback

B. No error, code is correct

C. Incorrect method createConnection instead of connect

D. Missing database name in connection config

Solution

1. Step 1: Check connection config

   While database name is missing, mysql2 allows connection without specifying database, so not a fatal error here.

2. Step 2: Analyze connection.end() placement

   connection.end() is called immediately after connect(), which may close connection before it is established or query runs.

3. Final Answer:

   Using connection.end() before connect callback -> Option A

4. Quick Check:

   Close connection only after queries finish [OK]

Hint: Call connection.end() after queries complete [OK]

Common Mistakes:

• Calling connection.end() too early
• Assuming database is always required
• Confusing createConnection with connect method

5. You want to run a query to select all users from a MySQL database using mysql2. Which code snippet correctly handles the connection, query, and closing the connection?

hard

A. const mysql = require('mysql2');
const conn = mysql.createConnection({host:'localhost', user:'root', password:'pass', database:'test'});
conn.connect(err => {
if (err) throw err;
conn.query('SELECT * FROM users', (err, results) => {
if (err) throw err;
console.log(results);
conn.end();
});
});

B. const mysql = require('mysql2');
const conn = mysql.createConnection({host:'localhost', user:'root', password:'pass', database:'test'});
conn.query('SELECT * FROM users', (err, results) => {
if (err) throw err;
console.log(results);
});
conn.end();

C. const mysql = require('mysql2');
const conn = mysql.createConnection({host:'localhost', user:'root', password:'pass', database:'test'});
conn.end();
conn.query('SELECT * FROM users', (err, results) => {
if (err) throw err;
console.log(results);
});

D. const mysql = require('mysql2');
const conn = mysql.createConnection({host:'localhost', user:'root', password:'pass'});
conn.query('SELECT * FROM users', (err, results) => {
if (err) throw err;
console.log(results);
conn.end();
});

Solution

1. Step 1: Properly connect before querying

   const mysql = require('mysql2'); const conn = mysql.createConnection({host:'localhost', user:'root', password:'pass', database:'test'}); conn.connect(err => { if (err) throw err; conn.query('SELECT * FROM users', (err, results) => { if (err) throw err; console.log(results); conn.end(); }); }); calls connect first, ensuring connection is ready before running the query.

2. Step 2: Close connection after query completes

   const mysql = require('mysql2'); const conn = mysql.createConnection({host:'localhost', user:'root', password:'pass', database:'test'}); conn.connect(err => { if (err) throw err; conn.query('SELECT * FROM users', (err, results) => { if (err) throw err; console.log(results); conn.end(); }); }); calls conn.end() inside the query callback, so connection closes only after query finishes.

3. Step 3: Check other options

   const mysql = require('mysql2'); const conn = mysql.createConnection({host:'localhost', user:'root', password:'pass', database:'test'}); conn.query('SELECT * FROM users', (err, results) => { if (err) throw err; console.log(results); }); conn.end(); calls conn.end() immediately after query, which may close connection too soon. const mysql = require('mysql2'); const conn = mysql.createConnection({host:'localhost', user:'root', password:'pass', database:'test'}); conn.end(); conn.query('SELECT * FROM users', (err, results) => { if (err) throw err; console.log(results); }); closes connection before query. const mysql = require('mysql2'); const conn = mysql.createConnection({host:'localhost', user:'root', password:'pass'}); conn.query('SELECT * FROM users', (err, results) => { if (err) throw err; console.log(results); conn.end(); }); misses database in config.

4. Final Answer:

   Correctly connects, queries, and closes connection in proper sequence using nested callbacks -> Option A

5. Quick Check:

   Connect first, query next, then close connection [OK]

Hint: Connect first, query inside callback, then close connection [OK]

Common Mistakes:

• Closing connection before query finishes
• Not waiting for connect callback before querying
• Omitting database in connection config