Node.js Tutorial: How to connect a MySQL Database and Node.js

node.js and MySQL

Node.js is an open-source server environment. It’s free and runs on various platform such as Windows, Linux, Mac OS X, etc. Node.js was written initially by Ryan Dahl in 2009. It’s written in C, C++, and JavaScript. It can generate dynamic page content, collect form data, manage data in your database, manage files on a server, and etc… In this tutorial, you will learn how to connect a MySQL database and Node.js

Read First 
JavaScript Tutorial: Introduction for beginners (Basic)
Node.js Tutorial: Introduction for beginners (Basic)
Node.js Tutorial: Send an Email with Attachment

What is SQL?

SQL (Structured Query Language) is a standard language for storing, managing and retrieving data in databases. SQL was developed at IBM by Donald Chamberlin and Raymond Boyce. and first released in 1991. SQL became a standard of the ANSI (American National Standards Institute) in 1986.

SQL Tutorial: Introduction for beginners (Basic)

How to Connect a MySQL Database and Node.js

Installation (MySQL)

XAMPP – Apache (A) + MariaDB(M) + PHP(P) + Perl(P)

Before starting this tutorial, you should have MySQL installed on your PC.

CtechF - How to install XAMPP - Screenshot

XAMPP is an Open source software. It’s a Stands of Cross-platform (X). we can create a local web server using XAMPP. It’s easy to use and it works with Windows, Linux, and Mac OS. You can use alternative software for XAMPP, such as WAMP, MAMP.

XAMPP Download link (121 MB) – https://www.apachefriends.org/index.html
WAMP download link (286 MB) – http://www.wampserver.com/en/#download-wrapper
MAMP download link (410 MB) – https://www.mamp.info/en/downloads/

How to install XAMPP

Click on the downloaded XAMPP setup. (If you got any permission message or warning message click “OK” or “YES” button to continue) It will open the XAMPP setup wizard. Click the next button to continue.

CtechF - How to install XAMPP - Screenshot

Now it asked, what components we want to install and what’s not. We only need Apache and MySQL. (select following Components and click the next button to continue)

  • Server
    • Apache – Apache Server
    • MySQL – Database Server
  • Program Language
    • PHP – Hypertext Preprocessor
  • Program Languages
    • phpMyAdmin – Database Control panel
CtechF - How to install XAMPP - Screenshot

Choose installation location and click Next (Default: C:\xampp)  (If you got Windows Defender Firewall Permission request, Tick both checkboxes and click Allow access.) Then click the next button to continue. Now it will install XAMPP (wait till the installation complete) After the installation complete. Click Finish. It will open the XAMPP Control panel automatically.

This is a XAMPP Control Panel.

CtechF - How to install XAMPP - Screenshot

Now click Start Apache and MySQL (It will start Apache and MySQL servers)

CtechF - How to install XAMPP - Screenshot

Completed

Connect a MySQL Database and Node.js

Creating a Project Directory

First, create a directory for our project. then enter the following command to create a package.json 

npm init -y
node.js - npm init

Install MySQL Driver

We use the “mysql” module for this tutorial. To download and install the module, open the cmd and execute the following command

npm install mysql --save
node.js - install mysql driver

Node.js/MySQL – Create Connection

Include the module

var db = require('mysql');

dbConnect.js

var db = require('mysql');

var conn = db.createConnection({
  host: "localhost",
  user: "username",
  password: "password"
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
});
connect a MySQL database and node.js

Open Command Prompt and navigate to the directory where you saved  “dbConnect.js” then enter the following command and press enter to execute your code.

node dbConnect.js

Node.js/MySQL – Creating a Database

createDB.js

var db = require('mysql');

var conn = db.createConnection({
  host: "localhost",
  user: "username",
  password: "password"
});

conn.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  conn.query("CREATE DATABASE db_nodejs", function (err, result) {
    if (err) throw err;
    console.log("Database created successfully");
  });
});

Open Command Prompt and navigate to the directory where you saved  “createDB.js” then enter the following command and press enter to execute your code.

node createDB.js

Node.js/MySQL – Creating a Table

createTable.js

var db = require('mysql');

var conn = db.createConnection({
  host: "localhost",
  user: "username",
  password: "password",
  database: "db_nodejs"
});

conn.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  
  var sql = "CREATE TABLE users (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(30) NOT NULL, lname VARCHAR(30), email VARCHAR(100) NOT NULL, contact VARCHAR(25), note VARCHAR(400))";
  
  conn.query(sql, function (err, result) {
    if (err) throw err;
    console.log("Table created successfully");
  });
});

Open Command Prompt and navigate to the directory where you saved  “createTable.js” then enter the following command and press enter to execute your code.

node createTable.js
node.js - MySQL

Node.js/MySQL – Insert Into Table – One Record

insertData.js

var db = require('mysql');

var conn = db.createConnection({
  host: "localhost",
  user: "username",
  password: "password",
  database: "db_nodejs"
});

conn.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  
   var sql = "INSERT INTO users (fname, lname, email, contact, note) VALUES ('Tony', 'Norman', 'tony@domain.com', '201 123-1234', 'Welcome')";

  
  conn.query(sql, function (err, result) {
    if (err) throw err;
    console.log(Data inserted!, ID: " + result.insertId); //Data inserted!, ID: 1
  });
});

Open Command Prompt and navigate to the directory where you saved  “insertData.js” then enter the following command and press enter to execute your code.

node insertData.js
node.js - MySQL

Node.js/MySQL – Insert Into Table – Multiple Records

insertDatas.js

var db = require('mysql');

var conn = db.createConnection({
  host: "localhost",
  user: "username",
  password: "password",
  database: "db_nodejs"
});

conn.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");

  var sql = "INSERT INTO users (fname, lname, email, contact, note) VALUES ?";

  var values = [
    ['John', 'Peter', 'Peter@123apple.com', '', ''],
    ['Sheldon', 'Cooper', 'sheldonleecooper@yahoo.com', '125478541', 'Moonpi'],
    ['Amy', '', 'amy@domain.com', '', 'Sheldon'],
    ['James', 'Cole', 'jc@2041fe.com', '', '2014'],
    ['Andy', 'Newt', 'newt@gmail.com', '123 154-5545', ''],
    ['Penny', '', 'Penny@freewifi.com', '', 'Leonard'],
    ['admin', 'CtechF', 'admin@ctechf.com', '154 584-5478', 'technology']
  ];
  conn.query(sql, [values], function (err, result) {
    if (err) throw err;
    console.log("Number of records inserted: " + result.affectedRows);
  });
});

Open Command Prompt and navigate to the directory where you saved  “insertDatas.js” then enter the following command and press enter to execute your code.

node insertDatas.js
connect a MySQL database and node.js
node.js - MySQL

Node.js/MySQL – Select *

select.js

var db = require('mysql');

var conn = db.createConnection({
  host: "localhost",
  user: "username",
  password: "password",
  database: "db_nodejs"
});

conn.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");

  var sql = "SELECT * FROM users";

  conn.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Open Command Prompt and navigate to the directory where you saved  “select.js” then enter the following command and press enter to execute your code.

node select.js
connect a MySQL database and node.js

Node.js/MySQL – Limit the Result

selectLimit.js

var db = require('mysql');

var conn = db.createConnection({
  host: "localhost",
  user: "username",
  password: "password",
  database: "db_nodejs"
});

conn.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");

  var sql = "SELECT * FROM users LIMIT 2";

  conn.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Open Command Prompt and navigate to the directory where you saved  “selectLimit.js” then enter the following command and press enter to execute your code.

node selectLimit.js
connect a MySQL database and node.js

Node.js/MySQL – Select (Where)

selectWhere.js

var db = require('mysql');

var conn = db.createConnection({
  host: "localhost",
  user: "username",
  password: "password",
  database: "db_nodejs"
});

conn.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");

  var sql = "SELECT * FROM users WHERE fname = 'Sheldon'";

  conn.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Open Command Prompt and navigate to the directory where you saved  “selectWhere.js” then enter the following command and press enter to execute your code.

node selectWhere.js
connect a MySQL database and node.js

Node.js/MySQL – Select (Order By)

selectOrderby.js

var db = require('mysql');

var conn = db.createConnection({
  host: "localhost",
  user: "username",
  password: "password",
  database: "db_nodejs"
});

conn.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");

  var sql = "SELECT * FROM users ORDER BY fname DESC"; //ASC or DESC

  conn.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Open Command Prompt and navigate to the directory where you saved  “selectOrderby.js” then enter the following command and press enter to execute your code.

node selectOrderby.js
connect a MySQL database and node.js

Node.js/MySQL – Update

update.js

var db = require('mysql');

var conn = db.createConnection({
  host: "localhost",
  user: "username",
  password: "password",
  database: "db_nodejs"
});

conn.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");

  var sql = "UPDATE users SET fname = 'Updated' WHERE id = 2";

  conn.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Open Command Prompt and navigate to the directory where you saved  “update.js” then enter the following command and press enter to execute your code.

node update.js
node.js - MySQL

Node.js/MySQL – Delete Record

delete.js

var db = require('mysql');

var conn = db.createConnection({
  host: "localhost",
  user: "username",
  password: "password",
  database: "db_nodejs"
});

conn.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");

  var sql = "DELETE FROM users WHERE id = 2";

  conn.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result.affectedRows + " Record(s) Deleted");
  });
});

Open Command Prompt and navigate to the directory where you saved  “delete.js” then enter the following command and press enter to execute your code.

node delete.js
node.js - MySQL

Node.js/MySQL – ALTER  Table

alter.js

var db = require('mysql');

var conn = db.createConnection({
  host: "localhost",
  user: "username",
  password: "password",
  database: "db_nodejs"
});

conn.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");

  var sql = "ALTER TABLE users ADD COLUMN age INT(5)";

  conn.query(sql, function (err, result) {
    if (err) throw err;
    console.log("Table altered");
  });
});

Open Command Prompt and navigate to the directory where you saved  “alter.js” then enter the following command and press enter to execute your code.

node alter.js
node.js - MySQL

Node.js/MySQL – Drop a Table

drop.js

var db = require('mysql');

var conn = db.createConnection({
  host: "localhost",
  user: "username",
  password: "password",
  database: "db_nodejs"
});

conn.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");

  var sql = "DROP TABLE users";

  conn.query(sql, function (err, result) {
    if (err) throw err;
    console.log("Table Droped!");
  });
});

Open Command Prompt and navigate to the directory where you saved  “drop.js” then enter the following command and press enter to execute your code.

node drop.js

github: https://github.com/ctechf/node.js_MySQL-Tutorial

Sources : 
https://pixabay.com/ (images)

Hello Friends,

Hope this post (Node.js Tutorial: How to connect a MySQL database and Node.js) will help you to learn something, If you enjoy my work then please share my posts with your friends and anyone who might be interested in programming and web developing. Please don’t forget to subscribe to my mailing list.

If you had any problem with this post, please do mention it in the comment section.

Like CtechF Facebook fan page: https://www.facebook.com/ctechf

What is Python?

Python is a scripting language and that is used for creating engineering-analysis tools, web development (server-side), software development, system scripting, and animation software. It designed by Guido van Rossum and first released in 1991. It’s an interpreted, High-level. General-purpose language. Python runs on Windows, Mac OS, and Linux.

Python Tutorial: Introduction for Beginners (Part 1)
Python Tutorial: Introduction for Beginners (Part 2)
Python Tutorial: How to Connect MySQL Database in Python
Python Tutorial: Python GUI Programming (Tkinter)
Python Tutorial: Send Emails Using Python
Python Tutorial: Django Tutorial for Beginners

What is HTML?

HTML (HyperText Markup Language) is developed by Tim Berners-Lee. An HTML document which can be displayed through a web browser is called a web page. HTML is not a computer language it’s a markup language.

For more information: HTML Tutorial: Introduction for beginners (Basic)

What is PHP?

PHP: Hypertext Preprocessor is a general-purpose server-side script language. it designed for web development. It designed by Rasmus Lerdorf in 1994 and first released in 1995. PHP is free to download and use. It can contain text, HTML, JavaScript, CSS and PHP code. it runs on Windows, Linux, Mac OS and etc… PHP can do many things such as generate dynamic web pages, manage data in your database, control user access, etc… PHP 7 is much faster than the previous versions. In this tutorial. You will learn to install and run PHP on Windows pc.

For more information
PHP 7 Tutorial: Introduction for Beginners (Basic)
PHP7 Tutorial | Part 2 – GET, POST and Cookies
PHP7 Tutorial | Part 3 – MySQL and PHP

How to be a Programmer

Liked it? Take a second to support CtechF on Patreon!

Leave a Reply

27 Shares
Share
Tweet
Share
Pin
Share