Lecture 22 - Node.js and SQL Connection

Final Project Milestone

Due tonight at 11PM (no late days)

The repo doesn't include the .gitignore you had in CP4/HW4

Make sure you don't push node_modules. You can copy the .gitignore from HW4/CP4 into your Final Project repo and add/commit/push it to ignore your node_modules (before pushing everything else)

A Few Common Questions

Why doesn't this work?

/**
 * Sends a hello message given a required name query parameter.
 * Example: /hello?name=dubs returns "Hello dubs!"
 * Sends a 400 error if missing the required name parameter.
 * All responses are plain text.
 */
app.get("/hello", (req, res) => {
  if (!req.query.name) {
    res.status(400).send("Missing required name parameter.");
  }
  console.log("This is logged after missing parameter check.");
  res.send("Hello " + req.query.name + "!");
});

JS

ending-request-demo.js

Running Demo Output

What Happened?

From the documentation of res.end (internally called by in res.send):

"This method signals to the server that all of the response headers and body have been sent; that server should consider this message complete."

They do not have any control over the termination of your JS program.

You can continue to do other things, such as reading/writing files, logging, etc.

However, you do not have the ability to modify the Response object (res).

You can solve the above problem using if/else logic - when trying to factor out errors, you can use middleware functions (see documented example from Monday's "Extra Resources").

A Fixed Version

/**
 * Sends a hello message given a required name query parameter.
 * Example: /hello?name=dubs returns "Hello dubs!"
 * Sends a 400 error if missing the required name parameter.
 * All responses are sent as plain text.
 */
app.get("/hello", (req, res) => {
  if (!req.query.name) {
    res.status(400).send("Missing required name parameter.");
  } else {
    console.log("This is logged after missing parameter check.");
    res.send("Hello " + req.query.name + "!");
  }
});

JS

Note: This can be subtle, but is common and will always be logged to your terminal line, often related to the ERR_HTTP_HEADERS_SENT error ("Cannot set headers after they are sent to the client").

Today's Agenda

More SQL commands and database manipulation, including:

• CREATE TABLE
• INSERT into a table

The Node.js/SQL Connection with the promise-mysql module

Check Your Understanding

What is the preferred technology for storing and manipulating large amounts of data on the server and why?

1. Text files on the server which can be easily modified while still keeping it organized.
2. JSON, because it is easy to read and parses quickly.
3. Punchcards, because servers can be hacked too easily.
4. Relational databases because they can quickly search, filter, update, and send large amounts of data.

Creating your own SQL Tables

1. You can create a new database in phpMyAdmin (e.g. pokedexdb)
2. Then you can add tables either by importing a .sql file (preferred), a .csv file (practiced in section yesterday) or copy/pasting the SQL code using the SQL query tab in phpMyAdmin
3. But how do we create tables?

CREATE TABLE: Syntax

CREATE TABLE is used to create a new table.

Syntax:

CREATE TABLE table_name(
  column1 datatype PRIMARY KEY,
  column2 datatype,
  column3 datatype,
  .....
  columnN datatype
);

SQL (template)

SQL Data Types

Each column must have a defined data type

The following are the most common:

Prefer VARCHAR to TEXT, and with as small a max limit as you are comfortable setting. This limits the amount of data someone can dump into your table.

Useful Column Constraints

The following are very common and useful in CREATE TABLE statements.

These are called constraints - they "constrain" the types of values you can insert in a column.

Today's reading on constraints is an excellent overview for more details.

• PRIMARY KEY (keyname): Used to specify a column or group of columns uniquely identifies a row in a table.
• AUTO_INCREMENT: Used with a primary key field to automatically generate the "next" value in a particular column when a new row is added. Only available in numerical fields.
• NOT NULL: prevents NULL entries in a column, requires the value to be set in INSERT statements.
• DEFAULT: specifies default values for a column if not provided in an INSERT statement

• UNIQUE: requires an attribute to be unique (useful for fields that are not PRIMARY KEY but should still be unique)

PRIMARY KEY and AUTO_INCREMENT

Every table should have a column which is used to uniquely identify each row. This improves efficiency and will prove very useful when using multiple tables.

CREATE TABLE students(
  id                       INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)        NOT NULL,
  username VARCHAR(255)    NOT NULL UNIQUE
  email VARCHAR(255)       NOT NULL
);

Basic SQL Example

Adding PRIMARY KEY makes it so that the code will error if that column ever has duplicates. It will use that column to identify each row quickly. This is usually an integer id, but can also be other types. Conventionally named id or prefixed with a letter (e.g. sid for "student id")

AUTO_INCREMENT will make it so that, if you provide no input for that column, it will pick the next unused value. Perfect for making it so you don't have to worry about what the next id is.

Remember the WPL Queue?

We introduced the WPL queue to teach Forms and validation on the client-side. What types of data might we want to store in a database (wpldb) for the WPL Queue?

Text-based

• The name of the student
• The email address
• The question text

But what about...

• The student number (this could also be text)
• The question length (2 or 10)
• A unique identifier for each question
• When the question was submitted

CREATE TABLE example

What would the CREATE TABLE SQL command look like to create a table to hold the queue for the WPL example?

CREATE TABLE queue(
   id             INT PRIMARY KEY AUTO_INCREMENT,
   name           VARCHAR(255),
   email          VARCHAR(255),
   student_id     INT,
   length         TINYINT,      -- e.g. 2 or 10
   question       VARCHAR(255), -- max of 255 characters per question
   creation_time  DATETIME DEFAULT NOW()
);

The .sql File

Can be imported to execute SQL commands, often to create new tables (conventionally starting with setup in the file name)

-- Author: Melissa Hovik
-- Last updated: 08.13.19
-- Creates a queue table of questions for the WPL database, wpldb.
CREATE TABLE queue(
   id             INT PRIMARY KEY AUTO_INCREMENT,
   name           VARCHAR(255),
   email          VARCHAR(255),
   student_id     INT,
   length         TINYINT,
   question       VARCHAR(255),
   creation_time  DATETIME DEFAULT NOW()
);

setup-wpl.sql

Note: Use -- for comments in SQL (for each line).

To import a .sql file like this in phpMyAdmin, you must use the Import tab with an existing database selected (e.g. wpldb).

Another CREATE TABLE example

This is an example using a few different DEFAULT constraints to represent a table for blog posts.

For DATETIME columns, it is convenient to use NOW() as the DEFAULT so that you don't have to calculate the date/time manually

-- Author: Melissa Hovik
-- Last updated: 08.13.19
-- Sets up a new blog_posts table in a blog database.
CREATE TABLE blog_posts(
  id              INT PRIMARY KEY AUTO_INCREMENT,
  title           VARCHAR(255) DEFAULT "Untitled Post",
  author          VARCHAR(100) DEFAULT "anonymous",
  category        VARCHAR(100) DEFAULT "other",
  creation_time   DATETIME DEFAULT NOW()
);

Other table commands

DROP TABLE table_name; deletes a table

DROP TABLE IF EXISTS table_name; deletes a table only if it exists, which prevents an error if it doesn't

CREATE TABLE IF NOT EXISTS blog_posts( ... )

• The IF NOT EXISTS option is useful if you want the table to be created only if it is not already in a database when importing/executing table creation.

INSERT

To insert a new record into a table, we use the INSERT INTO keyword:

CREATE TABLE cafemenu(
  id           INT  PRIMARY KEY AUTO_INCREMENT,
  name         VARCHAR(100) NOT NULL,
  category     VARCHAR(100) NOT NULL,
  description  VARCHAR(256) NOT NULL,
  image        VARCHAR(100) DEFAULT "food.png",
  qty          INT DEFAULT 0
);

INSERT INTO cafemenu (name, category, description)
VALUES ("Banana", "Fresh Fruit", "A fresh test banana.");

SQL

First provide the table name, then optionally the list of columns you want to set (by default it sets all columns). Columns left out will be set to NULL, unless they have AUTO_INCREMENT set.

Then provide the values for each column, which must match the column names specified.

The Node.js and SQL Connection

From Monday: "A Preview to Node.js + SQL"

let qry = "SELECT name, id, type, weakness FROM pokedex " +
          "WHERE name LIKE '%r%' AND id < 145 AND weakness = 'rock' " +
          "ORDER BY type, name DESC";
let rows = await db.query(filterQuery);
console.log(JSON.stringify(rows));
// [{"name":"Articuno","id":144,"type":"ice","weakness":"rock"}]

Node.js

You can find the full solution here which uses a pokedexdb that can be populated in phpMyAdmin with this pokedex.csv file (refer to yesterday's section on .csv imports)

Full-stack website organization

A full-stack website consists of three layers:

• Web Browser (client): HTML, CSS, JS
• Web Server: Node.js
• Database Server: SQL

Image source

The web server makes requests of the database server much like our client-side JS used AJAX

The promise-mysql module will handle most of this for us.

mysql vs. promise-mysql

mysql is the most popular module used to connect to a MySQL database in Node.js

Similar to the fs module functions, mysql functions are callback-last, with callbacks being error-first

We will use promise-mysql as an easier way to use mysql with async/await.

• "promise-mysql is a wrapper for mysql that wraps function calls with Bluebird promises."

Similar to promisifying fs functions, these promise-returning functions will make it much easier to avoid callback pyramids.

A Comparison

db.query("SELECT * FROM some_table", (err, rows) => {
  if (err) {
    // handle error
  } else {
    db.query("SELECT * FROM other_table", (err2, rows2) => {
    if (err2) {
      // handle error
    } else {
      // ... do something with all the results
    }
  }
}

JS (using callback-based mysql module)

try {
  let rows = await db.query("SELECT * FROM some_table");
  let rows2 = await db.query("SELECT * FROM other_table");
   // ... do something with all the results
} catch (err) {
  // handle generic connection error
}

JS (using promise-based promise-mysql-module)

The Basics of Using promise-mysql

1. Connecton to your database with mysql.createConnection - this creates a new MySQL connection with given configuration options. We store this created object as a variable (conventionally called db).
2. Use this connected object to execute SQL queries with db.query(sqlString)
3. When done with the database (including error-handling), you must db.end(), otherwise your program will hang.

Using SQL in Node.js

First install the promise-mysql module in your project.

• npm install promise-mysql

Then require it with the rest of your modules in your Node.js program.

• const mysql = require("promise-mysql");

Connecting to a database with mysql.createConnection

To connect to a database, you need the following information, which you can find on the MAMP start page:

• host (defaults to localhost)
• port (unlike the 8000 port in your Express app, you do not choose the port on MAMP - it is often set to 8889)
• the user name for the database (usually defaults to root)
• the database password (usually defaults to root)
• database name

We will use this configuration information to connect to MySQL and perform queries in Node.js.

SQL Connection

/**
 * Establishes a database connection to the pokedexdb and returns the database object.
 * Any errors that occur during connection should be caught in the function
 * that calls this one.
 * @returns {Object} - The database object for the connection.
 */
async function getDB() {
  let db = await mysql.createConnection({
    // Variables for connections to the database.
    host: "localhost",      // fill in with server name
    port: "8889",           // fill in with a port (will be different mac/pc)
    user: "root",           // fill in with username
    password: "root",       // fill in with password
    database: "pokedexdb"   // fill in with db name
  });
  return db;
}

JS (example)

Executing SQL queries with the db object

Once you have the db object, you can now execute SQL queries with db.query

This function takes a SQL query string and an optional array of options and returns a row of results.

let rows = db.query(sqlString, [options])

template

let rows = db.query("SELECT name, category FROM menu ORDER BY name;");

Node.js (example)

More about db.query(sqlString)

The query function returns an array of RowDataPackets, which represent information for each row matching the query. In the below example, we limit at most 2 rows in the result.

let qry = "SELECT name, type FROM pokedex LIMIT 2;";
let rows = await db.query(qry);
console.log(rows);

Node.js (example)

[
  RowDataPacket { name: 'Bulbasaur', type: 'grass' },
  RowDataPacket { name: 'Ivysaur', type: 'grass' }
]

output

Extracting the data from the RowDataPacket

The column (field) names for each row (record) can be accessed using dot notation (it's just an object!)

...
let qry = "SELECT name, type FROM pokedex LIMIT 2;";
let rows = await db.query(qry);
let firstRow = rows[0];
console.log("Name: " + firstRow.name + "(" + firstRow.type + ")");

Node.js (example)

Name: Bulbasaur (grass)

output

Note that only the column names specified in the SELECT statement will be accessible (for this example, name and type)

Closing the Connection

You must close the connection to the db, otherwise the program will hang.

To do so, we can use db.end()

let rows = db.query("...");
// do stuff with rows
db.end(); // close when you're done with the db object.

It's usually best to connect/close exactly once in the db - you can make multiple queries before closing.

try/catch

Database connections can have different problems: the database server could be down, the database could be missing or corrupted, the user/password credentials may be incorrect.

You can find a good review of MySQL error codes here - useful when you are debugging and/or want to handle errors differently (similar to how we can use err.code === "ENOENT" in our response logic)

try/catch helps us catch and identify when errors occur so we can handle the error correctly.

Using try/catch with promise-mysql functions

You can try/catch within getDB(), or you can try/catch in the function that calls getDB(), catching any errors that occur in the mysql.createConnection function.

try {
  let db = await getDB(); // an error could occur here
  let rows = await db.query("SELECT name FROM pokedex"); // or here
  // process the result rows somehow
} catch (err) {
  res.status(500).send("Something went wrong on the server. Please try again later.");
}

Node.js

But we're missing something...

A Fix: Checking a Defined db

async function queryDB() {
  let db;
  try {
    db = await getDB(); // connection error thrown in getDB();
    let qry = "SELECT name, type FROM pokedex;";
    let rows = await db.query(qry);
    console.log(rows);
  } catch (err) {
    console.log(err.message);
  }
  if (db) { // only defined if getDB() returned a successfully-connected object
    db.end();
  }
}

JS

Looking Ahead

In tomorrow's section, you'll get practice creating tables in .sql files and using SQL queries in Node.js/Express.

Next, we'll learn how to safely query tables that take user input (e.g. GET/POST parameters) and how to write queries that reference data from multiple tables!