qb

package module
v2.6.0+incompatible Latest Latest
Warning

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

Go to latest
Published: Feb 18, 2021 License: MIT Imports: 5 Imported by: 12

README

qb

qb is a library that allows you to build queries without using strings. This offers some unique advantages:

  • When changing your database queries that refer to old fields or tables won't compile until you update them
  • You can't misspell keywords or fieldnames, this saves a lot of time and many bugs
  • You can use tab completion
  • You can easily port a query to a different database
  • The order of commands in your query does not matter, this makes building queries in parts or adding optional statements easier

Installation

go get git.ultraware.nl/NiseVoid/qb/...

Quick start guide

1. Create a db.json

You can create a db.json manually or use qb-architect to generate it from your database

qb-architect example:

qb-architect -dbms psql host=127.0.0.1 username=qb_test dbname=qb_test > db.json

db.json example:

[
	{
		"name": "TableOne",
		"alias": "one", // optional
		"fields": [
			{
				"name": "Field1",
				"data_type": "int",     // optional
				"read_only": true       // optional
			},
			{
				"name": "Field2",
				"data_type": "varchar", // optional
				"size": 50,             // optional
			},
			{ ... }
		]
	},
	{
		"name": "TableTwo",
		"fields": [
			{"name": "Field1"},
			{"name": "Field2"},
			{"name": "Field3"}
		]
	}
]
2. Run qb-generator
qb-generator db.json tables.go
Recommendations
  • Don't commit qb-generator's generated code to your repo
  • Use a go generate command to run qb-generator
3. Make a qbdb.DB
package main

var db *qbdb.DB

func main() {
	database, err := sql.Open(driver, connectionString)
	if err != nil {
		panic(err)
	}

	db = autoqb.New(database)
}
4. Write queries!

You can now write queries, you can find examples below

Examples

Select
one := model.One()

q := one.Select(one.Field1, one.Field2).
	Where(qc.In(Field1, 1, 2, 3))

rows, err := db.Query(q)
if err != nil {
	panic(err)
}

for rows.Next() {
	f1, f2 := 0, ""
	err := rows.Scan(&f1, &f2)
	if err != nil {
		panic(err)
	}

	fmt.Println(f1, f2)
}
Insert
one := model.One()

q := one.Insert(one.Field1, one.Field2).
	Values(1, "Record 1").
	Values(2, "Record 2").
	Values(4, "Record 4")

_, err := db.Exec(q)
if err != nil {
	panic(err)
}
Update
one := model.One()

q := one.Update().
	Set(one.Field2, "Record 3").
	Where(qc.Eq(one.Field1, 4))

_, err := db.Exec(q)
if err != nil {
	panic(err)
}
Delete
one := model.One()

q := one.Delete(qc.Eq(one.Field1, 4))

_, err := db.Exec(q)
if err != nil {
	panic(err)
}
Prepare
one := model.One()

id := 0
q := one.Select(one.Field1, one.Field2).
	Where(qc.Eq(one.Field1, &id))

stmt, err := db.Prepare()
if err != nil {
	panic(err)
}

for _, v := range []int{1,2,3,4,5} {
	id = v

	row := stmt.QueryRow()

	f1, f2 := 0, ""
	err := row.Scan(&field1, &field2)
	if err != nil {
		panic(err)
	}

	fmt.Println(f1, f2)
}
Subqueries
one := model.One()

sq := one.Select(one.Field1).SubQuery()

q := sq.Select(sq.F[0])

rows, err := db.Query(q)
if err != nil {
	panic(err)
}

for rows.Next() {
	f1 := 0
	err := rows.Scan(&f1)
	if err != nil {
		panic(err)
	}

	fmt.Println(f1)
}

Alternatively, .CTE() can be used instead of .SubQuery() to use a CTE instead of a subquery

Custom functions
func dbfunc(f qb.Field) qb.Field {
    return qf.NewCalculatedField("dbfunc(", f, ")")
}
q := one.Select(dbfunc(one.Field1))
Custom conditions
func dbcond(f qb.Field) qb.Condition {
	return qc.NewCondition("dbcond(", f, ")")
}
q := one.Select(one.Field1).
	Where(dbcond(one.Field1))

Documentation

Index

Constants

View Source
const (
	Int = iota + 1
	String
	Bool
	Float
	Date
	Time
)

All defined DataTypes

Variables

View Source
var (
	COMMA   = `, `
	NEWLINE = "\n"
	INDENT  = "\t"
	VALUE   = `?`
)

Values used when building queries

Functions

func ConcatQuery

func ConcatQuery(c *Context, values ...interface{}) string

ConcatQuery combines strings and Fields into string. This function is not intended to be called directly

func GetFuncFrame

func GetFuncFrame() string

GetFuncFrame returns a function

func JoinQuery

func JoinQuery(c *Context, sep string, values []interface{}) string

JoinQuery joins fields or values into a string separated by sep. This function is not intended to be called directly

Types

type Alias

type Alias interface {
	Get(Source) string
}

Alias generates table aliasses. This type is not intended to be used directly

func AliasGenerator

func AliasGenerator() Alias

AliasGenerator returns an incrementing alias for each new Source. This function is not intended to be called directly

func NoAlias

func NoAlias() Alias

NoAlias returns no alias. This function is not intended to be called directly

type CTE

type CTE struct {
	F []Field
	// contains filtered or unexported fields
}

CTE is a type of subqueries

func (*CTE) Select

func (cte *CTE) Select(f ...Field) *SelectBuilder

Select starts a SELECT query

func (*CTE) TableString

func (cte *CTE) TableString(c *Context) string

TableString implements Source

func (*CTE) With

func (cte *CTE) With(b SQLBuilder) string

With generates the SQL for a WITH statement. This function is not intended to be called directly

type Condition

type Condition func(c *Context) string

Condition is used in the Where function

type Context

type Context struct {
	Driver Driver

	Values *[]interface{}

	CTEs *[]*CTE
	// contains filtered or unexported fields
}

Context contains all the data needed to build parts of a query. This type is not intended to be used directly

func NewContext

func NewContext(d Driver, a Alias) *Context

NewContext returns a new *Context

func (*Context) Add

func (c *Context) Add(v ...interface{})

Add adds a value to Values

func (*Context) Alias

func (c *Context) Alias(src Source) string

Alias returns an alias for the given Source

type DataType

type DataType uint16

DataType represents a type in a database

type DeleteBuilder

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

DeleteBuilder builds a DELETE query

func (DeleteBuilder) SQL

func (q DeleteBuilder) SQL(b SQLBuilder) (string, []interface{})

SQL returns a query string and a list of values

type Driver

type Driver interface {
	ValueString(int) string
	BoolString(bool) string
	EscapeCharacter() string
	UpsertSQL(*Table, []Field, Query) (string, []interface{})
	IgnoreConflictSQL(*Table, []Field) (string, []interface{})
	LimitOffset(SQL, int, int)
	Returning(SQLBuilder, Query, []Field) (string, []interface{})
	TypeName(DataType) string
	Override() OverrideMap
}

Driver implements databse-specific features

type Field

type Field interface {
	QueryString(*Context) string
}

Field represents a field in a query

func Default

func Default() Field

Default uses a field's default value

func MakeField

func MakeField(i interface{}) Field

MakeField returns the value as a Field, no operation performed when the value is already a field. This function is not intended to be called directly

func Value

func Value(v interface{}) Field

Value creats a new Field

type FieldOrder

type FieldOrder struct {
	Field Field
	Order string
}

FieldOrder specifies the order in which fields should be sorted

func Asc

func Asc(f Field) FieldOrder

Asc is used to sort in ascending order

func Desc

func Desc(f Field) FieldOrder

Desc is used to sort in descending order

type InsertBuilder

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

InsertBuilder builds an INSERT query

func (*InsertBuilder) IgnoreConflict

func (q *InsertBuilder) IgnoreConflict(conflict ...Field) *InsertBuilder

IgnoreConflict ignores conflicts from the insert query

func (*InsertBuilder) SQL

func (q *InsertBuilder) SQL(b SQLBuilder) (string, []interface{})

SQL returns a query string and a list of values

func (*InsertBuilder) Upsert

func (q *InsertBuilder) Upsert(query Query, conflict ...Field) *InsertBuilder

Upsert turns the INSERT query into an upsert query, only usable if your driver supports it

func (*InsertBuilder) Values

func (q *InsertBuilder) Values(values ...interface{}) *InsertBuilder

Values adds values to the query

type Join

type Join string

Join is the type of join

const (
	JoinInner Join = `INNER`
	JoinLeft  Join = `LEFT`
	JoinRight Join = `RIGHT`
	JoinCross Join = `CROSS`
)

All possible join types

type OverrideMap

type OverrideMap map[string]interface{}

OverrideMap allows a driver to override functions from qf and qc. This type is not intended to be used directly

func (OverrideMap) Add

func (m OverrideMap) Add(target, new interface{})

Add adds an override to the map

func (OverrideMap) Condition

func (m OverrideMap) Condition(source string, fallback interface{}, in []interface{}) Condition

Condition gets an override for qc, if there is no entry in the map fallback will be used

func (OverrideMap) Field

func (m OverrideMap) Field(source string, fallback interface{}, in []interface{}) Field

Field gets an override for qf, if there is no entry in the map fallback will be used

type Query

type Query interface {
	SQL(b SQLBuilder) (string, []interface{})
}

Query generates SQL

type ReturningBuilder

type ReturningBuilder struct {
	Query Query
	// contains filtered or unexported fields
}

ReturningBuilder builds a query with a RETURNING statement

func (ReturningBuilder) CTE

func (q ReturningBuilder) CTE(fields ...*Field) *CTE

CTE creates a new CTE (WITH) Query

func (ReturningBuilder) Fields

func (q ReturningBuilder) Fields() []Field

Fields returns a list of the fields used in the query

func (ReturningBuilder) SQL

func (q ReturningBuilder) SQL(b SQLBuilder) (string, []interface{})

SQL returns a query string and a list of values

func (ReturningBuilder) SubQuery

func (q ReturningBuilder) SubQuery(fields ...*Field) *SubQuery

SubQuery converts the SelectQuery to a SubQuery for use in further queries

type SQL

type SQL interface {
	WriteString(string)
	WriteLine(string)
	Rewrite(string)
	String() string
}

SQL represents an SQL string. Not intended to be used directly

type SQLBuilder

type SQLBuilder struct {
	Context *Context
	// contains filtered or unexported fields
}

SQLBuilder contains data and methods to generate SQL. This type is not intended to be used directly

func NewSQLBuilder

func NewSQLBuilder(d Driver) SQLBuilder

NewSQLBuilder returns a new SQLBuilder

func (*SQLBuilder) Conditions

func (b *SQLBuilder) Conditions(c []Condition, newline bool)

Conditions generates valid SQL for the given list of conditions

func (*SQLBuilder) Delete

func (b *SQLBuilder) Delete(t *Table)

Delete generates a SQL DELETE FROM line

func (*SQLBuilder) FieldToSQL

func (b *SQLBuilder) FieldToSQL(f Field) string

FieldToSQL converts a Field to a string

func (*SQLBuilder) From

func (b *SQLBuilder) From(src Source)

From generates a SQL FROM line

func (*SQLBuilder) GroupBy

func (b *SQLBuilder) GroupBy(f ...Field)

GroupBy generates a SQL GROUP BY line

func (*SQLBuilder) Having

func (b *SQLBuilder) Having(c ...Condition)

Having generates a SQL HAVING line

func (*SQLBuilder) Insert

func (b *SQLBuilder) Insert(t *Table, f []Field)

Insert generates a SQL INSERT line

func (*SQLBuilder) Join

func (b *SQLBuilder) Join(j ...join)

Join generates SQL JOIN lines

func (*SQLBuilder) LimitOffset

func (b *SQLBuilder) LimitOffset(l, o int)

LimitOffset generates a SQL LIMIT and OFFSET line

func (*SQLBuilder) List

func (b *SQLBuilder) List(f []Field, withAlias bool) string

List lists the given fields

func (*SQLBuilder) OrderBy

func (b *SQLBuilder) OrderBy(o ...FieldOrder)

OrderBy generates a SQL ORDER BY line

func (*SQLBuilder) Select

func (b *SQLBuilder) Select(withAlias bool, f ...Field)

Select generates a SQL SELECT line

func (*SQLBuilder) Set

func (b *SQLBuilder) Set(sets []set)

Set generates a SQL SET line

func (*SQLBuilder) SourceToSQL

func (b *SQLBuilder) SourceToSQL(s Source) string

SourceToSQL converts a Source to a string

func (*SQLBuilder) Update

func (b *SQLBuilder) Update(t *Table)

Update generates a SQL UPDATE line

func (*SQLBuilder) Values

func (b *SQLBuilder) Values(f [][]Field)

Values generates a SQL VALUES line

func (*SQLBuilder) Where

func (b *SQLBuilder) Where(c ...Condition)

Where generates SQL WHERE/AND lines

type SelectBuilder

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

SelectBuilder builds a SELECT query

func NewSelectBuilder

func NewSelectBuilder(f []Field, src Source) *SelectBuilder

NewSelectBuilder retruns a new SelectBuilder

func (*SelectBuilder) CTE

func (q *SelectBuilder) CTE(fields ...*Field) *CTE

CTE creates a new CTE (WITH) Query

func (*SelectBuilder) CrossJoin

func (q *SelectBuilder) CrossJoin(s Source) *SelectBuilder

CrossJoin adds a CROSS JOIN clause to the query

func (*SelectBuilder) Fields

func (q *SelectBuilder) Fields() []Field

Fields returns a list of the fields used in the query

func (*SelectBuilder) GroupBy

func (q *SelectBuilder) GroupBy(f ...Field) *SelectBuilder

GroupBy adds a GROUP BY clause to the query

func (*SelectBuilder) Having

func (q *SelectBuilder) Having(c ...Condition) *SelectBuilder

Having adds a HAVING clause to the query

func (*SelectBuilder) InnerJoin

func (q *SelectBuilder) InnerJoin(f1, f2 Field, c ...Condition) *SelectBuilder

InnerJoin adds an INNER JOIN clause to the query

func (*SelectBuilder) LeftJoin

func (q *SelectBuilder) LeftJoin(f1, f2 Field, c ...Condition) *SelectBuilder

LeftJoin adds a LEFT JOIN clause to the query

func (*SelectBuilder) Limit

func (q *SelectBuilder) Limit(i int) *SelectBuilder

Limit adds a LIMIT clause to the query

func (*SelectBuilder) ManualJoin

func (q *SelectBuilder) ManualJoin(t Join, s Source, c ...Condition) *SelectBuilder

ManualJoin manually joins a table Only use this if you know what you are doing

func (*SelectBuilder) Offset

func (q *SelectBuilder) Offset(i int) *SelectBuilder

Offset adds a OFFSET clause to the query

func (*SelectBuilder) OrderBy

func (q *SelectBuilder) OrderBy(o ...FieldOrder) *SelectBuilder

OrderBy adds a ORDER BY clause to the query

func (*SelectBuilder) RightJoin

func (q *SelectBuilder) RightJoin(f1, f2 Field, c ...Condition) *SelectBuilder

RightJoin adds a RIGHT JOIN clause to the query

func (*SelectBuilder) SQL

func (q *SelectBuilder) SQL(b SQLBuilder) (string, []interface{})

SQL returns a query string and a list of values

func (*SelectBuilder) SubQuery

func (q *SelectBuilder) SubQuery(fields ...*Field) *SubQuery

SubQuery converts the SelectQuery to a SubQuery for use in further queries

func (*SelectBuilder) Where

func (q *SelectBuilder) Where(c ...Condition) *SelectBuilder

Where adds conditions to the WHERE clause

type SelectQuery

type SelectQuery interface {
	Query

	SubQuery(...*Field) *SubQuery
	CTE(...*Field) *CTE
	Fields() []Field
	// contains filtered or unexported methods
}

SelectQuery represents a query that returns data

func Except

func Except(q1, q2 SelectQuery) SelectQuery

Except combines queries with an EXCEPT

func ExceptAll

func ExceptAll(q1, q2 SelectQuery) SelectQuery

ExceptAll combines queries with an EXCEPT ALL

func Intersect

func Intersect(q1, q2 SelectQuery) SelectQuery

Intersect combines queries with an INTERSECT

func IntersectAll

func IntersectAll(q1, q2 SelectQuery) SelectQuery

IntersectAll combines queries with an INTERSECT ALL

func Returning

func Returning(q Query, f ...Field) SelectQuery

Returning creates a RETURNING or OUTPUT query

func Union

func Union(q ...SelectQuery) SelectQuery

Union combines queries with an UNION

func UnionAll

func UnionAll(q ...SelectQuery) SelectQuery

UnionAll combines queries with an UNION ALL

type Source

type Source interface {
	TableString(*Context) string
	// contains filtered or unexported methods
}

Source represents a table or a subquery

type SubQuery

type SubQuery struct {
	F []Field
	// contains filtered or unexported fields
}

SubQuery represents a subquery

func (*SubQuery) Select

func (t *SubQuery) Select(f ...Field) *SelectBuilder

Select starts a SELECT query

func (*SubQuery) TableString

func (t *SubQuery) TableString(c *Context) string

TableString implements Source

type Table

type Table struct {
	Name   string
	Alias  string
	Escape bool
}

Table represents a table in the database. This type is used by qb-generator's generated code and is not intended to be used manually

func (*Table) Delete

func (t *Table) Delete(c1 Condition, c ...Condition) Query

Delete starts a DELETE query

func (*Table) Insert

func (t *Table) Insert(f []Field) *InsertBuilder

Insert starts an INSERT query

func (*Table) Select

func (t *Table) Select(f []Field) *SelectBuilder

Select starts a SELECT query

func (*Table) TableString

func (t *Table) TableString(c *Context) string

TableString implements Source

func (*Table) Update

func (t *Table) Update() *UpdateBuilder

Update starts an UPDATE query

type TableField

type TableField struct {
	Parent   Source
	Name     string
	Escape   bool
	ReadOnly bool
	Nullable bool
	Type     DataType
	Size     int
}

TableField represents a field in a table. This type is used by qb-generator's generated code and is not intended to be used manually

func (TableField) Copy

func (f TableField) Copy(src Source) *TableField

Copy creates a new instance of the field with a different Parent

func (TableField) QueryString

func (f TableField) QueryString(c *Context) string

QueryString implements Field

type UpdateBuilder

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

UpdateBuilder builds an UPDATE query

func (*UpdateBuilder) SQL

func (q *UpdateBuilder) SQL(b SQLBuilder) (string, []interface{})

SQL returns a query string and a list of values

func (*UpdateBuilder) Set

func (q *UpdateBuilder) Set(f Field, v interface{}) *UpdateBuilder

Set adds an update to the SET clause

func (*UpdateBuilder) Where

func (q *UpdateBuilder) Where(c ...Condition) *UpdateBuilder

Where adds conditions to the WHERE clause

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL