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!