Common SQL Types

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.

CREATE TABLE

We make tables with the CREATE TABLE keyword.

CREATE TABLE highscores(
  id INT,
  username VARCHAR(255),
  score INT
);

Basic SQL Example

First we provide the table name, in this case "highscores". We then give a comma-separated list of column names, with the type of each column coming after the name.

NOT NULL

Use the NOT NULL keyword to prevent null entries in a column.

CREATE TABLE highscores(
  id INT NOT NULL,
  username VARCHAR(255) NOT NULL,
  score INT NOT NULL
);

Basic SQL Example

A query that attempts to enter NULL into one of these columns will cause the query to fail.

When inserting data, you can pass NULL by not including that column in your query.

PRIMARY KEY, AUTO_INCREMENT

Every table should have a column which is used to uniquely identify each row. This improves efficiency and will prove very useful next week.

CREATE TABLE highscores(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL,
  score INT 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.

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.

DROP TABLE IF EXISTS

We cannot create a table if it already exists. Usually, we want our SQL files to be able to overwrite old tables if we re-import them.

DROP TABLE IF EXISTS highscores;
CREATE TABLE highscores(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL,
  score INT NOT NULL
);

Basic SQL Example

SQL files will often look like this, perhaps with some data inserted into the table afterwards. We use DROP TABLE to delete the old table. However, you cannot delete a non-existent table, so we use IF EXISTS to check.

Exercise 1: Creating an Orders Table

We want to add the following orders table to the cafe database. The entry below is for reference, but we won't be adding it.

On the slide below are specifications for each column.

The table should be named orders and should replace existing tables.

Code Quality note: SQL files, like any other, should have header comments. Use -- at the start of a comment line.

Once you finish the .sql file, import the table into your cafe database.

A solution

DROP TABLE IF EXISTS orders;
CREATE TABLE orders(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  phone_number VARCHAR(20),
  email VARCHAR(63),
  item_name VARCHAR(63) NOT NULL,
  qty INT DEFAULT 0,
  total_price DECIMAL(6,2) NOT NULL,
  order_time DATETIME DEFAULT NOW()
);

Exercise 2: Cafe API

We are going to build an API for a theoretical cafe (note that this one has different data than the CSE154 cafe from lecture). There are three endpoints:

• /menu: Returns all menu items, organized by category.
• /menu/:category: Returns all menu items in a category.
• /order: Submits an order to the server.

We will be using the cafe database we set up earlier, with the menu table and orders table.

Use Postman throughout the development process, and test often!

starter code

Implement a /menu GET endpoint. It should query the database for all of the items on the menu, returning them organized by category in the following format:

{
  "Bakery": [
    {
      "name": "Blueberry Scone",
      "subcategory": "Scones",
      "price": 3.50
    },
    ...
  ],
  ...
}

JSON response

Within each category, the results should be sorted alphabetically by name. Utilize the ORDER BY keyword to make this easier. Remember to db.end() any connections you make, and handle errors in async/await code.

An Note about Handling Parameter Values from Clients

For today, we're just focusing on using SQL with Node.js/Express

You can build a SQL query that uses GET/POST variables with string concatenation

However, in practice this leads to security vulnerabilities for malicious user input

We will learn the better approach with variable placeholders tomorrow!

Implement a /menu/:category GET endpoint. Given a category, it will respond with an array of items in that category, again sorted alphabetically by name:

[
  {
    "name": "Blueberry Scone",
    "subcategory": "Scones",
    "price": 3.50
  },
  ...
]

If there are no items for that category (the query result is empty) respond with a descriptive 400 status error.

Note that the format of the response is identical to the format returned by db.query("...");, so you can just send it back as JSON unaltered.

Implement a /order POST endpoint that inserts an order into the database. It has the following required body parameters:

• phone_number: A string representing someone's phone number (e.g. "206-154-2019").
• email: A string representing someone's email (e.g. "dubs@uw.edu").
• item_name: The name of an item on the menu. e.g. "Blueberry Scone"
• qty: The quantity of the item being bought. e.g. "2"
• tip: The tip for the order. e.g. "3.50" for $3.50

If any of these parameters are missing, send a descriptive 400 text error message.

If there is no item in the menu corresponding to the passed in item_name, you should give a different descriptive 400 error message.

If all is well, respond with the plain text, "Your order has been processed!"

More details on expected behavior are provided on the next slide.

When adding an order, add a record to the orders table as follows:

• phone_number: The same format as the passed phone number.
• email: The same format as the passed email
• item_name: The same format as the passed item name (make sure it exists in the menu table)
• qty: The same format as the passed quantity
• total_price: The price of the item (from the menu table) multiplied by the quantity, plus the tip. Remember to parse the POST parameters to calculate, as they are strings.
• Note about order_time: Because our CREATE TABLE used the constraint order_time DATETIME DEFAULT NOW(), we don't need to insert this value explicitly - SQL will calculate this for us using the DATETIME when the record is inserted.

When solving this problem, you should only make two queries, and should only call getDB once. It is important not to make redundant queries or connections. Use the query for the item price to check if the item exists.

Let's make the POST endpoint more flexible. Perhaps we want to allow users to provide either the email, the phone number, or both. Change the endpoint so that the user only has to provide at least one of the two parameters. You will need to alter the INSERT query to pass NULL instead of the quoted email/phone_number if it is not provided.

SOLUTION