CSE 154

Lecture 22: SQL

Reminders and Administrivia

CP4 due Wednesday by 11 pm PST

Install DB Browser for SQLite before section tomorrow

Today's Agenda

• SQL
• Intro to SQL + Node.js

SQL

A Relational Database

Relational Database: A method of structuring data as tables associated by shared attributes

A table row corresponds to a unit of data called a record; a column corresponds to an attribute of that record

In Excel-speak:

In the above image, the cells highlighted blue are the first "column" and the cells highlighted green are of the first "row" (or "record", or "tuple")

Tables can be visualized just like an Excel sheet, just with different terminology, and more programmatic capabilities.

Example Database

Structured Query Language (SQL)

A "domain-specific language" (HTML is also a DSL) designed specifically for data access and management.

Also like HTML, SQL is a declarative language: describes what data you are seeking, not exactly how to find it.

In SQL, you write statements. The main different types of statements we'll look at:

• Data Definition: Generally, what does your data look like?
• Data Manipulation: Change or access the data.

(There are others, but we won't be talking about them.)

SQL Basics

SELECT name FROM menu WHERE qty > 0;

INSERT into menu (name, category, qty, image)
VALUES ("Cookie", "Desserts", 154, "cookie.png");

SQL (menu table)

Structured Query Language (SQL): A language for searching/updating a database.

A standard syntax that is used by all database software (with minor variations). Generally case-insensitive.

Some Basic SQL Statements

• SELECT
• DISTINCT
• WHERE
• LIKE
• ORDER BY
• LIMIT

These are the basic "building-blocks" of forming "questions" (queries) in SQL

Example Database

The SQL SELECT Statement

Syntax:

SELECT column(s) FROM table;

Example:

SELECT name, release_year FROM Games;

Example output:

The SELECT statement is used to return data from a database.

It returns the data in a result table containing the row data for column name(s) given. Table and column names are case-sensitive.

The DISTINCT Modifier

Syntax:

SELECT DISTINCT column(s) FROM table;

SQL

The DISTINCT modifier eliminates duplicates from the result set.

SELECT release_year
FROM Games;

SELECT DISTINCT release_year
FROM Games;

The SQL WHERE Statement

Syntax:

SELECT column(s) FROM table WHERE condition(s);

Example:

SELECT name, release_year FROM Games WHERE genre = 'puzzle';

Example result:

The WHERE clause filters out rows based on their columns' data values. In large databases, it's critical to use a WHERE clause to reduce the result set in size.

Suggestion: When trying to write a query, think of the FROM part first, then the WHERE part, and lastly the SELECT part.

More about the WHERE Clause

Syntax:

WHERE column operator value(s)

SELECT name, release_year
FROM Games
WHERE release_year < 1990;

Check your Understanding

Write a SQL query that returns the name and platform of all games with a release_year before 2000.

SELECT name, platform
FROM Games
WHERE release_year < 2000;

Multiple WHERE Clauses: AND, OR

Example:

SELECT name, release_year FROM Games
WHERE release_year < 1990 AND genre='puzzle';

Example result:

Multiple WHERE conditions can be combined using AND or OR.

Approximate Matches with LIKE

Syntax:

WHERE column LIKE pattern

SELECT name, release_year FROM Games
WHERE name LIKE 'Spyro%'

Note: In SQLite, the text in the LIKE string is case-insensitive.

Sorting By a Column: ORDER BY

Syntax:

SELECT column(s) FROM table
ORDER BY column(s) ASC|DESC;

SELECT name FROM Games
ORDER BY name

SELECT name FROM Games
ORDER BY name DESC

The ORDER BY keyword is used to sort the result set in ascending or descending order (ascending if not specified)

Limiting Rows with LIMIT

Syntax:

LIMIT number

Example:

SELECT name FROM Games
WHERE genre='puzzle'
ORDER BY name
LIMIT 3;

Example result:

LIMIT can be used to get the top-N of a given category. It can also be useful as a sanity check to make sure you query doesn't return 100000 rows.

Check your understanding:

Write a SQL query that returns the name and genre of all games that have the word 'dragon' in them, ordered by release_year

SELECT name, genre
FROM Games
WHERE name LIKE '%dragon%'
ORDER BY release_year;

Additional Practice with SQL Queries

SQLZoo has multiple exercises (with built-in databases you don't need to worry about setting up) for practicing SELECT, WHERE, ORDER BY, LIMIT, LIKE, etc. We recommend you go through these for additional practice!

Creating your own SQL 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.
• AUTOINCREMENT: 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 AUTOINCREMENT

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 AUTOINCREMENT,
  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")

AUTOINCREMENT 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 AUTOINCREMENT,
   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 CURRENT_TIMESTAMP
);

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 AUTOINCREMENT,
   name           VARCHAR(255),
   email          VARCHAR(255),
   student_id     INT,
   length         TINYINT,
   question       VARCHAR(255),
   creation_time  DATETIME DEFAULT CURRENT_TIMESTAMP
);

setup-wpl.sql

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

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

-- Author: Andrew Wolfram
-- Last updated: 05.18.20
-- Sets up a new posts table in a blog database.
CREATE TABLE `posts` (
  `id` int(11) unsigned NOT NULL AUTOINCREMENT,
  `timestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `author` VARCHAR(200) DEFAULT 'Andrew',
  `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 AUTOINCREMENT,
  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 AUTOINCREMENT 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 = 'Andrew'" +
            "ORDER BY timestamp DESC" +
            "LIMIT 1";
let [rows, fields] = await db.all(qry);
console.log(JSON.stringify(rows));
// [{"id":5,"timestamp":"2020-05-18T20:04:23.000Z","author":"Andrew",
"title":"Yet another post","body":"OMG Another blog post about dogs. I just 
can't anymore. Where are the cats?"}]

Node.js

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 sqlite module will handle most of this for us.

The sqlite vs. sqlite3

sqlite3 is the most popular module used to connect to a SQLite database in Node.js

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

We will use sqlite which is a wrapper for sqlite that adds promises

Using SQL in Node.js

First install the sqlite3 and sqlite modules in your project.

• npm install sqlite3 sqlite

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

• const sqlite3 = require("sqlite3");
• const sqlite = require("sqlite");

SQL Connection

'use strict';
const sqlite3 = require('sqlite3');
const sqlite = require('sqlite');

* Establishes a database connection to the wpl 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.
*/
async function getDBConnection() {
    const db = await sqlite.open({
        filename: 'data/wpl.db',
        driver: sqlite3.Database
    });

    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.all

This function takes a SQL query string and an optional array of parameters/placeholder values and returns the resulting rows.

let rows = await db.all(sqlString)

template

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

Node.js (example)

More about db.all(sqlString)

The query function returns an array of JS objects, 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.all(qry);
console.log(rows);

Node.js (example)

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

output

Extracting the data

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.all(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 SQLite 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 sqlite functions

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

try {
  let rows = await db.all("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