The Info Docx: Nodejs MySQL | Complete Setup Guide + CRUD 2021

Nodejs MySQL is getting popular as they mix very well together. Today we will get to know how to setup Nodejs mySQL, connect with database, create db and tables and perform CRUD operation with Nodejs and mySQL.

Introduction

Node.js is one of the most popular open-source, cross-platform backend runtime environment that uses JavaScript. It is mainly used for the purpose of using JavaScript code outside the web browser. Nowadays, Node JS has become popular with mostly NoSQL databases mostly MongoDB. As relational database system like MySQL is popular in the market today we have brought this complete guide on Nodejs MySQL.

You will learn to use Node.js with MySQL database. You will know how you can setup environment, database, tables and also perform CRUD (Create, Read, Update and Delete) operations with Nodejs MySQL.

Getting Started with Nodejs MySQL

Setting up Development Environment

First of all you need to setup your development environment. Go to the official site of Node.js and install the latest version of Node.js. You also need to install mySQL in your PC. Download mySQL from the official site and install it in your PC.

Now you need to create a folder for the project. Go to command line and go to your preferred directory by using the command: cd as change directory. Now run the code: mkdir FOLDER_NAME .Then cd into the folder and run the code: touch index.js to create a js file. Then again run the code: npm init -y to initialize npm in your project.

Now, if you will be using VS Code as your code editor then run the code: code . to open the project in VS Code. If you are using other code editor then go to the folder and open the project manually.

Install MySQL Driver

To access a MySQL database with Node.js, you need a MySQL driver.

To download and install the "mysql" module, open the Command Terminal and execute the following:

npm i mysql

Now you have downloaded and installed a mysql database driver.

Connect with a Database

First of all, we need to import MySQL. In the index.js file.

const mysql = require("mysql");

Connection with the database.

const con = mysql.createConnection({
  host: "localhost",
  user: "yourUsername",
  password: "yourPassword",
  database: "databaseName",
});

call the connect() method on the connection object to connect to the MySQL database.

con.createConnection((err) => {
  if (err) throw err;
  console.log("Connected to MySQL server");
});

Creating a Database

To create a database in MySQL, use the "CREATE DATABASE" statement.

con.query("CREATE DATABASE mydbName", (err, result) => {
  if (err) throw err;
  console.log("Database Created");
});

Creating a Table

To create a table in MySQL use the "CREATE TABLE" statement.

const sql = "CREATE TABLE users(name VARCHAR(255), email  VARCHAR(255))";

con.query(sql, (err, result) => {
  if (err) throw err;
  console.log("Table Created");
});

Primary Key

While creating a table, you should also create a column with a unique key for each record. This can be done by defining a coolumn as "INT AUTO_INCREMENT PRIMARY KEY".

const sql =  "CREATE TABLE users( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), email  VARCHAR(255))";

con.query(sql, (err, result) => {
  if (err) throw err;
  console.log("Table with primary key created");
});

Add columns in existing Table

"ALTER TABLE" statement is used to add a column in an existing table.

const sql = "ALTER TABLE users ADD COLUMN password VARCHAR(255)";
con.query(sql, (err, result) => {
  if (err) throw err;
  console.log("Table Created");
});

Learn about CRUD operation of Nodejs MySQL on Part B.

Conclusion

In this blog, you have learnt how you can setup, install, connect, create database and create, add columns in table. In this Part A of Nodejs MySQL you have learnt the basics of Nodejs with MySQL. In the next blog you will learn to create, read, update and delete data in in MySQL with Nodejs.

To get more tips, ticks and guides on Coding follow The Info Docx.

REFERENCE- Poor Coders

Also Read:

Tags: Tags: nodejs mysql, nodejs mysql2, nodejs and mysql, nodejs mysql query, nodejs mysql connection, nodejs mysql tutorial, nodejs mysql example, nodejs mysql insert, node js mysql query, nodejs mysql connection pool, nodejs with mysql, nodejs mysql library, nodejs mysql bulk insert, npm mysql, mysql2 npm, mysql js, node mysql2,npm mysql, express mysql, mysql2 npm, mysql js, npm install mysql, nodejs mysql connection, node js connect to mysql, express js mysql, mysql2 nodejs, mysql2 node, node js mysql query, mysql with node js, node js and mysql

Nodejs MySQL | Complete Setup Guide + CRUD 2021 | Part - A