[翻译]Real-world SQL in Go: PartI
不管您是不是讨厌还是接受SQL,在某个时候您都会在项目中使用它。关系数据库结构并不总是能很好地映射到应用程序数据结构,但SQL的普遍性意味着它是开发人员在需要数据持久性时使用的基本工具。
虽然Go有GORM之类的对象关系映射库,但我们将避免这种集成库,而直接使用标准库包database/sql
。ORM库封装了SQL
底层的复杂性,在简单的数据访问中这很有帮助,但缺点是会使高级查询和调试更加复杂。
在本文中,我们将研究如何直接使用SQL
构造数据访问代码、在何处处理业务模型以及一些有用的SQL技巧。我将引用WTF Dial项目的代码。你可以通过这篇介绍性的博客文章来了解它。
实现服务接口
在上一篇关于CRUD
设计的博客文章中,我描述了一个用于管理实体(entity)的接口。一个dial
是衡量当前用户对自己所属团队的感到沮丧指标。同时该团队中其他成员也有对应的dial
指标。
// dial.go#L81-L122
type DialService interface {
FindDialByID(ctx context.Context, id int) (*Dial, error)
FindDials(ctx context.Context, filter DialFilter) ([]*Dial, int, error)
CreateDial(ctx context.Context, dial *Dial) error
UpdateDial(ctx context.Context, id int, upd DialUpdate) (*Dial, error)
DeleteDial(ctx context.Context, id int) error
}
这里我们使用SQLite作为我们的数据库。但是在这里讨论的概念适用于任何SQL数据库。 主要包含三个包wtf.DialService
,sqlite
,sqlite.DialService
。
封装数据库
将我们的包命名为通用的名字(比如sqlite
)似乎有些奇怪,但我们的包将封装底层的其他包,而不会暴露这些包的内部。我们包的调用者只能通过我们的包与SQL和SQLite交互。你命名为sqlite
database/sql
mattn/go-sqlite3
wtfsqlite
wtfsql
,但我个人觉得丑陋。
因为我们没有公开底层的SQL包,所以在我们的应用程序中提供自己的封装包类型:DB
sqlite
// sqlite/sqlite.go#L43-L58
// DB represents a database connection to our application.
type DB struct {
db *sql.DB
// Datasource name.
DSN string
}
// Open opens the database connection.
func (db *DB) Open() (err error)
// Close closes the database connection.
func (db *DB) Close() error
这种封装让我们可以在我们的方法中处理数据库的启动和关闭,而不需要关心底层数据库Open()
的Close()
的细节.
从SQL helpers 中分离接口实现
在我的包中,我发现解藕服务接口实现和实际执行SQL的函数很有用。服务实现存在于原因:
- 提供接口
wtf.DialService
的一个实现。 - 提供业务边界。
因此,服务方法通常很小,并且采用以下格式:
// sqlite/dial.go#L75-L99
func (s *DialService) CreateDial(ctx context.Context, dial *wtf.Dial) error {
tx, err := s.db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer tx.Rollback()
// Call helper functions...
return tx.Commit()
}
在本例中,我们启动一个事务,执行一些较低级别的SQL helpers
函数,然后提交。如果SQL helpers
函数中发生任何错误,那么进行回滚放弃更改。如果我们的函数到达末尾并成功调用,那么提交事务保存更改, defer tx.Rollback()
将不做操作.
一个典型的 helper function
如下
// sqlite/dial.go#L360-L418
func createDial(ctx context.Context, tx *Tx, dial *wtf.Dial) error
helper
函数是通用性的函数,它们不附加到特定的服务。因此它们可以被不同的服务使用,甚至可以被其他helper
函数调用。这是一种封装低级SQL调用,提供抽象的高级函数的简单方法。
实现实体查询
我们的 wtf.DialService
包中定义了 FindDials()
一个用于搜索Dial
的接口。
// dial.go#L81-L122
package wtf
type DialService interface {
FindDials(ctx context.Context, filter DialFilter) ([]*Dial, int, error)
...
}
服务具体实现
这个接口的具体实现在我们的包sqlite
中:
// sqlite/dial.go#L47-L73
func (s *DialService) FindDials(ctx context.Context, filter wtf.DialFilter) ([]*wtf.Dial, int, error) {
tx, err := s.db.BeginTx(ctx, nil)
if err != nil {
return nil, 0, err
}
defer tx.Rollback()
// 调用helper类
// Fetch list of matching dial objects.
dials, n, err := findDials(ctx, tx, filter)
if err != nil {
return dials, n, err
}
// Iterate over dials and attach associated owner user.
// This should be batched up if using a remote database server.
for _, dial := range dials {
if err := attachDialAssociations(ctx, tx, dial); err != nil {
return dials, n, err
}
}
return dials, n, nil
}
从较高的层次来看,它执行三个步骤:
- 建立业务边界。
- 查找匹配的
dial
。 - 查找每个
dial
的关联数据(例如dial
所有者)。
Helper methods
我们有一个用于搜索的helper方法,因为我们希望将其用于其他服务。例如,在查找用户时,我们可能还希望返回该用户的dial
列表。
我们的helper方法从较高的层次来看如下:
// sqlite/dial.go#L292-L358
// helper 方法, 和上文中的方法不一样,不属于DialService结构体
func findDials(ctx context.Context, tx *Tx, filter wtf.DialFilter) ([]*wtf.Dial, int, error) {
// Build WHERE clause...
// Execute query.
rows, err := tx.QueryContext(ctx, `SELECT ...`)
if err != nil {
return nil, n, err
}
defer rows.Close()
// Iterate over rows and deserialize into Dial objects.
dials := make([]*wtf.Dial, 0)
for rows.Next() {
var dial wtf.Dial
if rows.Scan(&dial.ID, ...); err != nil {
return nil, 0, err
}
dials = append(dials, &dial)
}
return dials, n, rows.Err()
}
必要的,我们尝试将SQL字段转换为应用程序域对象。wtf.Dial
中有几个有趣的细节。
SQL构建器与手写SQL
即使您不使用ORM,也可以使用Squirrel
之类的工具来构建SQL语句。我个人并没有用Squirrel
,但它看起来可能是一个很好的选择。然而,对于我的大多数查询,我发现手动书写SQL也是足够使用的。
查询条件通常是SQL
查询语句变化最多的部分,因此我们将重点讨论查询条件的生成。我们将一系列的过滤字段组合成程序中的一个业务模型(结构体)。我们通过where
wtf.DialFilter
AND
来构建一个添加数组
// sqlite/dial.go#L295-L311
// List of WHERE clause predicates. Start with an always true predicate
// so we have at least one when joining later.
where := []string{"1 = 1"}
// Bind parameters used by the WHERE clause.
var args []interface{}{}
// Add predicate for ID if specified on the filter.
if v := filter.ID; v != nil {
where = append(where, "id = ?")
args = append(args, *v)
}
然后我们可以使用strings.Join()
构建查询
whereClause := strings.Join(where, " AND ")
q := `SELECT ... FROM dials WHERE ` + whereClause
如果我们没有任何过滤字段,那么我们的查询语句就会匹配所有行。如果我们加上我们的ID过滤字段,它将会过滤ID。没有过滤字段的时候,我们添加了 "1 = 1"
额外的语句, 然而SQL语句优化器足够聪明,可以忽略一个始终为真的条件,例如: WHERE 1=1
WHERE 1=1 AND id=?1 = 1
统计行的总数
我们的服务接口要求返回用于分页的匹配dial
的总数。例如, 接口值返回100个匹配dial
中的20个,那么前端想知道匹配dial
的总数,这样他们就可以显示共有5页结果.
最简单的方法是先执行带有Limit的查询,获得部分结果,然后重新执行不带Limit的查询。然而,有一些SQL技巧可以让您将这些查询合并到一次查询中.
许多数据库(如SQLite或Postgres)提供了窗口功能。我们可以指定一个窗值口作为查询字段之一,它将忽略 OFFSET
LIMIT
注: 窗口功能详细参考该文章 SQL窗口函数
SELECT id, name, COUNT(*) OVER()
FROM dials
WHERE user_id = ?
OFFSET 40 LIMIT 20
// 计算原始查询中的总匹配计数
该字段返回空窗口上查询的总计数。它将为我们查询的每一行返回相同的值,这样我们就可以简单地在每一行的迭代中获取改值。
实现实体查找
实体查找这是一个非常常见的任务,因此我发现创建一个单独的函数 FindDials()
FindDialByID()
非常有用。
// dial.go#L81-L122
package wtf
type DialService interface {
FindDial(ctx context.Context, id int) (*Dial, error)
...
}
接口实现
我们的接口实现和前面的接口实现累屎类似,只是我们现在正在 查询单个dial
。
sqlite/dial.go#L26-L45
func (s *DialService) FindDialByID(ctx context.Context, id int) (*wtf.Dial, error) {
tx, err := s.db.BeginTx(ctx, nil)
if err != nil {
return nil, err
}
defer tx.Rollback()
// Fetch dial object and attach owner user.
dial, err := findDialByID(ctx, tx, id)
if err != nil {
return nil, err
} else if err := attachDialAssociations(ctx, tx, dial); err != nil {
return nil, err
}
return dial, nil
}
Helper 函数
我们的底层实际上包装了函数findDials()
,但它改变了语义,由查询多个变单个。
// sqlite/dial.go#L265-L275
// findDialByID is a helper function to retrieve a dial by ID.
// Returns ENOTFOUND if dial doesn't exist.
func findDialByID(ctx context.Context, tx *Tx, id int) (*wtf.Dial, error) {
dials, _, err := findDials(ctx, tx, wtf.DialFilter{ID: &id})
if err != nil {
return nil, err
} else if len(dials) == 0 {
return nil, &wtf.Error{Code: wtf.ENOTFOUND, Message: "Dial not found."}
}
return dials[0], nil
}
如果找不到数据,则会返回错误,函数希望给定的拨号存在。在这里,我们将一个空的结果集转换为 “not found” 错误。
您可以在Failure is Your Domain中找到有关使用于应用程序特定类型的详细信息。我将很快编写一个更新版本,其中包括 go1.13 错误包装,但现在大多数概念仍然有效正确。
优化主键查找
创建一个单独的函数来优化按ID查找拨dial
,而不是重用源代码,这可能会很有用。它允许您跳过片分配并优化查询字段列表,以包含相关的对象数据,例如该表的用户信息。
但是,复制查询代码会导致存在两处相似代码,会增加维护的难度。最好等到在代码中找到需要优化的路径,然后只对它们进行优化。
结论
在本文中,我们研究了如何实现业务模型服务接口的读取端,并将高级接口与低级SQL助手函数分开。我们还研究了如何通过与SQL窗口函数集成来优化分页查询。在下一篇文章中,我们将研究如何实现业务模型服务的写接口。
如果不小心的话,SQL很容易渗透到整个应用程序中。通过将数据库代码隔离到单个包中,我们可以确保应用程序代码的其余部分可以干净且独立。
如果您对本文有问题,评论,或建议,请访问GitHub Discussion board,这是一个Github讨论版,使用它可以进行更方便的交流。