Hello Sharpnerians . I hope you are doing really well.
Now that we know the different queries that we can work with in sql let’s try to see how we can connect it to a express app.
In this video, we will connect to a MySQL database using express js app .
We will then create a simple table named students in the database. This will give you a hands-on understanding of how to interact with MySQL using Node.js application.
Steps:
- Install the mysql2 library to interact with the MySQL database.
- Create a MySQL connection to the database.
- Write a query to create a table if it does not exist.
- Execute the query to create the table and close the connection.
Step 1: Install mysql2
To start, we need to install the mysql2 library to enable interaction with the MySQL database. Run the following command in your terminal:
npm install mysql2
Step 2: Create a Script to Connect and Create a Table
Create a new file called create-table.js and add the following code:
// Step 1: Import the express and mysql2 libraries
const express = require(‘express’);
const mysql = require(‘mysql2’);
// Step 2: Set up the express application
const app = express();
const port = 3000; // You can change the port as needed
// Step 3: Create a connection to the MySQL database
const connection = mysql.createConnection({
host: ‘localhost’, // Database host
user: ‘root’, // Replace with your MySQL username
password: ‘password’, // Replace with your MySQL password
database: ‘testdb’, // Replace with your database name
});
// Step 4: Connect to the database and create the table
connection.connect((err) => {
if (err) {
console.error(‘Error connecting to the database:’, err.message);
return;
}
console.log(‘Connected to the database.’);
// Step 5: Create a SQL query to create the “students” table
const createTableQuery = `
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE
);
`;
// Step 6: Execute the query to create the table
connection.query(createTableQuery, (err, result) => {
if (err) {
console.error(‘Error creating table:’, err.message);
connection.end();
return;
}
console.log(‘Table “students” created or already exists.’);
// Step 7: Close the database connection after creating the table
connection.end();
});
});
// Step 8: Define a basic route
app.get(‘/’, (req, res) => {
res.send(‘Hello, Sharpnerians! The MySQL database is connected, and the “students” table is set up.’);
});
// Step 9: Start the Express server
app.listen(port, () => {
console.log(`Server is running on http://localhost:${port}`);
});
Step 3: Run the Script
To run the script, navigate to your project folder in the terminal and execute the following command:
bash
Copy code
node create-table.js
Expected Output
If the script runs successfully, you will see the following messages:
plaintext
Connected to the database.
Table “students” created or already exists.
If there’s an issue connecting to the database or creating the table, you will see error messages indicating the problem.
Explanation of the Script
- Step 1: We import the mysql2 library, which allows us to interact with MySQL databases.
- Step 2: We create a connection to the database using mysql.createConnection(). We provide necessary credentials such as the host, user, password, and database name.
- Step 3: The connection.connect() method is used to establish a connection to the database. If an error occurs, we log it, otherwise, we print a success message.
- Step 4: A SQL query is created to check if the students table exists, and if not, it creates the table with three columns: id, name, and email.
- Step 5: The connection.query() method is used to execute the SQL query. If the query executes successfully, we log the success message. If there is an error, we handle it accordingly.
- Step 6: Once the query is executed, we close the database connection using connection.end() to release the resources.
Conclusion
In this simple script, we learned how to connect to a MySQL database from Node.js using the mysql2 library and create a table if it does not already exist. This forms the foundation for building more complex interactions with MySQL databases in your Node.js applications
