NodeJS and DB

F28WP Web Programming

2020-2021

## Overview * How to use NodeJS with **Database** (DB)? * Working with popular database * **Syntax and packages** * Examples * Questions and Discussion <aside class='notes'> </aside>

## Revision Question Which of the following command will show version of Node? * A - $ npm --version * B - $ node --version * C - $ npm getVersion * D - $ node getVersion <aside class='notes'> </aside>

## Answer Answer: **B - $ node --version** <aside class='notes'> </aside>

## Revision Question NodeJS is _____________ Language. * Server Side * Client Side * Both <aside class='notes'> </aside>

## Answer Answer: **Server Side** <aside class='notes'> </aside>

## Revision Question Is Node js multithreaded? * Yes * No <aside class='notes'> </aside>

## Answer Answer: **No** <aside class='notes'> </aside>

## Revision Question NodeJS is ___________ * Asynchronous * Synchronous <aside class='notes'> </aside>

## Answer Answer: **Asynchronous** <aside class='notes'> </aside>

## Revision Question Which function is used to include modules in Node Js. * include(); * require(); * attach(); <aside class='notes'> </aside>

## Answer Answer: **require();** <aside class='notes'> </aside>

## (Reminder) F27WD Web Design and **Databases** * F27WD Web Design and Databases (**Level 7**) * **First Year** (HTML/CSS/Basic JavaScript) * F27WD Details [Link](http://www.macs.hw.ac.uk/students/cs/courses/f27wd-web-design-and-databases/) * **Pre-requisite F28WP**: F27WD Web Design and Databases **or equivalent**. <aside class='notes'> Reminder, one of the preqequists of this course is that you have some basic knowledge - which includes some understanding of what a database is - basic database knowledge (e.g., should not be something completely new to you). </aside>

## NodeJS * NodeJS can be used in **database** applications * NodeJS supports the majority of popular databases - MongoDB, **MySQL**, SQLite, NoSQL, .. <aside class='notes'> </aside>

## NodeJS and Database <img src="./images/nodedb.png" style="border:0px;" width="80%" height="80%"> <aside class='notes'> </aside>

## NodeJS with MySQL <img src="./images/nodemysql.png" style="border:0pt;" width="80%" height="80%"> <aside class='notes'> </aside>

# **Why** MySQL? <aside class='notes'> </aside>

## **Why** MySQL? * Highly **reliable** * Industry **standard** * On-Demand scalability * Small footprint * Cross platform support * **Free and open-source** <aside class='notes'> </aside>

# How do we use **MySQL** with **NodeJS**? <aside class='notes'> </aside>

## MySQL with NodeJS * One of the most popular databases is **MySQL** * Install **"mysql"** module NodeJS can use this module to manipulate the MySQL database ``` var mysql = require('mysql'); ``` <aside class='notes'> </aside>

## MySQL with NodeJS * Connect to Database * Query Database (Check Status) * Create Database (In Code) * Update Database - Create/Delete/Change Table - Insert/Remove/Update Data * Access Database Data (query) <aside class='notes'> </aside>

## Create **Connection** ``` var mysql = require('mysql'); var con = mysql.createConnection({ host: "localhost", user: "yourusername", password: "yourpassword" }); con.connect(function(err) { if (err) throw err; console.log("Connected!"); }); ``` <aside class='notes'> </aside>

## **Query** Database ``` con.connect(function(err) { if (err) throw err; console.log("Connected!"); var sql = " ... your SQL query ... " con.query(sql, function (err, result) { if (err) throw err; console.log("Result: " + result); }); }); ``` <aside class='notes'> </aside>

## **Create** Database ``` var mysql = require('mysql'); var con = mysql.createConnection({ host: "localhost", user: "yourusername", password: "yourpassword" }); con.connect(function(err) { if (err) throw err; console.log("Connected!"); con.query("CREATE DATABASE mydb", function (err, result) { if (err) throw err; console.log("Database created"); }); }); ``` <aside class='notes'> </aside>

## **Create** Table ``` var mysql = require('mysql'); var con = mysql.createConnection({ host: "localhost", user: "yourusername", password: "yourpassword", database: "mydb" }); con.connect(function(err) { if (err) throw err; console.log("Connected!"); var sql = "CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))"; con.query(sql, function (err, result) { if (err) throw err; console.log("Table created"); }); }); ``` <aside class='notes'> </aside>

## Various Query Statements * Create Database * Create Table * Insert Into * Select From * Where * Ordered By * Delete * Drop Table * Update * Limit * Join Visit [w3schools details](https://www.w3schools.com/nodejs/nodejs_mysql.asp) <aside class='notes'> </aside>

jDoodle - Online SQL Sandbox

Easily test out your database commands

Examples/snippets

## SQLFiddle - Online SQL Sandbox * Easily test out your database commands * Examples and snippets * [www.sqlfiddle.com](http://www.sqlfiddle.com) <aside class='notes'> </aside>

## Where to start? * CodeSandbox - Setup Basic NodeJS Server/Client * Setup MySQL Database on FreeSQLDatabase - [www.freesqldatabase.com](https://www.freesqldatabase.com) * Example snippet ``` const mysql = require("mysql"); var connection = mysql.createPool({ connectionLimit: 50, host: "sql12.freesqldatabase.com", user: "sql12304235", password: "1Hj5jAY7Bv", database: "sql12304235" }); ``` <aside class='notes'> </aside> note: - createConnection: creates one connection and it lasts until you close it (or closed by the mysql server) - createPool: you request a connection from a pool, you will receive a connection that is not currently being used, or a new connection. If you're already at the connection limit, it will wait until a connection is available before it continues. These pooled connections do not need to be manually closed.

Try extending example

Minimal MySQL Example

Deleting/Creating/Adding

MySQL Database NodeJS

(remember Dependencies)

## Theory to Practice * Important you understand the rational behind NodeJS * Important you can setup a simple NodeJS server/client - e.g., using CodeSandbox * Important you are able to save/load information from a database - MySql * Important you're actually able to **implement** NodeJS in code (not just the theory) <aside class='notes'> </aside>

Try extending example (adding database)

Minimal Example (Mouses)

Client/Server

Sending/Receiving Data

(remember Dependencies)

## Summary * Understand NodeJS and how to work with a **Database** * **Manage** a Database (using NodeJS) * Create, insert and access data using NodeJS and **MySQL** * **Syntax** and libraries <aside class='notes'> </aside>

## To do this week ... * Read over the lectures <br> * Review the revision questions <br> * Work through tutorial practicals <br> * **Experiment** (get into good habits) <br> * Read w3schools - [Node&MySQL](https://www.w3schools.com/nodejs/nodejs_mysql.asp) - [CodeSandbox Node & MySQL Example](https://codesandbox.io/s/node-mysql-ksdxc) <aside class='notes'> </aside>