[翻译]Real-world SQL in Go: PartI

2021-01-27

实现服务接口
封装数据库
从SQL helpers 中分离接口实现
实现实体查询
1. 服务具体实现
2. Helper methods
SQL构建器与手写SQL
统计行的总数
实现实体查找
结论

原文由 Ben Johnson 发表在 gobeyond，推荐观看原文文章链接

不管您是不是讨厌还是接受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讨论版，使用它可以进行更方便的交流。

本文作者: Nick Yang

本文链接: https://stoneqi.github.io/2021/01/27/Real-world-SQL-in-Go-Part-I/