Documentation ¶
Overview ¶
Package sql is an extension to standard library "database/sql.DB" that provide common functionality across DBMS.
Index ¶
- Constants
- func JoinValues(s ...[]any) (all []any)
- type Client
- type ClientOptions
- type DMLKind
- type Meta
- func (meta *Meta) Bind(colName string, val any)
- func (meta *Meta) BindWhere(cond string, val any) int
- func (meta *Meta) Holders() string
- func (meta *Meta) Names() string
- func (meta *Meta) Sub() (sub *Meta)
- func (meta *Meta) UpdateFields() string
- func (meta *Meta) UpdateValues() (listVal []any)
- func (meta *Meta) WhereFields() string
- func (meta *Meta) WhereHolders() string
- type Row
- type Session
Examples ¶
- Meta (DeleteOnPostgresql)
- Meta (InsertOnPostgresql)
- Meta (SelectOnPostgresql)
- Meta (Subquery)
- Meta (SubqueryWithIndex)
- Meta.Bind
- Meta.BindWhere
- Meta.Holders (Mysql)
- Meta.Holders (Postgres)
- Meta.Names
- Meta.Sub
- Meta.UpdateFields
- Meta.UpdateValues
- Meta.WhereFields
- Meta.WhereHolders
- Row.ExtractSQLFields
Constants ¶
const ( DriverNameMysql = "mysql" DriverNamePostgres = "postgres" )
List of known driver name for database connection.
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
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 ¶
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 ¶
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
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).
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
NewMeta create new Meta using specific driver name. The driver affect the ListHolder value.
func (*Meta) Bind ¶ added in v0.53.0
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
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
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
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
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
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
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
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
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 ¶
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']
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.