In this blog, we will have a look at getting started with the MySQL npm package which is a Node.js client for connecting to MySQL, written in JavaScript. We will see how to use the package to connect to a MySQL database and perform the CRUD operations. We will begin with creating a new directory onto the local machine followed by initializing a node.js app to be able to code the way to connect to the database. Navigating to the MySQL Workbench software, we will see how to create a new connection and create a new schema. We will then install the MySQL package and insert a code snippet to create a connection with the database. Then in the MySQL Workbench, creating a new SQL file, we will create a new table and insert some junk data in it. Moving back to the code editor, we will then write code for performing the CRUD operations and parallelly test out the results.
Note: Please ensure that Node.js and MySQL Workbench are properly installed onto the local machine or else it might display multiple errors while following the process.
Required installations
- Node.js: It is a JavaScript runtime environment that executes JavaScript code outside the browsers.
- Mysql: It is an npm package known to be a Node.js client for connecting to MySQL and performing the different operations. The package is written in JavaScript.
- MySQL Workbench: It is a visual database design tool used to create a connection with the SQL databases and manage the design and overall operations for the same.
Hands-on
Create a new directory on your local machine.
Open the newly created directory into a code editor.
Right-click in the left navigation pane and click on New File.
Create a new file with the name package.json.
Right-click in the newly created directory and click on Git Bash Here.
Run the command: npm init
Press yes after selecting the default values.
Verify the configuration setup in the package.json file created above.
Now run the command: npm install mysql
A new package-lock.json file will be created in the directory.
Verify the package installation by navigating to the package.json file.
Create a new file with the name app.js.
Now, open MySQL Workbench software. Click on the plus sign to create a new connection.
Enter in the required configuration and store a password if needed. Click on Ok once done.
On the top nav bar, you will see the option to create a new schema in the connected server. Click on it.
Enter a name for the schema and click on Apply at the bottom.
Verify the command and click on Apply.
On success, you will see the screen as shown in the image below. Click on Finish.
Verify the creation of the schema in the left navigation pane.
Now, navigate to the code editor and add the following code in the app.js file to create a connection with the db.
Run the command:
node app.js
On success, it will display the connected messages.
Now, modifying the connection creation process, alter the code with the below code.
Run the command:
node app.js
Check if the connection was successful or not.
Now, navigate back to the MySQL Workbench and create a new SQL file.
Add the table creation query as shown below in the file and execute the query.
On success, you will see the newly created table under the schema.
Right-click on the schema and click on Set as Default Schema.
Now, in the SQL file, add the following insert query to input some data in the table.
Run the select all query to verify the data creation.
SELECT Operation
Navigate back to the code base and add the following Select Query code to fetch the dummy data.
The final code with the query looks as shown in the image below.
Run the command:
node app.js
On success, you will see the data as shown in the image below.
Use the below code to alter the output displaying it in a better way.
The final code looks as shown in the image below.
On executing the above code, you will see the output as shown in the image below.
INSERT Operation
Now, let’s execute the Insert operation. Add the following code to the code base.
On success, you will see the ID of the newly created user as shown in the image below.
You can verify the new user creation by navigating to the MySQL Workbench software.
UPDATE Operation
Use the below code adding it to the code base to perform the update operation.
Executing the code, on success, you will see the message as shown below which means that one row has been updated.
Now, we can use the select query code to identify the changes. Run the below code.
On success, you will see the updated value in the output.
We can verify the same by navigating to the MySQL Workbench. Run the below query and verify the change.
DELETE Operation
Now, use the below code to perform the Delete operation. We will delete the newly added entry.
On success, you will see the message as shown in the image below which stated that one row has been deleted.
We can verify the same using the Select operation code.
On success, you will see the entry deleted.
We can verify the same on MySQL Workbench. Run the below select query and verify the result.
Conclusion
In this hands-on, we had a look at how to get started with the MySQL npm package which is a Node.js client for connecting to MySQL, written in JavaScript. We saw how to use the package to connect to a MySQL database and perform the CRUD operations. We began with creating a new directory onto the local machine followed by initializing a node.js app to be able to code the way to connect to the database. Navigating to the MySQL Workbench software, we saw how to create a new connection and create a new schema. We then installed the MySQL package and inserted a code snippet to create a connection with the database. Then in the MySQL Workbench, creating a new SQL file, we created a new table and inserted some junk data in it. Moving back to the code editor, we then coded the different ways for performing the CRUD operations and parallelly tested out the results. We will come up with more such use cases in our upcoming blogs. Stay tuned to keep getting all updates about our upcoming new blogs on AWS and relevant technologies.
Meanwhile…
Keep Exploring -> Keep Learning -> Keep Mastering
This blog is part of our effort towards building a knowledgeable and kick-ass tech community. At Workfall, we strive to provide the best tech and pay opportunities to AWS-certified talents. If you’re looking to work with global clients, build kick-ass products while making big bucks doing so, give it a shot at workfall.com/partner today.
