基本 SQL 實作：從 CRUD 到交易管理

2025 iThome 鐵人賽

DAY 10

Modern Web

Golang x Echo 30 天：零基礎GO , 後端入門系列第 10 篇

17th鐵人賽

whois512139

2025-09-24 09:30:49

37 瀏覽

分享至

想像你在夜市開一攤「資料便當店」：
客人點餐是 Create，查菜單是 Read，改配菜是 Update，退費是 Delete。
當一口氣接到多人團購，你就需要一次打包、失敗就全退的 交易（Transaction）。
今天用 Go + Echo + PostgreSQL，一路把這幾招練起來，輕鬆又不打結。🚀

今天要完成什麼？

用 PostgreSQL 建一張超實用的 todos 表
在 Go + Echo 裡做：CRUD（建立、讀取、更新、刪除）
示範一次成功、一次失敗就全部回滾的 交易管理
全程用參數化查詢，擋掉 SQL Injection（駭客沒戲唱）🛡️

前置：你已經用 Docker 跑起 PostgreSQL，並用 pgxpool 連線成功。
連線字串放到環境變數 DATABASE_URL，更專業、更安全。

建資料表（SQL）

先在資料庫準備一張簡單、夠用的待辦清單：

-- 建立 todos 表：最小可用 + 實務常見欄位
CREATE TABLE IF NOT EXISTS todos (
  id          SERIAL PRIMARY KEY,      -- 自動流水號
  title       TEXT NOT NULL,           -- 待辦標題
  done        BOOLEAN NOT NULL DEFAULT FALSE, -- 是否完成
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(), -- 建立時間
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()  -- 更新時間
);
 
-- 幫 updated_at 自動更新（Postgres 15 可用 generated column/triggers，這裡先簡單用程式處理）

專案骨架（Go + Echo）

兩段小程式就能跑起來：一段負責連線池，一段是路由處理。

1) 連線池（`db.go`）

package main
 
import (
	"context"
	"log"
	"os"
	"time"
 
	"github.com/jackc/pgx/v4/pgxpool"
)
 
var db *pgxpool.Pool
 
func initDB() {
	dsn := os.Getenv("DATABASE_URL")
	if dsn == "" {
		// Demo 用；正式環境請一定用環境變數
		dsn = "postgresql://postgres:mysecretpassword@localhost:5432/postgres"
	}
 
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
 
	pool, err := pgxpool.Connect(ctx, dsn)
	if err != nil {
		log.Fatalf("連線資料庫失敗: %v", err)
	}
	db = pool
}

小重點：WithTimeout 幫你擋住「連不到就一直卡」的情況。⏱️

2) 啟動 Echo（`main.go`）

package main
 
import (
	"net/http"
 
	"github.com/labstack/echo/v4"
)
 
func main() {
	initDB()          // 連線池
	defer db.Close()  // 禮貌關門
 
	e := echo.New()
 
	// 路由：CRUD
	e.POST("/todos", createTodo)    // Create
	e.GET("/todos", listTodos)      // Read (全部)
	e.GET("/todos/:id", getTodo)    // Read (單筆)
	e.PUT("/todos/:id", updateTodo) // Update
	e.DELETE("/todos/:id", delTodo) // Delete
 
	// 交易範例
	e.POST("/todos/bulk", bulkCreate) // 一次新增多筆（成功才全部成功）
 
	e.GET("/", func(c echo.Context) error {
		return c.String(http.StatusOK, "Hello, Echo + SQL!")
	})
	e.Logger.Fatal(e.Start(":1323"))
}

CRUD：最精華的 5 個 Handler

註解放好放滿，閱讀不頭痛。
每段都用 參數化（$1, $2...），資料跟 SQL 分開，駭客插不了嘴。

Create（新增）

// createTodo 新增一筆待辦
func createTodo(c echo.Context) error {
	type req struct {
		Title string `json:"title"`
	}
	var r req
	if err := c.Bind(&r); err != nil || r.Title == "" {
		return c.JSON(400, echo.Map{"error": "title 必填"})
	}
 
	ctx, cancel := context.WithTimeout(c.Request().Context(), 3*time.Second)
	defer cancel()
 
	const q = `INSERT INTO todos (title) VALUES ($1) RETURNING id, title, done, created_at, updated_at`
	row := db.QueryRow(ctx, q, r.Title)
 
	var todo struct {
		ID        int64     `json:"id"`
		Title     string    `json:"title"`
		Done      bool      `json:"done"`
		CreatedAt string    `json:"created_at"`
		UpdatedAt string    `json:"updated_at"`
	}
	if err := row.Scan(&todo.ID, &todo.Title, &todo.Done, &todo.CreatedAt, &todo.UpdatedAt); err != nil {
		return c.JSON(500, echo.Map{"error": "新增失敗"})
	}
	return c.JSON(201, todo)
}

Read（查全部 + 單筆）

// listTodos 取全部（簡單版，不做分頁）
func listTodos(c echo.Context) error {
	ctx, cancel := context.WithTimeout(c.Request().Context(), 3*time.Second)
	defer cancel()
 
	const q = `SELECT id, title, done, created_at, updated_at FROM todos ORDER BY id DESC`
	rows, err := db.Query(ctx, q)
	if err != nil {
		return c.JSON(500, echo.Map{"error": "查詢失敗"})
	}
	defer rows.Close()
 
	var list []map[string]any
	for rows.Next() {
		var t struct {
			ID        int64
			Title     string
			Done      bool
			CreatedAt string
			UpdatedAt string
		}
		if err := rows.Scan(&t.ID, &t.Title, &t.Done, &t.CreatedAt, &t.UpdatedAt); err != nil {
			return c.JSON(500, echo.Map{"error": "讀取資料失敗"})
		}
		list = append(list, echo.Map{
			"id": t.ID, "title": t.Title, "done": t.Done,
			"created_at": t.CreatedAt, "updated_at": t.UpdatedAt,
		})
	}
	return c.JSON(200, list)
}
 
// getTodo 取單筆
func getTodo(c echo.Context) error {
	ctx, cancel := context.WithTimeout(c.Request().Context(), 3*time.Second)
	defer cancel()
 
	const q = `SELECT id, title, done, created_at, updated_at FROM todos WHERE id = $1`
	id := c.Param("id")
	row := db.QueryRow(ctx, q, id)
 
	var t struct {
		ID        int64
		Title     string
		Done      bool
		CreatedAt string
		UpdatedAt string
	}
	if err := row.Scan(&t.ID, &t.Title, &t.Done, &t.CreatedAt, &t.UpdatedAt); err != nil {
		return c.JSON(404, echo.Map{"error": "找不到這筆資料"})
	}
	return c.JSON(200, t)
}

Update（更新）

// updateTodo 更新 title 或 done
func updateTodo(c echo.Context) error {
	type req struct {
		Title *string `json:"title"` // 指標：可選
		Done  *bool   `json:"done"`
	}
	var r req
	if err := c.Bind(&r); err != nil {
		return c.JSON(400, echo.Map{"error": "格式錯誤"})
	}
	if r.Title == nil && r.Done == nil {
		return c.JSON(400, echo.Map{"error": "至少提供一個欄位"})
	}
 
	ctx, cancel := context.WithTimeout(c.Request().Context(), 3*time.Second)
	defer cancel()
 
	const q = `
	  UPDATE todos
	     SET title = COALESCE($2, title),
	         done  = COALESCE($3, done),
	         updated_at = now()
	   WHERE id = $1
	 RETURNING id, title, done, created_at, updated_at`
	id := c.Param("id")
	row := db.QueryRow(ctx, q, id, r.Title, r.Done)
 
	var t struct {
		ID        int64
		Title     string
		Done      bool
		CreatedAt string
		UpdatedAt string
	}
	if err := row.Scan(&t.ID, &t.Title, &t.Done, &t.CreatedAt, &t.UpdatedAt); err != nil {
		return c.JSON(404, echo.Map{"error": "更新失敗或資料不存在"})
	}
	return c.JSON(200, t)
}

Delete（刪除）

// delTodo 刪除單筆
func delTodo(c echo.Context) error {
	ctx, cancel := context.WithTimeout(c.Request().Context(), 3*time.Second)
	defer cancel()
 
	const q = `DELETE FROM todos WHERE id = $1`
	ct, err := db.Exec(ctx, q, c.Param("id"))
	if err != nil {
		return c.JSON(500, echo.Map{"error": "刪除失敗"})
	}
	if ct.RowsAffected() == 0 {
		return c.JSON(404, echo.Map{"error": "資料不存在"})
	}
	return c.NoContent(204)
}

交易管理（Transaction）：一次成功、全部成功 ✨

情境：一次新增多筆 todos。任何一筆失敗，就全部不要（回滾）。
這很像「團購便當」：一個人臨時說不吃了、或金流刷卡失敗，整單就取消，避免有人沒拿到便當。

// bulkCreate 一次新增多筆（要嘛全成功，要嘛全回滾）
func bulkCreate(c echo.Context) error {
	var items []struct{ Title string `json:"title"` }
	if err := c.Bind(&items); err != nil || len(items) == 0 {
		return c.JSON(400, echo.Map{"error": "請提供待辦清單陣列"})
	}
 
	ctx, cancel := context.WithTimeout(c.Request().Context(), 5*time.Second)
	defer cancel()
 
	// 開一筆交易
	tx, err := db.Begin(ctx)
	if err != nil {
		return c.JSON(500, echo.Map{"error": "交易開始失敗"})
	}
	// 出口策略：若中途回傳，確保回滾
	defer func() {
		_ = tx.Rollback(ctx) // 若已 Commit，這行會是 no-op
	}()
 
	const q = `INSERT INTO todos (title) VALUES ($1)`
	for _, it := range items {
		if it.Title == "" {
			return c.JSON(400, echo.Map{"error": "有空白標題，交易取消"})
		}
		if _, err := tx.Exec(ctx, q, it.Title); err != nil {
			return c.JSON(500, echo.Map{"error": "其中一筆新增失敗，交易取消"})
		}
	}
 
	// 全部 OK → 交易提交
	if err := tx.Commit(ctx); err != nil {
		return c.JSON(500, echo.Map{"error": "交易提交失敗"})
	}
	return c.JSON(201, echo.Map{"status": "ok", "count": len(items)})
}

為什麼要用交易？

保證資料一致性：要嘛全部成功，要嘛全部回復到沒動過
適合：跨多表更新、庫存扣量、金流、批次匯入

小抄：五個實戰眉角 📝

一定要參數化查詢：WHERE id = $1，資料與 SQL 分離，安全度 +100。
Context + Timeout：避免「等到天荒地老」。
回傳狀態碼：201 新增成功、404 找不到、400 使用者輸入有誤、500 伺服器錯誤。
交易出口策略：defer tx.Rollback()，就算中途出錯也能優雅收尾。
欄位更新時間：記得更新 updated_at，方便除錯與排序。

測試方法（超快手）

# 新增
curl -X POST http://localhost:1323/todos \
  -H "Content-Type: application/json" \
  -d '{"title":"買黑糖珍奶"}'
 
# 查全部
curl http://localhost:1323/todos
 
# 更新
curl -X PUT http://localhost:1323/todos/1 \
  -H "Content-Type: application/json" \
  -d '{"done":true}'
 
# 刪除
curl -X DELETE http://localhost:1323/todos/1
 
# 交易（批次新增）
curl -X POST http://localhost:1323/todos/bulk \
  -H "Content-Type: application/json" \
  -d '[{"title":"寫文章"},{"title":"運動30分鐘"}]'