Golang 資料庫查詢package目錄分類

本篇對「Golang sql PostgreSQL查詢範例」的程式碼做package目錄劃分。

主要劃分成：

• db - 取得資料庫連線。
• model - 資料庫對應物件。
• repo - 資料庫查詢程式。

專案目錄結構如下。

go-demo/
├── db/
│   └── db.go
├── model/
│   └── employee.go
├── repo/
│   └── employee.go
├── go.mod
├── go.sum
└── main.go

Repository的客戶端定義介面EmployeeRepository，測試時可以做mock。

main.go

package main

import (
    "fmt"

    "abc.com/demo/db"
    "abc.com/demo/model"
    "abc.com/demo/repo"
)

type EmployeeRepository interface {
    GetAllEmployees() ([]model.Employee, error)
    GetEmployeeByID(id int64) (*model.Employee, error)
}

func main() {
    db := db.OpenDB()
    defer db.Close()

    var er EmployeeRepository = repo.NewEmployeeRepository(db)
    emps, err := er.GetAllEmployees()
    if err != nil {
        panic(err)
    }
    fmt.Println(emps)

    emp, err := er.GetEmployeeByID(1)
    if err != nil {
        panic(err)
    }
    fmt.Println(*emp)
}

db/db.go

package db

import (
    "database/sql"
    "fmt"
    
    _ "github.com/lib/pq"
)

const (
    HOST     = "localhost"
    PORT     = "5432"
    DATABASE = "postgres"
    USER     = "admin"
    PASSWORD = "12345"
    SSL      = "disable"
)

func OpenDB() *sql.DB {
    driver := "postgres"
    dsn := fmt.Sprintf(
        "host=%s port=%s user=%s password=%s dbname=%s sslmode=%s",
        HOST, PORT, USER, PASSWORD, DATABASE, SSL)

    db, err := sql.Open(driver, dsn)
    if err != nil {
        panic("open database error")
    }
    return db
}

model/employee.go

package model

import "time"

type Employee struct {
    ID        int64
    Name      string
    Age       int
    CreatedAt time.Time
}

EmployeeRepositoryImpl實作介面EmployeeRepository。

repo/employee.go

package repo

import (
    "database/sql"

    "abc.com/demo/model"
)

type EmployeeRepositoryImpl struct {
    db *sql.DB
}

func NewEmployeeRepository(db *sql.DB) *EmployeeRepositoryImpl {
    return &EmployeeRepositoryImpl{
        db: db,
    }
}

func (er *EmployeeRepositoryImpl) GetAllEmployees() ([]model.Employee, error) {
    rows, err := er.db.Query("SELECT id, name, age, created_at FROM employee")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var emps []model.Employee
    for rows.Next() {
        var e model.Employee
        err = rows.Scan(&e.ID, &e.Name, &e.Age, &e.CreatedAt)
        if err != nil {
            return nil, err
        }
        emps = append(emps, e)
    }
    return emps, nil
}

func (er *EmployeeRepositoryImpl) GetEmployeeByID(id int64) (*model.Employee, error) {
    row := er.db.QueryRow("SELECT * FROM employee WHERE id = $1 LIMIT 1", id)
    var emp model.Employee
    err := row.Scan(
        &emp.ID,
        &emp.Name,
        &emp.Age,
        &emp.CreatedAt,
    )
    if err != nil {
        return nil, err
    }
    return &emp, nil
}

github。

參考「Golang mock struct methods」測試時對EmployeeRepositoryImpl做mock。