CSE 154

Lecture 23: SQLite

Reminders and Administrivia

CP4 due today by 11 pm PST

HW4 due next Wednesday by 11 pm PST (we are not charging late days)

Today's Agenda

• More SQL
• SQLite + Express

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.

DELETE

To delete a record from a table, we use the DELETE keyword:

DELETE
FROM cafemenu
WHERE name = 'Banana';

SQL

What happens if we forget to add a WHERE clause?

UPDATE

To update an existing record in a table, we use the UPDATE and SET keywords:

UPDATE cafemenu
SET description = 'World''s best banana'
WHERE name = 'Banana';

SQL

The Node.js and SQL Connection

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)

db.all(sqlString)

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

db.exec(sqlString)

The exec function executes all SQL queries within the given sqlString. It has no return value (i.e., the promise has no resolved value).

let sql = `CREATE TABLE pokedex(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(200) NOT NULL,
  type VARCHAR(50) NOT NULL);
  
  INSERT INTO pokedex (name, type)
  VALUES ('Bulbasaur', 'grass'),
         ('Ivysaur', 'grass');`;

await db.exec(sql);

Node.js (example)

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

Back to Blogger

SQL + User Input

Example

Suppose we had a POST endpoint to validate a user logging in:

let user = req.body.username;
let pw = req.body.password;
let qry = "SELECT * FROM users WHERE username='" + user +
        "' AND password='" + pw + "'";
// e.g. SELECT * FROM users WHERE username='foo' AND password='bar';
let result = await db.all(qry);
if (result.length === 0) {
res.type("text");
res.status(400).send("user not found");
} else {
res.json(result); // send the user info data (a single row) as JSON
}

JS

What if a user predicts we're using SQL with their parameters?

CONDITION_A OR CONDITION_B evaluates to TRUE in SQL if either condition is true. 1=1 always evaluates to TRUE. What if we have many users in the users database? What user data could be leaked in such a response?

let user = req.body.username; // "' OR 1=1 --"
let pw = req.body.password; // "oops :)"
let qry = "SELECT * FROM users WHERE username='" + user +
        "' AND password='" + pw + "'";
// e.g. SELECT * FROM users WHERE username='' OR 1=1 -- AND password='oops :)'
let result = await db.all(qry);
if (result.length === 0) {
res.type("text");
res.status(400).send("user not found");
} else {
res.json(result); // send the user info data (a single row) as JSON
}

JS

Improved Solution: ? Placeholders

let user = req.body.username;
let pw = req.body.password;
let qry = "SELECT * FROM users WHERE username=? AND password=?";
// the qry will be escaped internally and no rows will be returned
let result = await db.all(qry, [user, pw]);
if (result.length === 0) {
res.type("text");
res.status(400).send("user not found");
}

JS

db.run(sqlString, paramsArray)

The run function executes a single SQL query, sqlString, and replaces placeholder values (like "?"), with the values from the paramsArray. It has no return value (i.e., the promise has no resolved value).

let sql = 'INSERT INTO pokedex (name, type) VALUES (?, ?)';
await db.run(sql, ['Bulbasaur', 'grass']);

Node.js (example)

Little Bobby Tables

XKCD 327