Node.js with MySQL

Welcome to The Coding College! If you’re looking to integrate MySQL with Node.js, you’re in the right place. This guide will walk you through setting up MySQL in your Node.js projects, performing CRUD operations, and managing databases effectively.

Why Use Node.js with MySQL?

  • Scalability: Node.js handles asynchronous operations efficiently, making it suitable for database-driven apps.
  • Community Support: MySQL has robust documentation and support for Node.js libraries.
  • Flexibility: Ideal for applications requiring relational data management.

Setting Up the Environment

Prerequisites

  1. Node.js installed: Download Node.js.
  2. MySQL installed: Download MySQL.
  3. Basic knowledge of SQL queries.

Install the MySQL Package

Install the official MySQL driver for Node.js:

npm install mysql

Connecting Node.js to MySQL

Step 1: Import the MySQL Module

Create a file named app.js and import the MySQL module:

const mysql = require('mysql');

Step 2: Create a Database Connection

Set up the connection parameters for your MySQL database:

const connection = mysql.createConnection({
  host: 'localhost', // Your database host
  user: 'root', // Your database username
  password: 'your-password', // Your database password
  database: 'mydatabase', // Your database name
});

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

Run the script:

node app.js

Creating and Managing Databases

Create a Database

Use the CREATE DATABASE SQL query to create a database programmatically:

const createDatabase = 'CREATE DATABASE IF NOT EXISTS mydatabase';

connection.query(createDatabase, (err, result) => {
  if (err) throw err;
  console.log('Database created or already exists.');
});

Create a Table

Create a table named users with columns id, name, and email:

const createTable = `
  CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
  )
`;

connection.query(createTable, (err, result) => {
  if (err) throw err;
  console.log('Table created or already exists.');
});

CRUD Operations

Insert Data

Add a record to the users table:

const insertQuery = 'INSERT INTO users (name, email) VALUES (?, ?)';
const userData = ['John Doe', '[email protected]'];

connection.query(insertQuery, userData, (err, result) => {
  if (err) throw err;
  console.log('Record inserted:', result.insertId);
});

Read Data

Retrieve all users from the users table:

const selectQuery = 'SELECT * FROM users';

connection.query(selectQuery, (err, results) => {
  if (err) throw err;
  console.log('Users:', results);
});

Update Data

Update a user’s name by their email:

const updateQuery = 'UPDATE users SET name = ? WHERE email = ?';
const updateData = ['Jane Doe', '[email protected]'];

connection.query(updateQuery, updateData, (err, result) => {
  if (err) throw err;
  console.log('Records updated:', result.affectedRows);
});

Delete Data

Delete a user by their email:

const deleteQuery = 'DELETE FROM users WHERE email = ?';
const deleteData = ['[email protected]'];

connection.query(deleteQuery, deleteData, (err, result) => {
  if (err) throw err;
  console.log('Records deleted:', result.affectedRows);
});

Best Practices

  1. Use Connection Pools: Improve performance by reusing database connections.
const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'your-password',
  database: 'mydatabase',
});

pool.query('SELECT * FROM users', (err, results) => {
  if (err) throw err;
  console.log('Users:', results);
});
  1. Parameterize Queries: Prevent SQL injection attacks by using placeholders (?).
  2. Error Handling: Handle database connection errors gracefully to avoid app crashes.
  3. Environment Variables: Store sensitive information (e.g., credentials) in environment variables.

Advanced Topics

  1. ORM Integration: Use tools like Sequelize for advanced database management.
  2. Transactions: Ensure data integrity with database transactions.
  3. Scaling: Explore replication and sharding for high-traffic applications.

Frequently Asked Questions

Q1: Can I use MySQL with TypeScript in Node.js?
Yes, you can use TypeScript with the MySQL module by adding type definitions or using ORMs like Sequelize.

Q2: How do I secure my database credentials?
Use environment variables or secret managers to store sensitive information.

Q3: Can I connect to a remote MySQL server?
Yes, update the host parameter in the connection configuration with the remote server’s IP address.

Conclusion

Integrating MySQL with Node.js enables you to build robust, data-driven applications with ease. From simple CRUD operations to advanced database management, Node.js offers the tools you need for effective database handling.

At The Coding College, we’re committed to providing you with detailed tutorials to enhance your coding journey. Stay tuned for more guides and tips to level up your skills!

Leave a Comment