Executing CRUD Operations

Intro

In this tutorial, we will learn about performing CRUD operations in Node.js. CRUD stands for Create, Read, Update, and Delete - the four basic operations that can be performed on any data. We will be using a MySQL database for this tutorial but the concepts are applicable for any relational database.

Setting Up Node.js and MySQL

Before we start, we need to set up our Node.js environment and install the MySQL driver for Node.js. Follow the steps below:

1. Install Node.js and NPM (Node Package Manager) on your computer. You can download it from the official Node.js website.

2. Once you have Node.js and NPM installed, you need to install the MySQL driver. Open your terminal or command prompt and type the following command:

   npm install mysql

3. You should also have a MySQL server up and running. You can use a local installation or a remote server.

Connecting to the Database

First, we need to establish a connection to our MySQL database. Here is how you can do it:

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
});

Create (INSERT)

To insert data into a table, we use the INSERT INTO statement. The following example inserts a new record in the "users" table:

var sql = "INSERT INTO users (name, email) VALUES ('Company Inc', '[email protected]')";
con.query(sql, function (err, result) {
  if (err) throw err;
  console.log("1 record inserted");
});

Read (SELECT)

To fetch data from a table, we use the SELECT statement. The following example selects all records from the "users" table:

con.query("SELECT * FROM users", function (err, result, fields) {
  if (err) throw err;
  console.log(result);
});

Update (UPDATE)

To update existing records in a table, we use the UPDATE statement. The following example updates the email of a user:

var sql = "UPDATE users SET email = '[email protected]' WHERE name = 'Company Inc'";
con.query(sql, function (err, result) {
  if (err) throw err;
  console.log(result.affectedRows + " record(s) updated");
});

Delete (DELETE)

To delete records from a table, we use the DELETE statement. The following example deletes a record with name "Company Inc":

var sql = "DELETE FROM users WHERE name = 'Company Inc'";
con.query(sql, function (err, result) {
  if (err) throw err;
  console.log("Number of records deleted: " + result.affectedRows);
});

Conclusion

And that's it! You've just performed basic CRUD operations in Node.js with MySQL. Remember, practicing is the key to master these operations, so don't hesitate to play around with different queries and tables.

Happy coding!