09-databases.slide

# Databases
Course Go
Tags: golang, go

## Outline

- SQL
- RDBMSs
- Database migrations
- Go & SQL:
    - sql
    - sqlx
    - sqlc
    - GORM

## SQL

## SQL

- **Structured Query Language**
    - Data definition language
        - `CREATE`/`ALTER`/`DROP`
    - Data query language
        - `SELECT`/`FROM`/`WHERE`
    - Data manipulation language
        - `SELECT`/`INSERT`/`UPDATE`/`DELETE`
    - Data control language
        - `GRANT`/`REVOKE`
- Standard way of communicating with RDBMSs

[Wikipedia: SQL](https://en.wikipedia.org/wiki/SQL)

## SQL

.code assets/lecture-09/sql/example.sql

## RDBMS

- Oracle
    - Commercial product
    - Heavily used in enterprise systems
    - Fintech etc.
- [MySQL](https://www.mysql.com)
    - Owned by Oracle after the aquisition of Sun Microsystems [2009]
    - Open-source
    - Dual-licensing
- MS SQL

[DB-Engines: Ranking](https://db-engines.com/en/ranking)

## RDBMS

- [PostgreSQL](https://www.postgresql.org)
    - Originated from `Ingres` project at University of California, Berkeley
    - Free and open-source, MIT-like license
    - Feature-rich
    - Plenty of extensions
- [MariaDB](https://mariadb.com)
    - Fork of MySQL
    - Community-driven
    - Remains highly compatible with MySQL

[DB-Engines: Ranking](https://db-engines.com/en/ranking)

## RDBMS

- [SQLite](https://www.sqlite.org)
    - Open-source, public domain
    - Lightweight, serverless
    - Uses ordinary disk files

[DB-Engines: Ranking](https://db-engines.com/en/ranking)

## Database Migrations

## Database Migrations

- Changes to you database schema have to be made as your application evolves
- Done using database migrations
    - Can be easily tracked in time
    - Automated migration application
    - Versioned
- The migration tools usually create a custom table in database
    - Tracks current schema state

## Migrations

```
migrations/
├── 20240420114809_init.up.sql
├── 20240420114809_init.down.sql
├── 20240421013210_alter_user.up.sql
├── 20240421013210_alter_user.down.sql
└── 20240422221345_add_product.up.sql
└── 20240422221345_add_product.down.sql
```

`add_product` migration:

.code assets/lecture-09/migrations/product.up.sql

.code assets/lecture-09/migrations/product.down.sql

## migrate

- Offers both a Go library and a CLI
- Multiple [migration sources](https://github.com/golang-migrate/migrate?tab=readme-ov-file#migration-sources)
    - Filesystem
    - GitHub
    - Gitlab
    - AWS S3
    - Google Cloud Storage

[GitHub: migrate](https://github.com/golang-migrate/migrate)

## migrate CLI

- Creating new migrations:

```
$ migrate create -ext sql -dir migrations add_customer
/course-go/lectures/migrations/20240421124900_add_customer.up.sql
/course-go/lectures/migrations/20240421124900_add_customer.down.sql
```

- Applying migrations:

```
$ migrate -source file://migrations -database postgres://localhost:5432/postgres up
```

```
$ migrate -source file://migrations -database postgres://localhost:5432/postgres down 1
```

```
$ docker run -v app/migrations:/migrations --network host migrate/migrate
    -path=/migrations/ -database postgres://localhost:5432/database up 2
```

- Dropping the database:

```
$ migrate -source file://migrations \
    -database "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable" drop
```

[GitHub: migrate CLI](https://github.com/golang-migrate/migrate/tree/v4.17.1/cmd/migrate)

## migrate in code

.code assets/lecture-09/migrations/migrate.go /START OMIT/,/END OMIT

[Go Packages: migrate](https://pkg.go.dev/github.com/golang-migrate/migrate/v4)

## Alternatives

- [goose](https://github.com/pressly/goose)
- [atlas](https://github.com/ariga/atlas)
- [dbmate](https://github.com/amacneil/dbmate)

## Go & SQL

## Standard library

- Rather low-level
- Defines generic interfaces for SQL databases
- Needs to be used in conjuntion with a specific database driver
    - Plenty of [supported drivers](https://go.dev/wiki/SQLDrivers)
    - Drivers for all previously mentioned RDBMSs exist

[Go Packages: database/sql](https://pkg.go.dev/database/sql)

## Connection to a DB

.code assets/lecture-09/std/sql.go /MAIN START OMIT/,/MAIN END OMIT/

## DB type

- Returned by the `Open` functions
- Represents a connection pool
    - Can be tinkered with using methods like:
        - `SetConnMaxIdleTime(time.Duration)`
        - `SetConnMaxLifetime(time.Duration)`
        - `SetMaxIdleConns(int)`
        - `SetMaxOpenConns(int)`
- Safe for concurrent use
- Connections are represented by the [Conn](https://pkg.go.dev/database/sql#Conn) type
    - Always prefer the `DB` pool

[Go Packages: database/sql DB type](https://pkg.go.dev/database/sql#DB)

## Query types

- All of these exist in a `Context` variant
    - Again, always preferred
- **Exec**: no rows are returned

.code assets/lecture-09/std/sql.go /EXEC START OMIT/,/EXEC END OMIT/

- **QueryRow**: at most one row is expected

.code assets/lecture-09/std/sql.go /QUERYROW START OMIT/,/QUERYROW END OMIT/

## Query types

- **Query**: multiple values are expected

.code assets/lecture-09/std/sql.go /QUERY START OMIT/,/QUERY END OMIT/

## Transactions

- Manipulated via the [Tx](https://pkg.go.dev/database/sql#Tx) type returned by `Begin` functions

.code assets/lecture-09/std/sql.go /TRANSACTION START OMIT/,/TRANSACTION END OMIT/

## sqlx

## sqlx

- Extension to the standard `sql` library
- Provides plenty of additional features
    - Marshalling rows to structs
    - Named parameters
- The usage is not as tidious
- There is also a [SQLx](https://github.com/launchbadge/sqlx) library for Rust
    - Keep in mind when looking it up

[Go Packages: sqlx](https://pkg.go.dev/github.com/jmoiron/sqlx)

[GitHub IO: Illustrated guide to SQLX](http://jmoiron.github.io/sqlx/)

## Connecting to DB

.code assets/lecture-09/sqlx/sqlx.go /MAIN START OMIT/,/MAIN END OMIT/

[Github: sqlx usage](https://github.com/jmoiron/sqlx?tab=readme-ov-file#usage)

## Seed

.code assets/lecture-09/sqlx/sqlx.go /SEED START OMIT/,/SEED END OMIT/

[Github: sqlx usage](https://github.com/jmoiron/sqlx?tab=readme-ov-file#usage)

## Unmarshalling to structs

.code assets/lecture-09/sqlx/sqlx.go /SELECT START OMIT/,/SELECT END OMIT/

[Github: sqlx usage](https://github.com/jmoiron/sqlx?tab=readme-ov-file#usage)

## Maps in selects

.code assets/lecture-09/sqlx/sqlx.go /MAP START OMIT/,/MAP END OMIT/

[Github: sqlx usage](https://github.com/jmoiron/sqlx?tab=readme-ov-file#usage)

## Batch inserts

.code assets/lecture-09/sqlx/sqlx.go /BATCH-INSERT START OMIT/,/BATCH-INSERT END OMIT/

[Github: sqlx usage](https://github.com/jmoiron/sqlx?tab=readme-ov-file#usage)

## sqlc

## sqlc

- A SQL compiler
- An interesting take on query creation
    - Create SQL file with annotations
    - Generate Go code from annotations
- Supports additional languages
    - [Kotlin](https://github.com/sqlc-dev/sqlc-gen-kotlin), [Python](https://github.com/sqlc-dev/sqlc-gen-python), [TypeScript](https://github.com/sqlc-dev/sqlc-gen-typescript)
- Currently supports MySQL, PostgreSQL & SQLite for Go

[Go Packages: sqlc](https://pkg.go.dev/github.com/kyleconroy/sqlc)

## sqlc CLI

```
Usage:
  sqlc [command]

Available Commands:
  compile     Statically check SQL for syntax and type errors
  completion  Generate the autocompletion script for the specified shell
  createdb    Create an ephemeral database
  diff        Compare the generated files to the existing files
  generate    Generate source code from SQL
  help        Help about any command
  init        Create an empty sqlc.yaml settings file
  push        Push the schema, queries, and configuration for this project
  verify      Verify schema, queries, and configuration for this project
  version     Print the sqlc version number
  vet         Vet examines queries

Flags:
  -f, --file string    specify an alternate config file (default: sqlc.yaml)
  -h, --help           help for sqlc
      --no-database    disable database connections (default: false)
      --no-remote      disable remote execution (default: false)
```

[sqlc documentation](https://docs.sqlc.dev/en/latest/)

## Configuration

.code assets/lecture-09/sqlc/sqlc.yaml

[sqlx documentation: configuration](https://docs.sqlc.dev/en/latest/reference/config.html)

## Schema

.code assets/lecture-09/sqlc/schema.sql

## Query

.code assets/lecture-09/sqlc/query.sql

## Generated code

.code assets/lecture-09/sqlc/generated/query.sql.go /START OMIT/,/END OMIT/

## GORM

## GORM

- Go object-relational mapping (ORM)
    - High-level
- Full-featured ORM
    - Associations
    - Hooks
    - Eager loading
    - SQL builder
    - Automigrations
- Implements its own drivers
    - MySQL, PostgreSQL, SQLite, SQL Server, and TiDB

[GORM](https://gorm.io/index.html)

## Showcase

.code assets/lecture-09/gorm/gorm.go /OPEN START OMIT/,/OPEN END OMIT/

## Model

- Utility type
    - Designed for embedding

.code assets/lecture-09/gorm/models.go /MODEL START OMIT/,/MODEL END OMIT/

## Primary key conventions

- Field named **ID** is used as the default primary key

.code assets/lecture-09/gorm/models.go /USER START OMIT/,/USER END OMIT/

- Can be overwritten with a tag

.code assets/lecture-09/gorm/models.go /ANIMAL START OMIT/,/ANIMAL END OMIT/

## Naming conventions

- Can be adjusted using the [GORM config](https://gorm.io/docs/gorm_config.html#naming_strategy)
- All names are converted to snake case by default

.code assets/lecture-09/gorm/models.go /CATEGORY START OMIT/,/CATEGORY END OMIT/

- Uses pluralized struct names as table names
    - Can be overwritten using the `Tabler` interface

.code assets/lecture-09/gorm/models.go /TABLENAME START OMIT/,/TABLENAME END OMIT/

## Naming conventions

- Column names can be overwritten using tags

.code assets/lecture-09/gorm/models.go /SHOP START OMIT/,/SHOP END OMIT/

## Timestamp conventions

- `CreatedAt` and `UpdatedAt` are automatically managed for you

.code assets/lecture-09/gorm/gorm.go /CONVENTIONS START OMIT/,/CONVENTIONS END OMIT/

- Can be disabled

.code assets/lecture-09/gorm/models.go /ORDER START OMIT/,/ORDER END OMIT/

## CRUD: Create

.code assets/lecture-09/gorm/gorm.go /CREATE START OMIT/,/CREATE END OMIT/

[GORM: Create](https://gorm.io/docs/create.html)

## CRUD: Read

.code assets/lecture-09/gorm/gorm.go /READ START OMIT/,/READ END OMIT/

[GORM: Query](https://gorm.io/docs/query.html)

## CRUD: Read with conditions

.code assets/lecture-09/gorm/gorm.go /READ-CONDITIONS START OMIT/,/READ-CONDITIONS END OMIT/

[GORM: Query](https://gorm.io/docs/query.html)

## CRUD: Update

.code assets/lecture-09/gorm/gorm.go /UPDATE START OMIT/,/UPDATE END OMIT/

[GORM: Update](https://gorm.io/docs/update.html)

## CRUD: Delete 

.code assets/lecture-09/gorm/gorm.go /DELETE START OMIT/,/DELETE END OMIT/

[GORM: Delete](https://gorm.io/docs/delete.html)

## CRUD: Soft deletes 

.code assets/lecture-09/gorm/gorm.go /SOFT-DELETE START OMIT/,/SOFT-DELETE END OMIT/

[GORM: Soft Delete](https://gorm.io/docs/delete.html#Soft-Delete)

## Hooks

- Functions triggered on before/after specified event
    - Creation/Querying/Updating/Deletion
- Hooks
    - `BeforeSave`/`AfterSave`
    - `BeforeCreate`/`AfterCreate`
    - `BeforeUpdate`/`AfterUpdate`
    - `BeforeDelete`/`AfterDelete`
    - `AfterFind`

[GORM: Hooks](https://gorm.io/docs/hooks.html)

## Hooks

.code assets/lecture-09/gorm/hooks.go /START OMIT/,/END OMIT/

[GORM: Hooks](https://gorm.io/docs/hooks.html)

## Associations: Belongs To

.code assets/lecture-09/gorm/associations.go /IMPLICIT START OMIT/,/IMPLICIT END OMIT/

.code assets/lecture-09/gorm/associations.go /EXPLICIT START OMIT/,/EXPLICIT END OMIT/

[GORM: Belongs To](https://gorm.io/docs/belongs_to.html)

## Associations: Has One

.code assets/lecture-09/gorm/associations.go /HAS-ONE START OMIT/,/HAS-ONE END OMIT/

[GORM: Has One](https://gorm.io/docs/has_one.html)

## Associations: Has Many

.code assets/lecture-09/gorm/associations.go /HAS-MANY START OMIT/,/HAS-MANY END OMIT/

[GORM: Has Many](https://gorm.io/docs/has_many.html)

## Associations: Many To Many

.code assets/lecture-09/gorm/associations.go /MANY-TO-MANY START OMIT/,/MANY-TO-MANY END OMIT/

[GORM: Many To Many](https://gorm.io/docs/many_to_many.html)

## Raw SQL 

- Not all SQL queries can be executed using the ORM itself
- Supports raw SQL execution

.code assets/lecture-09/gorm/gorm.go /RAW START OMIT/,/RAW END OMIT/