sqlex

sqlex

简介

sqlex 是一个用于简化 SQL 操作的工具库，是对标准库中database/sql的增强，主要提供两个能力：

1. SQL语句的拼接
2. ORM功能

安装

go get cnb.cool/yangtzecoder/sqlex

用法

package main

import (
	"database/sql"
	"fmt"

	"cnb.cool/yangtzecoder/sqlex"
	_ "modernc.org/sqlite"
)

type User struct {
	ID   int
	Name string
	Age  int
}

func main() {
	db, _ := sql.Open("sqlite", ":memory:")
	defer db.Close()

	var id int
	var ids []int

	var user User
	var users []User

	ex := sqlex.Builder().WithDB(db).WithColumnNameMapper(sqlex.CamelCaseToSnakeCaseColumnName).Build()

	ex.ExecRaw("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
	ex.ExecRaw("INSERT INTO users (name, age) VALUES (?, ?)", "John", 30)
	ex.ExecRaw("INSERT INTO users (name, age) VALUES (?, ?)", "Jane", 25)

	var sb sqlex.SqlBuilder

	sb = ex.NewSqlBuilder().Append("SELECT id FROM users WHERE name = ?", "John")
	ex.Get(&id, sb)
	fmt.Println("id:", id) // id: 1

	sb = ex.NewSqlBuilder().Append("SELECT id FROM users")
	ex.GetMany(&ids, sb)
	fmt.Println("ids:", ids) // ids: [1 2]

	sb = ex.NewSqlBuilder().Append("SELECT * FROM users WHERE name = ?", "John")
	ex.QueryOne(&user, sb)
	fmt.Println("user:", user) // user: {1 John 30}

	sb = ex.NewSqlBuilder().Append("SELECT * FROM users")
	ex.Query(&users, sb)
	fmt.Println("users:", users) // users: [{1 John 30} {2 Jane 25}]

	sb = ex.NewSqlBuilder().SelectFrom("select * from users").Where("name = ?", "John")
	ex.Query(&users, sb)
	fmt.Println("users:", users) // users: [{1 John 30}]

	sb = ex.NewSqlBuilder().SelectFrom("select * from users").Where("name = ?", "John").Where("age = ?", 30)
	ex.Query(&users, sb)
	fmt.Println("users:", users) // users: [{1 John 30}]

	sb = ex.NewSqlBuilder().SelectFrom("select * from users").Where("name = ?", "John").WhereOr("name = ?", "Jane")
	ex.Query(&users, sb)
	fmt.Println("users:", users) // users: [{1 John 30} {2 Jane 25}]

	sb = ex.NewSqlBuilder().SelectFrom("select * from users").WhereIn("id", 1, 2)
	ex.Query(&users, sb)
	fmt.Println("users:", users) // users: [{1 John 30} {2 Jane 25}]

	sb = ex.NewSqlBuilder().SelectFrom("select * from users").WhereNotIn("id", []int{2, 3})
	ex.Query(&users, sb)
	fmt.Println("users:", users) // users: [{1 John 30}]

	sb = ex.NewSqlBuilder().SelectFrom("select * from users").Where("id in").In(1, 2)
	ex.Query(&users, sb)
	fmt.Println("users:", users) // users: [{1 John 30} {2 Jane 25}]

	sb = ex.NewSqlBuilder().SelectFrom("select * from users").Where("id in").In([]int{1, 2})
	ex.Query(&users, sb)
	fmt.Println("users:", users) // users: [{1 John 30} {2 Jane 25}]

	sb = ex.NewSqlBuilder().SelectFrom("select * from users").Where("id not in").In(2, 3)
	ex.Query(&users, sb)
	fmt.Println("users:", users) // users: [{1 John 30}]
}

版权声明

本项目基于 MIT 协议开源。