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 $$
-- $1 is a placeholder for the first input. when the function is executed, postgresql replaces $1 with the actual user_id value that is passed in by the caller.
select count(*) from borrow_logs where user_id = $1;
$$ 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 示例以及其他 postgresql 功能 和 json 处理。这些功能中的每一个都将通过相关的 postgresql 表定义和与它们交互的 golang 代码进行演示。
这部分的数据架构已经在上一节中准备好了，所以我们只需要添加更多的 golang 代码即可。
// querying the user_borrow_history view:
func getuserborrowhistory(c *gin.context) {
var history []struct {
userid uint `json:"user_id"`
username string `json:"user_name"`
booktitle string `json:"book_title"`
borrowedat time.time `json:"borrowed_at"`
returnedat *time.time `json:"returned_at,omitempty"`
}
if err := db.raw("select * from user_borrow_history where user_id = ?", c.param("user_id")).scan(&history).error; err != nil {
c.json(http.statusinternalservererror, gin.h{"error": err.error()})
return
}
c.json(http.statusok, history)
}
// using a cte in a query:
func getactiveborrows(c *gin.context) {
var logs []borrowlog
query := `
with active_borrows as (
select * from borrow_logs where returned_at is null
)
select * from active_borrows where user_id = ?`
if err := db.raw(query, c.param("user_id")).scan(&logs).error; err != nil {
c.json(http.statusinternalservererror, gin.h{"error": err.error()})
return
}
c.json(http.statusok, logs)
}
// full-text search in books:
func searchbooks(c *gin.context) {
var books []book
searchquery := c.query("q")
query := `
select * from books
where to_tsvector('english', title || ' ' || description) @@ plainto_tsquery('english', ?)
`
if err := db.raw(query, searchquery).scan(&books).error; err != nil {
c.json(http.statusinternalservererror, gin.h{"error": err.error()})
return
}
c.json(http.statusok, books)
}
// handling jsonb data:
func updatebookmetadata(c *gin.context) {
var metadata map[string]interface{}
if err := c.shouldbindjson(&metadata); err != nil {
c.json(http.statusbadrequest, gin.h{"error": err.error()})
return
}
bookid := c.param("book_id")
if err := db.model(&book{}).where("id = ?", bookid).update("metadata", metadata).error; err != nil {
c.json(http.statusinternalservererror, gin.h{"error": err.error()})
return
}
c.json(http.statusok, gin.h{"status": "metadata updated"})
}
// can query a specific field from a jsonb column using the ->> operator to extract a value as text:
func getbooktags(c *gin.context) {
var tags []string
bookid := c.param("book_id")
query := `select metadata->>'tags' from books where id = ?`
if err := db.raw(query, bookid).scan(&tags).error; err != nil {
c.json(http.statusinternalservererror, gin.h{"error": err.error()})
return
}
c.json(http.statusok, gin.h{"tags": tags})
}
// to add or update fields in a jsonb column, use the jsonb_set function:
func updatebookpagecount(c *gin.context) {
bookid := c.param("book_id")
var input struct {
pagecount int `json:"page_count"`
}
if err := c.shouldbindjson(&input); err != nil {
c.json(http.statusbadrequest, gin.h{"error": err.error()})
return
}
query := `
update books
set metadata = jsonb_set(metadata, '{page_count}', to_jsonb(?::int), true)
where id = ?`
if err := db.exec(query, input.pagecount, bookid).error; err != nil {
c.json(http.statusinternalservererror, gin.h{"error": err.error()})
return
}
c.json(http.statusok, gin.h{"status": "page count updated"})
}
特点总结：
- 视图：使用 user_borrow_history 视图简化对数据的访问，使复杂的联接更易于查询。
- cte：使用with子句进行有组织的查询，例如获取活动借用日志。
- 全文索引：通过 to_tsvector 上的 gin 索引增强书籍的搜索能力。
-
json 处理：
立即学习“go语言免费学习笔记（深入）”；
- 使用 jsonb 类型存储和更新丰富的元数据。
- getbooktags 从元数据 jsonb 列中检索特定的 json 字段（标签）。
- updatebookpagecount 更新或添加元数据 jsonb 列中的 page_count 字段。
通过将 db.raw 和 db.exec 用于 gorm 的原始 sql，您可以利用 postgresql 的强大功能，同时为应用程序的其他部分保留 gorm 的 orm 功能。这使得该解决方案既灵活又功能丰富。
7.其他高级功能
在这个扩展示例中，我将展示如何使用 golang 和 postgresql 集成以下功能：
- vacuum：用于回收死元组占用的存储并防止表膨胀。
- mvcc：通过维护不同版本的行来允许并发事务的概念。
- 窗口函数：用于在与当前行相关的一组表行上执行计算。
1. 在golang中使用vacuum
vacuum 通常用作维护任务，而不是直接来自应用程序代码。但是，您可以使用 gorm 的 exec 来运行它以进行内务管理：
func vacuumbooks(c *gin.context) {
if err := db.exec("vacuum analyze books").error; err != nil {
c.json(http.statusinternalservererror, gin.h{"error": err.error()})
return
}
c.json(http.statusok, gin.h{"status": "vacuum performed successfully"})
}
- vacuum analyze books：回收存储并更新查询规划器为 books 表使用的统计信息。
- 运行 vacuum 通常是在非高峰时段或作为维护脚本的一部分而不是针对每个请求运行。
2.了解mvcc（多版本并发控制）
postgresql 的 mvcc 通过保留不同版本的行来允许并发事务。以下是如何使用事务在 golang 中演示 mvcc 行为的示例：
func updatebooktitle(c *gin.context) {
bookid := c.param("book_id")
var input struct {
newtitle string `json:"new_title"`
}
if err := c.shouldbindjson(&input); err != nil {
c.json(http.statusbadrequest, gin.h{"error": err.error()})
return
}
// start a transaction to demonstrate mvcc
tx := db.begin()
defer func() {
if r := recover(); r != nil {
tx.rollback()
}
}()
var book book
if err := tx.set("gorm:query_option", "for update").first(&book, bookid).error; err != nil {
tx.rollback()
c.json(http.statusnotfound, gin.h{"error": "book not found"})
return
}
// simulate an update to demonstrate mvcc handling
book.title = input.newtitle
if err := tx.save(&book).error; err != nil {
tx.rollback()
c.json(http.statusinternalservererror, gin.h{"error": err.error()})
return
}
// commit the transaction
tx.commit()
c.json(http.statusok, gin.h{"status": "book title updated"})
}
- for update：在事务期间锁定要更新的选定行，防止其他事务修改它，直到当前事务完成。
- 这确保了并发访问期间的一致性，展示了 mvcc 如何允许并发读取但锁定行以进行更新。
3. 将窗口函数与 gorm 结合使用
窗口函数用于对与当前行相关的一组表行执行计算。以下是使用窗口函数计算每位作者借阅书籍的运行总数的示例：
func getAuthorBorrowStats(c *gin.Context) {
var stats []struct {
AuthorID int `json:"author_id"`
AuthorName string `json:"author_name"`
TotalBorrows int `json:"total_borrows"`
RunningTotal int `json:"running_total"`
}
query := `
SELECT
a.id AS author_id,
a.name AS author_name,
COUNT(bl.id) AS total_borrows,
SUM(COUNT(bl.id)) OVER (PARTITION BY a.id ORDER BY bl.borrowed_at) AS running_total
FROM authors a
LEFT JOIN books b ON b.author_id = a.id
LEFT JOIN borrow_logs bl ON bl.book_id = b.id
GROUP BY a.id, a.name, bl.borrowed_at
ORDER BY a.id, bl.borrowed_at
`
if err := db.Raw(query).Scan(&stats).Error; err != nil {
c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
return
}
c.JSON(http.StatusOK, stats)
}
- sum(count(bl.id)) over (partition by a.id order by bl.borrowed_at)：一个窗口函数，计算每个作者借阅书籍的运行总数，按borrowed_at日期排序。
- 这可以提供见解，例如每个作者的借阅趋势如何随时间变化。
