sqlquery

package
v0.1.10 Latest Latest
Warning

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

 Go to latest Published: Nov 12, 2020 License: MIT Imports: 9 Imported by: 0

Documentation

Overview

sqlquery is a simple programmatically sql query builder. The idea was to create a unique Builder which can be used with any database driver in go.

Features: Unique Placeholder for all database drivers, Batching function for large Inserts, Whitelist, Quote Identifiers, SQL queries and durations log debugging

Example

Example of the basic usage of the builder. For more details please see the Examples in the methods. 

package main

import (
	"fmt"

	"github.com/patrickascher/gofw/sqlquery"
)

func main() {
	cfg := sqlquery.Config{
		Driver:   "mysql",
		Host:     "127.0.0.1",
		Port:     3319,
		Username: "root",
		Password: "root",
		Database: "gofw",
	}

	// Builder from config
	builder, err := sqlquery.New(cfg, nil)
	if err != nil {
		//...
	}

	// Select stmt
	row, err := builder.Select("users").First()
	if err != nil {
		//...
	}
	fmt.Println(row)

	// Insert stmt
	rows, err := builder.Insert("users").Values([]map[string]interface{}{{"id:": 1, "name": "John", "surname": "Doe"}}).Exec()
	if err != nil {
		//...
	}
	fmt.Println(rows)

	// Update stmt
	res, err := builder.Update("users").Set(map[string]interface{}{"name": "John", "surname": "Doe"}).Where("id = ?", 1).Exec()
	if err != nil {
		//...
	}
	fmt.Println(res)

	// Delete stmt
	res, err = builder.Delete("users").Where("id = ?", 1).Exec()
	if err != nil {
		//...
	}
	fmt.Println(res)

	// Describe stmt
	cols, err := builder.Information("users").Describe()
	if err != nil {
		//...
	}
	fmt.Println(cols)

	// ForeignKey stmt
	fks, err := builder.Information("users").ForeignKeys()
	if err != nil {
		//...
	}
	fmt.Println(fks)
}

Output:

Index

Examples

Constants

View Source 
const (
	WHERE = iota + 1
	HAVING
	LIMIT
	ORDER
	OFFSET
	GROUP
	ON
)

Allowed conditions

View Source 
const (
	LEFT = iota + 1
	RIGHT
	INNER
)

Allowed join types.

View Source 
const BATCH = 200

BATCH - default value for batching the insert stmt. TODO bug when batching only working if it exactly matches the value otherwise sql expected 300 arguments, got 18 error TODO to fix this, we have to add a stmt as slice.

View Source 
const PLACEHOLDER = "?"
View Source 
const PLACEHOLDER_APPEND = "§§"

PLACEHOLDER character.

Variables

View Source 
var (
	ErrArgumentType        = errors.New("sqlquery: this argument type %v is not allowed")
	ErrPlaceholderMismatch = errors.New("sqlquery: %v placeholder(%v) and arguments(%v) does not fit")
)

Error messages.

View Source 
var (
	ErrUnknownProvider       = errors.New("sqlquery/driver: unknown driver %q")
	ErrNoProvider            = errors.New("sqlquery/driver: empty driver-name or driver is nil")
	ErrProviderAlreadyExists = errors.New("sqlquery/driver: driver %#v is already registered")
)

Error messages.

View Source 
var (
	ErrValueMissing = errors.New("sqlquery: No value is set")
	ErrColumn       = errors.New("sqlquery: Column %v does not exist")
)

Error messages.

View Source 
var (
	ErrJoinType = errors.New("sqlquery: wrong join type %#v is used")
)

Error messages.

View Source 
var (
	ErrNoTx = errors.New("sqlquery: no tx exists")
)

Error messages.

Functions

func Raw 

func Raw(c string) string

Raw can be used if the identifier should not be quoted.

func Register 

func Register(name string, driver driver) error

Register the sqlquery drive. This should be called in the init() of the providers. If the sqlquery name/driver is empty or is already registered, an error will return.

Types

type Builder 

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

Builder type.

func New 

func New(cfg Config, db *sql.DB) (Builder, error)

New Builder instance with the given configuration. If the db argument is nil, a new db connection will be created. It is highly recommended to use one open connection to avoid overhead. TODO: Idea: only one interface argument, in a type select we can figure out if it was a config or *sql.DB.

Example 
package main

import (
	"database/sql"
	"fmt"

	"github.com/patrickascher/gofw/sqlquery"
)

func main() {
	cfg := sqlquery.Config{
		Driver:   "mysql",
		Host:     "127.0.0.1",
		Port:     3319,
		Username: "root",
		Password: "root",
		Database: "gofw",
	}

	// Builder from config.
	builder, err := sqlquery.New(cfg, nil)
	if err != nil {
		//...
	}
	fmt.Println(builder)

	// Builder from adapter - like this a global open pool could be provided.
	db, err := sql.Open("mysql", "dns")
	if err != nil {
		//...
	}
	builder, err = sqlquery.New(cfg, db)
	if err != nil {
		//...
	}
	fmt.Println(builder)
}

Output:

func (*Builder) Commit 

func (b *Builder) Commit() error

Commit the builder transaction. Error will return if no transaction was created or there is a commit error.

func (Builder) Config 

func (b Builder) Config() Config

func (*Builder) Delete 

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

Delete - please see the Delete type documentation.

Example 
package main

import (
	"fmt"

	"github.com/patrickascher/gofw/sqlquery"
)

func main() {

	// Builder from config.
	b, err := sqlquery.New(sqlquery.Config{}, nil)
	if err != nil {
		//...
	}

	res, err := b.Delete("users").
		Where("id = ?", 2).
		Exec() //execute

	if err != nil {
		//...
	}
	fmt.Println(res)
}

Output:

func (*Builder) Driver 

func (b *Builder) Driver() DriverI

func (*Builder) HasTx 

func (b *Builder) HasTx() bool

func (*Builder) Information 

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

Information - please see the Information type documentation.

Example 
package main

import (
	"fmt"

	"github.com/patrickascher/gofw/sqlquery"
)

func main() {

	// Builder from config.
	b, err := sqlquery.New(sqlquery.Config{}, nil)
	if err != nil {
		//...
	}

	// Describe table columns.
	cols, err := b.Information("users").Describe("id", "name")
	if err != nil {
		//...
	}
	fmt.Println(cols)

	// FKs of the table.
	fks, err := b.Information("users").ForeignKeys()
	if err != nil {
		//...
	}
	fmt.Println(fks)
}

Output:

func (*Builder) Insert 

func (b *Builder) Insert(into string) *Insert

Insert - please see the Select type documentation.

Example 
package main

import (
	"fmt"

	"github.com/patrickascher/gofw/sqlquery"
)

func main() {

	// Builder from config.
	b, err := sqlquery.New(sqlquery.Config{}, nil)
	if err != nil {
		//...
	}

	lastID := 0
	res, err := b.Insert("users").
		Batch(1).                                                    //if more than 1 values are set, a batching would be executed.
		Columns("id", "name").                                       // set specific columns order
		Values([]map[string]interface{}{{"id": 1, "name": "John"}}). // values
		LastInsertedID("id", &lastID).                               // set last inserted id
		Exec()                                                       //execute

	if err != nil {
		//...
	}
	fmt.Println(res)
}

Output:

func (Builder) QuoteIdentifier 

func (b Builder) QuoteIdentifier(col string) string

QuoteIdentifier by the driver quote character.

func (*Builder) Rollback 

func (b *Builder) Rollback() error

Rollback the builder transaction. Error will return if no transaction was created or there is a rollback error.

func (*Builder) Select 

func (b *Builder) Select(from string) *Select

Select - please see the Select type documentation.

Example 
// Builder from config.
b, err := sqlquery.New(sqlquery.Config{}, nil)
if err != nil {
	//...
}

// select a single row
row, err := b.Select("users").
	Columns("id", "name").
	Where("id = ?", 1).
	First()
if err != nil {
	//...
}
fmt.Println(row)

// select all rows
jc := sqlquery.Condition{}
rows, err := b.Select("users").
	Columns("id", "name").
	Where("id > ?", 10).
	Limit(10).
	Offset(5).
	Order("surname").
	Group("age").
	Join(sqlquery.LEFT, "company", jc.On("user.company = company.id AND company.branches > ?", 2)).
	All()
if err != nil {
	//...
}
_ = rows.Close()
fmt.Println(rows)

Output:

func (*Builder) SetLogger 

func (b *Builder) SetLogger(l *logger.Logger)

func (*Builder) Tx 

func (b *Builder) Tx() error

Tx creates a new transaction for the builder. If a tx exists, all requests (select, update, insert, delete) will be handled in that transaction.

Example 
// Builder from config.
b, err := sqlquery.New(sqlquery.Config{}, nil)
if err != nil {
	//...
}

// start tx
err = b.Tx()
if err != nil {
	//...
}

// some bundled stmts
_, err = b.Update("users").Set(map[string]interface{}{"id": 1, "name": "John"}).Where("id = ?", 1).Exec()
if err != nil {
	err = b.Rollback()
	//...
}
_, err = b.Delete("users").Where("id = ?", 10).Exec()
if err != nil {
	err = b.Rollback()
	//...
}

// commit tx
err = b.Commit()
if err != nil {
	//...
}

Output:

func (*Builder) Update 

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

Update - please see the Update type documentation.

Example 
package main

import (
	"fmt"

	"github.com/patrickascher/gofw/sqlquery"
)

func main() {

	// Builder from config.
	b, err := sqlquery.New(sqlquery.Config{}, nil)
	if err != nil {
		//...
	}

	res, err := b.Update("users").
		Columns("id", "name").
		Set(map[string]interface{}{"name": "Bar"}).
		Where("id = ?", 2).
		Exec() //execute

	if err != nil {
		//...
	}
	fmt.Println(res)
}

Output:

type Column 

type Column struct {
	Table         string
	Name          string
	Position      int
	NullAble      bool
	PrimaryKey    bool
	Unique        bool
	Type          types.Interface
	DefaultValue  sql.NullString
	Length        sql.NullInt64
	Autoincrement bool
}

Column represents a database table column.

type Condition 

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

Condition type.

func Copy 

func Copy(c *Condition) *Condition

func NewCondition 

func NewCondition() *Condition

func (*Condition) Config 

func (c *Condition) Config(values bool, condition ...int) string

Config returns the requested condition. The first argument defines if the result should include the values or not. Caution, the values are not escaped and should only be used for test or debugging. On database level these are placeholders and getting escaped by the driver. 

c.Config(false,WHERE,ORDER) // WHERE and ORDER are being rendered.

func (*Condition) Group 

func (c *Condition) Group(group ...string) *Condition

Group condition. Group should only be called once. If its called more often, the last values count. Column names are not quoted TODO?. 

c.Group("id","name") // GROUP BY id, name

func (*Condition) Having 

func (c *Condition) Having(stmt string, args ...interface{}) *Condition

Having condition. Having can be called multiple times on a sql statement and gets chained by AND. If you need an OR Condition, be aware to set the right brackets or write the whole condition in one HAVING call. Arrays and slices can be passed as argument. 

c.Having("amount > ?",100)
c.Having("id IN (?)",[]int{10,11,12})

func (*Condition) Limit 

func (c *Condition) Limit(l int) *Condition

Limit condition. Limit should be called once. If its called more often, the last values count. 

c.Limit(10)

func (*Condition) Offset 

func (c *Condition) Offset(o int) *Condition

Offset condition. Offset should be called once. If its called more often, the last values count. 

c.Offset(5)

func (*Condition) On 

func (c *Condition) On(stmt string, args ...interface{}) *Condition

On condition for sql joins. On should only be called once. If its called more often, the last values count. Arrays and slices can be passed as argument. 

c.On("user.company = company.id AND user.id > ?",100)

func (*Condition) Order 

func (c *Condition) Order(order ...string) *Condition

Order condition. If a column has a `-` prefix, DESC order will get set. Order should only be called once. If its called more often, the last values count. Column names are not quoted TODO?. 

c.Order("id","-name") // ORDER BY id ASC, name DESC

func (*Condition) Reset 

func (c *Condition) Reset(reset ...int)

Reset the condition by one or more conditions. If the argument is empty, all conditions are reset. 

c.Reset() // all will be reset
c.Reset(WHERE,HAVING) // only WHERE and HAVING are reset.

func (*Condition) SetWhere 

func (c *Condition) SetWhere(w string)

DEPRECATED - this is just a quick fix. the wholde sqlquery.condition has to get rewritten because of the where string. not enough manipulation chances.

func (*Condition) Where 

func (c *Condition) Where(stmt string, args ...interface{}) *Condition

Where condition. Where can be called multiple times on a sql statement and gets chained by AND. If you need an OR Condition, be aware to set the right brackets or write the whole condition in one WHERE call. Arrays and slices can be passed as argument. 

c.Where("id = ?",1)
c.Where("id IN (?)",[]int{10,11,12})

type Config 

type Config struct {
	Name               string `json:"name"`
	Driver             string `json:"driver"`
	Host               string `json:"host"`
	Port               int    `json:"port"`
	Username           string `json:"username"`
	Password           string `json:"password"`
	Database           string `json:"database"`
	Schema             string `json:"schema"`
	MaxOpenConnections int    `json:"maxOpenConnections"`
	MaxIdleConnections int    `json:"maxIdleConnections"`
	MaxConnLifetime    int    `json:"maxConnLifetime"` // in Minutes
	Debug              bool   `json:"debug"`
	PreQuery           string `json:"preQuery"`
}

Config stores all information about the database.

type Delete 

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

Delete type.

func (*Delete) Condition 

func (s *Delete) Condition(c *Condition) *Delete

Condition adds your own condition to the stmt. Only WHERE conditions are allowed.

func (*Delete) Exec 

func (s *Delete) Exec() (sql.Result, error)

Exec the sql query through the Builder. An error will return if the arguments and placeholders mismatch or the sql.Exec creates with an error.

func (*Delete) String 

func (s *Delete) String() (stmt string, args []interface{}, err error)

String returns the statement and arguments. An error will return if the arguments and placeholders mismatch.

func (*Delete) Where 

func (s *Delete) Where(stmt string, args ...interface{}) *Delete

Where - please see the Condition.Where documentation.

type DriverI 

type DriverI interface {
	// Connection should return an open connection. The implementation depends on the driver.
	// As proposal, a connection should be unique to avoid overhead. Store it in on package level if its created.
	Connection() *sql.DB

	// The character which will be used for quoting columns.
	QuoteCharacterColumn() string

	// Describe the columns of the given table.
	Describe(b *Builder, db string, table string, cols []string) ([]Column, error)

	// ForeignKeys of the given table.
	ForeignKeys(b *Builder, db string, table string) ([]*ForeignKey, error)

	// Placeholder for the go driver.
	Placeholder() *Placeholder

	// Config returns the given configuration of the driver.
	Config() Config

	// TypeMapping should unify the different column types of different database types.
	TypeMapping(string, Column) types.Interface
}

Driver interface.

type ForeignKey 

type ForeignKey struct {
	Name      string
	Primary   Relation
	Secondary Relation
}

ForeignKey represents a table relation. TODO: already define the relation type? 1:1,1:n,n:n?

type Information 

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

Information struct.

func (Information) Describe 

func (i Information) Describe(columns ...string) ([]Column, error)

Describe the table columns. Specific columns can be set, if empty every column will be described. By default the configure database is used, except the table name has a dot notation.

func (Information) ForeignKeys 

func (i Information) ForeignKeys() ([]*ForeignKey, error)

ForeignKeys of table. By default the configure database is used, except the table name has a dot notation.

type Insert 

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

Insert type.

func (*Insert) Batch 

func (s *Insert) Batch(b int) *Insert

Batch sets the batching size.

func (*Insert) Columns 

func (s *Insert) Columns(c ...string) *Insert

Columns define a fixed column order for the insert.

func (*Insert) Exec 

func (s *Insert) Exec() ([]sql.Result, error)

Exec the sql query through the Builder. If a LastInsertedID is set, the ptr will be set. An error will return if the arguments and placeholders mismatch or no value was set.

func (*Insert) LastInsertedID 

func (s *Insert) LastInsertedID(column string, ID interface{}) *Insert

LastInsertedID gets the last id over different drivers.

func (*Insert) String 

func (s *Insert) String() (stmt string, args [][]interface{}, err error)

String returns the statement and arguments An error will return if the arguments and placeholders mismatch or no value was set.

func (*Insert) Values 

func (s *Insert) Values(values []map[string]interface{}) *Insert

Values set the column/value pair.

type Placeholder 

type Placeholder struct {
	Numeric bool // must be true if the database uses something like $1,$2,...

	Char string // database placeholder character
	// contains filtered or unexported fields
}

Placeholder is used to ensure an unique placeholder for different database adapters.

type Relation 

type Relation struct {
	Table  string
	Column string
}

Relation defines the table and column of a relation.

type Select 

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

Select type.

func (Select) All 

func (s Select) All() (*sql.Rows, error)

All returns the found rows by using the *db.Query method. All returns a *sql.Rows, dont forget to Close it! An error will return if the arguments and placeholders mismatch.

func (*Select) Columns 

func (s *Select) Columns(cols ...string) *Select

Columns set new columns to the select stmt. If no columns are added, the * will be used.

func (*Select) Condition 

func (s *Select) Condition(c *Condition) *Select

Condition adds your own condition to the stmt.

func (Select) First 

func (s Select) First() (*sql.Row, error)

First will return only one row. Its a wrapper for DB.QueryRow

func (*Select) Group 

func (s *Select) Group(group ...string) *Select

Group - please see the Condition.Group documentation.

func (*Select) Having 

func (s *Select) Having(stmt string, args ...interface{}) *Select

Having - please see the Condition.Having documentation.

func (*Select) Join 

func (s *Select) Join(joinType int, table string, condition *Condition) *Select

Join - please see the Condition.Join documentation.

func (*Select) Limit 

func (s *Select) Limit(l int) *Select

Limit - please see the Condition.Limit documentation.

func (*Select) Offset 

func (s *Select) Offset(l int) *Select

Offset - please see the Condition.Offset documentation.

func (*Select) Order 

func (s *Select) Order(order ...string) *Select

Order - please see the Condition.Order documentation.

func (Select) String 

func (s Select) String() (string, []interface{}, error)

String returns the statement and arguments. An error will return if the arguments and placeholders mismatch.

func (*Select) Where 

func (s *Select) Where(stmt string, args ...interface{}) *Select

Where - please see the Condition.Where documentation.

type Update 

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

Update type.

func (*Update) Columns 

func (s *Update) Columns(c ...string) *Update

Columns define a fixed column order

func (*Update) Condition 

func (s *Update) Condition(c *Condition) *Update

Condition adds a ptr to a existing condition.

func (*Update) Exec 

func (s *Update) Exec() (sql.Result, error)

Exec the sql query through the Builder. An error will return if the arguments and placeholders mismatch, no value was set or the sql query returns one.

func (*Update) Set 

func (s *Update) Set(values map[string]interface{}) *Update

Set the column/value pair

func (*Update) String 

func (s *Update) String() (stmt string, args []interface{}, err error)

String returns the statement and arguments An error will return if the arguments and placeholders mismatch or no value was set.

func (*Update) Where 

func (s *Update) Where(stmt string, args ...interface{}) *Update

Where - please see the Condition.Where documentation.

Notes

Bugs

  • condition/render this logic fails if the placeholder is numeric and has the same char as placeholder.

Source Files

View all Source files

Directories

Path Synopsis
mysql
Package driver contains some out of the box db drivers which implement the sqlquery.Driver interface.
 Package driver contains some out of the box db drivers which implement the sqlquery.Driver interface.
oracle
Package driver contains some out of the box db drivers which implement the sqlquery.Driver interface.
 Package driver contains some out of the box db drivers which implement the sqlquery.Driver interface.

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.