NodeJs

Node.js and MySQL: Building a CRUD Application

Posted on by thuan

Node.js is a powerful platform for building server-side applications, and MySQL is one of the most popular relational database management systems. In this article, we will explore how to use Node.js and MySQL to build a CRUD (Create, Read, Update, Delete) application.

Setting Up

Before we begin, we need to set up our Node.js and MySQL environments. We need to install the mysql module to interact with the MySQL database.

To install the mysql module, run the following command in your terminal:

npm install mysql

We also need to create a MySQL database and a table. For this tutorial, we will create a database called mydatabase and a table called users with the following columns: id, name, email, and password.

sql
CREATE DATABASE mydatabase;
USE mydatabase;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255),
  password VARCHAR(255)
);

Connecting to the Database

To connect to the MySQL database, we need to create a connection object using the mysql module.

javascript
const mysql = require('mysql');

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

connection.connect((error) => {
  if (error) {
    console.error('Error connecting to the database: ' + error.stack);
    return;
  }

  console.log('Connected to the database as id ' + connection.threadId);
});

In this code, we create a connection object with the host, user, password, and database properties. We then connect to the database and log the connection thread ID to the console.

Creating Data

To create data in the users table, we need to execute an SQL query using the connection.query method.

go
const user = { name: 'John Doe', email: 'johndoe@example.com', password: 'password' };

connection.query('INSERT INTO users SET ?', user, (error, result) => {
  if (error) {
    console.error(error);
    return;
  }

  console.log('Created user with ID ' + result.insertId);
});

In this code, we define an object user with the name, email, and password properties. We then execute an SQL query to insert the user object into the users table. We log the ID of the inserted user to the console.

Reading Data

To read data from the users table, we need to execute an SQL query using the connection.query method.

lua
connection.query('SELECT * FROM users', (error, results) => {
  if (error) {
    console.error(error);
    return;
  }

  console.log(results);
});

In this code, we execute an SQL query to select all rows from the users table. We log the results to the console.

Updating Data

To update data in the users table, we need to execute an SQL query using the connection.query method.

lua
connection.query('UPDATE users SET name = ? WHERE id = ?', ['Jane Doe', 1], (error, result) => {
  if (error) {
    console.error(error);
    return;
  }

  console.log('Updated ' + result.affectedRows + ' rows');
});

In this code, we execute an SQL query to update the name column of the row with the id of 1 in the users table. We log the number of updated rows to the console.

Deleting Data

To update data in the users table, we need to execute an SQL query using the connection.query method.

lua
connection.query('DELETE FROM users WHERE id = ?', [1], (error, result) => {
  if (error) {
    console.error(error);
    return;
  }

  console.log('Deleted ' + result.affectedRows + ' rows');
});

In this code, we execute an SQL query to delete the row with the id of 1 from the users table. We log the number of deleted rows to the console.

Conclusion

In this article, we explored how to use Node.js and MySQL to build a CRUD application. We learned how to connect to the database, create, read, update, and delete data from the users table. This is just the beginning, and there’s a lot more to learn about Node.js and MySQL. I hope this article provides a good starting point for building your own applications.

0368826868