Lecture 25: More SQL and Node.js with SQL

Reminders:

• HW4 locks tonight at 11pm. Whatever state your code is in, click turn-in before 11pm.
• Take a look through The Common Mistakes Guide before turning in.
• Don't push node_modules
• HW5 (last one!) will be going out soon. Stay Tuned.

Reminders, continued

We've learned a lot in this course:

• 5 different languages or frameworks: HTML, CSS, JavaScript, Node.js/Express, SQL
• How the Browser creates the DOM
• How CSS overrides itself and "cascades"
• How to do asynchronous programming (what?!)
• How to do asynchronous programming on both client and server. At the same time (sort of; it's asynchronous!)
• Creating databases, how to store data, how a "full stack" website works.
• The Internet™

However you've done, however the final will go: we've done a lot together.

Thank you for coming along this journey with me and our TAs.

Today's Agenda

More SQL commands and database manipulation, including:

• CREATE TABLE
• INSERT into a table

The Node.js/SQL Connection with the mysql2 module

Check Your Understanding:

Where is the best place to validate user input? Why?

1. On the client side, using HTML5 input validation.
2. On the client side, using JavaScript and RegExes
3. On the server side, in Node, with a combination of RegExes, checking for undefined, and others.
4. Before we insert into the database.
5. None of the above
6. All of the above

Creating your own SQL Tables

1. You can create a new database in phpMyAdmin (e.g., pokedexdb, or a blogsdb)
2. Then you can add tables either by importing a .sql file (preferred), a .csv file (practiced in section) 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.

This 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() or CURRENT_TIMESTAMP as the DEFAULT so that you don't have to calculate the date/time manually

-- Author: Andrew Fitz Gibbon
-- Last updated: 11.22.19
-- Sets up a new posts table in a blog database.
CREATE TABLE `posts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `author` VARCHAR(200) DEFAULT 'Fitz',
  `title` VARCHAR(200) DEFAULT NULL,
  `body` text,
  PRIMARY KEY (`id`)
); 

Note how some of this looks different than the previous CREATE TABLE. Both are valid

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

A Preview to Node.js + SQL

let qry = "SELECT * FROM posts WHERE author = 'Fitz'" +
            "ORDER BY timestamp DESC" +
            "LIMIT 1";
let [rows, fields] = await db.query(qry);
console.log(JSON.stringify(rows));
// [{"id":5,"timestamp":"2019-11-22T20:04:23.000Z","author":"Fitz",
"title":"Yet another post","body":"OMG Another blog post about cats. I just 
can't anymore. Where are the dogs?"}]

Node.js

You can find the full solution here which uses a Blog database and posts table that can be populated in phpMyAdmin with this posts.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 mysql2 module will handle most of this for us.

The mysql vs. mysql2 vs. mysql2/promise

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 mysql2

This module has a promise version built in, like fs, but we import with: require('mysql2/promise');

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

The Basics of Using mysql2

1. Start off a "pool" of connections that we can send queries to. This connection pool will manage all of the communications between Node and MySQL.
2. Use this pool object to execute SQL queries with db.query(sqlString)

Using SQL in Node.js

First install the mysql2 module in your project.

• npm install mysql2

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

• const mysql = require("mysql2/promise");

Connecting to a database with mysql.createPool

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 blog database 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.
 */
const db = mysql.createPool({
  // 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: "Blog"        // fill in with db name
});

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 the resulting rows and some metadata about the fields.

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

template

let [rows, fields] = await 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 TextRows, 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, fields] = await db.query(qry);
console.log(rows);

Node.js (example)

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

output

Extracting the data from the TextRow

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, fields] = 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)

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 mysql2 functions

You can try/catch just like you would for fs.readFile(), catching any errors that occur in the db.query function.

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

Node.js

What's in an error anyways?

Common Errors thrown by db.query

Looking Ahead

Next week, 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!