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

原文由 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.DialServicesqlitesqlite.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
}

从较高的层次来看,它执行三个步骤:

  1. 建立业务边界。
  2. 查找匹配的 dial
  3. 查找每个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讨论版,使用它可以进行更方便的交流。

not found!