tyr

package module
v0.7.1 Latest Latest
Warning

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

 Go to latest Published: Nov 2, 2021 License: MIT Imports: 16 Imported by: 4

README

tyr

Just Simple SQL Builder and DB Helper

$ go get -u github.com/kubuskotak/tyr

import "github.com/kubuskotak/tyr"

Driver support

  • MySQL
  • PostgreSQL
  • SQLite3
  • MsSQL

Examples

SelectStmt with where-value interpolation
buf := NewBuffer()

ids := []int64{1, 2, 3, 4, 5}
Select("*").From("suggestions").Where("id IN ?", ids).ToSQL(dialect.MySQL, buf)
SelectStmt with joins
buf := NewBuffer()

Select("*").From("suggestions").
Join("subdomains", "suggestions.subdomain_id = subdomains.id").ToSQL(dialect.MySQL, buf)

Select("*").From("suggestions").
LeftJoin("subdomains", "suggestions.subdomain_id = subdomains.id").ToSQL(dialect.MySQL, buf)

// join multiple tables
Select("*").From("suggestions").
Join("subdomains", "suggestions.subdomain_id = subdomains.id").
Join("accounts", "subdomains.accounts_id = accounts.id").ToSQL(dialect.MySQL, buf)
SelectStmt with raw SQL
SelectBySql("SELECT `title`, `body` FROM `suggestions` ORDER BY `id` ASC LIMIT 10")
InsertStmt adds data from struct
type Suggestion struct {
ID        int64
Title        NullString
CreatedAt    time.Time
}
sugg := &Suggestion{
Title:        NewNullString("Gopher"),
CreatedAt:    time.Now(),
}

buf := NewBuffer()
InsertInto("suggestions").
Columns("title").
Record(&sugg).ToSQL(dialect.MySQL, buf)
InsertStmt adds data from value
InsertInto("suggestions").
Pair("title", "Gopher").
Pair("body", "I love go.")

Thanks

Inspiration and fork from these awesome libraries:

Documentation

Index

Examples

Constants

View Source 
const (
	POSTGRES string = "postgres"
	MYSQL    string = "mysql"
)

Variables

View Source 
var (
	ErrNotFound           = errors.New("not found")
	ErrNotSupported       = errors.New("not supported")
	ErrTableNotSpecified  = errors.New("table not specified")
	ErrColumnNotSpecified = errors.New("column not specified")
	ErrInvalidPointer     = errors.New("attempt to load into an invalid pointer")
	ErrPlaceholderCount   = errors.New("wrong placeholder count")
	ErrInvalidSliceLength = errors.New("length of slice is 0. length must be >= 1")
	ErrCantConvertToTime  = errors.New("can't convert to time.Time")
	ErrInvalidTimestring  = errors.New("invalid time string")
)

package errors

View Source 
var NameMapping = camelCaseToSnakeCase
View Source 
var Now = nowSentinel{}

Now is a value that serializes to the current time in UTC.

Functions

func GetTagColumns 

func GetTagColumns(structValue interface{}) []string

func InterfaceLoader 

func InterfaceLoader(value, concreteType interface{}) interface{}

func InterpolateForDialect 

func InterpolateForDialect(query string, value []interface{}, d Dialect) (string, error)

InterpolateForDialect replaces placeholder in query with corresponding value in dialect.

It can be also used for debugging custom Builder.

Every time you call database/sql's db.Query("SELECT ...") method, under the hood, the mysql driver will create a prepared statement, execute it, and then throw it away. This has a big performance cost.

gocraft/dbr doesn't use prepared statements. We ported mysql's query escape functionality directly into our package, which means we interpolate all of those question marks with their arguments before they get to MySQL. The result of this is that it's way faster, and just as secure.

Check out these benchmarks from https://github.com/tyler-smith/golang-sql-benchmark.

func Load 

func Load(rows *sql.Rows, value interface{}) (int, error)

Load loads any value from sql.Rows.

value can be:

1. simple type like int64, string, etc.

2. sql.Scanner, which allows loading with custom types.

3. map; the first column from SQL result loaded to the key, and the rest of columns will be loaded into the value. This is useful to dedup SQL result with first column.

4. map of slice; like map, values with the same key are collected with a slice.

func Union 

func Union(builder ...Builder) interface {
	Builder
	As(string) Builder
}

Union builds `... UNION ...`.

func UnionAll 

func UnionAll(builder ...Builder) interface {
	Builder
	As(string) Builder
}

UnionAll builds `... UNION ALL ...`.

Types

type Buffer 

type Buffer interface {
	WriteString(string) (int, error)
	String() string

	WriteValue(v ...interface{}) (err error)
	Value() []interface{}
}

Buffer collects strings, and values that are ready to be interpolated. This is used internally to efficiently build SQL statement.

func NewBuffer 

func NewBuffer() Buffer

NewBuffer creates a new Buffer.

type BuildFunc 

type BuildFunc func(Dialect, Buffer) error

BuildFunc implements Builder.

func (BuildFunc) Build 

func (b BuildFunc) Build(d Dialect, buf Buffer) error

func (BuildFunc) ToSQL 

func (b BuildFunc) ToSQL(d Dialect, buf Buffer) error

ToSQL calls itself to build SQL.

type Builder 

type Builder interface {
	Build(Dialect, Buffer) error
	ToSQL(Dialect, Buffer) error
}

Builder builds SQL in Dialect like MySQL, and PostgreSQL. The raw SQL and values are stored in Buffer.

The core of tyr is interpolation, which can expand ? with arbitrary SQL. If you need a feature that is not currently supported, you can build it on your own (or use Expr).

To do that, the value that you wish to be expanded with ? needs to implement Builder.

func And 

func And(cond ...Builder) Builder

And creates AND from a list of conditions.

Example 
And(
	Or(
		Gt("created_at", "2015-09-10"),
		Lte("created_at", "2015-09-11"),
	),
	Eq("title", "hello world"),
)

Output:

func Eq 

func Eq(column string, value interface{}) Builder

Eq is `=`. When value is nil, it will be translated to `IS NULL`. When value is a slice, it will be translated to `IN`. Otherwise it will be translated to `=`.

func Expr 

func Expr(query string, value ...interface{}) Builder

Expr allows raw expression to be used when current SQL syntax is not supported by gocraft/dbr.

func Gt 

func Gt(column string, value interface{}) Builder

Gt is `>`.

func Gte 

func Gte(column string, value interface{}) Builder

Gte is '>='.

func Like 

func Like(column, value string, escape ...string) Builder

Like is `LIKE`, with an optional `ESCAPE` clause

func Lt 

func Lt(column string, value interface{}) Builder

Lt is '<'.

func Lte 

func Lte(column string, value interface{}) Builder

Lte is `<=`.

func Neq 

func Neq(column string, value interface{}) Builder

Neq is `!=`. When value is nil, it will be translated to `IS NOT NULL`. When value is a slice, it will be translated to `NOT IN`. Otherwise it will be translated to `!=`.

func NotLike 

func NotLike(column, value string, escape ...string) Builder

NotLike is `NOT LIKE`, with an optional `ESCAPE` clause

func Or 

func Or(cond ...Builder) Builder

Or creates OR from a list of conditions.

type Comments 

type Comments []string

Comments represents a set of sql comments

func (Comments) Append 

func (c Comments) Append(comment string) Comments

Append a new sql comment to a set of comments

func (Comments) Build 

func (c Comments) Build(d Dialect, buf Buffer) error

Build writes each comment in the form of "/* some comment */\n"

type DeleteBuilder 

type DeleteBuilder = DeleteStmt

type DeleteStmt 

type DeleteStmt struct {
	Dialect

	Table      string
	WhereCond  []Builder
	LimitCount int64
	// contains filtered or unexported fields
}

DeleteStmt builds `DELETE ...`.

Example 
DeleteFrom("suggestions").Where("id = ?", 1)

Output:

func DeleteBySql 

func DeleteBySql(query string, value ...interface{}) *DeleteStmt

DeleteBySql creates a DeleteStmt from raw query.

func DeleteFrom 

func DeleteFrom(table string) *DeleteStmt

DeleteFrom creates a DeleteStmt.

func (*DeleteStmt) Build 

func (b *DeleteStmt) Build(d Dialect, buf Buffer) error

func (*DeleteStmt) Comment 

func (b *DeleteStmt) Comment(comment string) *DeleteStmt

func (*DeleteStmt) Limit 

func (b *DeleteStmt) Limit(n uint64) *DeleteStmt

func (*DeleteStmt) ToSQL 

func (b *DeleteStmt) ToSQL(d Dialect, i Buffer) error

func (*DeleteStmt) Where 

func (b *DeleteStmt) Where(query interface{}, value ...interface{}) *DeleteStmt

Where adds a where condition. query can be Builder or string. value is used only if query type is string.

type Dialect 

type Dialect interface {
	QuoteIdent(id string) string

	EncodeString(s string) string
	EncodeBool(b bool) string
	EncodeTime(t time.Time) string
	EncodeBytes(b []byte) string

	Placeholder(n int) string
}

Dialect abstracts database driver differences in encoding types, and placeholders.

type Driver 

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

type Error 

type Error struct {
	Code    string
	Message string
}

func CatchErr 

func CatchErr(err error) *Error

func (*Error) Error 

func (e *Error) Error() string

type Event 

type Event struct {
	Type EventType
	Data interface{}
}

type EventFunc 

type EventFunc func(Event)

type EventHandler 

type EventHandler struct {
	Event    Event
	EventMap EventMap
}

func NewEventHandler 

func NewEventHandler() *EventHandler

func (*EventHandler) Dispatcher 

func (h *EventHandler) Dispatcher(ctx context.Context)

Dispatcher the handler function to invoke event

func (*EventHandler) Handle 

func (h *EventHandler) Handle(ctx context.Context, e EventType, f EventFunc)

Handle register the handler function to handle an event type

func (*EventHandler) Notify 

func (h *EventHandler) Notify(ctx context.Context, event Event)

Notify function to invoke event handle

type EventMap 

type EventMap map[EventType][]EventFunc

type EventType 

type EventType string
const (
	ReadQuery    EventType = "READ_QUERY"
	CreatedQuery EventType = "CREATE_QUERY"
	UpdatedQuery EventType = "UPDATE_QUERY"
	DeletedQuery EventType = "DELETE_QUERY"
)

type I 

type I string

I is quoted identifier

Example 
// I, identifier, can be used to quote.
I("suggestions.id").As("id") // `suggestions`.`id`

Output:

func (I) As 

func (i I) As(alias string) Builder

As creates an alias for expr.

func (I) Build 

func (i I) Build(d Dialect, buf Buffer) error

Build quotes string with dialect.

type InsertBuilder 

type InsertBuilder = InsertStmt

type InsertStmt 

type InsertStmt struct {
	Dialect

	Table        string
	Column       []string
	Value        [][]interface{}
	Ignored      bool
	ReturnColumn []string
	RecordID     *int64
	// contains filtered or unexported fields
}

InsertStmt builds `INSERT INTO ...`.

func InsertBySql 

func InsertBySql(query string, value ...interface{}) *InsertStmt

InsertBySql creates an InsertStmt from raw query.

func InsertInto 

func InsertInto(table string) *InsertStmt

InsertInto creates an InsertStmt.

func (*InsertStmt) Build 

func (b *InsertStmt) Build(d Dialect, buf Buffer) error

func (*InsertStmt) Columns 

func (b *InsertStmt) Columns(column ...string) *InsertStmt

func (*InsertStmt) Comment 

func (b *InsertStmt) Comment(comment string) *InsertStmt

Comment adds a comment to prepended. All multi-line sql comment characters are stripped

func (*InsertStmt) Ignore 

func (b *InsertStmt) Ignore() *InsertStmt

Ignore any insertion errors

func (*InsertStmt) Pair 

func (b *InsertStmt) Pair(column string, value interface{}) *InsertStmt

Pair adds (column, value) to be inserted. It is an error to mix Pair with Values and Record.

Example 
InsertInto("suggestions").
	Pair("title", "Gopher").
	Pair("body", "I love go.")

Output:

func (*InsertStmt) Record 

func (b *InsertStmt) Record(structValue interface{}) *InsertStmt

Record adds a tuple for columns from a struct.

If there is a field called "Id" or "ID" in the struct, it will be set to LastInsertId.

Example 
type Suggestion struct {
	ID        int64
	Title     NullString
	CreatedAt time.Time
}
sugg := &Suggestion{
	Title:     NewNullString("Gopher"),
	CreatedAt: time.Now(),
}

InsertInto("suggestions").
	Columns("title").
	Record(&sugg)

// id is set automatically
fmt.Println(sugg.ID)

Output:

func (*InsertStmt) Returning 

func (b *InsertStmt) Returning(column ...string) *InsertStmt

Returning specifies the returning columns for postgres/mssql.

func (*InsertStmt) ToSQL 

func (b *InsertStmt) ToSQL(d Dialect, i Buffer) error

func (*InsertStmt) Values 

func (b *InsertStmt) Values(value ...interface{}) *InsertStmt

Values adds a tuple to be inserted. The order of the tuple should match Columns.

type NullBool 

type NullBool struct {
	sql.NullBool
}

NullBool is a type that can be null or a bool.

func NewNullBool 

func NewNullBool(v interface{}) (n NullBool)

NewNullBool creates a NullBool with Scan().

func (NullBool) MarshalJSON 

func (n NullBool) MarshalJSON() ([]byte, error)

MarshalJSON correctly serializes a NullBool to JSON.

func (*NullBool) UnmarshalJSON 

func (n *NullBool) UnmarshalJSON(b []byte) error

UnmarshalJSON correctly deserializes a NullBool from JSON.

type NullFloat64 

type NullFloat64 struct {
	sql.NullFloat64
}

NullFloat64 is a type that can be null or a float64.

func NewNullFloat64 

func NewNullFloat64(v interface{}) (n NullFloat64)

NewNullFloat64 creates a NullFloat64 with Scan().

func (NullFloat64) MarshalJSON 

func (n NullFloat64) MarshalJSON() ([]byte, error)

MarshalJSON correctly serializes a NullFloat64 to JSON.

func (*NullFloat64) UnmarshalJSON 

func (n *NullFloat64) UnmarshalJSON(b []byte) error

UnmarshalJSON correctly deserializes a NullFloat64 from JSON.

type NullInt64 

type NullInt64 struct {
	sql.NullInt64
}

NullInt64 is a type that can be null or an int.

func NewNullInt64 

func NewNullInt64(v interface{}) (n NullInt64)

NewNullInt64 creates a NullInt64 with Scan().

func (NullInt64) MarshalJSON 

func (n NullInt64) MarshalJSON() ([]byte, error)

MarshalJSON correctly serializes a NullInt64 to JSON.

func (*NullInt64) UnmarshalJSON 

func (n *NullInt64) UnmarshalJSON(b []byte) error

UnmarshalJSON correctly deserializes a NullInt64 from JSON.

type NullString 

type NullString struct {
	sql.NullString
}

NullString is a type that can be null or a string.

func NewNullString 

func NewNullString(v interface{}) (n NullString)

NewNullString creates a NullString with Scan().

func (NullString) MarshalJSON 

func (n NullString) MarshalJSON() ([]byte, error)

MarshalJSON correctly serializes a NullString to JSON.

func (*NullString) UnmarshalJSON 

func (n *NullString) UnmarshalJSON(b []byte) error

UnmarshalJSON correctly deserializes a NullString from JSON.

type NullTime 

type NullTime struct {
	Time  time.Time
	Valid bool // Valid is true if Time is not NULL
}

NullTime is a type that can be null or a time.

func NewNullTime 

func NewNullTime(v interface{}) (n NullTime)

NewNullTime creates a NullTime with Scan().

func (NullTime) MarshalJSON 

func (n NullTime) MarshalJSON() ([]byte, error)

MarshalJSON correctly serializes a NullTime to JSON.

func (*NullTime) Scan 

func (n *NullTime) Scan(value interface{}) error

Scan implements the Scanner interface. The value type must be time.Time or string / []byte (formatted time-string), otherwise Scan fails.

func (*NullTime) UnmarshalJSON 

func (n *NullTime) UnmarshalJSON(b []byte) error

UnmarshalJSON correctly deserializes a NullTime from JSON.

func (NullTime) Value 

func (n NullTime) Value() (driver.Value, error)

Value implements the driver Valuer interface.

type Query 

type Query struct {
	Dialect
}

type SelectStmt 

type SelectStmt struct {
	Dialect

	IsDistinct bool

	Column    []interface{}
	Table     interface{}
	JoinTable []Builder

	WhereCond  []Builder
	Group      []Builder
	HavingCond []Builder
	Order      []Builder
	Suffixes   []Builder

	LimitCount  int64
	OffsetCount int64
	// contains filtered or unexported fields
}

SelectStmt builds `SELECT ...`.

func Select 

func Select(column ...interface{}) *SelectStmt

Select creates a SelectStmt.

Example 
Select("title", "body").
	From("suggestions").
	OrderBy("id").
	Limit(10)

Output:

func SelectBySql 

func SelectBySql(query string, value ...interface{}) *SelectStmt

SelectBySql creates a SelectStmt from raw query.

Example 
SelectBySql("SELECT `title`, `body` FROM `suggestions` ORDER BY `id` ASC LIMIT 10")

Output:

func (*SelectStmt) As 

func (b *SelectStmt) As(alias string) Builder

As creates alias for select statement.

Example 
Select("count(id)").From(
	Select("*").From("suggestions").As("count"),
)

Output:

func (*SelectStmt) Build 

func (b *SelectStmt) Build(d Dialect, buf Buffer) error

func (*SelectStmt) Comment 

func (b *SelectStmt) Comment(comment string) *SelectStmt

func (*SelectStmt) Distinct 

func (b *SelectStmt) Distinct() *SelectStmt

func (*SelectStmt) From 

func (b *SelectStmt) From(table interface{}) *SelectStmt

From specifies table to select from. table can be Builder like SelectStmt, or string.

func (*SelectStmt) FullJoin 

func (b *SelectStmt) FullJoin(table, on interface{}) *SelectStmt

FullJoin add full-join. on can be Builder or string.

func (*SelectStmt) GroupBy 

func (b *SelectStmt) GroupBy(col ...string) *SelectStmt

GroupBy specifies columns for grouping.

func (*SelectStmt) Having 

func (b *SelectStmt) Having(query interface{}, value ...interface{}) *SelectStmt

Having adds a having condition. query can be Builder or string. value is used only if query type is string.

func (*SelectStmt) Join 

func (b *SelectStmt) Join(table, on interface{}) *SelectStmt

Join add inner-join. on can be Builder or string.

Example 
Select("*").From("suggestions").
	Join("subdomains", "suggestions.subdomain_id = subdomains.id")

Select("*").From("suggestions").
	LeftJoin("subdomains", "suggestions.subdomain_id = subdomains.id")

// join multiple tables
Select("*").From("suggestions").
	Join("subdomains", "suggestions.subdomain_id = subdomains.id").
	Join("accounts", "subdomains.accounts_id = accounts.id")

Output:

func (*SelectStmt) LeftJoin 

func (b *SelectStmt) LeftJoin(table, on interface{}) *SelectStmt

LeftJoin add left-join. on can be Builder or string.

func (*SelectStmt) Limit 

func (b *SelectStmt) Limit(n uint64) *SelectStmt

func (*SelectStmt) Offset 

func (b *SelectStmt) Offset(n uint64) *SelectStmt

func (*SelectStmt) OrderAsc 

func (b *SelectStmt) OrderAsc(col string) *SelectStmt

func (*SelectStmt) OrderBy 

func (b *SelectStmt) OrderBy(col string) *SelectStmt

OrderBy specifies columns for ordering.

func (*SelectStmt) OrderDesc 

func (b *SelectStmt) OrderDesc(col string) *SelectStmt

func (*SelectStmt) OrderDir 

func (b *SelectStmt) OrderDir(col string, isAsc bool) *SelectStmt

OrderDir is a helper for OrderAsc and OrderDesc.

func (*SelectStmt) Paginate 

func (b *SelectStmt) Paginate(page, perPage uint64) *SelectStmt

Paginate fetches a page in a naive way for a small set of data.

func (*SelectStmt) RightJoin 

func (b *SelectStmt) RightJoin(table, on interface{}) *SelectStmt

RightJoin add right-join. on can be Builder or string.

func (*SelectStmt) Seek 

func (b *SelectStmt) Seek(col string, cursor, limit uint64) *SelectStmt

Seek fetches a page in key set way for a large set of data.

func (*SelectStmt) Suffix 

func (b *SelectStmt) Suffix(suffix string, value ...interface{}) *SelectStmt

Suffix adds an expression to the end of the query. This is useful to add dialect-specific clauses like FOR UPDATE

func (*SelectStmt) ToSQL 

func (b *SelectStmt) ToSQL(d Dialect, i Buffer) error

func (*SelectStmt) Where 

func (b *SelectStmt) Where(query interface{}, value ...interface{}) *SelectStmt

Where adds a where condition. query can be Builder or string. value is used only if query type is string.

Example 
ids := []int64{1, 2, 3, 4, 5}
Select("*").From("suggestions").Where("id IN ?", ids)

Output:

type Sql 

type Sql struct {
	*sql.DB
	Event *EventHandler
}

func New 

func New(args SqlConnParams) (*Sql, error)

func (*Sql) Notify 

func (s *Sql) Notify(ctx context.Context, event Event)

func (*Sql) SetEvent 

func (s *Sql) SetEvent(handler *EventHandler)

func (*Sql) Subscriber 

func (s *Sql) Subscriber(ctx context.Context, t EventType, fn EventFunc)

func (*Sql) WithTransaction 

func (s *Sql) WithTransaction(ctx context.Context, fn func(context.Context, *sql.Tx) error) error

type SqlConnParams 

type SqlConnParams struct {
	Driver, Dsn string
}

type Store 

type Store interface {
	Notify(ctx context.Context, event Event)
	Subscriber(ctx context.Context, t EventType, fn EventFunc)
	WithTransaction(ctx context.Context, fn func(ctx context.Context, tx *sql.Tx) error) error
}

type UpdateBuilder 

type UpdateBuilder = UpdateStmt

type UpdateStmt 

type UpdateStmt struct {
	Dialect

	Table        string
	Value        map[string]interface{}
	WhereCond    []Builder
	ReturnColumn []string
	LimitCount   int64
	// contains filtered or unexported fields
}

UpdateStmt builds `UPDATE ...`.

func Update 

func Update(table string) *UpdateStmt

Update creates an UpdateStmt.

func UpdateBySql 

func UpdateBySql(query string, value ...interface{}) *UpdateStmt

UpdateBySql creates an UpdateStmt with raw query.

func (*UpdateStmt) Build 

func (b *UpdateStmt) Build(d Dialect, buf Buffer) error

func (*UpdateStmt) Comment 

func (b *UpdateStmt) Comment(comment string) *UpdateStmt

func (*UpdateStmt) DecrBy 

func (b *UpdateStmt) DecrBy(column string, value interface{}) *UpdateStmt

DecrBy decreases column by value

func (*UpdateStmt) IncrBy 

func (b *UpdateStmt) IncrBy(column string, value interface{}) *UpdateStmt

IncrBy increases column by value

func (*UpdateStmt) Limit 

func (b *UpdateStmt) Limit(n uint64) *UpdateStmt

func (*UpdateStmt) Returning 

func (b *UpdateStmt) Returning(column ...string) *UpdateStmt

Returning specifies the returning columns for postgres.

func (*UpdateStmt) Set 

func (b *UpdateStmt) Set(column string, value interface{}) *UpdateStmt

Set updates column with value.

func (*UpdateStmt) SetMap 

func (b *UpdateStmt) SetMap(m map[string]interface{}) *UpdateStmt

SetMap specifies a map of (column, value) to update in bulk.

func (*UpdateStmt) ToSQL 

func (b *UpdateStmt) ToSQL(d Dialect, i Buffer) error

func (*UpdateStmt) Where 

func (b *UpdateStmt) Where(query interface{}, value ...interface{}) *UpdateStmt

Where adds a where condition. query can be Builder or string. value is used only if query type is string.

Source Files

View all Source files

Directories

Path Synopsis

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.