GORM - basic

Pooling

connection = [1]in use [2]not use & return to the pool

fail to release connections back to pool => running out of resources.

username:password@tcp(127.0.0.1:3001)/dbname

Don’t Open() and Close() databases frequently.

always prepare queries to be used multiple times = Prepared statements is better than concatenating strings (SQL injection)

GORM - psql

import (
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/postgres"
)

func main() {
  db, err := gorm.Open(
    "postgres", 
    "host=myhost user=gorm dbname=gorm sslmode=disable password=mypassword"
  )
  defer db.Close()
}

GORM - sqlite

import (
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
)

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

GORM - migrate

AutoMigrate: 只會創建table, row, index. 不會change type, delete.

// AutoMigrate
db.AutoMigrate(&User{})

// is Model `User` exist?
db.HasTable(&User{})

// is table `users` exist?
db.HasTable("users")

// create table
db.CreateTable(&User{})

// delete table
db.DropTable(&User{})

// delete tables: User & products
db.DropTableIfExists(&User{}, "products")

// modify column
db.Model(&User{}).ModifyColumn("description", "text")

// delete column
db.Model(&User{}).DropColumn("description")

// add foreign key
db.Model(&User{}).AddForeignKey("city_id", "cities(id)", "RESTRICT", "RESTRICT")

// add idx_user_name index @ name column
db.Model(&User{}).AddIndex("idx_user_name", "name")
db.Model(&User{}).AddUniqueIndex("idx_user_name", "name")

// add idx_user_name_age index @ name/age columns
db.Model(&User{}).AddIndex("idx_user_name_age", "name", "age")
db.Model(&User{}).AddUniqueIndex("idx_user_name_age", "name", "age")

// delete index
db.Model(&User{}).RemoveIndex("idx_user_name")

GORM - CRUD

// create
db.Create(&Product{Code: "L1212", Price: 1000})

// read
var p Product
db.First(&p, 1)
db.First(&p, "code = ?", "L1212")

// update
db.Model(&p).Update("Price", 2000)

// delete
db.Delete(&p)