MySQL connection with mysql2 in Node.js - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this MySQL connection code?

Consider this Node.js code using mysql2 to connect to a MySQL database and query a table named 'users'. What will be logged to the console?

Node.js

import mysql from 'mysql2/promise';

async function testConnection() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'wrongpassword',
    database: 'testdb'
  });
  const [rows] = await connection.execute('SELECT * FROM users');
  console.log(rows);
}

testConnection().catch(err => console.log(err.message));

A SyntaxError: Unexpected token 'await'

B An array of user objects from the 'users' table is logged.

C Error: Table 'testdb.users' doesn't exist

D Error: Access denied for user 'root'@'localhost' (using password: YES)

💡 Hint

Check the password used in the connection configuration.

The password provided is incorrect, so the connection fails with an access denied error.

❓ component_behavior

intermediate

What happens if you forget to close the MySQL connection?

In a Node.js app using mysql2 with async/await, what is the likely effect of not calling connection.end() after queries?

A The database automatically closes the connection after 1 second.

B The app will immediately crash with an error.

C The app may keep running but eventually exhausts database connections causing errors.

D The queries will not run without calling connection.end().

💡 Hint

Think about resource management and connection limits.

Not closing connections can cause connection leaks, exhausting the database's allowed connections and causing errors.

📝 Syntax

advanced

Which option correctly creates a MySQL connection pool with mysql2?

Select the code snippet that correctly creates a connection pool using mysql2 in Node.js.

A const pool = mysql2.createPool({ host: 'localhost', user: 'root', password: 'pass', database: 'db' });

B const pool = mysql.createPool({ host: 'localhost', user: 'root', password: 'pass', database: 'db' });

C const pool = mysql.createPoolConnection({ host: 'localhost', user: 'root', password: 'pass', database: 'db' });

D const pool = mysql2.createConnectionPool({ host: 'localhost', user: 'root', password: 'pass', database: 'db' });

💡 Hint

Check the exact method name for creating a pool in mysql2.

The correct method is createPool from the mysql2 import. Other options use incorrect method names or namespaces.

🔧 Debug

advanced

Why does this mysql2 query throw a TypeError?

Look at this code snippet. Why does it throw a TypeError: connection.execute is not a function?

Node.js

import mysql from 'mysql2';

async function run() {
  const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'pass',
    database: 'db'
  });
  const [rows] = await connection.execute('SELECT 1');
  console.log(rows);
}

run();

A Because createConnection returns a callback-based connection, not a promise-based one, so execute is undefined.

B Because the SQL query syntax is invalid.

C Because the database 'db' does not exist.

D Because await cannot be used outside an async function.

💡 Hint

Check the import and method used to create the connection.

The mysql2 package's createConnection without '/promise' returns a callback API connection without execute method returning promises.

🧠 Conceptual

expert

How does mysql2 handle prepared statements with parameter binding?

Which statement best describes how mysql2 handles prepared statements and parameter binding in queries?

A mysql2 automatically escapes parameters passed as an array to execute(), preventing SQL injection.

B mysql2 requires manual escaping of parameters before passing them to queries.

C mysql2 only supports parameter binding for SELECT queries, not INSERT or UPDATE.

D mysql2 does not support prepared statements or parameter binding.

💡 Hint

Think about security and how parameters are handled in execute().

mysql2's execute() method accepts parameters as an array and safely escapes them to prevent SQL injection.

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