Getting Started with MySQL and Node.js: A Basic Code Example

MySQL is one of the most popular open-source relational database management systems, and Node.js is a powerful JavaScript runtime environment. Combining these two technologies allows you to build robust and scalable applications that can store and retrieve data efficiently. In this article, we will guide you through the process of getting started with MySQL and Node.js using a basic code example.

Prerequisites

Before you begin, make sure you have the following prerequisites installed:

Node.js: You can download and install Node.js from the official website (nodejs.org).
MySQL: Install MySQL Server and MySQL Command Line Client from the official MySQL website (mysql.com/downloads).

Setting Up the Database Connection

To start using MySQL with Node.js, you need to establish a connection to your MySQL database. The mysql package provides an easy-to-use interface for interacting with MySQL databases. Here's a code example that sets up the database connection:

mysql = require('mysql');
var con = mysql.createConnection({
  host: "127.0.0.1",
  user: "yourUsername",
  password: "yourPassword",
  database: "mydb"
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  // Your database operations go here
});

In the above code, we create a new connection using the mysql.createConnection() method. Replace "yourUsername" and "yourPassword" with your MySQL username and password. Additionally, set the "host" property to the appropriate IP address or hostname of your MySQL server. Finally, specify the "database" you want to connect to.

Performing Database Operations

Once the database connection is established, you can perform various operations such as creating tables, inserting data, selecting data, and deleting records. Let's explore each operation using the provided code example.

Creating a Table

To create a table in MySQL, you can use the SQL CREATE TABLE statement. Here's an example that creates a table named "students" with two columns:

sql = "CREATE TABLE students(name VARCHAR(30), mobileNumber VARCHAR(20));"

con.query(sql, function(err, result) {
  if (err) throw err;
  console.log("Table created successfully!");
});

In the above code, we define the sql variable to hold the SQL statement for creating the table. The con.query() method executes the SQL query and provides a callback function to handle the result or any errors.

Inserting Data

To insert data into a table, you can use the SQL INSERT INTO statement. Here's an example that inserts a record into the "students" table:

inserting = "INSERT INTO students VALUES('Sneha', 9564489456)"

con.query(inserting, function(err, result) {
  if (err) throw err;
  console.log("Record inserted successfully!");
});

In the above code, we define the inserting variable to hold the SQL statement for inserting the data. The VALUES keyword specifies the values for each column in the table.

Selecting Data

To retrieve data from a table, you can use the SQL SELECT statement. Here's an example that selects all records from the "students" table:

desc = "SELECT DISTINCT * FROM students"

con.query(desc, function(err, result) {
  if (err) throw err;
  console.log("Table contents: ", result);
});

In the above code, we define the desc variable to hold the SQL statement for selecting the data. The DISTINCT keyword ensures that only unique records are returned. The * symbol selects all columns from the table.

Deleting Records

To remove records from a table, you can use the SQL DELETE FROM statement. Here's an example that deletes records from the "students" table based on a specific condition:

del = "DELETE FROM students WHERE name = 'S'"

con.query(del, function(err, result) {
  if (err) throw err;
  console.log("Record deleted successfully!");
});

In the above code, we define the del variable to hold the SQL statement for deleting the records. The WHERE clause specifies the condition for deleting records that match the given criteria.

Executing the Code

To execute the complete code example, save the file with a .js extension (e.g., app.js). Open your terminal or command prompt, navigate to the directory where the file is located, and run the following command:

node app.js

If the code executes successfully, you should see the output messages indicating the status of each operation, such as "Connected!", "Table created successfully!", "Record inserted successfully!", and "Record deleted successfully!".

Conclusion

In this article, we covered the basics of getting started with MySQL and Node.js. We walked through setting up the database connection and performing essential operations such as creating tables, inserting data, selecting data, and deleting records. By combining the power of MySQL and Node.js, you can build robust and efficient applications that interact with relational databases. This is just the beginning, and there is a lot more to explore and learn.

Happy coding!

Sneha Write's