Node.js MySQL Get Started

Node.js provides the ability to interact with MySQL databases using various modules. One of the most popular modules is mysql2, which offers a simple interface for connecting to and querying MySQL databases.

 

Key Features of Node.js MySQL

1. Connection to MySQL: Establish a connection to a MySQL database from a Node.js application.
2. Execute Queries: Run SQL queries, including SELECT, INSERT, UPDATE, and DELETE.
3. Promise-based API: Supports both callback-based and promise-based APIs for easier handling of asynchronous operations.

 

Step 1 Prerequisites

Before starting, make sure you have MySQL installed and running. Also, install the mysql2 package in your Node.js project.

npm install mysql2

Step 2 Create a Database Connection

To interact with MySQL, first, establish a connection using the mysql2 package.

Example Code

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',        // Replace with your MySQL username
  password: '',        // Replace with your MySQL password
  database: 'testdb'    // Replace with your database name
});

// Connect to the MySQL server
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }
  console.log('Connected to MySQL as id ' + connection.threadId);
});

Step 3 Running Queries

Once a connection is established, you can run SQL queries such as SELECT, INSERT, UPDATE, and DELETE.

Example Code for Running a SELECT Query

// Run a simple SELECT query
connection.query('SELECT * FROM users', (err, results, fields) => {
  if (err) {
    console.error('Error executing query:', err.stack);
    return;
  }
  console.log('Query results:', results);
});

Output:

Query results: [
  { id: 1, name: 'Alice', email: '[email protected]' },
  { id: 2, name: 'Bob', email: '[email protected]' }
]

Example Code for Inserting Data

// Insert a new user into the 'users' table
const user = { name: 'Charlie', email: '[email protected]' };

connection.query('INSERT INTO users SET ?', user, (err, results) => {
  if (err) {
    console.error('Error executing insert:', err.stack);
    return;
  }
  console.log('Inserted user ID:', results.insertId);
});

Output:

Inserted user ID: 3

Step 4 Using Promises for Queries

mysql2 also supports promises, which allow you to handle asynchronous operations more easily using async/await.

Example Code with Promises

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

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

  try {
    const [rows, fields] = await connection.execute('SELECT * FROM users');
    console.log('Users:', rows);
  } catch (err) {
    console.error('Error executing query:', err);
  } finally {
    await connection.end();
  }
}

getUsers();

Output:

Users: [
  { id: 1, name: 'Alice', email: '[email protected]' },
  { id: 2, name: 'Bob', email: '[email protected]' }
]

Step 5 Closing the Connection

It is important to close the connection to MySQL once your queries are complete.

Example Code

connection.end((err) => {
  if (err) {
    console.error('Error closing the connection:', err.stack);
    return;
  }
  console.log('Connection closed');
});

 

Summary

Node.js provides an easy way to interact with MySQL databases using the mysql2 module. You can establish a connection, run queries, handle results using callbacks or promises, and close the connection after the operations are complete. This makes it simple to integrate MySQL into your Node.js applications for data management.