Help us shape the future of Go! Take the 2026 Go Developer Survey

sql

package
v0.2.1 Latest Latest
Warning

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

 Go to latest Published: Oct 17, 2025 License: Apache-2.0 Imports: 16 Imported by: 2

Documentation

Overview

package sqlx provides a is a wrap of golang database/sql package to hide logic for different drivers, the three main functions of this package are: 1. generate query from HTTP input 2. execute query against different SQL databases 3. provide helper functions to get meta information from database

Index

Constants

View Source 
const (
	UNKNOWN = iota
	QUESTION
	DOLLAR
)

Bindvar types supported by Rebind, BindMap and BindStruct.

View Source 
const (
	// PG errors
	// https://www.postgresql.org/docs/current/errcodes-appendix.html
	PGIntegrityConstraintViolation = "23"
	PGSyntaxError                  = "42"

	// MySQL errors
	// https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
	MYErrNoDefaultForField = 1364

	// SQLite errors
	// https://www.sqlite.org/rescode.html
	SQLiteConstraintNotNULL    = 1299
	SQLiteConstraintPrimaryKey = 1555
	SQLiteConstraintUnique     = 2067
)
View Source 
const DefaultTimeout = 2 * time.Minute

DefaultTimeout for all database operations

Variables

View Source 
var (

	// TODO: benchmark performance of regexp match VS map access
	// the code below could be simplified by using regexp, but declare it in a
	// map should result in better performance in theory.
	Types = map[string]func() any{
		"TINYINT":     func() any { return new(sql.NullInt64) },
		"SMALLINT":    func() any { return new(sql.NullInt64) },
		"SMALLSERIAL": func() any { return new(sql.NullInt64) },
		"SERIAL":      func() any { return new(sql.NullInt64) },
		"INT":         func() any { return new(sql.NullInt64) },
		"INTEGER":     func() any { return new(sql.NullInt64) },
		"BIGINT":      func() any { return new(sql.NullInt64) },
		"BIGSERIAL":   func() any { return new(sql.NullInt64) },

		"DEC":              func() any { return new(sql.NullFloat64) },
		"DECIMAL":          func() any { return new(sql.NullFloat64) },
		"NUMERIC":          func() any { return new(sql.NullFloat64) },
		"FLOAT":            func() any { return new(sql.NullFloat64) },
		"REAL":             func() any { return new(sql.NullFloat64) },
		"DOUBLE":           func() any { return new(sql.NullFloat64) },
		"DOUBLE PRECISION": func() any { return new(sql.NullFloat64) },

		"BOOL":    func() any { return new(sql.NullBool) },
		"BOOLEAN": func() any { return new(sql.NullBool) },

		"JSON": func() any { return new(sql.NullString) },

		"CHAR":      func() any { return new(sql.NullString) },
		"VARCHAR":   func() any { return new(sql.NullString) },
		"NVARCHAR":  func() any { return new(sql.NullString) },
		"TEXT":      func() any { return new(sql.NullString) },
		"UUID":      func() any { return new(sql.NullString) },
		"ENUM":      func() any { return new(sql.NullString) },
		"BLOB":      func() any { return new(sql.NullString) },
		"BINARY":    func() any { return new(sql.NullString) },
		"XML":       func() any { return new(sql.NullString) },
		"DATE":      func() any { return new(sql.NullString) },
		"DATETIME":  func() any { return new(sql.NullString) },
		"TIMESTAMP": func() any { return new(sql.NullString) },
	}

	TypeConverters = map[string]TypeConverter{
		"TINYINT":     func(i any) any { return i.(*sql.NullInt64).Int64 },
		"SMALLINT":    func(i any) any { return i.(*sql.NullInt64).Int64 },
		"SMALLSERIAL": func(i any) any { return i.(*sql.NullInt64).Int64 },
		"SERIAL":      func(i any) any { return i.(*sql.NullInt64).Int64 },
		"INT":         func(i any) any { return i.(*sql.NullInt64).Int64 },
		"INTEGER":     func(i any) any { return i.(*sql.NullInt64).Int64 },
		"BIGINT":      func(i any) any { return i.(*sql.NullInt64).Int64 },
		"BIGSERIAL":   func(i any) any { return i.(*sql.NullInt64).Int64 },

		"DEC":              func(i any) any { return i.(*sql.NullFloat64).Float64 },
		"DECIMAL":          func(i any) any { return i.(*sql.NullFloat64).Float64 },
		"NUMERIC":          func(i any) any { return i.(*sql.NullFloat64).Float64 },
		"FLOAT":            func(i any) any { return i.(*sql.NullFloat64).Float64 },
		"REAL":             func(i any) any { return i.(*sql.NullFloat64).Float64 },
		"DOUBLE":           func(i any) any { return i.(*sql.NullFloat64).Float64 },
		"DOUBLE PRECISION": func(i any) any { return i.(*sql.NullFloat64).Float64 },

		"BOOL":    func(i any) any { return i.(*sql.NullBool).Bool },
		"BOOLEAN": func(i any) any { return i.(*sql.NullBool).Bool },

		"CHAR":      func(i any) any { return i.(*sql.NullString).String },
		"VARCHAR":   func(i any) any { return i.(*sql.NullString).String },
		"NVARCHAR":  func(i any) any { return i.(*sql.NullString).String },
		"TEXT":      func(i any) any { return i.(*sql.NullString).String },
		"UUID":      func(i any) any { return i.(*sql.NullString).String },
		"ENUM":      func(i any) any { return i.(*sql.NullString).String },
		"BLOB":      func(i any) any { return i.(*sql.NullString).String },
		"BINARY":    func(i any) any { return i.(*sql.NullString).String },
		"XML":       func(i any) any { return i.(*sql.NullString).String },
		"DATE":      func(i any) any { return i.(*sql.NullString).String },
		"DATETIME":  func(i any) any { return i.(*sql.NullString).String },
		"TIMESTAMP": func(i any) any { return i.(*sql.NullString).String },

		"JSON": func(i any) any {
			rawData := i.(*sql.NullString).String
			if s, err := strconv.ParseFloat(rawData, 64); err == nil {
				return s
			}
			if s, err := strconv.ParseBool(rawData); err == nil {
				return s
			}
			return rawData
		},
	}

	Operators = map[string]string{
		"eq":    " = ",
		"ne":    " <> ",
		"gt":    " > ",
		"lt":    " < ",
		"gte":   " >= ",
		"lte":   " <= ",
		"like":  " like ",
		"ilike": " ilike ",
		"is":    " is ",
		"in":    " in ",
		"cs":    " @> ",
		"cd":    " <@ ",
	}

	ReservedWords = map[string]struct{}{
		"select": {},
		"order":  {},
		"count":  {},
	}
)

Functions

func Rebind 

func Rebind(driverName, query string) string

Rebind a query from the default driverName(mysql) to the target driverName.

Types

type Column 

type Column struct {
	ColumnName string `json:"column_name"`
	DataType   string `json:"data_type"`
	NotNull    bool   `json:"notnull"`
	Pk         bool   `json:"pk"`
}

Column represents a table column with name and type

func (*Column) String 

func (c *Column) String() string

type DB 

type DB struct {
	*stdSQL.DB
	DriverName string
}

DB is a wrapper of the golang database/sql DB struct with a DriverName to handle generic logic against different SQL database

func Open 

func Open(url string) (*DB, error)

Open connects to database by specify database url and ping it

func (*DB) Close added in v0.1.1 

func (db *DB) Close()

func (*DB) ExecQuery 

func (db *DB) ExecQuery(ctx context.Context, query string, args ...any) (int64, error)

ExecQuery execute and query in database and return rows affected or an error

func (*DB) FetchData 

func (db *DB) FetchData(ctx context.Context, query string, args ...any) ([]map[string]any, error)

FetchData execute query and fetch data from database, it always return an array or error

func (*DB) FetchOne 

func (db *DB) FetchOne(ctx context.Context, query string, args ...any) (map[string]any, error)

FetchOne execute query and fetch data from database, it returns one row or error

func (*DB) FetchTables 

func (db *DB) FetchTables() map[string]*Table

FetchTables return all the tables in current database along with all the columns name and datatype

type DriverType 

type DriverType int
const (
	Postgres DriverType = iota
	MySQL
	SQLite
)

type Error 

type Error struct {
	Code int
	Msg  string
}

Error converts database error to http code

func NewError 

func NewError(code int, msg string) Error

func (Error) Error 

func (e Error) Error() string

type Helper 

type Helper interface {
	GetTablesSQL() string
	GetColumnsSQL(string) string
}

type MyHelper 

type MyHelper struct{}

func (MyHelper) GetColumnsSQL 

func (h MyHelper) GetColumnsSQL(tableName string) string

func (MyHelper) GetTablesSQL 

func (h MyHelper) GetTablesSQL() string

type PGHelper 

type PGHelper struct{}

func (PGHelper) GetColumnsSQL 

func (h PGHelper) GetColumnsSQL(tableName string) string

func (PGHelper) GetTablesSQL 

func (h PGHelper) GetTablesSQL() string

type PostData 

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

func (*PostData) Set 

func (pd *PostData) Set(column string, val any)

Set sets custom column and val to each objects

func (*PostData) SetQuery 

func (pd *PostData) SetQuery(index uint) (*SetQuery, error)

SetQuery return set sql for update TODO: bulk update

func (*PostData) UnmarshalJSON 

func (pd *PostData) UnmarshalJSON(b []byte) error

UnmarshalJSON implements json.Unmarshaler

func (*PostData) ValuesQuery 

func (pd *PostData) ValuesQuery() (*ValuesQuery, error)

valuesQuery convert post data to values query for insertion

type SQLiteHelper 

type SQLiteHelper struct{}

func (SQLiteHelper) GetColumnsSQL 

func (h SQLiteHelper) GetColumnsSQL(tableName string) string

func (SQLiteHelper) GetTablesSQL 

func (h SQLiteHelper) GetTablesSQL() string

type SetQuery 

type SetQuery struct {
	Index uint // index for next field, args number plus 1
	Query string
	Args  []any
}

e.g. UPDATE table SET a="a",b="b" index=3 sql="a=$1, b=$2" args=["a", "b"]

type Table 

type Table struct {
	Name       string
	PrimaryKey string
	Columns    []*Column
}

Table represents a table in database with name and columns

func (*Table) String 

func (t *Table) String() string

type TypeConverter 

type TypeConverter func(any) any

type URLQuery 

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

func NewURLQuery 

func NewURLQuery(values url.Values, driver string) *URLQuery

func (*URLQuery) IsCount 

func (q *URLQuery) IsCount() bool

func (*URLQuery) IsDebug 

func (q *URLQuery) IsDebug() bool

func (*URLQuery) IsMine 

func (q *URLQuery) IsMine() bool

func (*URLQuery) IsSingular 

func (q *URLQuery) IsSingular() bool

func (*URLQuery) OrderQuery 

func (q *URLQuery) OrderQuery() string

OrderQuery returns sql order query string

func (*URLQuery) Page 

func (q *URLQuery) Page() (page, pageSize int)

func (*URLQuery) SelectQuery 

func (q *URLQuery) SelectQuery() (string, error)

SelectQuery return sql projection string

func (*URLQuery) Set 

func (q *URLQuery) Set(key, value string)

func (*URLQuery) WhereQuery 

func (q *URLQuery) WhereQuery(index uint) (newIndex uint, query string, args []any)

WhereQuery returns sql and args for where clause

type ValuesQuery 

type ValuesQuery struct {
	Index        uint // index for next field, args number plus 1
	Columns      []string
	Placeholders []string
	Args         []any
}

e.g. INSERT INTO a (c1, c2) VALUES (v1,v2),(v3,v4) index=4 columns=["c1", "c2"] vals=["$1,$2", "$3,$4"] args=[v1,v2,v3,v4]

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.