sqle

package module
v1.5.0 Latest Latest
Warning

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

 Go to latest Published: Apr 29, 2024 License: MIT Imports: 18 Imported by: 2

README

SQLE

A SQL-First/ORM-like Golang SQL enhanced package.

License Tests Go Reference Codecov GitHub Release Go Report Card

The SQLE package provides extensions to Go’s built-in database/sql package for more efficient, comprehensive interactions with databases in Go. The SQLE package is backward-compatible and extendable, so you can easily use it with the database/sql package.

The SQLE package takes the sql-first approach and provides functionalities for marshaling rows into struct, map,slice and primitive types.

The SQLE package also provides functionalities for schema auto migration, logging, contexts, prepared statements, advanced database operations like sharding, and much more.

You’ll find the SQLE package useful if you’re not a fan of full-featured ORMs and you prefer to use the database/sql package with extra support and functionalities.

Features

  • Works with any database engine(eg MySQL, Postgres, SQLite...etc) by Go-MySQL-Driver
  • ORM-like experience using good old SQL. SQLE supports structs, maps, primitive types, and slices of map/structs/primitive types.
  • SQLBuilder
  • 100% compatible drop-in replacement of "database/sql". Code is really easy to migrate from database/sql to SQLE. see examples
  • ShardID is a snowflakes-like distributed unique identifier with extended metadata : worker, table rotation and database sharding, and sortable by time
  • Table AutoRotation
  • Database AutoSharding
  • MapR Query
  • Migration: migrate database with sql files organized in filesystem. it supports to migrate table and multiple rotated tables on all sharding database instances.

Tutorials

All examples on https://go.dev/doc/tutorial/database-access can directly work with sqle.DB instance.

See full examples on https://github.com/yaitoo/auth

Install SQLE
  • install latest commit from main branch
go get github.com/yaitoo/sqle@main
  • install latest release
go get github.com/yaitoo/sqle@latest
Connecting to a Database

SQLE directly connects to a database by sql.DB instance.

    driver := viper.GetString("db.driver")
    dsn := viper.GetString("db.dsn")

    var db *sqle.DB

    switch driver {
        case "sqlite":
            sqldb, err := sql.Open("sqlite3", "file:"+dsn+"?cache=shared")
            if err != nil {
                panic(fmt.Sprintf("db: failed to open sqlite database %s", dsn))
            }

            db = sqle.Open(sqldb)


        case "mysql":
            sqldb, err := sql.Open("mysql", dsn)
            if err != nil {
                panic(fmt.Sprintf("db: failed to open mysql database %s", dsn))
            }

            db = sqle.Open(sqldb)
        
        default:
            panic(fmt.Sprintf("db: driver %s is not supported yet", driver))
    }
    
    if  err := db.Ping(); err == nil {
        panic("db: database is unreachable")
    }
Create
  • create album by sql
func addAlbum(alb Album) (int64, error) {
    result, err := db.Exec("INSERT INTO album (title, artist, price) VALUES (?, ?, ?)", alb.Title, alb.Artist, alb.Price)
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
    id, err := result.LastInsertId()
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
    return id, nil
}
  • create album by named sql statement
func addAlbum(alb Album) (int64, error) {
    cmd := sqle.New("INSERT INTO album (title, artist, price) VALUES ({title}, {artist}, {price}").
      Param("title", alb.Title).
      Param("artist", alb.Artist).
      Param("price", alb.Price)

    result, err := db.ExecBuilder(context.TODO(),cmd)
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
    id, err := result.LastInsertId()
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
    return id, nil
}
  • create album by InsertBuilder feature
func addAlbum(alb Album) (int64, error) {
    cmd := sqle.New().Insert("album").
      Set("title", alb.Title).
      Set("artist", alb.Artist).
      Set("price", alb.Price).
      End()

    result, err := db.ExecBuilder(context.TODO(),cmd)
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
    id, err := result.LastInsertId()
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
    return id, nil
}
  • create album by map object
func addAlbum(alb Album) (int64, error) {
    inputs := map[string]any{
      "title":alb.Title,
      "artist":alb.Artist,
      "price":alb.Price,
    }

    cmd := sqle.New().Insert("album").
      SetMap(inputs)
      End()

    result, err := db.ExecBuilder(context.TODO(),cmd)
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
    id, err := result.LastInsertId()
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
    return id, nil
}
  • create album by ORM-like feature
func addAlbum(alb Album) (int64, error) {
    cmd := sqle.New().Insert("album").
      SetModel(alb).
      End()

    result, err := db.ExecBuilder(context.TODO(),cmd)
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
    id, err := result.LastInsertId()
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
    return id, nil
}
Query
query for multiple rows
  • query albums by sql
func albumsByArtist(name string) ([]Album, error) {
    // An albums slice to hold data from returned rows.
    var albums []Album

    rows, err := db.Query("SELECT * FROM album WHERE artist = ?", name)
    if err != nil {
        return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
    }
    defer rows.Close()
    // Loop through rows, using Scan to assign column data to struct fields.
    for rows.Next() {
        var alb Album
        if err := rows.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
            return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
        }
        albums = append(albums, alb)
    }
    if err := rows.Err(); err != nil {
        return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
    }
    return albums, nil
}
  • query albums by named sql statement
func albumsByArtist(name string) ([]Album, error) {
    // An albums slice to hold data from returned rows.
    var albums []Album

    cmd := sql.New("SELECT * FROM album WHERE artist = {artist}").
    Param("artist",name)

    rows, err := db.QueryBuilder(context.TODO(), cmd)
    if err != nil {
        return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
    }
    defer rows.Close()
    // Loop through rows, using Scan to assign column data to struct fields.
    for rows.Next() {
        var alb Album
        if err := rows.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
            return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
        }
        albums = append(albums, alb)
    }
    if err := rows.Err(); err != nil {
        return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
    }
    return albums, nil
}
  • query albums by WhereBuilder feature
func albumsByArtist(name string) ([]Album, error) {
    // An albums slice to hold data from returned rows.
    var albums []Album

    cmd := sql.New("SELECT * FROM album").Where().
    If(name != "").And("artist = {artist}").Param("artist",name)

    err := db.QueryBuilder(context.TODO(), cmd).Bind(&albums)
    if err != nil {
        return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
    }
    
    return albums, nil
}
  • query albums by ORM-like feature
func albumsByArtist(name string) ([]Album, error) {
    // An albums slice to hold data from returned rows.
    var albums []Album

    cmd := sql.New().Select("album").Where().
        If(name != "").And("artist = {artist}").
        Param("artist",name)

    err := db.QueryBuilder(context.TODO(), cmd).Bind(&albums)
    if err != nil {
        return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
    }
    
    return albums, nil
}
query for a single row
  • query album by sql
func albumByID(id int64) (Album, error) {
    // An album to hold data from the returned row.
    var alb Album

    row := db.QueryRow("SELECT * FROM album WHERE id = ?", id)
    if err := row.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
        if err == sql.ErrNoRows {
            return alb, fmt.Errorf("albumsById %d: no such album", id)
        }
        return alb, fmt.Errorf("albumsById %d: %v", id, err)
    }
    return alb, nil
}
  • query album by named sql statement
func albumByID(id int64) (Album, error) {
    // An album to hold data from the returned row.
    var alb Album
    cmd := sqle.New("SELECT * FROM album").
        Where("id = {id}").
        Param("id",id)

    row := db.QueryRowBuilder(context.TODO(),cmd)
    if err := row.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
        if err == sql.ErrNoRows {
            return alb, fmt.Errorf("albumsById %d: no such album", id)
        }
        return alb, fmt.Errorf("albumsById %d: %v", id, err)
    }
    return alb, nil
}
  • query album by ORM-like feature
func albumByID(id int64) (Album, error) {
    // An album to hold data from the returned row.
    var alb Album
    cmd := sqle.New().Select("album").
        Where("id = {id}").
        Param("id",id)

    err := db.QueryRowBuilder(context.TODO(),cmd).Bind(&alb)
    if err != nil {
        if err == sql.ErrNoRows {
            return alb, fmt.Errorf("albumsById %d: no such album", id)
        }
        return alb, fmt.Errorf("albumsById %d: %v", id, err)
    }
    return alb, nil
}
Update
  • update album by sql
func updateAlbum(alb Album) error {
    _, err := db.Exec("UPDATE album SET title=?, artist=?, price=? WHERE id=?", alb.Title, alb.Artist, alb.Price, alb.ID)
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
    return err
}
  • update album by named sql statement
func updateAlbum(alb Album) error {
    cmd := sqle.New("UPDATE album SET title={title}, artist={artist}, price={price} WHERE id={id}").
        Param("title", alb.Title).
        Param("artist", alb.Artist).
        Param("price", alb.Price).
        Param("id", alb.ID)

    _, err := db.ExecBuilder(context.TODO(), cmd)
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
   
    return err
}
  • update album by UpdateBuilder feature
func updateAlbum(alb Album) error {
    cmd := sqle.New().Update("album").
        Set("title", alb.Title).
        Set("artist", alb.Artist).
        Set("price", alb.Price).
        Set("id", alb.ID).
        Where("id={id}").Param("id", alb.ID)

    _, err := db.ExecBuilder(context.TODO(), cmd)
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
   
    return err
}
  • update album by ORM-like feature
func updateAlbum(alb Album) error {
    cmd := sqle.New().Update("album").
        SetModel(alb).
        Where("id={id}").Param("id", alb.ID)

    _, err := db.ExecBuilder(context.TODO(), cmd)
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
   
    return err
}
Delete
  • delete album by sql
func deleteAlbumByID(id int64) error {
    _, err := db.Exec("DELETE FROM album WHERE id = ?", id)

    return err 
}
  • delete album by named sql statement
func deleteAlbumByID(id int64) error {
    _, err := db.ExecBuilder(context.TODO(), sqle.New().Delete("album").Where("id = {id}").
        Param("id",id))

    return err 
}
Transaction

perform a set of operations within a transaction

func deleteAlbums(ids []int64) error {

    return db.Transaction(ctx, &sql.TxOptions{}, func(ctx context.Context,tx *sqle.Tx) error {
        var err error
        for _, id := range ids {
            _, err = tx.Exec("DELETE FROM album WHERE id=?",id)
            if err != nil {
                return err
            }
        }
    })
}

Table Rotation

use shardid.ID to enable rotate feature for a table based on option (NoRotate/MonthlyRotate/WeeklyRotate/DailyRotate)

gen := shardid.New(shardid.WithMonthlyRotate())
id := gen.Next()

b := New().On(id) // call `On` to enable rotate feature, and setup an input variable <rotate>
b.Delete("orders<rotate>").Where().
    If(true).And("order_id = {order_id}").
    If(false).And("member_id").
    Param("order_id", "order_123456")


db.ExecBuilder(context.TODO(),b) // DELETE FROM `orders_20240220` WHERE order_id = ?

see more examples

Database Sharding

use shardid.ID to enable sharding feature for any sql

gen := shardid.New(WithDatabase(10)) // 10 database instances
id := gen.Next()

b := New().On(id) // call `On` to setup an input variable named `rotate`, and enable rotation feature
b.Delete("orders<rotate>").Where().
    If(true).And("order_id = {order_id}").
    If(false).And("member_id").
    Param("order_id", "order_123456")


db.On(id). // automatically select database based on `id.DatabaseID`
 ExecBuilder(context.TODO(),b) // DELETE FROM `orders` WHERE order_id = ?

see more examples

MapR Query

SQLE uses MapR[T] queryer to connect and retrieve data from rotated tables and sharding databases. see more examples

Migration

SQLE discovers migrations from local file system or go embed file system. There are two objects here.

  • version directory

it should be named as semver style. eg 0.0.1/1.0.2. invalid naming will be ignored.

  • migration script file

the file format is {rank}_{description}.{suffix} eg 1_locale.sql. the suffix can be filtered in Discover. eg m.Discover(os.DirFS("./db")), WithSuffix(".postgres")). .sql is default suffix filter. the files should be organized as

├── db
│   └── 0.0.1
│       ├── 1_locale.sql
│       ├── 2_member.sql
│       ├── 3_order.sql
│   └── 0.0.2
│       ├── 1_add_times_orders.sql
│       ├── 2_logs.sql

and database can be migrated in code

    //go:embed db
	var migrations embed.FS

	m := migrate.New(db)
    // m.Discover(os.DirFS("./db"))
	if err := m.Discover(migrations); err != nil {
		panic(" db: migrate " + err.Error())
	}

	err = m.Init(context.TODO())
	if err != nil {
		panic(" db: migrate " + err.Error())
	}

	err = m.Migrate(context.TODO())
	if err != nil {
		panic(" db: migrate " + err.Error())
	}

see more examples

if a table has been rotated, and migration should be started with /* rotate: monthly=20240201-20240401*/ as first line. so the migration is applied automatically on the table and all it's rotated tables from 20240201 to 20240401.

/* rotate: monthly = 20240201 - 20240401 */
CREATE TABLE IF NOT EXISTS monthly_logs<rotate> (
    id int NOT NULL,
    msg varchar(50) NOT NULL,
    PRIMARY KEY (id)
);

monthly_logs, monthly_logs_202402, monthly_logs_202403 and monthly_logs_202404 will be migrated automatically.

see more examples

if rotate is enabled for any table, rotate should be executed periodically in a cron job. so rotated tables will be created periodically.

├── db
│   └── monthly
│       ├── members.sql
│   └── weekly
│       ├── orders.sql
│   └── daily
│       ├── logs.sql

see more examples

Security: SQL Injection

SQLE uses the database/sql‘s argument placeholders to build parameterized SQL statement, which will automatically escape arguments to avoid SQL injection. eg if it is PostgreSQL, please apply UsePostgres on SQLBuilder or change DefaultSQLQuote and DefaultSQLParameterize to update parameterization options.

func UsePostgres(b *Builder) {
	b.Quote = "`"
	b.Parameterize = func(name string, index int) string {
		return "$" + strconv.Itoa(index)
	}
}

Contributing

Contributions are welcome! If you're interested in contributing, please feel free to contribute to SQLE

License

MIT License

Documentation

Overview

Package sqle provides a SQLBuilder for constructing SQL statements in a programmatic way. It allows you to build SELECT, INSERT, UPDATE, and DELETE statements with ease.

Index

Constants

This section is empty.

Variables

View Source 
var (
	StmtMaxIdleTime = 3 * time.Minute
	ErrMissingDHT   = errors.New("sqle: missing_dht")
)
View Source 
var (
	ErrMustPointer       = errors.New("sqle: dest must be a pointer")
	ErrMustSlice         = errors.New("sqle: dest must be a slice")
	ErrMustStruct        = errors.New("sqle: dest must be a struct")
	ErrMustNotNilPointer = errors.New("sqle: dest must be not a nil pointer")
	ErrTypeNotBindable   = errors.New("sqle: dest type is not bindable")
	ErrMustStringKey     = errors.New("sqle: map key must be string type")
)
View Source 
var (
	// ErrInvalidParamVariable is an error that is returned when an invalid parameter variable is encountered.
	ErrInvalidParamVariable = errors.New("sqle: invalid param variable")

	// DefaultSQLQuote is the default character used to escape column names in UPDATE and INSERT statements.
	DefaultSQLQuote = "`"

	// DefaultSQLParameterize is the default function used to parameterize values in SQL statements.
	DefaultSQLParameterize = func(name string, index int) string {
		return "?"
	}
)

Functions

func UseMySQL added in v1.1.0 

func UseMySQL(b *Builder)

func UseOracle added in v1.1.0 

func UseOracle(b *Builder)

func UsePostgres added in v1.1.0 

func UsePostgres(b *Builder)

Types

type Binder 

type Binder interface {
	Bind(v reflect.Value, columns []string) []any
}

type Bool added in v1.5.0 

type Bool bool

Bool is an implementation of a bool for the MySQL type BIT(1).

func (*Bool) Scan added in v1.5.0 

func (b *Bool) Scan(src interface{}) error

Scan implements the sql.Scanner interface, and turns the bit field incoming from MySQL into a BitBool

func (Bool) Value added in v1.5.0 

func (b Bool) Value() (driver.Value, error)

Value implements the driver.Valuer interface, and turns the BitBool into a bit field (BIT(1)) for MySQL storage.

type Builder 

type Builder struct {
	Quote        string // escape column name in UPDATE and INSERT
	Parameterize func(name string, index int) string
	// contains filtered or unexported fields
}

Builder is a SQL query builder that allows you to construct SQL statements.

func New 

func New(cmd ...string) *Builder

New creates a new instance of the Builder with the given initial command(s).

func (*Builder) Build 

func (b *Builder) Build() (string, []any, error)

Build constructs the final SQL statement and returns it along with the parameter values.

func (*Builder) Delete added in v1.1.0 

func (b *Builder) Delete(table string) *Builder

Delete adds a DELETE statement to the current query builder. Returns the current query builder.

func (*Builder) If added in v1.3.0 

func (b *Builder) If(predicate bool) *Builder

If sets a condition that determines whether the subsequent SQL command should be executed. If the predicate is false, the command is skipped.

func (*Builder) Input 

func (b *Builder) Input(name, value string) *Builder

Input sets the value of an input variable in the Builder.

func (*Builder) Inputs 

func (b *Builder) Inputs(v map[string]string) *Builder

Inputs sets multiple input variables in the Builder.

func (*Builder) Insert 

func (b *Builder) Insert(table string) *InsertBuilder

Insert starts a new InsertBuilder and sets the table to insert into. Returns the new InsertBuilder.

func (*Builder) On added in v1.2.0 

func (b *Builder) On(id shardid.ID) *Builder

On sets the "rotate" input variable to the given shard ID's rotate name. Returns the current query builder.

func (*Builder) Order added in v1.4.3 

func (b *Builder) Order(allowedColumns ...string) *OrderByBuilder

Order create an OrderByBuilder with allowed columns to prevent sql injection. NB: any input is allowed if it is not provided

func (*Builder) Param 

func (b *Builder) Param(name string, value any) *Builder

Param sets the value of a parameter variable in the Builder.

func (*Builder) Params 

func (b *Builder) Params(v map[string]any) *Builder

Params sets multiple parameter variables in the Builder.

func (*Builder) SQL 

func (b *Builder) SQL(cmd string) *Builder

SQL appends the given SQL command to the Builder's statement. If the Builder's shouldSkip flag is set, the command is skipped.

func (*Builder) Select added in v1.1.0 

func (b *Builder) Select(table string, columns ...string) *Builder

Select adds a SELECT statement to the current query builder. If no columns are specified, it selects all columns using "*". Returns the current query builder.

func (*Builder) String 

func (b *Builder) String() string

String returns the SQL statement constructed by the Builder.

func (*Builder) Update 

func (b *Builder) Update(table string) *UpdateBuilder

Update starts a new UpdateBuilder and sets the table to update. Returns the new UpdateBuilder.

func (*Builder) Where 

func (b *Builder) Where(criteria ...string) *WhereBuilder

Where adds a WHERE clause to the SQL statement. It takes one or more criteria strings as arguments. Each criteria string represents a condition in the WHERE clause. If a criteria string is empty, it will be ignored. Returns a *WhereBuilder that can be used to further build the SQL statement.

func (*Builder) WithOrderBy added in v1.4.4 

func (b *Builder) WithOrderBy(ob *OrderByBuilder) *OrderByBuilder

WithOrderBy sets the order by clause for the SQL query. It takes an instance of the OrderByBuilder and adds the allowed columns to the Builder's order list. It also appends the SQL string representation of the OrderByBuilder to the Builder's SQL string. It returns a new instance of the OrderByBuilder.

func (*Builder) WithWhere added in v1.4.4 

func (b *Builder) WithWhere(wb *WhereBuilder) *WhereBuilder

WithWhere adds the input and parameter values from the given WhereBuilder to the current Builder and sets the WHERE clause of the SQL statement to the string representation of the WhereBuilder's statement. It returns the modified WhereBuilder.

type BuilderOption added in v1.1.0 

type BuilderOption func(opts *BuilderOptions)

func WithAllow added in v1.1.0 

func WithAllow(columns ...string) BuilderOption

WithAllow only allowed columns can be written to db

func WithToName added in v1.1.0 

func WithToName(fn func(string) string) BuilderOption

type BuilderOptions added in v1.1.0 

type BuilderOptions struct {
	ToName  func(string) string // db column naming convert method
	Columns []string            // db column filter
}

type Client added in v1.5.0 

type Client struct {
	*sql.DB
	sync.Mutex

	Index int
	// contains filtered or unexported fields
}

func (*Client) Begin added in v1.5.0 

func (db *Client) Begin(opts *sql.TxOptions) (*Tx, error)

func (*Client) BeginTx added in v1.5.0 

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

func (*Client) Exec added in v1.5.0 

func (db *Client) Exec(query string, args ...any) (sql.Result, error)

func (*Client) ExecBuilder added in v1.5.0 

func (db *Client) ExecBuilder(ctx context.Context, b *Builder) (sql.Result, error)

func (*Client) ExecContext added in v1.5.0 

func (db *Client) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)

func (*Client) Query added in v1.5.0 

func (db *Client) Query(query string, args ...any) (*Rows, error)

func (*Client) QueryBuilder added in v1.5.0 

func (db *Client) QueryBuilder(ctx context.Context, b *Builder) (*Rows, error)

func (*Client) QueryContext added in v1.5.0 

func (db *Client) QueryContext(ctx context.Context, query string, args ...any) (*Rows, error)

func (*Client) QueryRow added in v1.5.0 

func (db *Client) QueryRow(query string, args ...any) *Row

func (*Client) QueryRowBuilder added in v1.5.0 

func (db *Client) QueryRowBuilder(ctx context.Context, b *Builder) *Row

func (*Client) QueryRowContext added in v1.5.0 

func (db *Client) QueryRowContext(ctx context.Context, query string, args ...any) *Row

func (*Client) Transaction added in v1.5.0 

func (db *Client) Transaction(ctx context.Context, opts *sql.TxOptions, fn func(ctx context.Context, tx *Tx) error) error

type Connector added in v1.5.0 

type Connector interface {
	// Query executes a query that returns multiple rows.
	// It takes a query string and optional arguments.
	// It returns a pointer to a Rows object and an error, if any.
	Query(query string, args ...any) (*Rows, error)

	// QueryBuilder executes a query using a Builder object.
	// It takes a context and a Builder object.
	// It returns a pointer to a Rows object and an error, if any.
	QueryBuilder(ctx context.Context, b *Builder) (*Rows, error)

	// QueryContext executes a query that returns multiple rows using a context.
	// It takes a context, a query string, and optional arguments.
	// It returns a pointer to a Rows object and an error, if any.
	QueryContext(ctx context.Context, query string, args ...any) (*Rows, error)

	// QueryRow executes a query that returns a single row.
	// It takes a query string and optional arguments.
	// It returns a pointer to a Row object.
	QueryRow(query string, args ...any) *Row

	// QueryRowBuilder executes a query that returns a single row using a Builder object.
	// It takes a context and a Builder object.
	// It returns a pointer to a Row object.
	QueryRowBuilder(ctx context.Context, b *Builder) *Row

	// QueryRowContext executes a query that returns a single row using a context.
	// It takes a context, a query string, and optional arguments.
	// It returns a pointer to a Row object.
	QueryRowContext(ctx context.Context, query string, args ...any) *Row

	// Exec executes a query that doesn't return any rows.
	// It takes a query string and optional arguments.
	// It returns a sql.Result object and an error, if any.
	Exec(query string, args ...any) (sql.Result, error)

	// ExecContext executes a query that doesn't return any rows using a context.
	// It takes a context, a query string, and optional arguments.
	// It returns a sql.Result object and an error, if any.
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)

	// ExecBuilder executes a query that doesn't return any rows using a Builder object.
	// It takes a context and a Builder object.
	// It returns a sql.Result object and an error, if any.
	ExecBuilder(ctx context.Context, b *Builder) (sql.Result, error)
}

Connector represents a database connector that provides methods for executing queries and commands. Conn and Tx both implement this interface.

type DB 

type DB struct {
	*Client
	// contains filtered or unexported fields
}

DB represents a database connection pool with sharding support.

func Open 

func Open(dbs ...*sql.DB) *DB

Open creates a new DB instance with the provided database connections.

func (*DB) Add added in v1.4.0 

func (db *DB) Add(dbs ...*sql.DB)

Add dynamically scales out the DB with new databases.

func (*DB) GetDHT added in v1.4.1 

func (db *DB) GetDHT(name string) *shardid.DHT

GetDHT returns the DHT (Distributed Hash Table) with the specified name.

func (*DB) NewDHT added in v1.4.0 

func (db *DB) NewDHT(name string, dbs ...int)

NewDHT creates a new DHT (Distributed Hash Table) with the specified databases.

func (*DB) On added in v1.2.0 

func (db *DB) On(id shardid.ID) *Client

On selects the database context based on the shardid ID.

func (*DB) OnDHT added in v1.4.0 

func (db *DB) OnDHT(key string, names ...string) (*Client, error)

OnDHT selects the database context based on the DHT (Distributed Hash Table) key.

type DTC added in v1.5.0 

type DTC struct {
	// contains filtered or unexported fields
}

DTC Distributed Transaction Coordinator

func NewDTC added in v1.5.0 

func NewDTC(ctx context.Context, opts *sql.TxOptions) *DTC

NewDTC creates a new instance of DTC.

func (*DTC) Commit added in v1.5.0 

func (d *DTC) Commit() error

Commit commits all the prepared transactions in the DTC.

func (*DTC) Prepare added in v1.5.0 

func (d *DTC) Prepare(client *Client, exec func(ctx context.Context, conn Connector) error, revert func(ctx context.Context, conn Connector) error)

Prepare adds a new transaction session to the DTC.

func (*DTC) Rollback added in v1.5.0 

func (d *DTC) Rollback() []error

Rollback rolls back all the prepared transactions in the DTC.

type Duration added in v1.3.1 

type Duration time.Duration

func (Duration) Duration added in v1.3.1 

func (d Duration) Duration() time.Duration

func (*Duration) Scan added in v1.3.1 

func (d *Duration) Scan(src interface{}) error

Scan implements the sql.Scanner interface, and turns the VARCHAR field incoming from MySQL into a Duration

func (Duration) Value added in v1.3.1 

func (d Duration) Value() (driver.Value, error)

Value implements the driver.Valuer interface, and turns the Duration into a VARCHAR field for MySQL storage.

type Errors added in v1.3.0 

type Errors struct {
	// contains filtered or unexported fields
}

func (*Errors) Error added in v1.3.0 

func (e *Errors) Error() string

type Input 

type Input string

Input represents an input token.

func (Input) String 

func (t Input) String() string

String returns the string representation of the token.

func (Input) Type 

func (t Input) Type() TokenType

Type returns the type of the token. skipcq: RVV-B0013

type InsertBuilder 

type InsertBuilder struct {
	// contains filtered or unexported fields
}

func (*InsertBuilder) End 

func (ib *InsertBuilder) End() *Builder

func (*InsertBuilder) If 

func (ib *InsertBuilder) If(predicate bool) *InsertBuilder

func (*InsertBuilder) Set 

func (ib *InsertBuilder) Set(name string, value any) *InsertBuilder

func (*InsertBuilder) SetMap 

func (ib *InsertBuilder) SetMap(m map[string]any, opts ...BuilderOption) *InsertBuilder

func (*InsertBuilder) SetModel 

func (ib *InsertBuilder) SetModel(m any) *InsertBuilder

type LimitOption added in v1.4.4 

type LimitOption func(q *LimitOptions)

LimitOption is a function type that modifies a LimitedQuery.

func WithOrderBy added in v1.4.4 

func WithOrderBy(ob *OrderByBuilder) LimitOption

WithOrderBy is a LimitedQueryOption that sets the ordering of the LimitedQuery.

func WithPageSize added in v1.4.4 

func WithPageSize(size int64) LimitOption

WithPageSize is a LimitedQueryOption that sets the page size of the LimitedQuery.

type LimitOptions added in v1.4.4 

type LimitOptions struct {
	Offset  int64           // Offset represents the number of rows to skip before returning the result set.
	Limit   int64           // Limit represents the maximum number of results to be returned.
	OrderBy *OrderByBuilder // OrderBy represents the ordering of the query.
}

LimitOptions represents a query with pagination and ordering options.

type LimitResult added in v1.4.4 

type LimitResult[T any] struct {
	Items []T   `json:"items,omitempty"` // Items contains the limited result items.
	Total int64 `json:"total,omitempty"` // Total represents the total count.
}

LimitResult represents a limited result set with items and total count.

type MapR added in v1.3.0 

type MapR[T any] struct {
	// contains filtered or unexported fields
}

MapR is a Map/Reduce Query Provider based on databases.

func (*MapR[T]) Count added in v1.3.0 

func (q *MapR[T]) Count(ctx context.Context, rotatedTables []string, b *Builder) (int64, error)

Count executes the query and returns the count of results.

func (*MapR[T]) First added in v1.3.0 

func (q *MapR[T]) First(ctx context.Context, rotatedTables []string, b *Builder) (T, error)

First executes the query and returns the first result.

func (*MapR[T]) Query added in v1.3.0 

func (q *MapR[T]) Query(ctx context.Context, rotatedTables []string, b *Builder, less func(i, j T) bool) ([]T, error)

Query executes the query and returns a list of results.

func (*MapR[T]) QueryLimit added in v1.3.0 

func (q *MapR[T]) QueryLimit(ctx context.Context, rotatedTables []string, b *Builder, less func(i, j T) bool, limit int) ([]T, error)

QueryLimit executes the query and returns a limited list of results.

type OrderByBuilder added in v1.4.2 

type OrderByBuilder struct {
	*Builder // The underlying SQL query builder.
	// contains filtered or unexported fields
}

OrderByBuilder represents a SQL ORDER BY clause builder. It is used to construct ORDER BY clauses for SQL queries.

func NewOrderBy added in v1.4.4 

func NewOrderBy(allowedColumns ...string) *OrderByBuilder

NewOrderBy creates a new instance of the OrderByBuilder. It takes a variadic parameter `allowedColumns` which specifies the columns that are allowed to be used in the ORDER BY clause.

func (*OrderByBuilder) By added in v1.4.3 

func (ob *OrderByBuilder) By(raw string) *OrderByBuilder

By order by raw sql. eg By("a asc, b desc")

func (*OrderByBuilder) ByAsc added in v1.4.3 

func (ob *OrderByBuilder) ByAsc(columns ...string) *OrderByBuilder

ByAsc order by ascending with columns

func (*OrderByBuilder) ByDesc added in v1.4.3 

func (ob *OrderByBuilder) ByDesc(columns ...string) *OrderByBuilder

ByDesc order by descending with columns

type Param 

type Param string

Param represents a parameter token.

func (Param) String 

func (t Param) String() string

String returns the string representation of the token.

func (Param) Type 

func (t Param) Type() TokenType

Type returns the type of the token. skipcq: RVV-B0013

type Query added in v1.3.0 

type Query[T any] struct {
	// contains filtered or unexported fields
}

func NewQuery added in v1.3.0 

func NewQuery[T any](db *DB, options ...QueryOption[T]) *Query[T]

NewQuery creates a new Query instance. It takes a *DB as the first argument and optional QueryOption functions as the rest. It returns a pointer to the created Query instance.

func (*Query[T]) Count added in v1.3.0 

func (q *Query[T]) Count(ctx context.Context, b *Builder) (int64, error)

Count executes the query and returns the number of results. It takes a context.Context and a *Builder as arguments. It returns the count as an integer and an error, if any.

func (*Query[T]) First added in v1.3.0 

func (q *Query[T]) First(ctx context.Context, b *Builder) (T, error)

First executes the query and returns the first result. It takes a context.Context and a *Builder as arguments. It returns the result of type T and an error, if any.

func (*Query[T]) Query added in v1.3.0 

func (q *Query[T]) Query(ctx context.Context, b *Builder, less func(i, j T) bool) ([]T, error)

Query executes the query and returns all the results. It takes a context.Context, a *Builder, and a comparison function as arguments. The comparison function is used to sort the results. It returns a slice of results of type T and an error, if any.

func (*Query[T]) QueryLimit added in v1.3.0 

func (q *Query[T]) QueryLimit(ctx context.Context, b *Builder, less func(i, j T) bool, limit int) ([]T, error)

QueryLimit executes the query and returns a limited number of results. It takes a context.Context, a *Builder, a comparison function, and a limit as arguments. The comparison function is used to sort the results. The limit specifies the maximum number of results to return. It returns a slice of results of type T and an error, if any.

type QueryOption added in v1.3.0 

type QueryOption[T any] func(q *Query[T])

func WithDays added in v1.3.0 

func WithDays[T any](start, end time.Time) QueryOption[T]

func WithMonths added in v1.3.0 

func WithMonths[T any](start, end time.Time) QueryOption[T]

func WithQueryer added in v1.3.0 

func WithQueryer[T any](qr Queryer[T]) QueryOption[T]

func WithWeeks added in v1.3.0 

func WithWeeks[T any](start, end time.Time) QueryOption[T]

type Queryer added in v1.3.0 

type Queryer[T any] interface {
	// First retrieves the first result that matches the query criteria.
	First(ctx context.Context, rotatedTables []string, b *Builder) (T, error)

	// Count returns the number of results that match the query criteria.
	Count(ctx context.Context, rotatedTables []string, b *Builder) (int64, error)

	// Query retrieves all results that match the query criteria and sorts them using less function if it is provided.
	Query(ctx context.Context, rotatedTables []string, b *Builder, less func(i, j T) bool) ([]T, error)

	// QueryLimit retrieves a limited number of results that match the query criteria, sorts them using the provided less function, and limits the number of results to the specified limit.
	QueryLimit(ctx context.Context, rotatedTables []string, b *Builder, less func(i, j T) bool, limit int) ([]T, error)
}

Queryer is a query provider interface that defines methods for querying data.

type Row 

type Row struct {
	// contains filtered or unexported fields
}

func (*Row) Bind 

func (r *Row) Bind(dest any) error

func (*Row) Close added in v1.4.1 

func (r *Row) Close() error

func (*Row) Err 

func (r *Row) Err() error

func (*Row) Scan 

func (r *Row) Scan(dest ...any) error

type Rows 

type Rows struct {
	*sql.Rows
	// contains filtered or unexported fields
}

func (*Rows) Bind 

func (r *Rows) Bind(dest any) error

func (*Rows) Close added in v1.4.1 

func (r *Rows) Close() error

func (*Rows) Scan added in v1.4.1 

func (r *Rows) Scan(dest ...any) error

type Stmt added in v1.4.1 

type Stmt struct {
	*sql.Stmt
	// contains filtered or unexported fields
}

func (*Stmt) Reuse added in v1.4.1 

func (s *Stmt) Reuse()

type Text 

type Text string

Text represents a text token.

func (Text) String 

func (t Text) String() string

String returns the string representation of the token.

func (Text) Type 

func (t Text) Type() TokenType

Type returns the type of the token. skipcq: RVV-B0013

type Time added in v1.5.0 

type Time struct {
	sql.NullTime
}

Time represents a nullable time value.

func NewTime added in v1.5.0 

func NewTime(t time.Time, valid bool) Time

NewTime creates a new Time object with the given time and valid flag.

func (Time) MarshalJSON added in v1.5.0 

func (t Time) MarshalJSON() ([]byte, error)

MarshalJSON implements the json.Marshaler interface

func (*Time) Scan added in v1.5.0 

func (t *Time) Scan(value any) error

Scan implements the sql.Scanner interface.

func (*Time) Time added in v1.5.0 

func (t *Time) Time() time.Time

Time returns the underlying time.Time value of the Time struct.

func (*Time) UnmarshalJSON added in v1.5.0 

func (t *Time) UnmarshalJSON(data []byte) error

UnmarshalJSON implements the json.Unmarshaler interface

func (Time) Value added in v1.5.0 

func (t Time) Value() (driver.Value, error)

Value implements the driver.Valuer interface.

type Token 

type Token interface {
	Type() TokenType
	String() string
}

Token is an interface that represents a SQL token.

type TokenType 

type TokenType uint

TokenType represents the type of a token. 

const (
	TextToken  TokenType = 0
	InputToken TokenType = 1
	ParamToken TokenType = 2
)

type Tokenizer 

type Tokenizer struct {
	Raw    string
	Tokens []Token
}

func Tokenize 

func Tokenize(text string) *Tokenizer

type Tx 

type Tx struct {
	*sql.Tx
	// contains filtered or unexported fields
}

func (*Tx) Commit added in v1.1.0 

func (tx *Tx) Commit() error

func (*Tx) Exec 

func (tx *Tx) Exec(query string, args ...any) (sql.Result, error)

func (*Tx) ExecBuilder 

func (tx *Tx) ExecBuilder(ctx context.Context, b *Builder) (sql.Result, error)

func (*Tx) ExecContext 

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

func (*Tx) Lock 

func (*Tx) Lock()

func (*Tx) Query 

func (tx *Tx) Query(query string, args ...any) (*Rows, error)

func (*Tx) QueryBuilder 

func (tx *Tx) QueryBuilder(ctx context.Context, b *Builder) (*Rows, error)

func (*Tx) QueryContext 

func (tx *Tx) QueryContext(ctx context.Context, query string, args ...any) (*Rows, error)

func (*Tx) QueryRow 

func (tx *Tx) QueryRow(query string, args ...any) *Row

func (*Tx) QueryRowBuilder 

func (tx *Tx) QueryRowBuilder(ctx context.Context, b *Builder) *Row

func (*Tx) QueryRowContext 

func (tx *Tx) QueryRowContext(ctx context.Context, query string, args ...any) *Row

func (*Tx) Rollback added in v1.1.0 

func (tx *Tx) Rollback() error

func (*Tx) Unlock 

func (*Tx) Unlock()

type UpdateBuilder 

type UpdateBuilder struct {
	*Builder
	// contains filtered or unexported fields
}

func (*UpdateBuilder) If 

func (ub *UpdateBuilder) If(predicate bool) *UpdateBuilder

func (*UpdateBuilder) Set 

func (ub *UpdateBuilder) Set(name string, value any) *UpdateBuilder

func (*UpdateBuilder) SetExpr 

func (ub *UpdateBuilder) SetExpr(cmd string) *UpdateBuilder

func (*UpdateBuilder) SetMap 

func (ub *UpdateBuilder) SetMap(m map[string]any, opts ...BuilderOption) *UpdateBuilder

func (*UpdateBuilder) SetModel 

func (ub *UpdateBuilder) SetModel(dest any) *UpdateBuilder

type WhereBuilder 

type WhereBuilder struct {
	*Builder
	// contains filtered or unexported fields
}

WhereBuilder is a struct that represents a SQL WHERE clause builder.

func NewWhere added in v1.4.4 

func NewWhere() *WhereBuilder

NewWhere creates a new instance of WhereBuilder.

func (*WhereBuilder) And 

func (wb *WhereBuilder) And(criteria string) *WhereBuilder

And adds an AND condition to the WHERE clause.

func (*WhereBuilder) End 

func (wb *WhereBuilder) End() *Builder

End returns the underlying Builder instance.

func (*WhereBuilder) If 

func (wb *WhereBuilder) If(predicate bool) *WhereBuilder

If sets a condition to skip the subsequent SQL statements. If the predicate is false, the subsequent SQL statements will be skipped.

func (*WhereBuilder) Or 

func (wb *WhereBuilder) Or(criteria string) *WhereBuilder

Or adds an OR condition to the WHERE clause.

func (*WhereBuilder) SQL 

func (wb *WhereBuilder) SQL(op string, criteria string) *WhereBuilder

SQL adds a condition to the WHERE clause with the specified operator.

Source Files

View all Source files

Directories

Path Synopsis
cmd

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.