Golang RESTful API 與 Gin、Gorm、PostgreSQL

Golang RESTful API 服務的綜合範例，該服務使用 gin 進行路由、gorm 進行 ORM 以及 PostgreSQL 作為資料庫。此範例包括以下 PostgreSQL 功能：資料庫和表格建立、資料插入和查詢、索引、函數和預存程序、觸發器、視圖、CTE、事務、限制和 JSON 處理。

1. 項目設定

假設你已經設定了 PostgreSQL、Golang 和 go mod，初始化項目：

mkdir library-api
cd library-api
go mod init library-api

專案結構

/library-api
|-- db.sql
|-- main.go
|-- go.mod

2.安裝依賴項

安裝必要的軟體包：

go get github.com/gin-gonic/gin
go get gorm.io/gorm
go get gorm.io/driver/postgres

3.PostgreSQL 架構

這是一個用於建立資料庫模式的 SQL 腳本：

-- Create the library database.
CREATE DATABASE library;

-- Connect to the library database.
\c library;

-- Create tables.
CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    bio TEXT
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    -- This creates a foreign key constraint:
    -- It establishes a relationship between author_id in the books table and the id column in the authors table, ensuring that each author_id corresponds to an existing id in the authors table.
    -- ON DELETE CASCADE: This means that if an author is deleted from the authors table, all related records in the books table (i.e., books written by that author) will automatically be deleted as well.
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE,
    published_date DATE NOT NULL,
    description TEXT,
    details JSONB
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- CREATE TABLE borrow_logs (
--     id SERIAL PRIMARY KEY,
--     user_id INTEGER REFERENCES users(id),
--     book_id INTEGER REFERENCES books(id),
--     borrowed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
--     returned_at TIMESTAMP
-- );

-- Create a partitioned table for borrow logs based on year.
-- The borrow_logs table is partitioned by year using PARTITION BY RANGE (borrowed_at).
CREATE TABLE borrow_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    book_id INTEGER REFERENCES books(id),
    borrowed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    returned_at TIMESTAMP
) PARTITION BY RANGE (borrowed_at);

-- Create partitions for each year.
-- Automatic Routing: PostgreSQL automatically directs INSERT operations to the appropriate partition (borrow_logs_2023 or borrow_logs_2024) based on the borrowed_at date.
CREATE TABLE borrow_logs_2023 PARTITION OF borrow_logs
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE borrow_logs_2024 PARTITION OF borrow_logs
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
-- Benefit: This helps in improving query performance and managing large datasets by ensuring that data for each year is stored separately.



-- Indexes for faster searching.
CREATE INDEX idx_books_published_date ON books (published_date);
CREATE INDEX idx_books_details ON books USING GIN (details);
-- GIN Index (Generalized Inverted Index).  It is particularly useful for indexing columns with complex data types like arrays, JSONB, or text search fields


-- Add a full-text index to the title and description of books
CREATE INDEX book_text_idx ON books USING GIN (to_tsvector('english', title || ' ' || description));
-- to_tsvector('english', ...) converts the concatenated title and description fields into a Text Search Vector (tsv) suitable for full-text searching.
-- The || operator concatenates the title and description fields, so both fields are indexed together for searching.
-- 'english' specifies the language dictionary, which helps with stemming and stop-word filtering.


-- Create a simple view for books with author information.
CREATE VIEW book_author_view AS
SELECT books.id AS book_id, books.title, authors.name AS author_name
FROM books
JOIN authors ON books.author_id = authors.id;

-- Create a view to get user borrow history
CREATE VIEW user_borrow_history AS
SELECT
    u.id AS user_id,
    u.name AS user_name,
    b.title AS book_title,
    bl.borrowed_at,
    bl.returned_at
FROM
    users u
    JOIN borrow_logs bl ON u.id = bl.user_id
    JOIN books b ON bl.book_id = b.id;

-- Use a CTE to get all active borrow logs (not yet returned)
WITH active_borrows AS (
    SELECT * FROM borrow_logs WHERE returned_at IS NULL
)
SELECT * FROM active_borrows;

-- Function to calculate the number of books borrowed by a user.
-- Creates a function that takes an INT parameter user_id and returns an INT value. If the function already exists, it will replace it.
CREATE OR REPLACE FUNCTION get_borrow_count(user_id INT) RETURNS INT AS $$
    --  is a placeholder for the first input. When the function is executed, PostgreSQL replaces  with the actual user_id value that is passed in by the caller.
    SELECT COUNT(*) FROM borrow_logs WHERE user_id = ;
$$ LANGUAGE SQL;
-- AS $$ ... $$: This defines the body of the function between the dollar signs ($$).
-- LANGUAGE SQL: Specifies that the function is written in SQL.


-- Trigger to log activities.
CREATE TABLE activity_logs (
    id SERIAL PRIMARY KEY,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_activity() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO activity_logs (description)
    -- NEW refers to the new row being inserted or modified by the triggering event.
    VALUES ('A borrow_log entry has been added with ID ' || NEW.id);
    -- The function returns NEW, which means that the new data will be used as it is after the trigger action.
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- It uses plpgsql, which is a procedural language in PostgreSQL

CREATE TRIGGER log_borrow_activity
AFTER INSERT ON borrow_logs
FOR EACH ROW EXECUTE FUNCTION log_activity();

-- Add a JSONB column to store metadata
ALTER TABLE books ADD COLUMN metadata JSONB;
-- Example metadata: {"tags": ["fiction", "bestseller"], "page_count": 320}

4.Go語言代碼

這是使用 Gin 和 GORM 的 RESTful API 的完整範例：

package main

import (
    "net/http"
    "time"

    "github.com/gin-gonic/gin"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

type Author struct {
    ID   uint   `gorm:"primaryKey"`
    Name string `gorm:"not null;unique"`
    Bio  string
}

type Book struct {
    ID            uint                   `gorm:"primaryKey"`
    Title         string                 `gorm:"not null"`
    AuthorID      uint                   `gorm:"not null"`
    PublishedDate time.Time              `gorm:"not null"`
    Details       map[string]interface{} `gorm:"type:jsonb"`
}

type User struct {
    ID        uint   `gorm:"primaryKey"`
    Name      string `gorm:"not null"`
    Email     string `gorm:"not null;unique"`
    CreatedAt time.Time
}

type BorrowLog struct {
    ID         uint      `gorm:"primaryKey"`
    UserID     uint      `gorm:"not null"`
    BookID     uint      `gorm:"not null"`
    BorrowedAt time.Time `gorm:"default:CURRENT_TIMESTAMP"`
    ReturnedAt *time.Time
}

var db *gorm.DB

func initDB() {
    dsn := "host=localhost user=postgres password=yourpassword dbname=library port=5432 sslmode=disable"
    var err error
    db, err = gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        panic("failed to connect to database")
    }

    // Auto-migrate models.
    db.AutoMigrate(&Author{}, &Book{}, &User{}, &BorrowLog{})
}

func main() {
    initDB()
    r := gin.Default()

    r.POST("/authors", createAuthor)
    r.POST("/books", createBook)
    r.POST("/users", createUser)
    r.POST("/borrow", borrowBook)
    r.GET("/borrow/:id", getBorrowCount)
    r.GET("/books", listBooks)

    r.Run(":8080")
}

func createAuthor(c *gin.Context) {
    var author Author
    if err := c.ShouldBindJSON(&author); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }
    if err := db.Create(&author).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    c.JSON(http.StatusOK, author)
}

func createBook(c *gin.Context) {
    var book Book
    if err := c.ShouldBindJSON(&book); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }
    if err := db.Create(&book).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    c.JSON(http.StatusOK, book)
}

func createUser(c *gin.Context) {
    var user User
    if err := c.ShouldBindJSON(&user); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }
    if err := db.Create(&user).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    c.JSON(http.StatusOK, user)
}

// The Golang code does not need changes specifically to use the partitioned tables; the partitioning is handled by PostgreSQL
// you simply insert into the borrow_logs table, and PostgreSQL will automatically route the data to the correct partition.
func borrowBook(c *gin.Context) {
    var log BorrowLog
    if err := c.ShouldBindJSON(&log); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    tx := db.Begin()
    if err := tx.Create(&log).Error; err != nil {
        tx.Rollback()
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    tx.Commit()
    c.JSON(http.StatusOK, log)
}

func getBorrowCount(c *gin.Context) {
    userID := c.Param("id")
    var count int
    if err := db.Raw("SELECT get_borrow_count(?)", userID).Scan(&count).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    c.JSON(http.StatusOK, gin.H{"borrow_count": count})
}

// When querying a partitioned table in PostgreSQL using Golang, no changes are needed in the query logic or code.
// You interact with the parent table (borrow_logs in this case) as you would with any normal table, and PostgreSQL automatically manages retrieving the data from the appropriate partitions.
// Performance: PostgreSQL optimizes the query by scanning only the relevant partitions, which can significantly speed up queries when dealing with large datasets.
// Here’s how you might query the borrow_logs table using GORM, even though it’s partitioned:
func getBorrowLogs(c *gin.Context) {
    var logs []BorrowLog
    if err := db.Where("user_id = ?", c.Param("user_id")).Find(&logs).Error; err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    c.JSON(http.StatusOK, logs)
}

func listBooks(c *gin.Context) {
    var books []Book
    db.Preload("Author").Find(&books)
    c.JSON(http.StatusOK, books)
}

Golang代碼說明：

• 資料庫初始化：連接PostgreSQL資料庫並初始化GORM。
• 路由：定義建立作者、書籍、使用者、借閱書籍、取得借閱計數的路由。
• 事務處理：借書時使用事務來確保一致性。
• Preload：使用 GORM 的 Preload 來連接相關表格（作者與書籍）。
• 預存程序呼叫：使用 db.Raw 呼叫自訂 PostgreSQL 函數來計算借用計數。

5. 運行API

• 執行 PostgreSQL SQL 腳本來建立資料表、索引、檢視、函式和觸發器。

• 使用
  啟動Golang伺服器

   go run main.go
  

現在，您擁有了一個全面的Golang RESTful API，它涵蓋了各種 PostgreSQL 功能，使其成為學習或面試的強大範例。

6.新增更多功能。

讓我們透過合併視圖、CTE（通用表表達式）、全文索引來增強Golang RESTful API 範例以及其他Postgregre 功能 和JSON 處理。這些功能中的每一個都將透過相關的 PostgreSQL 表定義和與它們互動的 Golang 程式碼進行演示。

這部分的資料架構已經在上一節中準備好了，所以我們只需要添加更多的 golang 程式碼。

mkdir library-api
cd library-api
go mod init library-api

特點總結：

• 視圖：使用 user_borrow_history 視圖簡化對資料的訪問​​，使複雜的聯接更易於查詢。
• CTE：使用WITH子句進行有組織的查詢，例如取得活動借用日誌。
• 全文索引：透過 to_tsvector 上的 GIN 索引增強書籍的搜尋能力。

• JSON 處理：

  • 使用 JSONB 類型儲存和更新豐富的元資料。
  • getBookTags 從元資料 JSONB 欄位中檢索特定的 JSON 欄位（標籤）。
  • updateBookPageCount 更新或新增元資料 JSONB 欄位中的 page_count 欄位。

  透過將 db.Raw 和 db.Exec 用於 GORM 的原始 SQL，您可以利用 PostgreSQL 的強大功能，同時為應用程式的其他部分保留 GORM 的 ORM 功能。這使得該解決方案既靈活又功能豐富。

7.其他進階功能

在這個擴充範例中，我將展示如何使用 Golang 和 PostgreSQL 整合以下功能：

1. VACUUM：用於回收死元組所佔用的儲存空間並防止表格膨脹。
2. MVCC：透過維護不同版本的行來允許並發事務的概念。
3. 視窗函數：用於在與目前行相關的一組表格行上執行計算。

1.在Golang中使用VACUUM

VACUUM 通常用作維護任務，而不是直接來自應用程式程式碼。但是，您可以使用 GORM 的 Exec 來運行它以進行內務管理：

/library-api
|-- db.sql
|-- main.go
|-- go.mod

• VACUUM ANALYZE books：回收儲存並更新查詢規劃器為 books 表所使用的統計資料。
• 執行 VACUUM 通常是在非尖峰時段或作為維護腳本的一部分而不是針對每個請求運行。

2.了解MVCC（多版本並發控制）

PostgreSQL 的 MVCC 透過保留不同版本的行來允許並發交易。以下是如何使用交易在 Golang 中示範 MVCC 行為的範例：

go get github.com/gin-gonic/gin
go get gorm.io/gorm
go get gorm.io/driver/postgres

• FOR UPDATE：在交易期間鎖定所選行的更新，防止其他交易修改它，直到目前交易完成。
• 這確保了並發存取期間的一致性，顯示了 MVCC 如何允許並發讀取但鎖定行以進行更新。

3. 將視窗函數與 GORM 結合使用

視窗函數用於對與目前行相關的一組表行執行計算。以下是使用視窗函數計算每位作者借閱書籍的運行總數的範例：

mkdir library-api
cd library-api
go mod init library-api

• SUM(COUNT(bl.id)) OVER (PARTITION BY a.id ORDER BY bl.borrowed_at)：一個視窗函數，計算每個作者藉閱書籍的運行總數，按borrowed_at日期排序。
• 這可以提供見解，例如每個作者的借閱趨勢如何隨時間變化。

以上是Golang RESTful API 與 Gin、Gorm、PostgreSQL的詳細內容。更多資訊請關注PHP中文網其他相關文章！