MySQL with Node.js

SQL

Node.js

10/01/2019

MySQL is common with PHP, C++, Node, Java, Python, Ruby, and so on. In this post, we'll work out Node.js.

Node.js

Client requests --> NodeJS --> MySQL DB
MySQL DB --> NodeJS --> Client (any device)

Unlike JavaScript which modifies contents on the client side, Node.js is an interpreter and environment for JS with useful libraries that works on the server side. Node JS only runs in V8 engine(mainly by Google Chrome) while JS runs in Spider monkey(Fire Fox), Java Script Core(Safari), and V8.

Nods.js with MySQL (Node package)

Documentation on Github

Installation

BASH

$ npm install mysql

Import in js

var mysql = require("mysql")

Get host name (cloud server)

SQL

SELECT @@hostname;
SHOW VARIABLES WHERE Variable_name LIKE '%host%';

TEXT

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| host_cache_size               | 279   |
| hostname                      | goorm |
| performance_schema_hosts_size | -1    |
| report_host                   |       |
+-------------------------------+-------+

When needed, you can get hosts for all incoming requests

SQL

SELECT host FROM information_schema.processlist;

TEXT

+-----------+
| host      |
+-----------+
| localhost |
+-----------+

Get user name

SQL

SELECT CURRENT_USER();

TEXT

+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+

Connect to MySQL

var mysql = require("mysql")

var connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  database: "subscription_db",
})

// ... codes

// end connection
connection.end()

Make sure to end connection with connection.end()

Run queries

//...
var query = "SELECT 2 * 9 AS res"
connection.query(query, function(error, results, fields) {
  if (error) throw error
  console.log(results)
  console.log(results[0])
  console.log(results[0].res + "\n")
})

var query2 = "SELECT CURTIME() as time, CURDATE() as date, NOW() as now"
connection.query(query2, function(error, results, fields) {
  if (error) throw error
  console.log(results)
  console.log(results[0].time)
  console.log(results[0].date)
  console.log(results[0].now)
})

TEXT

$ node main.js
[ RowDataPacket { res: 18 } ]
RowDataPacket { res: 18 }
18

[ RowDataPacket {
    time: '07:00:11',
    date: 2019-10-02T00:00:00.000Z,
    now: 2019-10-02T07:00:11.000Z } ]
07:00:11
2019-10-02T00:00:00.000Z
2019-10-02T07:00:11.000Z

function() is a call back
results returns all data in an array, []

Work with created database

Create database in MySQL

SQL

CREATE DATABASE subscription_db;
USE subscription_db;

CREATE TABLE users (
    email VARCHAR(255) PRIMARY KEY,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users(email) VALUES
('[email protected]'), ('[email protected]');

Test with js

//...
var query = "SELECT * FROM users"
connection.query(query, function(error, results, fields) {
  if (error) throw error
  console.log(results)
  console.log(results[0])
  console.log(results[1])
  console.log(results[1].email)
})

TEXT

$ node main.js
[ RowDataPacket {
    email: '[email protected]',
    created_at: 2019-10-02T07:55:34.000Z },
  RowDataPacket {
    email: '[email protected]',
    created_at: 2019-10-02T07:55:34.000Z } ]
RowDataPacket {
  email: '[email protected]',
  created_at: 2019-10-02T07:55:34.000Z }
RowDataPacket {
  email: '[email protected]',
  created_at: 2019-10-02T07:55:34.000Z }
[email protected]

Getting # users

SELECT * FROM users
- Retrieve with results.length (not recommended)
SELECT COUNT(*) AS total FROM users
- Retrieve with results[0].total

Insert with Node

Hard coded (not recommended)

//...
var query = 'INSERT INTO users(email) VALUES ("[email protected]")'
connection.query(query, function(error, results, fields) {
  if (error) throw error
  console.log(results)
})

TEXT

$ node main.js
OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

What you want to do

var person = { email: "[email protected]" }
connection.query("INSERT INTO users SET ?", person, function(error, results) {
  if (error) throw error
  console.log(results)
})

Usage

var var_name = {key: data_type, key2: data_type, ...};
SET ? dynamically adds the data

Insert fake data

//...
var faker = require("faker")

var person = {
  email: faker.internet.email(),
  created_at: faker.date.past(),
}
connection.query("INSERT INTO users SET ?", person, function(error, results) {
  if (error) throw error
  console.log(results)
})

Note: return value of faker.data.past() isn't compatible with MySQL; however, MySQL in Node.js is smart enough to convert it to MySQL format (yyyy-mm-dd hh:mm:ss).

MySQL connection as variable

//...
var person = {
  email: faker.internet.email(),
  created_at: faker.date.past(),
}

console.log(person)

var res = connection.query("INSERT INTO users SET ?", person, function(
  error,
  results
) {
  if (error) throw error
  console.log(results)
})

console.log(res.sql)

TEXT

{ email: '[email protected]',
  created_at: 2019-08-08T18:21:06.815Z }

INSERT INTO users SET `email` = '[email protected]', `created_at` = '2019-08-08 18:21:06.815'

OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

res.sql shows the sql query inserted. Note that 2019-08-08T18:21:06.815Z was converted to 2019-08-08 18:21:06.815.

Bulk insert

Inserting multiple users

var ppl = [
  ["[email protected]", "2018-09-27"],
  ["[email protected]", "2019-02-22"],
  ["[email protected]", "2019-03-01"],
]

var q = "INSERT INTO users (email, created_at) VALUES ?"

connection.query(q, [ppl], function(err, results) {
  console.log(err)
  console.log(results)
})

[ppl] is used as parameter

Test the data with faker

var ppl = []
var num_add = 500

for (i = 0; i < num_add; i++) {
  ppl.push([faker.internet.email(), faker.date.past()])
}
console.log(ppl) /// check data

TEXT

$ node main.js
  [ '[email protected]', 2019-09-24T23:24:55.864Z ],
  [ '[email protected]', 2019-06-12T15:08:56.010Z ],
  [ '[email protected]', 2019-03-24T13:36:13.934Z ],
  [ '[email protected]', 2018-12-30T10:46:25.695Z ],
  [ '[email protected]', 2019-04-29T09:38:46.102Z ],
  [ '[email protected]', 2019-09-16T23:34:40.805Z ],
  [ '[email protected]', 2018-11-15T22:43:19.552Z ],
  [ '[email protected]', 2019-09-17T02:24:26.731Z ],
  [ '[email protected]', 2019-03-08T06:57:48.539Z ],
  [ '[email protected]', 2019-08-09T17:02:42.409Z ],
  [ '[email protected]', 2019-06-18T11:36:39.618Z ],
  [ '[email protected]', 2019-01-24T17:21:49.967Z ],
  [ '[email protected]', 2019-07-20T09:30:36.802Z ],
  [ '[email protected]', 2019-09-11T09:01:48.108Z ],
  [ '[email protected]', 2019-06-10T05:24:39.850Z ],
  [ '[email protected]', 2019-01-16T17:13:52.463Z ],
  [ '[email protected]', 2019-05-20T22:35:40.538Z ],
  [ '[email protected]', 2019-07-02T16:31:43.440Z ],
  [ '[email protected]', 2018-10-23T06:23:07.751Z ],
  [ '[email protected]', 2019-05-24T00:50:27.236Z ],
  [ '[email protected]', 2018-11-19T06:49:13.985Z ],
  [ '[email protected]', 2019-09-25T05:40:37.278Z ],
  [ '[email protected]', 2019-06-09T00:57:04.997Z ],
  [ '[email protected]', 2019-04-03T18:03:12.261Z ],
  [ '[email protected]', 2018-10-17T12:41:17.869Z ],
  [ '[email protected]', 2019-06-19T09:59:09.289Z ],
  [ '[email protected]', 2019-08-07T14:15:06.439Z ],
  [ '[email protected]', 2019-02-05T02:08:18.620Z ],
  [ '[email protected]', 2019-05-23T03:00:00.608Z ],
  [ '[email protected]', 2018-11-22T12:12:06.693Z ],
  [ '[email protected]', 2019-09-03T21:37:31.277Z ],
  ... 400 more items ]

Insert

var ppl = []
var num_add = 500

for (i = 0; i < num_add; i++) {
  ppl.push([faker.internet.email(), faker.date.past()])
}
// console.log(ppl); /// check data

var q = "INSERT INTO users (email, created_at) VALUES ?"

connection.query(q, [ppl], function(err, results) {
  console.log(err)
  console.log(results)
})

TEXT

$ node main.js
null
OkPacket {
  fieldCount: 0,
  affectedRows: 500,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Records: 500  Duplicates: 0  Warnings: 0',
  protocol41: true,
  changedRows: 0 }

Retrieve data with MySQL

Find the earliest date a user joined
Find email of the first user
number of users followed by month order by num users in descending order
Get number of yahoo emails
Calculate Total number of users for each email host

var q1 =
  'SELECT DATE_FORMAT(created_at, "%M %D %Y") AS "earliest_date" FROM users ORDER BY created_at LIMIT 1'
var q12 =
  'SELECT DATE_FORMAT(MIN(created_at),"%M %D %Y") AS earliest_date FROM users'

// won't work if there are multiple records with same created_at value
// var q2 = 'SELECT email, created_at FROM users ORDER BY created_at LIMIT 1';
var q2 =
  "SELECT * FROM users WHERE created_at= (SELECT MIN(created_at) FROM users)"

var q3 =
  'SELECT DATE_FORMAT(created_at, "%M") AS month, COUNT(*) AS cnt FROM users GROUP BY month ORDER BY cnt DESC'
// var q32 = 'SELECT MONTHNAME(created_at) AS month, COUNT(*) as count FROM users GROUP BY month ORDER BY count DESC';

var q4 =
  'SELECT COUNT(email) AS yahoo_users FROM users WHERE email LIKE "%@yahoo.com"'
var q5 =
  'SELECT CASE WHEN email LIKE "%gmail.com%" THEN "gmail" WHEN email LIKE "%yahoo%" THEN "yahoo" WHEN email LIKE "%hotmail.com%" THEN "hotmail" ELSE "other" END AS  provider, COUNT(*) FROM users GROUP BY provider'

connection.query(q5, function(error, results, fields) {
  if (error) throw error
  console.log(results)
})

WRITTEN BY

Ellis Min

Keeping a record

GitHub