qb

package module
v2.1.3+incompatible Latest Latest
Warning

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

 Go to latest Published: Apr 26, 2019 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

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

All possible join types

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 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
	UpsertSQL(*Table, []Field, Query) (string, []interface{})
	Limit(SQL, 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) 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

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) Limit 

func (b *SQLBuilder) Limit(i int)

Limit generates a SQL LIMIT line

func (*SQLBuilder) List 

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

List lists the given fields

func (*SQLBuilder) Offset 

func (b *SQLBuilder) Offset(i int)

Offset generates a SQL OFFSET line

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
}

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
	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

Source Files

View all Source files

Directories

Path Synopsis
driver
autoqb
msqb
msqb/msqf
myqb
myqb/myqf
pgqb
pgqb/pgqc
pgqb/pgqf
internal
tests/internal/model
testutil
internal/db
internal/db/msarchitect
internal/db/msarchitect/msmodel
internal/db/myarchitect
internal/db/myarchitect/mymodel
internal/db/pgarchitect
internal/db/pgarchitect/pgmodel
internal/util

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.