Golang简单强大的SQL数据库操作插件库KSQL的使用

KSQL是一个为Golang设计的简单而强大的SQL数据库操作库，旨在提供易于使用且功能丰富的API来与SQL数据库交互。

主要特点

• 每个操作只返回一次错误，便于错误处理
• 提供日常操作的辅助函数：Insert、Patch和Delete
• 通用且强大的查询和扫描数据到结构体的功能
• 基于成熟的库如database/sql和pgx构建
• 支持sql.Scanner和sql.Valuer接口
• 支持pgx的特殊类型（使用kpgx时）

快速入门示例

package main

import (
	"context"
	"errors"
	"fmt"
	"log"

	"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"`
	Posts []Post
}

// Post与User是多对一关系
var PostsTable = ksql.NewTable("posts", "post_id")

type Post struct {
	ID     int    `ksql:"post_id"`
	UserID int    `ksql:"user_id"`
	Title  string `ksql:"title"`
	Text   string `ksql:"text"`
}

// Address与User是一对一关系
var AddressesTable = ksql.NewTable("addresses", "id")

type Address struct {
	ID       int    `ksql:"id"`
	UserID   int    `ksql:"user_id"`
	FullAddr string `ksql:"full_addr"`
}

func main() {
	ctx := context.Background()
	dbURL, closeDB := startExampleDB(ctx)
	defer closeDB()

	db, err := kpgx.New(ctx, dbURL, ksql.Config{})
	if err != nil {
		log.Fatalf("unable connect to database: %s", err)
	}
	defer db.Close()

	// 查询部分属性可以使用自定义结构体
	var count []struct {
		Count int    `ksql:"count"`
		Type  string `ksql:"type"`
	}
	err = db.Query(ctx, &count, "SELECT type, count(*) as count FROM users GROUP BY type")
	if err != nil {
		log.Fatalf("unable to query users: %s", err)
	}

	fmt.Println("number of users by type:", count)

	// 从数据库加载实体时，如果省略SELECT部分，KSQL会自动构建
	var adminUsers []User
	err = db.Query(ctx, &adminUsers, "FROM users WHERE type = $1", "admin")
	if err != nil {
		log.Fatalf("unable to query admin users: %s", err)
	}

	fmt.Println("admin users:", adminUsers)

	// 加载用户及其帖子的好方法
	var user User
	err = errors.Join(
		db.QueryOne(ctx, &user, "FROM users WHERE user_id = $1", 42),
		db.Query(ctx, &user.Posts, "FROM posts WHERE user_id = $1", user.ID),
	)
	if err != nil {
		log.Fatalf("unable to query users: %s", err)
	}

	fmt.Println("user with posts:", user)

	// 从连接表中检索数据
	var rows []struct {
		OneUser    User    `tablename:"users"`
		OneAddress Address `tablename:"addr"`
	}
	err = db.Query(ctx, &rows,
		`FROM users
		JOIN addresses addr
		  ON users.user_id = addr.user_id`,
	)
	if err != nil {
		log.Fatalf("unable to query users: %s", err)
	}

	fmt.Println("rows of joined tables:", rows)
}

CRUD操作示例

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"`

	// 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()

	// 创建表
	_, 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.Delete(ctx, UsersTable, alison.ID)
	if err != nil {
		panic(err.Error())
	}

	// 查询单条记录
	var cris User
	err = db.QueryOne(ctx, &cris, "FROM users WHERE name = ? ORDER BY id", "Cristina")
	if err != nil {
		panic(err.Error())
	}

	// 更新记录
	cris.Name = "Cris"
	err = db.Patch(ctx, UsersTable, cris)

	// 部分更新技术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())
	}

	// 查询多条记录
	var users []User
	err = db.Query(ctx, &users, "FROM users LIMIT 10")
	if err != nil {
		panic(err.Error())
	}

	// 事务处理
	err = db.Transaction(ctx, func(db ksql.Provider) error {
		var cris2 User
		err = db.QueryOne(ctx, &cris2, "FROM users WHERE id = ?", cris.ID)
		if err != nil {
			return err
		}

		err = db.Patch(ctx, UsersTable, PartialUpdateUser{
			ID:  cris2.ID,
			Age: nullable.Int(29),
		})
		if err != nil {
			panic(err.Error())
		}

		return nil
	})
	if err != nil {
		panic(err.Error())
	}
}

支持的适配器

KSQL支持多种数据库适配器：

1. Postgres (使用pgx v4):

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

2. Postgres (使用pgx v5):

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

3. MySQL:

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

4. SQLServer:

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

5. SQLite3 (基于CGO):

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

6. SQLite (无CGO):

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

KSQL提供了简单而强大的API来操作SQL数据库，通过结构体标签和辅助方法简化了常见的数据库操作，同时保持了良好的性能。