ploto

package module
v0.8.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Apr 24, 2026 License: MIT Imports: 16 Imported by: 0

README

ploto

A go Library for scan database/sql rows to struct、slice、other types, and support SQL logging

extensions to golang's database/sql

功能

  • Scan rows, 支持struct,slice,map,其他基本类型
  • 数据库配置连接管理
  • sql日志
  • Query/QueryContext
  • QueryRow/QueryRowContext
  • Create
  • Update
  • UpdateColumns

说明

ploto 是对 database/sql 的轻量扩展，不引入完整 ORM 的模型管理、关联关系、自动迁移等能力，重点是补足常用的数据读写辅助。

  • 保留 database/sql 的原生使用方式，底层仍然直接基于 *sql.DB / *sql.Tx
  • Query/QueryContext 结果 Scan 支持 *Slice、*Struct、*Map、*int 等基本类型
  • QueryRow/QueryRowContext 结果 Scan 支持 *Struct、*Map、*int 等基本类型，结果为空返回 sql.ErrNoRows
  • Create 支持根据 struct 的 db tag 生成 insert SQL，并在自增主键场景下回填字段
  • Update 支持根据 struct 的 db tag 生成 update SQL，且要求必须传入 WHERE 条件
  • UpdateColumns 支持通过 map[string]any 更新部分字段，且要求必须传入 WHERE 条件
  • Tx 保留和 DB 一致的 Create、Update、UpdateColumns、Query、Exec 能力，并增加 transaction_id 日志

Using

配合多数据库管理一起使用
package main

import (
    "encoding/json"
    "fmt"
    "github.com/yangyin5127/ploto"
     _ "github.com/go-sql-driver/mysql"
)

//Sql日志输出
type MyStdLogger struct {
}

func (m *MyStdLogger) Info(ctx context.Context, format string, v ...interface{}) {
	//...
}
func (m *MyStdLogger) Debug(ctx context.Context, format string, v ...interface{}) {
	//....
}

func (m *MyStdLogger) Warn(ctx context.Context, format string, v ...interface{}) {
	//...
}

func (m *MyStdLogger) Error(ctx context.Context, format string, v ...interface{}) {
	//....
}


func getConfig() (config Configs) {
    testConfig := `{"mysql": {
        "clients": {
            "test":{
                "host": "127.0.0.1",
                "port": 3306,
                "user": "root",
                "password": "root",
                "database": "test"
            }
        },
        "default": {
            "port": 3306,
            "dialect": "mysql",
            "pool": {
                "maxIdleConns": 2,
                "maxLeftTime": 60000, 
                "maxOpenConns": 5
            },
            "dialectOptions": {
                "parseTime":true,
                "multiStatements": true,
                "writeTimeout": "3000ms",
                "readTimeout": "3000ms",
                "timeout":"3000ms",
				"parseTime": true,
				"loc":"Local",
            }   
        }
    }}`

    var conf Configs

    json.Unmarshal([]byte(testConfig), &conf)

    // fmt.Printf("conf %+v", conf)
    return conf

}

type User struct {
    Id          int64  `db:"id"`
    Name        string `db:"name"`
    CreatedTime string `db:"created_time"`
    UpdatedTime string `db:"updated_time"`
}

type Configs struct {
    Mysql ploto.DialectConfig `json:"mysql"`
   // Mssql ploto.DialectConfig `json:"mssql"`
}

func main() {

    configs := getConfig()
	defaultLogger := &MyStdLogger{}

    db, err := ploto.Open(configs.Mysql, defaultLogger)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    
    var users []User
    err = db.Use("test").Query("select * from users where id<100").Scan(&users)
    if err != nil {
        panic(err)
    }
    fmt.Printf("users %+v", users)

	//Exec....
	result, err := db.Use("test").Exec("update users set name=? where  id=?","xxx",1)
    if err != nil {
		//...
        panic(err)
    }
	
	
	//Exec....
	result, err := db.Use("test").Exec("insert uesrs(name,created_time) values(?,now())","xxx")
    if err != nil {
		//...
        panic(err)
    }

	//Create....
	type CreateUser struct {
		UserID      int64  `db:"user_id,primary,auto"` // db:"column_name[,primary][,auto]"
		Name        string `db:"name"`
		CreatedTime string `db:"created_time"`
	}

	newUser := &CreateUser{
		Name:        "xxx",
		CreatedTime: "2024-01-01 00:00:00",
	}

	_, err = db.Use("test").Create("users", newUser)
	if err != nil {
		panic(err)
	}

	fmt.Printf("new user id=%d\n", newUser.UserID)

	//Update....
	newUser.Name = "yyy"
	_, err = db.Use("test").Update("users", newUser, "user_id=?", newUser.UserID)
	if err != nil {
		panic(err)
	}

	//UpdateColumns....
	_, err = db.Use("test").UpdateColumns("users", map[string]any{
		"name": "zzz",
	}, "user_id=?", newUser.UserID)
	if err != nil {
		panic(err)
	}

}
Create 约定
  • 仅处理带 db tag 的导出字段，db:"-" 会忽略
  • db tag 第一个值是列名，例如 db:"user_id"
  • 可选项 primary/pk 标记主键，auto 标记自增
  • 自增主键字段在零值时会自动从 insert 字段里排除，create 成功后回填到对象
  • 如果列名是 id，即使不写 primary,auto 也会按自增主键兼容处理
  • 主键列名不要求是 id，例如 db:"account_no,primary,auto" 也支持
Update 约定
  • 仅处理带 db tag 的导出字段
  • where 条件必填，空字符串会直接返回错误，防止更新全表
  • 主键字段不会进入 SET
  • db:"id" 会按主键兼容处理，不会被更新
type User struct {
	ID   int64  `db:"id"`
	Name string `db:"name"`
	Age  int    `db:"age"`
}

user := &User{
	ID:   1,
	Name: "alice",
	Age:  20,
}

_, err := db.Update("users", user, "id=?", user.ID)

生成 SQL 类似：

UPDATE `users` SET `name`=?,`age`=? WHERE id=?
UpdateColumns 约定
  • 适合局部字段更新，参数为 map[string]any
  • map 的 key 直接作为数据库列名使用，不走 db tag 映射
  • where 条件必填，空字符串会直接返回错误，防止更新全表
  • columns 为空时会直接返回错误
  • 为了保证 SQL 稳定，更新列会按 key 排序生成
_, err := db.UpdateColumns("users", map[string]any{
	"name": "alice",
	"age":  20,
}, "id=?", 1)

生成 SQL 类似：

UPDATE `users` SET `age`=?,`name`=? WHERE id=?
只用Scan功能

支持对rows结果转化到struct,slice，int等

struct定义字段tag为db

type User struct {
    Id          int64  `db:"id"`
    Name        string `db:"name"`
    CreatedTime string `db:"created_time"`
    UpdatedTime string `db:"updated_time"`
}


package main

import (
	"database/sql"
	"fmt"
	"github.com/yangyin5127/ploto"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "user:password@/database")
	if err != nil {
		panic(err.Error()) // Just for example purpose. You should use proper error handling instead of panic
	}
	defer db.Close()

	//scan rows to slices
	var users []User
	rows, err = db.Query("select * from users where id<100")
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	for rows.Next() {
		var user User
		err := ploto.Scan(rows, &user)
		users = append(users, user)
	}


	//ScanResult等同上代码
	var users []User
	rows, err = db.Query("select * from users where id<100")
	if err != nil {
		panic(err)
	}

	//No need to Close
	err := ploto.ScanResult(rows, &users)

	//.....
	// select count(1) as cnt from users

	if rows.Next() {
		var a int64
		ploto.Scan(rows,&a)
	}
	//.....

	// select * from users where id=1

	if rows.Next() {
		var user User 
		ploto.Scan(rows,&user)
	}
	//.....
}

数据库配置

配置支持多数据库连接，格式如下：

mysql
{"mysql": {
		"clients": {
			"test":{
				"host": "127.0.0.1",
				"port": 3307,
				"user": "test",
				"password": "asfasdf@#sddfsdf",
				"database": "test"
			}
		},
		"default": {
			"port": 3306,
			"dialect": "mysql",
			"pool": {
				"maxIdleConns": 2,
				"maxLeftTime": 60000, 
				"maxOpenConns": 5
			},
			"dialectOptions": {
				"parseTime":"true",
				"multiStatements": "true",
				"writeTimeout": "3000ms",
				"readTimeout": "3000ms",
				"timeout":"3000ms",
				"parseTime": "true",
				"loc":"Local",

			}	
		}
	}}

更多dialectOptions参数见: https://github.com/go-sql-driver/mysql#parameters

mssql
{"mssql": {
		"clients": {
	 
			"test":{
				"host": "127.0.0.1",
				"user": "sa",
				"password": "test123",
				"database": "test",
				"pool": {
					"maxIdleConns": 20,
					"maxLeftTime": 60000,
					"maxOpenConns": 50
				},
				"dialectOptions": {
					"dial timeout": "10"

				}
			}
		},
		"default": {
			"port": 1433,
			"dialect": "sqlserver", //or mssql
			"pool": {
				"maxIdleConns": 2,
				"maxLeftTime": 60000,
				"maxOpenConns": 5
			},
			"dialectOptions": {
				"dial timeout": "3"
			}
		}
	}}

更多dialectOptions 参数见:https://github.com/denisenkom/go-mssqldb#connection-parameters-and-dsn

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func Scan 

func Scan(rows *sql.Rows, dest interface{}) error

Scan

func ScanResult 

func ScanResult(rows *sql.Rows, dest interface{}) error

func ScanSlice 

func ScanSlice(rows *sql.Rows, dest interface{}) error

Types

type DB 

type DB struct {
	*sql.DB
	LogSql bool
	// contains filtered or unexported fields
}

func (*DB) Begin 

func (db *DB) Begin() (*Tx, error)

Begin starts a transaction. The default isolation level is dependent on the driver.

func (*DB) BeginTx 

func (db *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error)

BeginTx starts a transaction.

The provided context is used until the transaction is committed or rolled back. If the context is canceled, the sql package will roll back the transaction. Tx.Commit will return an error if the context provided to BeginTx is canceled.

The provided TxOptions is optional and may be nil if defaults should be used. If a non-default isolation level is used that the driver doesn't support, an error will be returned.

func (*DB) Create added in v0.8.0 

func (db *DB) Create(table string, value any) (sql.Result, error)

Create inserts value into table using db tags on exported struct fields.

Example: 

type User struct {
	ID   int64  `db:"id"`
	Name string `db:"name"`
}

user := &User{Name: "alice"}
_, err := db.Create("users", user)

func (*DB) CreateContext added in v0.8.0 

func (db *DB) CreateContext(ctx context.Context, table string, value any) (sql.Result, error)

CreateContext inserts value into table using db tags on exported struct fields.

Example: 

ctx := context.Background()
_, err := db.CreateContext(ctx, "users", &User{Name: "alice"})

func (*DB) Exec 

func (db *DB) Exec(query string, args ...interface{}) (sql.Result, error)

Exec executes a query without returning any rows. The args are for any placeholder parameters in the query

func (*DB) ExecContext 

func (db *DB) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)

ExecContext executes a query without returning any rows. The args are for any placeholder parameters in the query.

func (*DB) Query 

func (db *DB) Query(query string, args ...interface{}) *RowsResult

Query executes a query that returns RowsResult, typically a SELECT. The args are for any placeholder parameters in the query.

func (*DB) QueryContext 

func (db *DB) QueryContext(ctx context.Context, query string, args ...interface{}) *RowsResult

QueryContext executes a query that returns RowsResult, typically a SELECT. The args are for any placeholder parameters in the query.

func (*DB) QueryRow 

func (db *DB) QueryRow(query string, args ...interface{}) *RowResult

QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

func (*DB) QueryRowContext 

func (db *DB) QueryRowContext(ctx context.Context, query string, args ...interface{}) *RowResult

QueryRowContext executes a query that is expected to return at most one row. QueryRowContext always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

func (*DB) RawDB 

func (db *DB) RawDB() *sql.DB

RawDB return the *sql.DB

func (*DB) Update added in v0.8.0 

func (db *DB) Update(table string, value any, where string, whereArgs ...any) (sql.Result, error)

Update updates table columns from value using db tags on exported struct fields. where is required to avoid updating all rows.

Example: 

user := &User{ID: 1, Name: "alice"}
_, err := db.Update("users", user, "id=?", user.ID)

func (*DB) UpdateColumns added in v0.8.0 

func (db *DB) UpdateColumns(table string, columns map[string]any, where string, whereArgs ...any) (sql.Result, error)

func (*DB) UpdateColumnsContext added in v0.8.0 

func (db *DB) UpdateColumnsContext(ctx context.Context, table string, columns map[string]any, where string, whereArgs ...any) (sql.Result, error)

func (*DB) UpdateContext added in v0.8.0 

func (db *DB) UpdateContext(ctx context.Context, table string, value any, where string, whereArgs ...any) (sql.Result, error)

UpdateContext updates table columns from value using db tags on exported struct fields. where is required to avoid updating all rows.

Example: 

ctx := context.Background()
_, err := db.UpdateContext(ctx, "users", user, "id=?", user.ID)

type DefaultLogger 

type DefaultLogger struct {
}

func (DefaultLogger) Debug 

func (l DefaultLogger) Debug(ctx context.Context, format string, v ...interface{})

func (DefaultLogger) Error 

func (l DefaultLogger) Error(ctx context.Context, format string, v ...interface{})

func (DefaultLogger) Info 

func (l DefaultLogger) Info(ctx context.Context, format string, v ...interface{})

func (DefaultLogger) Warn 

func (l DefaultLogger) Warn(ctx context.Context, format string, v ...interface{})

type Dialect 

type Dialect struct {
	Clients map[string]*DB
	Configs DialectConfig
	// contains filtered or unexported fields
}

func Open 

func Open(configs DialectConfig, log LoggerInterface) (*Dialect, error)

Init init all the database clients

func (*Dialect) Close 

func (dialect *Dialect) Close() error

Close Close the database

func (*Dialect) CreateClient 

func (dialect *Dialect) CreateClient(database string) (db *DB, err error)

CreateClient create the db pool for the database

func (*Dialect) Use 

func (dialect *Dialect) Use(database string) (db *DB)

Use get the db's conn

type DialectClientOption 

type DialectClientOption struct {
	Host           string                   `json:"host"`
	Port           int                      `json:"port"`
	User           string                   `json:"user"`
	Password       string                   `json:"password"`
	Database       string                   `json:"database"`
	Dialect        string                   `json:"dialect"`
	Logging        *bool                    `json:"logging"`
	Pool           *DialectClientOptionPool `json:"pool"`
	Charset        string                   `json:"charset"`
	DialectOptions map[string]string        `json:"dialectOptions"`
}

type DialectClientOptionPool 

type DialectClientOptionPool struct {
	MaxIdleConns int `json:"maxIdleConns"`
	MaxLeftTime  int `json:"maxLeftTime"`
	MaxOpenConns int `json:"maxOpenConns"`
}

type DialectConfig 

type DialectConfig struct {
	Clients map[string]*DialectClientOption `json:"clients"`
	Default *DialectClientOption            `json:"default"`
}

type DialectDSN 

type DialectDSN interface {
	GetDialectDSN(database string, config *DialectClientOption) string
}

type LoggerInterface 

type LoggerInterface interface {
	Debug(context.Context, string, ...interface{})
	Info(context.Context, string, ...interface{})
	Warn(context.Context, string, ...interface{})
	Error(context.Context, string, ...interface{})
}

type Mssql 

type Mssql struct {
}

Mssql mssql dialector

func (Mssql) GetDialectDSN 

func (m Mssql) GetDialectDSN(database string, config *DialectClientOption) string

GetDialectDSN 

**config:{
	 	"clients": {
			"share":{
				"host": "127.0.0.1",
				"user": "sa",
				"password": "test123",
				"database": "test"
			},
			"test":{
				"host": "127.0.0.1",
				"port": 1433,
				"user": "sa",
				"password": "test123",
				"database": "test",
				"pool": {
					"maxIdleConns": 20,
					"maxLeftTime": 60000,
					"maxOpenConns": 50
				},
				"dialectOptions": {
					"dial timeout": "10"

				}
			}
		},
		"default": {
			"port": 1433,
			"dialect": "sqlserver",
			"pool": {
				"maxIdleConns": 2,
				"maxLeftTime": 60000,
				"maxOpenConns": 5
			},
			"dialectOptions": {
				"dial timeout": "3"
			}
		}
	}

*

type Mysql 

type Mysql struct {
}

Mysql mysql dialector

func (Mysql) GetDialectDSN 

func (m Mysql) GetDialectDSN(database string, config *DialectClientOption) string

GetDialectDSN 

**config:{
	 	"clients": {
			"share":{
				"host": "127.0.0.1",
				"user": "test",
				"password": "test123",
				"database": "test"
			},
			"test":{
				"host": "127.0.0.1",
				"port": 3307,
				"user": "test",
				"password": "test123",
				"database": "test",
				"pool": {
					"maxIdleConns": 20,
					"maxLeftTime": 60000,
					"maxOpenConns": 50
				},
				"dialectOptions": {
					"writeTimeout": "2000ms",
					"readTimeout": "2000ms",
					"timeout":"2000ms"
				}
			}
		},
		"default": {
			"port": 3306,
			"dialect": "mysql",
			"pool": {
				"maxIdleConns": 2,
				"maxLeftTime": 60000,
				"maxOpenConns": 5
			},
			"dialectOptions": {
				"writeTimeout": "3000ms",
				"readTimeout": "3000ms",
				"timeout":"3000ms"
			}
		}
	}

*

type RowResult 

type RowResult struct {
	LastError error
	// contains filtered or unexported fields
}

func (*RowResult) Err 

func (r *RowResult) Err() error

RowResult return the error of RowResult

func (*RowResult) Scan 

func (r *RowResult) Scan(dest interface{}) error

Scan RowResult's scan

type RowsResult 

type RowsResult struct {
	*sql.Rows
	LastError error
}

func (RowsResult) Close 

func (r RowsResult) Close() error

Close returns the connection to the connection pool

func (*RowsResult) Raw 

func (r *RowsResult) Raw() (*sql.Rows, error)

Raw

func (*RowsResult) Scan 

func (r *RowsResult) Scan(dest interface{}) error

Scan

type Tx 

type Tx struct {
	*sql.Tx
	DB             *DB
	TransactionID  string
	TransactionCtx context.Context
}

func (*Tx) Commit 

func (tx *Tx) Commit() error

Commit commits the transaction.

func (*Tx) Create added in v0.8.0 

func (tx *Tx) Create(table string, value any) (sql.Result, error)

Create inserts value into table within the current transaction.

Example: 

_, err := tx.Create("users", &User{Name: "alice"})

func (*Tx) CreateContext added in v0.8.0 

func (tx *Tx) CreateContext(ctx context.Context, table string, value any) (sql.Result, error)

CreateContext inserts value into table within the current transaction.

Example: 

ctx := context.Background()
_, err := tx.CreateContext(ctx, "users", &User{Name: "alice"})

func (*Tx) Exec 

func (tx *Tx) Exec(query string, args ...interface{}) (sql.Result, error)

Exec executes a query that doesn't return rows. For example: an INSERT and UPDATE.

func (*Tx) ExecContext 

func (tx *Tx) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)

ExecContext executes a query that doesn't return rows. For example: an INSERT and UPDATE.

func (*Tx) Query 

func (tx *Tx) Query(query string, args ...interface{}) *RowsResult

Query executes a query that returns rows, typically a SELECT.

func (*Tx) QueryContext 

func (tx *Tx) QueryContext(ctx context.Context, query string, args ...interface{}) *RowsResult

QueryContext executes a query that returns rows, typically a SELECT.

func (*Tx) QueryRow 

func (tx *Tx) QueryRow(query string, args ...interface{}) *RowResult

QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

func (*Tx) QueryRowContext 

func (tx *Tx) QueryRowContext(ctx context.Context, query string, args ...interface{}) *RowResult

QueryRowContext executes a query that is expected to return at most one row. QueryRowContext always returns a non-nil value. Errors are deferred until Row's Scan method is called. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

func (*Tx) Rollback 

func (tx *Tx) Rollback() error

Rollback aborts the transaction.

func (*Tx) Update added in v0.8.0 

func (tx *Tx) Update(table string, value any, where string, whereArgs ...any) (sql.Result, error)

Update updates table columns from value within the current transaction. where is required to avoid updating all rows.

Example: 

_, err := tx.Update("users", user, "id=?", user.ID)

func (*Tx) UpdateColumns added in v0.8.0 

func (tx *Tx) UpdateColumns(table string, columns map[string]any, where string, whereArgs ...any) (sql.Result, error)

func (*Tx) UpdateColumnsContext added in v0.8.0 

func (tx *Tx) UpdateColumnsContext(ctx context.Context, table string, columns map[string]any, where string, whereArgs ...any) (sql.Result, error)

func (*Tx) UpdateContext added in v0.8.0 

func (tx *Tx) UpdateContext(ctx context.Context, table string, value any, where string, whereArgs ...any) (sql.Result, error)

UpdateContext updates table columns from value within the current transaction. where is required to avoid updating all rows.

Example: 

ctx := context.Background()
_, err := tx.UpdateContext(ctx, "users", user, "id=?", user.ID)

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.