Crash course on MySQL

Introduction to MySQL along with difference between SQL and No-SQL

What is a Database?

A database is a separate application that stores a collection of data. Each database has one or more distinct APIs for creating, accessing, managing, searching and replicating the data it holds.

Other kinds of data stores can also be used, such as files on the file system or large hash tables in memory but data fetching and writing would not be so fast and easy with those type of systems.

Nowadays, we use relational database management systems (RDBMS) to store and manage huge volume of data. This is called relational database because all the data is stored into different tables and relations are established using primary keys or other keys known as Foreign Keys.

A Relational DataBase Management System (RDBMS) is a software that −

• Enables you to implement a database with tables, columns and indexes.
• Guarantees the Referential Integrity between rows of various tables.
• Updates the indexes automatically.
• Interprets an SQL query and combines information from various tables.

Differences between SQL and No-SQL

Topics	SQL	No-SQL
Full form	Structured Query Language	Not only SQL / Non-SQL / non-relational
Schema	SQL databases must have predefined schema	NoSQL databases are schema less
Type	Store data in table based fromat	Storage of data is in key value pairs
General Use Case	Used for ACID compliant tasks	Used for fast availability of data
Scalability	Vertical	Horizontal
Excels at	Multi-row transaction	unstructured data storage
Consistency	Strong consistency	Eventual Consistency
Designed For	OLAP	OLTP
example	Oracle, Postgres, and MySQL	MongoDB, Redis, DynamoDB, Hbase, Casandra

Installation and Setting up MySQL

Installation of MySQL are done in the following steps

1. Installing MySQL

Update the apt repository to the latest

$ sudo apt update

Install the package mysql-server

$ sudo apt install mysql-server

Run the service

$ sudo systemctl start mysql.service

2. Configuration

Install the MySQL through mysql_secure_installation

$ sudo mysql_secure_installation

Go through the secure installation process and setup password for the root user.

Setting up privileges

running MySQL with sudo privileges

$ sudo mysql

setting up the password

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';

Flushing privileges

mysql> FLUSH PRIVILEGES;

Granting privileges

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

After this process is complete login to MySQL with root user with password

Getting into the MySQL

$ mysql -u root -p
Enter password: ********

## after correct password
mysql>

Now you have access to the MySQL as root and can access MySQL through node, with root user username and password.

CRUD operations through raw query

Database/ Schema Creation

## Schema / Database creation
CREATE DATABASE test;

Table Creation

## Table creation
CREATE TABLE test.users (
    id int NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    email varchar(255) UNIQUE,
    phone int,
    PRIMARY KEY (id)
);

INSERT

INSERT INTO test.users (name, email, phone) 
VALUES ("Shuvam", "shuvamlamichhane@gmail.com", 9800000000);

SELECT

SELECT * FROM test.users;

UPDATE

UPDATE test.users SET name = "Shuvam Lamichhane" WHERE name = "Shuvam";

DELETE

DELETE FROM test.users where email = "shuvamlamichhane@gmail.com";

Using MySQL along with node using mysql2 package

Installing the package

$ npm i mysql2

Initializing the DB

const mysql = require('mysql2');

const pool = mysql.createPool({
    port: process.env.DBPORT,
    user: process.env.DBUSER,
    password: process.env.DBPASSWORD,
    host: process.env.DBHOST,
    database: process.env.DBNAME
});

module.exports = pool.promise();

File: db.js

CRUD Operations

INSERT

const db = require("./db");

const id = "1234";
const email = "scanskill@scanskill.com";
const password = "V3RYSTr0ngP@55w0rD";
const status = "active";
const user_type = "admin";

const insertUserQuery = 
`
  INSERT INTO user (id, email, password, status, user_type)
  VALUES ('${id}', '${email}', '${password}', '${status}', '${user_type}')
`;
//* Inserting user to the database
await db.execute(insertUserQuery);

SELECT

const db = require("./db");

const id = "1234";
const statue = "active";

const selectQuery = 
`
  SELECT * FROM user 
  WHERE id = '${id}' AND status = '${status}'
`;

const [userPresent] = await db.execute(selectQuery);

UPDATE

const db = require("./db");

const id = "1234";
const email = "scanskill2@scanskill.com";
const password = "weakpassword";
const status = "inactive";
const user_type = "normal";

let updateQuery = 
` 
  UPDATE user SET email = '${email}', password = '${password}', 
  user_type = '${user_type}', status = '${status}', updated_at = CURRENT_TIMESTAMP 
  WHERE id = '${id}'
`;

//* executing the query
await db.execute(updateQuery);

DELETE

const db = require("./db");

const id = "1234";

let deleteQuery = ` DELETE FROM user WHERE id = '${id}' `;

//* executing the query
await db.execute(deleteQuery);

Using MySQL along with node using Sequilize package

installing the package

$ npm i sequelize mysql2

sequelize puts it’s wrapper on top of mysql2.

Initializing the db

const { Sequelize, QueryTypes } = require("sequelize");
require("dotenv").config();

const db = new Sequelize({
  host: process.env.DB_HOST,
  username: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DATABASE, // if removed whole database is accessible
  dialect: "mysql", // uses mysql2 under the hood.
  logging: false
});

async function syncDB() {
    Logger.info("Connected to the Database")
  return await db.sync();
}

when mysql is chosen as the dialect mysql2 package should be installed.

CRUD Operations

CREATE / INSERT

const insertUserQuery = 
`
  INSERT INTO user (id, email, password, status, user_type,created_at, updated_at)
  VALUES (:id, :email, :password, :status, :user_type, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
`;
  //* Inserting user to the database
await db.query(insertUserQuery, {
  replacements: {
    id: "1234",
    email: "scanskill@scanskill.com",
    password: "V3RYSTr0ngP@55w0rD",
    status: "active",
    user_type: "admin",
  },
  type: QueryTypes.INSERT,
});

READ / SELECT

const selectQuery = 
`
  SELECT * FROM user 
  WHERE email = :email AND status = :status
`;
const [userPresent] = await db.query(selectQuery, {
  replacements: {
    email: "scanskill@scanskill.com",
    status: "active",
  },
  type: QueryTypes.SELECT,
});

UPDATE

let updateQuery = 
` 
  UPDATE user SET email = :email, password = :password, 
  user_type = :user_type, status = :status, updated_at = CURRENT_TIMESTAMP 
  WHERE id = :id
`;

//* executing the query
await db.query(updateQuery, {
    replacements: {
      email: "scanskill1@scanskill.com",
      password: "N0RmA1P@55W0rD",
      user_type: "normal",
      status: "inactive",
      id: "1234",
  },
  type: QueryTypes.UPDATE,
});

DELETE

let deleteQuery = ` DELETE FROM user WHERE id = :id `;

//* executing the query
await db.query(deleteQuery, {
    replacements: {
      id: "1234",
  },
  type: QueryTypes.DELETE,
});