How to implement CRUD in golang ?

今天我們將學習如何用Golang編寫程式碼來執行資料庫的CRUD（創建、讀取、更新、刪除）操作，目前大致有以下4種的主流用法，我們接下來會大致介紹並比較:

Database/SQL

// Pass a row SQL query and then scan the result into target variables.
err := pool.QueryRowContext(ctx, "select p.name from people as p where p.id = :id;", sql.Named("id", id)).Scan(&name)
	if err != nil {
		log.Fatal("unable to execute search query", err)
	}

• Low-level standard library.
• Generic interface around SQL (or SQL-like) databases
• Very fast & Straightforward
• Manual mapping SQL fields to variables
• Easy to make mistakes , not caught until runtime.

Gorm

// Create a Record
user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()}
result := db.Create(&user)

// Get the first record ordered by primary key
db.First(&user)
// SELECT * FROM users ORDER BY id LIMIT 1;

// Get one record, no specified order
db.Take(&user)
// SELECT * FROM users LIMIT 1;

// Get last record, ordered by primary key desc
db.Last(&user)
// SELECT * FROM users ORDER BY id DESC LIMIT 1;

result := db.First(&user)
result.RowsAffected // returns count of records found
result.Error        // returns error or nil

// check error ErrRecordNotFound
errors.Is(result.Error, gorm.ErrRecordNotFound)

• High-level Object Relational Mapping library(ORM) for Golang
• CRUD functions already implemented
• Very short production code
• Must learn to write queries using Gorm function
• When having a complex SQL query( join table ) also need to learn how to declare associations tags.
• Run slowly on high load (3-5 times slower than the standard library)

SQLX

// if you have null fields and use SELECT *, you must use sql.Null* in your struct
    places := []Place{}
    err = db.Select(&places, "SELECT * FROM place ORDER BY telcode ASC")
    if err != nil {
        fmt.Println(err)
        return
    }
    usa, singsing, honkers := places[0], places[1], places[2]

• Middle-way approach
• Quite fast & easy to use (like database/sql )
• Fields mapping via query text & struct tags
• Failure won’t occur until runtime

SQLC

// Write SQL code
CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

// Tell SQLC what need to generate
-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = $1 LIMIT 1;

-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;

// Automatic generation by SQLC
//list all authors
	authors, err := queries.ListAuthors(ctx)
	if err != nil {
		return err
	}
	log.Println(authors)

• Very fast & easy to use
• Automatic code generation
• Catch SQL query errors before generating codes.
• Only support postgresSQL and MySQL