Databases

CIS 193 – Go Programming

Course Logistics

• Homework 8 (available on course website) is due on April 4th on Canvas at 11:59 PM
• Piazza Link: piazza.com/class/ixxxkc67kac4vp
• Canvas Link: canvas.upenn.edu/courses/1350686

Introduction to SQL

What is SQL?

• Stand for Structured Query Language
• Used to let users query, manipulate and transform data from a relational database

What is a relational database?

• A collection of related two dimensional tables - with named columns and rows of data

SQL Tables

Tables in a relational database can form and define relations between the data

Tables have a schema, which is the underlying skeleton structure of the database

Data Types

• Columns correspond to distinct data types
• For example: CHAR(20), INTEGER, FLOAT, etc

SQL: Creating a Table

Here, we can specify the table name, columns names (with types), and other restrictions

CREATE TABLE movies (
    id INTEGER PRIMARY KEY,
    title TEXT,
    director TEXT,
    year INTEGER, 
    length_minutes INTEGER
);

Syntax

column DataType TableConstraint DEFAULT default_value,

TableConstraint: For example, PRIMARY KEY, UNIQUE, NOT NULL, FOREIGN KEY, etc

SQL: Inserting Rows

INSERT INTO boxoffice
(movie_id, rating, sales_in_millions)
VALUES (1, 9.9, 283742034 / 1000000),
VALUES (2, 4.9, 563732214 / 1000000);

Specifying the columns is optional, otherwise it will follow the structure of the table

SQL: Simple Queries

Syntax

SELECT column, another_column, …
FROM mytable
WHERE condition
    AND/OR another_condition
    AND/OR …;

For example, to get all the movies directed by Wes Anderson, we can do:

SELECT title, director FROM movies 
WHERE director = "John Lasseter";

SQL: Queries with Filtering and Sorting

Syntax

SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;L

SQL: Deleting and Updating

Delete Syntax

DELETE FROM mytable
WHERE condition;

Update Syntax

UPDATE mytable
SET column = value_or_expr, 
    other_column = another_value_or_expr, 
    …
WHERE condition;

Demo

SQL: Intro to Joins

Used for multi-table queries

• Tables can share information about the same entity
• They use a primary key that identifies that entity uniquely across the database

Syntax of an inner join:

SELECT column, another_table_column, …
FROM mytable
INNER JOIN another_table 
    ON mytable.id = another_table.id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

Using GORM with Go types

Embed Struct

type Product struct {
    gorm.Model
    Name  string
    Price uint
}

gorm.Model provides the fields ID, CreatedAt, UpdatedAt, and DeletedAt

Struct tags

Use struct tags to designate column types. There are many options, see documentation.

type Product struct {
    gorm.Model
    Name  string `gorm:"size:255"`
    Price uint
}

Basic CRUD Usage

To add data to your database, simply use db.Create(...)

product := Product{Name: "DigitalOcean Basic Droplet", Price: 5}
db.Create(&product)

To query for data from the database, use db.First(...) and db.Find(...)

var product Product
db.First(&product)

To make sophisticated queries, you can use plain SQL or struct/maps.

db.Where("name = ?", "Amazon Web Services").First(&product)
db.Where(&Product{Name: "Amazon Web Services").First(&product)
db.Where(map[string]interface{}{"name": "Amazon Web Services"}).First(&product)

These 3 methods are equivalent and result in the exact same SQL query:

SELECT * FROM products WHERE name = "Amazon Web Services" LIMIT 1;

Basic CRUD Usage

To delete an entry from the database, use db.Delete(...)

db.Delete(&product)

To update a model, use db.Save(...)

product.Price = 120
db.Save(&product)

To update a single field, there is a shortcut: model.Update(field, value)

db.Model(&product).Update("price", 80)

Associations

The biggest advantage of an ORM is with linked models. Relational databases are best with related models (duh!).

There are many types of relationships - belongs to, has many, many to many, etc.

To actually use these, refer to the GORM documentation. Here's a simple example:

type User struct {
    gorm.Model
    Products []Product
}

db.Model(&user).Related(&products)

In this example, a user has many products, but the products do not belong in any way to a user. Thus, we can implement a "cart" feature to the user by adding a "has many" type relationship.

Database Migrations

What if your database schema changes?

Hint: It always changes!

To migrate the schema of the current type's definition, use db.AutoMigrate(...)

db.AutoMigrate(&Product{})

You can also migrate multiple models at once:

db.AutoMigrate(&Product{}, &User{})

Important: the table name is the pluralized version of the struct name. For example, Product will have table name products.

Important: AutoMigrate will only create tables, columns, and indexes, but will not make any other changes automatically (ex: deleting a column).

Final Project

• Can work individually or in groups of 2
• ~10-20 hours of work per person
• Demos on last day of class (no final exam!)

Homework 9

• Final Project Proposals
• Complete by Tuesday, April 11

Thank you