In this article, I will demonstrate how to establish a connection with SQL and perform various CRUD operations in a few simple and easy steps. I will be using phpMyAdmin as MySQL database.
Prerequisites
- Install the latest LTS version of Node.JS from https://nodejs.org/en/download/
- Install the latest version of Visual Studio 2019 from https://visualstudio.microsoft.com/downloads/
- Prior experience in the usage of MongoDB with NodeJS.
Why phpMyAdmin as a database?
phpMyAdmin has a very good UI/UX where we can easily create a new database, new table, insert multiple values to columns at a time, can change and add a primary /foreign key, export/import complete database, etc with few clicks.
In order to run phpMyAdmin server, we need to download XAMPP. You can download XAMPP from XAMPP official site.
Now that we are done with the installation, let us try to start the MySQL through XAMPP.
To Start MySQL through phpMyAdmin click start on Apache and MySQL and you can see the port’s number when no error occurs. To open phpMyAdmin go to http://localhost/phpmyadmin/ .
How to use phpMyAdmin?
When phpMyAdmin is visited, you can create a new database by clicking on new and giving a name to the database, and for creating tables click on create.
Give a name to the table and select the number of columns and click on Go to create a table.
You can give names to your column and select the type format of data such as INT, VARCHAR, TEXT, etc. To add Auto Increment to an attribute click on A_I on the respective attribute. Before saving, to see a preview of SQL code, click on Preview SQL and then save.
To see the structure of the table, click on the structure on the navbar. To add an attribute as Primary Key hover on More and select primary. To add more columns select the extra number of columns to be added and click on Go.
Now to add values in the columns click on insert in the navbar and give the required information in the respective columns. To add more rows change the number of new rows to be created at the bottom of the page. After filling data click on Go and changes will be saved.
To see values added in the table click on browse and see the information added. You can edit, copy, and delete each row or complete table information. Now to add more data in columns follow the above procedure.
By the above procedure, you can create a database and add columns in the database and insert data in the columns.
Establishing a connection between phpMyAdmin and NodeJS
Here I will show how to connect Node.js with MySQL database to store the data of learners in it. Since my main aim is to demonstrate data transactions from the database, I will be mainly focussing on the connection with MySQL.
You need to install the required packages like express.js,body-parser, nodemon, etc. which we use even in integrating with MongoDB.
Create a new file script.js and import the required packages in your file.
Next, you need to establish a connection with your MySQL database. For that provide the required details such as host, user, password and database. Be very careful while entering the details else the MySQL won’t authorize the connection. By-default host is “localhost” , user is “root” and empty string is password.
The above code will establish connections between MySQL of phpMyAdmin and NodeJS. Make sure by giving the correct database name as given in phpMyAdmin. Now let’s see how to do get and post methods.
The above code is a get method route. Store the SQL command needed to retrieve information in a variable say getQuery. When the “/”- route is visited the command runs and stores all the data in the result variable. We can manipulate the command and get required columns and add “where” or “group by” or “order by”, etc statements in getQuery. You can even send the result to books.ejs file by the above code and use it when required in the ejs file.
To see the code of books.ejs file click here.
Now let’s see the procedure for the post method.
After inserting the given information in the form. The details must store in the database. So here post route is used.
To insert information from an ejs file, first store in a variable using body-parser package. Here in variable “sql ”insert command is given but in values ? are placed since they are inserted dynamically. In getQuery variable we can insert name, number, etc information using mysql.format and pass it through con.query. After inserting into the database you can see “1 record inserted” statement in the console.
You can see the inserted element in phpMyAdmin along with previously existed elements as shown in the above picture.
Since the database got updated, the books.ejs file also gets updated and the new book is shown on the page.
By the above methods, we can add get and posts requests in nodeJS using MySQL database.
To see a complete project using MySQL with nodeJS with all the CRUD Operations visit my Github.
In this article, we’ve looked at the implementation of MySQL using NodeJS. You can see and learn more methods through W3Schools .
