sql

package
v0.53.1 Latest Latest
Warning

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

 Go to latest Published: Mar 2, 2024 License: BSD-3-Clause Imports: 13 Imported by: 0

Documentation

Overview

Package sql is an extension to standard library "database/sql.DB" that provide common functionality across DBMS.

Index

Examples

Constants

View Source 
const (
	DriverNameMysql    = "mysql"
	DriverNamePostgres = "postgres"
)

List of known driver name for database connection.

View Source 
const DefaultPlaceHolder = "?"

DefaultPlaceHolder define default placeholder for DML, which is placeholder for MySQL.

Variables

This section is empty.

Functions

func JoinValues added in v0.53.0 

func JoinValues(s ...[]any) (all []any)

JoinValues join list of slice of values into single slice.

Types

type Client 

type Client struct {
	*sql.DB
	ClientOptions
	TableNames []string // List of tables in database.
}

Client provide a wrapper for generic database instance.

func NewClient added in v0.22.0 

func NewClient(opts ClientOptions) (cl *Client, err error)

NewClient create and initialize new database client.

func (*Client) FetchTableNames 

func (cl *Client) FetchTableNames() (tableNames []string, err error)

FetchTableNames return the table names in current database schema sorted in ascending order.

func (*Client) Migrate added in v0.16.0 

func (cl *Client) Migrate(tableMigration string, fs http.FileSystem) (err error)

Migrate the database using list of SQL files inside a directory. Each SQL file in directory will be executed in alphabetical order based on the last state.

The table parameter contains the name of table where the state of migration will be saved. If its empty default to "_migration". The state including the SQL file name that has been executed and the timestamp.

func (*Client) TruncateTable 

func (cl *Client) TruncateTable(tableName string) (err error)

TruncateTable truncate all data on table `tableName` with cascade option. On PostgreSQL, any identity columns (for example, serial) will be reset back to its initial value.

type ClientOptions added in v0.22.0 

type ClientOptions struct {
	DriverName   string
	DSN          string
	MigrationDir string
}

ClientOptions contains options to connect to database server, including the migration directory.

type DMLKind added in v0.53.0 

type DMLKind string

DMLKind define the kind for Data Manipulation Language (DML). 

const (
	DMLKindDelete DMLKind = `DELETE`
	DMLKindInsert DMLKind = `INSERT`
	DMLKindSelect DMLKind = `SELECT`
	DMLKindUpdate DMLKind = `UPDATE`
)

List of valid DMLKind.

type Meta added in v0.53.0 

type Meta struct {

	// ListName contains list of column name.
	ListName []string

	// ListHolder contains list of column holder, as in "?" or "$x",
	// depends on the driver.
	ListHolder []string

	// ListValue contains list of column values, either for insert or
	// select.
	ListValue []any

	// ListWhereCond contains list of condition to be joined with
	// ListHolder.
	// The text is a free form, does not need to be a column name.
	ListWhereCond []string

	// ListWhereValue contains list of values for where condition.
	ListWhereValue []any

	// Index collect all holder integer value, as in "1,2,3,...".
	Index []any
	// contains filtered or unexported fields
}

Meta contains the DML meta data, including driver name, list of column names, list of column holders, and list of values.

Example (DeleteOnPostgresql) 
package main

import (
	"fmt"

	"github.com/shuLhan/share/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta  = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindUpdate)
		qid   = 1
		qname = `hello`
	)

	meta.BindWhere(`id=`, qid)
	meta.BindWhere(`OR name=`, qname)

	var q = fmt.Sprintf(`DELETE FROM t WHERE %s;`, meta.WhereFields())

	// db.Exec(q, meta.ListWhereValue...)

	fmt.Println(q)
	fmt.Println(meta.ListWhereValue)

}

Output:

DELETE FROM t WHERE id=$1 OR name=$2;
[1 hello]
Example (InsertOnPostgresql) 
package main

import (
	"fmt"

	"github.com/shuLhan/share/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindInsert)
		t    = Table{
			ID:   1,
			Name: `hello`,
		}
	)

	meta.Bind(`id`, t.ID)
	meta.Bind(`name`, t.Name)

	var q = fmt.Sprintf(`INSERT INTO t (%s) VALUES (%s);`, meta.Names(), meta.Holders())

	// db.Exec(q, meta.ListValue...)

	fmt.Println(q)
	fmt.Println(meta.ListValue)

}

Output:

INSERT INTO t (id,name) VALUES ($1,$2);
[1 hello]
Example (SelectOnPostgresql) 
package main

import (
	"fmt"

	"github.com/shuLhan/share/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta  = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)
		t     = Table{}
		qid   = 1
		qname = `hello`
	)

	meta.Bind(`id`, &t.ID)
	meta.Bind(`name`, &t.Name)
	meta.BindWhere(`id=`, qid)
	meta.BindWhere(`OR name=`, qname)

	var q = fmt.Sprintf(`SELECT %s FROM t WHERE %s;`, meta.Names(), meta.WhereFields())

	// db.QueryRow(q, meta.ListWhereValue...).Scan(meta.ListValue...)

	fmt.Println(q)
	fmt.Println(`WHERE=`, meta.ListWhereValue)
	fmt.Println(len(meta.ListValue))

}

Output:

SELECT id,name FROM t WHERE id=$1 OR name=$2;
WHERE= [1 hello]
2
Example (Subquery)

Sometime the query need to be stiched piece by piece. 

package main

import (
	"fmt"
	"strings"

	"github.com/shuLhan/share/lib/sql"
)

func main() {
	type Table struct {
		Name  string
		ID    int
		SubID int
	}

	var (
		meta  = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)
		id    = 1
		subid = 500
		t     Table
		qb    strings.Builder
		idx   int
	)

	meta.Bind(`id`, &t.ID)
	meta.Bind(`sub_id`, &t.SubID)
	meta.Bind(`name`, &t.Name)

	fmt.Fprintf(&qb, `SELECT %s FROM t WHERE 1=1`, meta.Names())

	if id != 0 {
		idx = meta.BindWhere(``, id)
		fmt.Fprintf(&qb, ` AND id = $%d`, idx)
	}
	if subid != 0 {
		idx = meta.BindWhere(``, subid)
		fmt.Fprintf(&qb, ` AND sub_id = (SELECT id FROM u WHERE u.id = $%d);`, idx)
	}

	// db.Exec(qb.String(),meta.ListWhereValue...).Scan(meta.ListValue...)

	fmt.Println(qb.String())
	fmt.Println(meta.ListWhereValue)

}

Output:

SELECT id,sub_id,name FROM t WHERE 1=1 AND id = $1 AND sub_id = (SELECT id FROM u WHERE u.id = $2);
[1 500]
Example (SubqueryWithIndex) 
package main

import (
	"fmt"
	"strings"

	"github.com/shuLhan/share/lib/sql"
)

func main() {
	type Table struct {
		Name  string
		ID    int
		SubID int
	}

	var (
		meta  = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)
		id    = 1
		subid = 500
		t     Table
	)

	meta.Bind(`id`, &t.ID)
	meta.Bind(`sub_id`, &t.SubID)
	meta.Bind(`name`, &t.Name)

	var qb strings.Builder

	fmt.Fprintf(&qb, `SELECT %s FROM t WHERE 1=1`, meta.Names())
	if id != 0 {
		qb.WriteString(` AND id = $%d`)
		meta.BindWhere(`id`, id)
	}
	if subid != 0 {
		qb.WriteString(` AND sub_id = (SELECT id FROM u WHERE u.id = $%d);`)
		meta.BindWhere(`sub_id`, subid)
	}

	var q = fmt.Sprintf(qb.String(), meta.Index...)

	// db.Exec(q, meta.ListWhereValue...).Scan(meta.ListValue...)

	fmt.Println(q)
	fmt.Println(meta.Index)
	fmt.Println(meta.ListWhereValue)

}

Output:

SELECT id,sub_id,name FROM t WHERE 1=1 AND id = $1 AND sub_id = (SELECT id FROM u WHERE u.id = $2);
[1 2]
[1 500]

func NewMeta added in v0.53.0 

func NewMeta(driverName string, dmlKind DMLKind) (meta *Meta)

NewMeta create new Meta using specific driver name. The driver affect the ListHolder value.

func (*Meta) Bind added in v0.53.0 

func (meta *Meta) Bind(colName string, val any)

Bind column name and variable for DML INSERT, SELECT, or UPDATE. It is a no-op for DML DELETE.

Example 
package main

import (
	"fmt"

	"github.com/shuLhan/share/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta = sql.NewMeta(sql.DriverNameMysql, sql.DMLKindSelect)
		t    = Table{}
	)

	meta.Bind(`id`, &t.ID)
	meta.Bind(`name`, &t.Name)

	var q = fmt.Sprintf(`SELECT %s FROM t;`, meta.Names())

	// db.Exec(q).Scan(meta.ListValue...)

	fmt.Println(q)
	fmt.Printf("%T %T", meta.ListValue...)

}

Output:

SELECT id,name FROM t;
*int *string

func (*Meta) BindWhere added in v0.53.0 

func (meta *Meta) BindWhere(cond string, val any) int

BindWhere bind value for where condition.

The cond string is optional, can be a column name with operator or any text like "AND col=" or "OR col=".

It return the length of [Meta.ListHolder].

It is a no-operation for DML INSERT.

Example 
package main

import (
	"fmt"

	"github.com/shuLhan/share/lib/sql"
)

func main() {
	var (
		meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)
		vals = []any{
			int(1000),
			string(`JohnDoe`),
		}
		idx int
	)

	idx = meta.BindWhere(``, vals[0])
	fmt.Printf("WHERE id=$%d\n", idx)

	idx = meta.BindWhere(``, vals[1])
	fmt.Printf("AND name=$%d\n", idx)

	fmt.Println(meta.ListWhereValue)

}

Output:

WHERE id=$1
AND name=$2
[1000 JohnDoe]

func (*Meta) Holders added in v0.53.0 

func (meta *Meta) Holders() string

Holders generate string of holder, for example "$1, $2, ...", for DML INSERT-VALUES.

Example (Mysql) 
package main

import (
	"fmt"

	"github.com/shuLhan/share/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta = sql.NewMeta(sql.DriverNameMysql, sql.DMLKindInsert)
		t    = Table{Name: `newname`, ID: 2}
	)

	meta.Bind(`id`, &t.ID)
	meta.Bind(`name`, &t.Name)

	fmt.Printf("INSERT INTO t VALUES (%s);\n", meta.Holders())
}

Output:

INSERT INTO t VALUES (?,?);
Example (Postgres) 
package main

import (
	"fmt"

	"github.com/shuLhan/share/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindInsert)
		t    = Table{Name: `newname`, ID: 2}
	)

	meta.Bind(`id`, &t.ID)
	meta.Bind(`name`, &t.Name)

	fmt.Printf("INSERT INTO t VALUES (%s);\n", meta.Holders())
}

Output:

INSERT INTO t VALUES ($1,$2);

func (*Meta) Names added in v0.53.0 

func (meta *Meta) Names() string

Names generate string of column names, for example "col1, col2, ...", for DML INSERT or SELECT.

It will return an empty string if kind is DML UPDATE or DELETE.

Example 
package main

import (
	"fmt"

	"github.com/shuLhan/share/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)
		t    = Table{}
	)

	meta.Bind(`id`, &t.ID)
	meta.Bind(`name`, &t.Name)

	fmt.Printf("SELECT %s FROM t;\n", meta.Names())
}

Output:

SELECT id,name FROM t;

func (*Meta) Sub added in v0.53.0 

func (meta *Meta) Sub() (sub *Meta)

Sub return the child of Meta for building subquery.

Example 
package main

import (
	"fmt"

	"github.com/shuLhan/share/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)
		t    = Table{}
		qid  = 1
	)

	meta.Bind(`id`, &t.ID)
	meta.Bind(`name`, &t.Name)
	meta.BindWhere(`id`, qid)

	var (
		metain = meta.Sub()
		qnames = []string{`hello`, `world`}
	)

	metain.BindWhere(``, qnames[0])
	metain.BindWhere(``, qnames[1])

	var q = fmt.Sprintf(`SELECT %s FROM t WHERE id=$1 OR name IN (%s);`,
		meta.Names(), metain.Holders())

	var qparams = sql.JoinValues(meta.ListWhereValue, metain.ListWhereValue)

	// db.QueryRow(q, qparams...).Scan(meta.ListValue...)

	fmt.Println(q)
	fmt.Println(`SELECT #n=`, len(meta.ListValue))
	fmt.Println(`WHERE=`, meta.ListWhereValue)
	fmt.Println(`WHERE IN=`, metain.ListWhereValue)
	fmt.Println(`qparams=`, qparams)

}

Output:

SELECT id,name FROM t WHERE id=$1 OR name IN ($2,$3);
SELECT #n= 2
WHERE= [1]
WHERE IN= [hello world]
qparams= [1 hello world]

func (*Meta) UpdateFields added in v0.53.0 

func (meta *Meta) UpdateFields() string

UpdateFields generate string of "col1=<holder>, col2=<holder>, ..." for DML UPDATE.

It will return an empty string if kind is not UPDATE.

Example 
package main

import (
	"fmt"

	"github.com/shuLhan/share/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindUpdate)
		t    = Table{
			ID:   2,
			Name: `world`,
		}
		qid   = 1
		qname = `hello`
	)

	meta.Bind(`id`, t.ID)
	meta.Bind(`name`, t.Name)
	meta.BindWhere(`id=`, qid)
	meta.BindWhere(`AND name=`, qname)

	var q = fmt.Sprintf(`UPDATE t SET %s WHERE %s;`, meta.UpdateFields(), meta.WhereFields())

	// db.Exec(q, meta.UpdateValues()...);

	fmt.Println(q)
	fmt.Println(`SET=`, meta.ListValue)
	fmt.Println(`WHERE=`, meta.ListWhereValue)
	fmt.Println(`Exec=`, meta.UpdateValues())

}

Output:

UPDATE t SET id=$1,name=$2 WHERE id=$3 AND name=$4;
SET= [2 world]
WHERE= [1 hello]
Exec= [2 world 1 hello]

func (*Meta) UpdateValues added in v0.53.0 

func (meta *Meta) UpdateValues() (listVal []any)

UpdateValues return the merged of ListValue and ListWhereValue for DML UPDATE.

It will return nil if kind is not DML UPDATE.

Example 
package main

import (
	"fmt"

	"github.com/shuLhan/share/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindUpdate)
		t    = Table{
			ID:   2,
			Name: `world`,
		}
		qid   = 1
		qname = `hello`
	)

	meta.Bind(`id`, t.ID)
	meta.Bind(`name`, t.Name)
	meta.BindWhere(`id`, qid)
	meta.BindWhere(`name`, qname)

	var q = fmt.Sprintf(`UPDATE t SET id=$%d,name=$%d WHERE id=$%d AND name=$%d;`, meta.Index...)

	// db.Exec(q, meta.UpdateValues()...);

	fmt.Println(q)
	fmt.Println(`Index=`, meta.Index)
	fmt.Println(`SET=`, meta.ListValue)
	fmt.Println(`WHERE=`, meta.ListWhereValue)
	fmt.Println(`Exec=`, meta.UpdateValues())

}

Output:

UPDATE t SET id=$1,name=$2 WHERE id=$3 AND name=$4;
Index= [1 2 3 4]
SET= [2 world]
WHERE= [1 hello]
Exec= [2 world 1 hello]

func (*Meta) WhereFields added in v0.53.0 

func (meta *Meta) WhereFields() string

WhereFields merge the ListWhereCond and ListHolder.

It will return an empty string if kind is DML INSERT.

Example 
package main

import (
	"fmt"

	"github.com/shuLhan/share/lib/sql"
)

func main() {
	var meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)

	meta.BindWhere(`id=`, 1000)
	meta.BindWhere(`AND name=`, `share`)

	fmt.Printf(`SELECT * FROM t WHERE %s;`, meta.WhereFields())
}

Output:

SELECT * FROM t WHERE id=$1 AND name=$2;

func (*Meta) WhereHolders added in v0.53.0 

func (meta *Meta) WhereHolders() string

WhereHolders generate string of holder, for example "$1,$2, ...", based on number of item added with Meta.BindWhere. Similar to method Holders but for where condition.

It will return an empty string if kind is DML INSERT.

Example 
package main

import (
	"fmt"

	"github.com/shuLhan/share/lib/sql"
)

func main() {
	var meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)

	meta.BindWhere(`id`, 1000)
	meta.BindWhere(`name`, `share`)

	fmt.Printf(`SELECT * FROM t WHERE id IN (%s);`, meta.WhereHolders())
}

Output:

SELECT * FROM t WHERE id IN ($1,$2);

type Row 

type Row map[string]interface{}

Row represent a column-name and value in a tuple. The map's key is the column name in database and the map's value is the column's value. This type can be used to create dynamic insert-update fields.

DEPRECATED: use Meta instead.

func (Row) ExtractSQLFields 

func (row Row) ExtractSQLFields(driverName string) (names, holders []string, values []interface{})

ExtractSQLFields extract the column's name, column place holder, and column values as slices.

The driverName define the returned place holders. If the driverName is "postgres" then the list of holders will be returned as counter, for example "$1", "$2" and so on. If the driverName is "mysql" or empty or unknown the the list of holders will be returned as list of "?".

The returned names will be sorted in ascending order.

Example 
row := Row{
	"col_3": "'update'",
	"col_2": 1,
	"col_1": true,
}
names, holders, values := row.ExtractSQLFields("?")
fnames := strings.Join(names, ",")
fholders := strings.Join(holders, ",")
q := `INSERT INTO table (` + fnames + `) VALUES (` + fholders + `)`
fmt.Printf("Query: %s\n", q)

// err := db.Exec(q, values...)
fmt.Println(values)

names, holders, values = row.ExtractSQLFields("postgres")
fnames = strings.Join(names, ",")
fholders = strings.Join(holders, ",")
q = `INSERT INTO table (` + fnames + `) VALUES (` + fholders + `)`
fmt.Printf("Query for PostgreSQL: %s\n", q)

// err := db.Exec(q, values...)
fmt.Println(values)

Output:

Query: INSERT INTO table (col_1,col_2,col_3) VALUES (?,?,?)
[true 1 'update']
Query for PostgreSQL: INSERT INTO table (col_1,col_2,col_3) VALUES ($1,$2,$3)
[true 1 'update']

func (Row) Meta added in v0.53.0 

func (row Row) Meta(driverName string) (meta *Meta)

Meta convert the Row into Meta.

type Session 

type Session interface {
	Exec(query string, args ...interface{}) (sql.Result, error)
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	Prepare(query string) (*sql.Stmt, error)
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
	Query(query string, args ...interface{}) (*sql.Rows, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRow(query string, args ...interface{}) *sql.Row
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

Session is an interface that represent both sql.DB and sql.Tx.

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.