CIS 193 – Go Programming

Prakhar Bhandari, Adel Qalieh

CIS 193

Course Logistics

Introduction to SQL

What is SQL?

What is a relational database?

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

SQL: Creating a Table

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

    title TEXT,
    director TEXT,
    year INTEGER, 
    length_minutes INTEGER


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


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


SELECT column, another_column, …
FROM mytable
WHERE condition(s)
LIMIT num_limit OFFSET num_offset;L

SQL: Deleting and Updating

Delete Syntax

WHERE condition;

Update Syntax

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


SQL: Intro to Joins

Used for multi-table queries

Syntax of an inner join:

SELECT column, another_table_column, …
FROM mytable
INNER JOIN another_table 
    ON =
WHERE condition(s)
LIMIT num_limit OFFSET num_offset;

Using SQL in golang

You can use this SQL knowledge with Go to interact with databases with database/sql

import (
    _ "" // or some other driver

This package supports SQL syntax

db, err := sql.Open("sqlite3", "./test.db")
defer db.Close()

_, err = db.Exec("CREATE TABLE movie_info ( id INTEGER, name VARCHAR(32), rating FLOAT )")

rows, err := db.Query("SELECT * FROM movie_info")


Introduction to ORM

Object-relational mapping is a way of converting data between native data structures from different languages. Translation: "object" = your programming language, "relational" = your relational database, and "mapping" = the bridge between them.

In this case, we are talking about converting between Go types and SQL types.

ORM Packages

There is no standard ORM in the standard library. Here are some options:

Getting started with GORM


go get -u


Choose a SQL dialect, in this case SQLite. Good options include SQLite, MySQL, and PostgreSQL.

import (
    _ ""

Connect to database

The parameters use depend entirely on your dialect. See the GORM documentation.

db, err := gorm.Open("sqlite3", "/tmp/gorm.db")
defer db.Close()

Using GORM with Go types

Embed Struct

type Product struct {
    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 {
    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}

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

var product 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(...)


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

product.Price = 120

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

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


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 {
    Products []Product


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(...)


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

Homework 9

Thank you

Prakhar Bhandari, Adel Qalieh

CIS 193