KSQL - 保持简单的SQL库

KSQL的创建目的是为Golang提供一个真正简单且令人满意的SQL数据库交互工具。

KSQL的核心目标不是提供其他库没有的新功能(尽管我们确实有一些)，而是提供一个经过深思熟虑和精心设计的API，让用户更容易学习、调试和避免常见陷阱。

KSQL还与其后端解耦，因此与数据库的实际通信是由知名且值得信赖的技术(即pgx和database/sql)执行的。在某些情况下，你甚至可以为KSQL创建自己的后端适配器。

在本README中，你将找到库的"入门"示例，对于更高级的用例，请阅读我们的Wiki。

突出特点

• 每个操作只返回一次错误，因此更容易处理它们
• 提供日常操作的辅助函数，即：插入、补丁和删除
• 用于查询和将数据扫描到结构体的通用和强大的函数
• 基于现有的经过实战检验的库，如database/sql和pgx
• 支持sql.Scanner和sql.Valuer以及所有pgx特殊类型(使用kpgx时)
• 还有许多其他旨在简化你的工作的功能

让我们从一些代码开始：

以下简短示例是一个TLDR版本，用于说明使用KSQL有多容易。

你将在下面的章节中找到更完整的示例。

package main

import (
	"context"
	"fmt"
	"log"
	"os"

	"github.com/vingarcia/ksql"
	"github.com/vingarcia/ksql/adapters/kpgx"
)

var UsersTable = ksql.NewTable("users", "user_id")

type User struct {
	ID   int    `ksql:"user_id"`
	Name string `ksql:"name"`
	Type string `ksql:"type"`
}

func main() {
	ctx := context.Background()
	db, err := kpgx.New(ctx, os.Getenv("POSTGRES_URL"), ksql.Config{})
	if err != nil {
		log.Fatalf("无法连接到数据库：%s", err)
	}
	defer db.Close()

	// 要只查询某些属性，你可以
	// 创建一个自定义结构体，像这样：
	var count []struct {
		Count string `ksql:"count"`
		Type string `ksql:"type"`
	}
	err = db.Query(ctx, &count, "SELECT type, count(*) as count FROM users WHERE type = $1 GROUP BY type", "admin")
	if err != nil {
		log.Fatalf("无法查询用户：%s", err)
	}

	fmt.Println("按类型的用户数量：", count)

	// 对于从数据库加载实体，如果你省略SELECT部分，KSQL可以为你构建查询，像这样：
	var users []User
	err = db.Query(ctx, &users, "FROM users WHERE type = $1", "admin")
	if err != nil {
		log.Fatalf("无法查询用户：%s", err)
	}

	fmt.Println("用户：", users)
}

支持的适配器：

我们支持几种不同的适配器，其中一个在上面有说明(kpgx)，其他适配器有完全相同的签名，但适用于不同的数据库或驱动程序版本，它们是：

• kpgx.New(ctx, os.Getenv("DATABASE_URL"), ksql.Config{}) 用于Postgres，它基于pgxpool和pgx版本4，通过以下命令下载：

  go get github.com/vingarcia/ksql/adapters/kpgx

• kpgx5.New(ctx, os.Getenv("DATABASE_URL"), ksql.Config{}) 用于Postgres，它基于pgxpool和pgx版本5，通过以下命令下载：

  go get github.com/vingarcia/ksql/adapters/kpgx5

• kmysql.New(ctx, os.Getenv("DATABASE_URL"), ksql.Config{}) 用于MySQL，它基于database/sql，通过以下命令下载：

  go get github.com/vingarcia/ksql/adapters/kmysql

• ksqlserver.New(ctx, os.Getenv("DATABASE_URL"), ksql.Config{}) 用于SQLServer，它基于database/sql，通过以下命令下载：

  go get github.com/vingarcia/ksql/adapters/ksqlserver

• ksqlite3.New(ctx, os.Getenv("DATBAASE_PATH"), ksql.Config{}) 用于SQLite3，它基于database/sql和mattn/go-sqlite3，依赖CGO，通过以下命令下载：

  go get github.com/vingarcia/ksql/adapters/ksqlite3

• ksqlite.New(ctx, os.Getenv("DATABASE_PATH"), ksql.Config{}) 用于SQLite，它基于database/sql和modernc.org/sqlite，不需要CGO，通过以下命令下载：

  go get github.com/vingarcia/ksql/adapters/modernc-ksqlite

更详细的示例请参见：

• ./examples/all_adapters/all_adapters.go

KSQL接口

当前接口包含用户预期使用的方法，也用于在需要时轻松模拟整个库。

该接口在项目中声明为ksql.Provider，如下所示。

我们计划保持它非常简单，只有少数经过深思熟虑的函数涵盖所有用例，所以不要期待太多添加：

// Provider描述KSQL的公共行为
//
// Insert、Patch、Delete和QueryOne函数在未找到记录或操作期间未更改任何行时返回`ksql.ErrRecordNotFound`。
type Provider interface {
	Insert(ctx context.Context, table Table, record interface{}) error
	Patch(ctx context.Context, table Table, record interface{}) error
	Delete(ctx context.Context, table Table, idOrRecord interface{}) error

	Query(ctx context.Context, records interface{}, query string, params ...interface{}) error
	QueryOne(ctx context.Context, record interface{}, query string, params ...interface{}) error
	QueryChunks(ctx context.Context, parser ChunkParser) error

	Exec(ctx context.Context, query string, params ...interface{}) (Result, error)
	Transaction(ctx context.Context, fn func(Provider) error) error
}

使用KSQL

在下面的示例中，我们将涵盖所有最常见的用例，例如：

1. 插入记录
2. 更新记录
3. 删除记录
4. 查询一条或多条记录
5. 进行事务

更高级的用例在我们的Wiki中有单独的页面说明：

• 对大型查询使用分块查询
• 使用Select Builder避免代码重复
• 在带有JOINs的查询中重用现有结构体
• 测试工具和ksql.Mock

对于更常见的用例，请阅读下面的示例，该示例也可在这里获得，如果你想自己编译。

package main

import (
	"context"
	"fmt"
	"time"

	"github.com/vingarcia/ksql"
	"github.com/vingarcia/ksql/adapters/ksqlite3"
	"github.com/vingarcia/ksql/nullable"
)

type User struct {
	ID   int    `ksql:"id"`
	Name string `ksql:"name"`
	Age  int    `ksql:"age"`

	// 以下属性使用了KSQL修饰符，
	// 你可以在我们的Wiki上找到更多相关信息：
	//
	// - https://github.com/VinGarcia/ksql/wiki/Modifiers
	//

	// `json`修饰符将地址作为JSON保存在数据库中
	Address Address `ksql:"address,json"`

	// timeNowUTC修饰符将在保存之前将此字段设置为`time.Now().UTC()`：
	UpdatedAt time.Time `ksql:"updated_at,timeNowUTC"`

	// timeNowUTC/skipUpdates修饰符将仅在首次创建时将此字段设置为`time.Now().UTC()`，
	// 并在更新期间忽略它。
	CreatedAt time.Time `ksql:"created_at,timeNowUTC/skipUpdates"`
}

type PartialUpdateUser struct {
	ID      int      `ksql:"id"`
	Name    *string  `ksql:"name"`
	Age     *int     `ksql:"age"`
	Address *Address `ksql:"address,json"`
}

type Address struct {
	State string `json:"state"`
	City  string `json:"city"`
}

// UsersTable告诉KSQL表名，并且可以使用默认的主键列名："id"
var UsersTable = ksql.NewTable("users")

func main() {
	ctx := context.Background()

	// 在这个例子中，我们将使用sqlite3：
	db, err := ksqlite3.New(ctx, "/tmp/hello.sqlite", ksql.Config{
		MaxOpenConns: 1,
	})
	if err != nil {
		panic(err.Error())
	}
	defer db.Close()

	// 在下面的定义中，请注意BLOB是
	// 我们在sqlite中可以用于存储JSON的唯一类型。
	_, err = db.Exec(ctx, `CREATE TABLE IF NOT EXISTS users (
	  id INTEGER PRIMARY KEY,
		age INTEGER,
		name TEXT,
		address BLOB,
		created_at DATETIME,
		updated_at DATETIME
	)`)
	if err != nil {
		panic(err.Error())
	}

	var alison = User{
		Name: "Alison",
		Age:  22,
		Address: Address{
			State: "MG",
		},
	}
	err = db.Insert(ctx, UsersTable, &alison)
	if err != nil {
		panic(err.Error())
	}
	fmt.Println("Alison ID:", alison.ID)

	// 内联插入：
	err = db.Insert(ctx, UsersTable, &User{
		Name: "Cristina",
		Age:  27,
		Address: Address{
			State: "SP",
		},
	})
	if err != nil {
		panic(err.Error())
	}

	// 删除Alison：
	err = db.Delete(ctx, UsersTable, alison.ID)
	if err != nil {
		panic(err.Error())
	}

	// 检索Cristina，注意如果你省略查询的SELECT部分，
	// KSQL将根据结构体的字段为你高效地构建它：
	var cris User
	err = db.QueryOne(ctx, &cris, "FROM users WHERE name = ? ORDER BY id", "Cristina")
	if err != nil {
		panic(err.Error())
	}
	fmt.Printf("Cristina: %#v\n", cris)

	// 更新Cristina的所有字段：
	cris.Name = "Cris"
	err = db.Patch(ctx, UsersTable, cris)

	// 只更改Cristina的年龄，不触及其他字段：

	// 部分更新技巧1：
	err = db.Patch(ctx, UsersTable, struct {
		ID  int `ksql:"id"`
		Age int `ksql:"age"`
	}{ID: cris.ID, Age: 28})
	if err != nil {
		panic(err.Error())
	}

	// 部分更新技巧2：
	err = db.Patch(ctx, UsersTable, PartialUpdateUser{
		ID:  cris.ID,
		Age: nullable.Int(28), // (只是一个指向int的指针，如果为null则不会更新)
	})
	if err != nil {
		panic(err.Error())
	}

	// 列出数据库中的前10个用户
	// (每次运行此示例时都会创建一个新的Cristina)
	//
	// 注意：使用此函数时建议设置LIMIT，因为
	
> 值得注意的是,KSQL 仅在使用 postgres 时缓存预处理语句,因为这是由 `pgx` 执行的,这意味着当使用 MySQL、SQLServer 或 SQLite 时,如果你还计划使用预处理语句,其他库如 `sqlx` 将比 KSQL 快得多。

> 我们正在努力为这些其他数据库添加缓存预处理语句的支持。

### 基准测试结果

要理解下面的基准测试,你必须知道所有测试都是使用 Postgres 12.1 进行的,并且我们比较了以下工具:

- 使用封装 `database/sql` 的适配器的 KSQL
- 使用封装 `pgx` 的适配器的 KSQL  
- `database/sql`
- `sqlx`
- `pgx` (使用 `pgxpool`)
- `gorm`
- `sqlc`
- `sqlboiler`

对于每个工具,我们运行 3 种不同的查询:

`insert-one` 查询如下:

`INSERT INTO users (name, age) VALUES ($1, $2) RETURNING id`

`single-row` 查询如下:

`SELECT id, name, age FROM users OFFSET $1 LIMIT 1`

`multiple-rows` 查询如下:

`SELECT id, name, age FROM users OFFSET $1 LIMIT 10`

请记住,一些测试的工具(如 GORM)实际上在内部构建查询,因此用于基准测试的实际代码可能与上面的示例略有不同。

不多说了,以下是结果:

```bash
$ make bench TIME=5s
sqlc generate
go test -bench=. -benchtime=5s
goos: linux
goarch: amd64
pkg: github.com/vingarcia/ksql/benchmarks
cpu: Intel(R) Core(TM) i7-10750H CPU @ 2.60GHz
BenchmarkInsert/ksql/sql-adapter/insert-one-12         	    9711	    618727 ns/op
BenchmarkInsert/ksql/pgx-adapter/insert-one-12         	   10000	    555967 ns/op
BenchmarkInsert/sql/insert-one-12                      	    9450	    624334 ns/op
BenchmarkInsert/sql/prep-stmt/insert-one-12            	   10000	    555119 ns/op
BenchmarkInsert/sqlx/insert-one-12                     	    9552	    632986 ns/op
BenchmarkInsert/sqlx/prep-stmt/insert-one-12           	   10000	    560244 ns/op
BenchmarkInsert/pgxpool/insert-one-12                  	   10000	    553535 ns/op
BenchmarkInsert/gorm/insert-one-12                     	    9231	    668423 ns/op
BenchmarkInsert/sqlc/insert-one-12                     	    9589	    632277 ns/op
BenchmarkInsert/sqlc/prep-stmt/insert-one-12           	   10803	    560301 ns/op
BenchmarkInsert/sqlboiler/insert-one-12                	    9790	    631464 ns/op
BenchmarkQuery/ksql/sql-adapter/single-row-12          	   44436	    131191 ns/op
BenchmarkQuery/ksql/sql-adapter/multiple-rows-12       	   42087	    143795 ns/op
BenchmarkQuery/ksql/pgx-adapter/single-row-12          	   86192	     65447 ns/op
BenchmarkQuery/ksql/pgx-adapter/multiple-rows-12       	   74106	     79004 ns/op
BenchmarkQuery/sql/single-row-12                       	   44719	    134491 ns/op
BenchmarkQuery/sql/multiple-rows-12                    	   43218	    138309 ns/op
BenchmarkQuery/sql/prep-stmt/single-row-12             	   89328	     64162 ns/op
BenchmarkQuery/sql/prep-stmt/multiple-rows-12          	   84282	     71454 ns/op
BenchmarkQuery/sqlx/single-row-12                      	   44118	    132928 ns/op
BenchmarkQuery/sqlx/multiple-rows-12                   	   43824	    137235 ns/op
BenchmarkQuery/sqlx/prep-stmt/single-row-12            	   87570	     66610 ns/op
BenchmarkQuery/sqlx/prep-stmt/multiple-rows-12         	   82202	     72660 ns/op
BenchmarkQuery/pgxpool/single-row-12                   	   94034	     63373 ns/op
BenchmarkQuery/pgxpool/multiple-rows-12                	   86275	     70275 ns/op
BenchmarkQuery/gorm/single-row-12                      	   83052	     71539 ns/op
BenchmarkQuery/gorm/multiple-rows-12                   	   62636	     89652 ns/op
BenchmarkQuery/sqlc/single-row-12                      	   44329	    132659 ns/op
BenchmarkQuery/sqlc/multiple-rows-12                   	   44440	    139026 ns/op
BenchmarkQuery/sqlc/prep-stmt/single-row-12            	   91486	     66679 ns/op
BenchmarkQuery/sqlc/prep-stmt/multiple-rows-12         	   78583	     72583 ns/op
BenchmarkQuery/sqlboiler/single-row-12                 	   70030	     87089 ns/op
BenchmarkQuery/sqlboiler/multiple-rows-12              	   69961	     84376 ns/op
PASS
ok  	github.com/vingarcia/ksql/benchmarks	221.596s
基准测试执行时间: 2023-10-22
基准测试执行的提交: 35b6882317e82de7773fb3908332e8ac3d127010

运行 KSQL 测试(针对贡献者)

测试使用 docker-test 来设置所有支持的数据库,这意味着:

• 你需要安装 docker

• 你必须能够不使用 sudo 就运行 docker,即 如果你不是 root 用户,你应该将自己添加到 docker 组,例如:

  $ sudo usermod <your_username> -aG docker

  然后重新启动你的登录会话(或者直接重启)

• 最后,只需运行一次 make pre-download-all-images,这样你的测试就不会 因下载数据库镜像而超时。

之后,你可以通过以下方式运行测试:

make test

待办事项列表

• 添加 Upsert 辅助方法
• 尝试实现自动预处理语句缓存,类似 pgx 的做法
• 更新 ksqltest.FillStructWith 以处理带有 ksql:"..,json" 标记的属性
• 改进错误消息(进行中)
• 完成 kbuilder 包

优化机会

• 测试在字段信息上使用指针是否更快
• 考虑将缓存的 structInfo 作为参数传递给所有使用它的函数, 这样我们就不需要在同一次调用中多次获取它。
• 使用缓存来存储经常使用的查询(像 pgx 一样)
• 在 ksql.NewTable() 中预加载所有方言的插入方法
• 对辅助函数 Update、Insert 和 Delete 使用预处理语句。

可能的 V2 版本功能

• 将 .Transaction(db ksql.Provider) 更改为 .Transaction(ctx context.Context)
• 使 .Query() 方法返回 type Query interface { One(); All(); Chunks(); }
• 有一个 Update() 方法,不像 Patch() 那样忽略 NULL 值进行更新
  • 有一个新的修饰符 skipNullUpdates,使 Update 函数执行 Patch 的工作
  • 移除 Patch 函数
• 将 NewTable() 重命名为 Table(),这样在方便时可以内联声明