buildsqlx

package module
v1.0.6 Latest Latest
Warning

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

 Go to latest Published: Apr 21, 2024 License: MIT Imports: 13 Imported by: 1

README

buildsqlx

Go Database query builder library Tweet

Mentioned in Awesome Go Go Report Card Build and run GoDoc codecov

Installation

go get -u github.com/arthurkushman/buildsqlx

Selects, Ordering, Limit & Offset

You may not always want to select all columns from a database table. Using the select method, you can specify a custom select clause for the query:

package yourpackage

import (
	"database/sql"

	"github.com/arthurkushman/buildsqlx"
	_ "github.com/lib/pq"
)

var db = buildsqlx.NewDb(buildsqlx.NewConnection("postgres", "user=postgres dbname=postgres password=postgres sslmode=disable"))

func main() {
	qDb := db.Table("posts").Select("title", "body")

	type DataStruct struct {
		Foo string
		Bar string
		Baz *int64
	}

	dataStruct := DataStruct{}
	var testStructs []DataStruct
	// If you already have a query builder instance and you wish to add a column to its existing select clause, you may use the addSelect method:
	err := qDb.AddSelect("points").GroupBy("topic").OrderBy("points", "DESC").Limit(15).Offset(5).EachToStruct(func(rows *sql.Rows) error {
		err = db.Next(rows, &dataStruct)
		if err != nil {
			return err
		}

		testStructs = append(testStructs, dataStruct)
		return nil
	})
}
InRandomOrder
err = db.Table("users").Select("name", "post", "user_id").InRandomOrder().ScanStruct(dataStruct)

GroupBy / Having

The GroupBy and Having methods may be used to group the query results. The having method's signature is similar to that of the Where method:

err = db.table("users").GroupBy("account_id").Having("account_id", ">", 100).ScanStruct(dataStruct)

Where, AndWhere, OrWhere clauses

You may use the Where method on a query builder instance to add where clauses to the query. The most basic call to where requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. Finally, the third argument is the value to evaluate against the column.

err = db.Table("table1").Select("foo", "bar", "baz").Where("foo", "=", cmp).AndWhere("bar", "!=", "foo").OrWhere("baz", "=", 123)..ScanStruct(dataStruct)

You may chain where constraints together as well as add or clauses to the query. The OrWhere method accepts the same arguments as the Where method.

WhereIn / WhereNotIn

The WhereIn method verifies that a given column's value is contained within the given slice:

err = db.Table("table1").WhereIn("id", []int64{1, 2, 3}).OrWhereIn("name", []string{"John", "Paul"}).ScanStruct(dataStruct)

WhereNull / WhereNotNull

The WhereNull method verifies that the value of the given column is NULL:

err = db.Table("posts").WhereNull("points").OrWhereNotNull("title")..ScanStruct(dataStruct)

Left / Right / Cross / Inner / Left Outer Joins

The query builder may also be used to write join statements. To perform a basic "inner join", you may use the InnerJoin method on a query builder instance. The first argument passed to the join method is the name of the table you need to join to, while the remaining arguments specify the column constraints for the join. You can even join to multiple tables in a single query:

err = db.Table("users").Select("name", "post", "user_id").LeftJoin("posts", "users.id", "=", "posts.user_id").EachToStruct(func(rows *sql.Rows) error {
    err = db.Next(rows, &dataStruct)
    if err != nil {
        return err
    }
    
    testStructs = append(testStructs, dataStruct)
    return nil
})

Inserts

The query builder also provides an Insert method for inserting records into the database table. The Insert/InsertBatch methods accept a structure (or slice of structs) of column names and values:

// insert without getting id
err = db.Table("table1").Insert(DataStruct{
    Foo: "foo foo foo",
    Bar: "bar bar bar",
    Baz: &baz,
})

// insert returning id
id, err := db.Table("table1").InsertGetId(DataStruct{
    Foo: "foo foo foo",
    Bar: "bar bar bar",
    Baz: &baz,
})

// batch insert 
err = db.Table("table1").InsertBatch([]DataStruct{
    {Foo: "foo foo foo", Bar: "bar bar bar", Baz: &baz},
    {Foo: "foo foo foo foo", Bar: "bar bar bar bar", Baz: &baz},
    {Foo: "foo foo foo foo foo", Bar: "bar bar bar bar bar", Baz: &baz},
})

Updates

In addition to inserting records into the database, the query builder can also update existing records using the update method. The update method, like the insert method, accepts a slice of column and value pairs containing the columns to be updated. You may constrain the update query using where clauses:

rows, err := db.Table("posts").Where("points", ">", 3).Update(DataStruct{
    Title: "awesome",
})

Delete

The query builder may also be used to delete records from the table via the delete method. You may constrain delete statements by adding where clauses before calling the delete method:

rows, err := db.Table("posts").Where("points", "=", 123).Delete()

Drop, Truncate, Rename

db.Drop("table_name")

db.DropIfExists("table_name")

db.Truncate("table_name")

db.Rename("table_name1", "table_name2")

Increment & Decrement

The query builder also provides convenient methods for incrementing or decrementing the value of a given column. This is a shortcut, providing a more expressive and terse interface compared to manually writing the update statement.

Both of these methods accept 2 arguments: the column to modify, a second argument to control the amount by which the column should be incremented or decremented:

db.Table("users").Increment("votes", 3)

db.Table("users").Decrement("votes", 1)

Union / Union All

The query builder also provides a quick way to "union" two queries together. For example, you may create an initial query and use the union method to union it with a second query:

union := db.Table("posts").Select("title", "likes").Union()
res, err := union.Table("users").Select("name", "points").ScanStruct(dataStruct)

// or if UNION ALL is of need
// union := db.Table("posts").Select("title", "likes").UnionAll()

Transaction mode

You can run arbitrary queries mixed with any code in transaction mode getting an error and as a result rollback if something went wrong or committed if everything is ok:

err := db.InTransaction(func () (interface{}, error) {
    return db.Table("users").Select("name", "post", "user_id").ScanStruct(dataStruct)
})

Dump, Dd

You may use the Dd or Dump methods while building a query to dump the query bindings and SQL. The dd method will display the debug information and then stop executing the request. The dump method will display the debug information but allow the request to keep executing:

	// to print raw sql query to stdout 
	db.Table("table_name").Select("foo", "bar", "baz").Where("foo", "=", cmp).AndWhere("bar", "!=", "foo").Dump()

	// or to print to stdout and exit a.k.a dump and die
	db.Table("table_name").Select("foo", "bar", "baz").Where("foo", "=", cmp).AndWhere("bar", "!=", "foo").Dd()

Check if table exists

tblExists, err := db.HasTable("public", "posts")

Check if columns exist in a table within schema

colsExists, err := db.HasColumns("public", "posts", "title", "user_id")

Retrieving A Single Row / Column From A Table

If you just need to retrieve a single row from the database table, you may use the First func. This method will return a single map[string]interface{}:

err = db.Table("posts").Select("title").OrderBy("created_at", "desc").First(dataStruct)

// usage ex: dataStruct.Title

If you don't even need an entire row, you may extract a single value from a record using the Value method. This method will return the value of the column directly:

err = db.Table("users").OrderBy("points", "desc").Value(dataStruct, "name")

// dataStruct.Name -> "Alex Shmidt"

To retrieve a single row by its id column value, use the find method:

user, err := db.Table("users").Find(dataStruct, id)

// dataStruct.ID, dataStruct.Name, dataStruct.Email etc

WhereExists / WhereNotExists

The whereExists method allows you to write where exists SQL clauses. The whereExists method accepts a *DB argument, which will receive a query builder instance allowing you to define the query that should be placed inside the "exists" clause:

err = db.Table("users").Select("name").WhereExists(
    db.Table("users").Select("name").Where("points", ">=", int64(12345)),
).First(dataStruct)

Any query that is of need to build one can place inside WhereExists clause/func.

WhereBetween / WhereNotBetween

The whereBetween func verifies that a column's value is between two values:

err = db.Table(UsersTable).Select("name").WhereBetween("points", 1233, 12345).ScanStruct(&testStruct)

The whereNotBetween func verifies that a column's value lies outside of two values:

err = db.Table(UsersTable).Select("name").WhereNotBetween("points", 123, 123456).ScanStruct(&testStruct)

Determining If Records Exist

Instead of using the Count method to determine if any records exist that match your query's constraints, you may use the exists and doesntExist methods:

exists, err := db.Table(UsersTable).Select("name").Where("points", ">=", int64(12345)).Exists()
// use an inverse DoesntExists() if needed

Aggregates

The query builder also provides a variety of aggregate methods such as Count, Max, Min, Avg, and Sum. You may call any of these methods after constructing your query:

cnt, err := db.Table(UsersTable).WHere("points", ">=", 1234).Count()

avg, err := db.Table(UsersTable).Avg("points")

mx, err := db.Table(UsersTable).Max("points")

mn, err := db.Table(UsersTable).Min("points")

sum, err := db.Table(UsersTable).Sum("points")

Create table

To create a new database table, use the CreateTable method. The Schema method accepts two arguments. The first is the name of the table, while the second is an anonymous function/closure which receives a Table struct that may be used to define the new table:

res, err := db.Schema("big_tbl", func(table *Table) error {
    table.Increments("id")
    table.String("title", 128).Default("The quick brown fox jumped over the lazy dog").Unique("idx_ttl")
    table.SmallInt("cnt").Default(1)
    table.Integer("points").NotNull()
    table.BigInt("likes").Index("idx_likes")
    table.Text("comment").Comment("user comment").Collation("de_DE")
    table.DblPrecision("likes_to_points").Default(0.0)
    table.Char("tag", 10)
    table.DateTime("created_at", true)
    table.DateTimeTz("updated_at", true)
    table.Decimal("tax", 2, 2)
    table.TsVector("body")
    table.TsQuery("body_query")
    table.Jsonb("settings")
    table.Point("pt")
    table.Polygon("poly")
    table.TableComment("big table for big data")
    
    return nil
})

// to make a foreign key constraint from another table
_, err = db.Schema("tbl_to_ref", func (table *Table) error {
    table.Increments("id")
    table.Integer("big_tbl_id").ForeignKey("fk_idx_big_tbl_id", "big_tbl", "id").Concurrently().IfNotExists()
    // to add index on existing column just repeat stmt + index e.g.:
    table.Char("tag", 10).Index("idx_tag").Include("likes", "created_at")
    table.Rename("settings", "options")
    
    return nil
})

Add / Modify / Drop columns

The Table structure in the Schema's 2nd argument may be used to update existing tables. Just the way you've been created it. The Change method allows you to modify some existing column types to a new type or modify the column's attributes.

res, err := db.Schema("tbl_name", func(table *Table) error {
    table.String("title", 128).Change()
    
    return nil
})

Use DropColumn method to remove any column:

res, err := db.Schema("tbl_name", func(table *Table) error {
    table.DropColumn("deleted_at").IfExists()
    // To drop an index on the column    
    table.DropIndex("idx_title")
    
    return nil
})

Chunking Results

If you need to work with thousands of database records, consider using the chunk method. This method retrieves a small chunk of the results at a time and feeds each chunk into a closure for processing.

var sumOfPoints int64
dataStruct := &DataStructUser{}
err = db.Table(UsersTable).Select("name", "points").Chunk(dataStruct, 100, func(users []any) bool {
    for _, v := range users {
        user := v.(DataStructUser) 
        // your code goes here e.g.:
        sumOfPoints += user.Points
    }
	
    // or you can return false here to stop running chunks 
    return true
})

Pluck / PluckMap

If you would like to get values of a particular column(s) of a struct and place them into slice - use Pluck method:

    dataStruct := &DataStructUser{}
    res, err := db.Table(UsersTable).Pluck(dataStruct)
    for k, v := range res {
        val := v.(DataStructUser)
        fmt.Println(val.Name) // f.e.: Alex Shmidt
    }
	
    // or use a PluckMap method to aggregate key/value pairs to a map
    res, err := db.Table(UsersTable).PluckMap(dataStruct, "name", "points")
    for k, m := range res {
        for key, value := range m {
            keyVal := key.(string)
            valueVal := value.(DataStructUser) 
            // rest of the code ...
        }
    }

PS Why use buildsqlx? Because it is simple and fast, yet versatile. The builder code-style has been inherited from greatest web-frameworks, so u can easily query anything from db.

Supporters gratitude:

JetBrains logo

Documentation

Index

Constants

View Source 
const (
	TypeSerial       = "SERIAL"
	TypeBigSerial    = "BIGSERIAL"
	TypeSmallInt     = "SMALLINT"
	TypeInt          = "INTEGER"
	TypeBigInt       = "BIGINT"
	TypeBoolean      = "BOOLEAN"
	TypeText         = "TEXT"
	TypeVarchar      = "VARCHAR"
	TypeChar         = "CHAR"
	TypeDate         = "DATE"
	TypeTime         = "TIME"
	TypeDateTime     = "TIMESTAMP"
	TypeDateTimeTz   = "TIMESTAMPTZ"
	CurrentDate      = "CURRENT_DATE"
	CurrentTime      = "CURRENT_TIME"
	CurrentDateTime  = "NOW()"
	TypeDblPrecision = "DOUBLE PRECISION"
	TypeNumeric      = "NUMERIC"
	TypeTsVector     = "TSVECTOR"
	TypeTsQuery      = "TSQUERY"
	TypeJson         = "JSON"
	TypeJsonb        = "JSONB"
	TypePoint        = "POINT"
	TypePolygon      = "POLYGON"
)

column types

View Source 
const (
	DefaultSchema  = "public"
	SemiColon      = ";"
	AlterTable     = "ALTER TABLE "
	Add            = " ADD "
	Modify         = " ALTER "
	Drop           = " DROP "
	Rename         = " RENAME "
	IfExistsExp    = " IF EXISTS "
	IfNotExistsExp = " IF NOT EXISTS "
	Concurrently   = " CONCURRENTLY "
	Constraint     = " CONSTRAINT "
)

specific for PostgreSQL driver + SQL std

View Source 
const (
	IfExistsUndeclared = iota
	IfExists
	IfNotExists
)

Variables

View Source 
var ErrNoMoreRows = errors.New("sql: no more rows")

ErrNoMoreRows is returned by Next when there were no more rows

Functions

This section is empty.

Types

type Connection 

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

Connection encloses DB struct

func NewConnection 

func NewConnection(driverName, dataSourceName string) *Connection

NewConnection returns pre-defined Connection structure

func NewConnectionFromDb added in v0.8.7 

func NewConnectionFromDb(db *sql.DB) *Connection

NewConnectionFromDb returns re-defined Connection structure created via db handle with connection(s)

type DB 

type DB struct {
	Builder *builder
	Conn    *Connection
	Txn     *Txn
}

DB is an entity that composite builder and Conn types

func NewDb 

func NewDb(c *Connection) *DB

NewDb constructs default DB structure

func (*DB) AddSelect 

func (r *DB) AddSelect(args ...string) *DB

AddSelect accepts additional columns to select from a table

func (*DB) AndHavingRaw 

func (r *DB) AndHavingRaw(raw string) *DB

AndHavingRaw accepts custom string to apply it to having clause with logical OR

func (*DB) AndWhere 

func (r *DB) AndWhere(operand, operator string, val any) *DB

AndWhere accepts left operand-operator-right operand to apply them to where clause with AND logical operator

func (*DB) AndWhereBetween 

func (r *DB) AndWhereBetween(col string, val1, val2 any) *DB

AndWhereBetween sets the clause AND BETWEEN 2 values

func (*DB) AndWhereIn 

func (r *DB) AndWhereIn(field string, in any) *DB

AndWhereIn appends OR IN (val1, val2, val3...) stmt to WHERE clause

func (*DB) AndWhereNotBetween 

func (r *DB) AndWhereNotBetween(col string, val1, val2 any) *DB

AndWhereNotBetween sets the clause AND BETWEEN 2 values

func (*DB) AndWhereNotIn 

func (r *DB) AndWhereNotIn(field string, in any) *DB

AndWhereNotIn appends OR NOT IN (val1, val2, val3...) stmt to WHERE clause

func (*DB) AndWhereNotNull 

func (r *DB) AndWhereNotNull(field string) *DB

AndWhereNotNull appends fieldName IS NOT NULL stmt to WHERE clause

func (*DB) AndWhereNull 

func (r *DB) AndWhereNull(field string) *DB

AndWhereNull appends fieldName IS NULL stmt to WHERE clause

func (*DB) AndWhereRaw 

func (r *DB) AndWhereRaw(raw string) *DB

AndWhereRaw accepts custom string to apply it to where clause with logical OR

func (*DB) Avg 

func (r *DB) Avg(column string) (avg float64, err error)

Avg calculates average for specified column

func (*DB) Chunk 

func (r *DB) Chunk(src any, amount int64, fn func(rows []any) bool) error

Chunk run queries by chinks by passing user-land function with an ability to stop execution when needed by returning false and proceed to execute queries when return true

func (*DB) Count 

func (r *DB) Count() (cnt int64, err error)

Count counts resulting rows based on clause

func (*DB) Dd 

func (r *DB) Dd()

Dd prints raw sql to stdout and exit

func (*DB) Decrement 

func (r *DB) Decrement(column string, on uint64) (int64, error)

Decrement column on passed value

func (*DB) Delete 

func (r *DB) Delete() (int64, error)

Delete builds a DELETE stmt with corresponding where clause if stated returning affected rows

func (*DB) DoesntExists 

func (r *DB) DoesntExists() (bool, error)

DoesntExists an inverse of Exists

func (*DB) Drop 

func (r *DB) Drop(tables string) (sql.Result, error)

Drop drops >=1 tables

func (*DB) DropIfExists 

func (r *DB) DropIfExists(tables ...string) (res sql.Result, err error)

DropIfExists drops >=1 tables if they are existent

func (*DB) Dump 

func (r *DB) Dump()

Dump prints raw sql to stdout

func (*DB) EachToStruct added in v1.0.0 

func (r *DB) EachToStruct(fn EachToStructFunc) error

EachToStruct scans query into specific struct per row with iterative behaviour

func (*DB) Exists 

func (r *DB) Exists() (exists bool, err error)

Exists checks whether conditional rows are existing (returns true) or not (returns false)

func (*DB) Find added in v0.7.6 

func (r *DB) Find(src any, id uint64) error

Find retrieves a single row by it's id column value

func (*DB) First 

func (r *DB) First(src any) error

First getting the 1st row of query

func (*DB) From 

func (r *DB) From(fromTbl string) *DB

From prepares sql stmt to set data from another table, ex.: UPDATE employees SET sales_count = sales_count + 1 FROM accounts

func (*DB) FullJoin 

func (r *DB) FullJoin(table, left, operator, right string) *DB

FullJoin joins tables by getting all elements of both sets

func (*DB) FullOuterJoin 

func (r *DB) FullOuterJoin(table, left, operator, right string) *DB

FullOuterJoin joins tables by getting an outer sets

func (*DB) GroupBy 

func (r *DB) GroupBy(expr string) *DB

GroupBy adds GROUP BY expression to SQL stmt

func (*DB) HasColumns 

func (r *DB) HasColumns(schema, tbl string, cols ...string) (colsExists bool, err error)

HasColumns checks whether those cols exists in a particular schema/table

func (*DB) HasTable 

func (r *DB) HasTable(schema, tbl string) (tblExists bool, err error)

HasTable determines whether table exists in particular schema

func (*DB) Having 

func (r *DB) Having(operand, operator string, val any) *DB

Having similar to Where but used with GroupBy to apply over the grouped results

func (*DB) HavingRaw 

func (r *DB) HavingRaw(raw string) *DB

HavingRaw accepts custom string to apply it to having clause

func (*DB) InRandomOrder 

func (r *DB) InRandomOrder() *DB

InRandomOrder add ORDER BY random() - note be cautious on big data-tables it can lead to slowing down perf

func (*DB) InTransaction 

func (r *DB) InTransaction(fn func() (any, error)) error

InTransaction executes fn passed as an argument in transaction mode if there are no results returned - txn will be rolled back, otherwise committed and returned

func (*DB) Increment 

func (r *DB) Increment(column string, on uint64) (int64, error)

Increment column on passed value

func (*DB) InnerJoin 

func (r *DB) InnerJoin(table, left, operator, right string) *DB

InnerJoin joins tables by getting elements if found in both

func (*DB) Insert 

func (r *DB) Insert(data any) error

Insert inserts one row with param bindings for struct

func (*DB) InsertBatch 

func (r *DB) InsertBatch(data any) error

InsertBatch inserts multiple rows based on transaction

func (*DB) InsertGetId 

func (r *DB) InsertGetId(data any) (uint64, error)

InsertGetId inserts one row with param bindings and returning id

func (*DB) LeftJoin 

func (r *DB) LeftJoin(table, left, operator, right string) *DB

LeftJoin joins tables by getting elements from left without those that null on the right

func (*DB) Limit 

func (r *DB) Limit(lim int64) *DB

Limit accepts limit to end slicing results to

func (*DB) LockForUpdate 

func (r *DB) LockForUpdate() *DB

LockForUpdate locks table/row

func (*DB) Max 

func (r *DB) Max(column string) (max float64, err error)

Max calculates maximum for specified column

func (*DB) Min 

func (r *DB) Min(column string) (min float64, err error)

Min calculates minimum for specified column

func (*DB) Next added in v1.0.0 

func (r *DB) Next(rows *sql.Rows, src any) error

Next will parse the next row into a struct passed as src parameter. Returns ErrNoMoreRows if there are no more row to parse

func (*DB) Offset 

func (r *DB) Offset(off int64) *DB

Offset accepts offset to start slicing results from

func (*DB) OrHavingRaw 

func (r *DB) OrHavingRaw(raw string) *DB

OrHavingRaw accepts custom string to apply it to having clause with logical OR

func (*DB) OrWhere 

func (r *DB) OrWhere(operand, operator string, val any) *DB

OrWhere accepts left operand-operator-right operand to apply them to where clause with OR logical operator

func (*DB) OrWhereBetween 

func (r *DB) OrWhereBetween(col string, val1, val2 any) *DB

OrWhereBetween sets the clause OR BETWEEN 2 values

func (*DB) OrWhereIn 

func (r *DB) OrWhereIn(field string, in any) *DB

OrWhereIn appends OR IN (val1, val2, val3...) stmt to WHERE clause

func (*DB) OrWhereNotBetween 

func (r *DB) OrWhereNotBetween(col string, val1, val2 any) *DB

OrWhereNotBetween sets the clause OR BETWEEN 2 values

func (*DB) OrWhereNotIn 

func (r *DB) OrWhereNotIn(field string, in any) *DB

OrWhereNotIn appends OR NOT IN (val1, val2, val3...) stmt to WHERE clause

func (*DB) OrWhereNotNull 

func (r *DB) OrWhereNotNull(field string) *DB

OrWhereNotNull appends fieldName IS NOT NULL stmt to WHERE clause

func (*DB) OrWhereNull 

func (r *DB) OrWhereNull(field string) *DB

OrWhereNull appends fieldName IS NULL stmt to WHERE clause

func (*DB) OrWhereRaw 

func (r *DB) OrWhereRaw(raw string) *DB

OrWhereRaw accepts custom string to apply it to where clause with logical OR

func (*DB) OrderBy 

func (r *DB) OrderBy(column string, direction string) *DB

OrderBy adds ORDER BY expression to SQL stmt

func (*DB) OrderByRaw 

func (r *DB) OrderByRaw(exp string) *DB

OrderByRaw adds ORDER BY raw expression to SQL stmt

func (*DB) Pluck 

func (r *DB) Pluck(src any) ([]any, error)

Pluck getting values of a particular column(s) of a struct and place them into slice

func (*DB) PluckMap 

func (r *DB) PluckMap(src any, colKey, colValue string) (val []map[any]any, err error)

PluckMap getting values of a particular key/value columns and place them into map values of the returning map is a structure passed as src and filled with data from DB

func (*DB) Rename 

func (r *DB) Rename(from, to string) (sql.Result, error)

Rename renames from - to new table name

func (*DB) Replace 

func (r *DB) Replace(data any, conflict string) (int64, error)

Replace inserts data if conflicting row hasn't been found, else it will update an existing one

func (*DB) RightJoin 

func (r *DB) RightJoin(table, left, operator, right string) *DB

RightJoin joins tables by getting elements from right without those that null on the left

func (*DB) ScanStruct added in v1.0.0 

func (r *DB) ScanStruct(src any) error

ScanStruct scans query into specific struct

func (*DB) Schema 

func (r *DB) Schema(tblName string, fn func(table *Table) error) (res sql.Result, err error)

Schema creates and/or manipulates table structure with an appropriate types/indices/comments/defaults/nulls etc

func (*DB) SchemaIfNotExists added in v0.8.0 

func (r *DB) SchemaIfNotExists(tblName string, fn func(table *Table) error) (res sql.Result, err error)

SchemaIfNotExists creates table structure if not exists with an appropriate types/indices/comments/defaults/nulls etc

func (*DB) Select 

func (r *DB) Select(args ...string) *DB

Select accepts columns to select from a table

func (*DB) SelectRaw 

func (r *DB) SelectRaw(raw string) *DB

SelectRaw accepts custom string to select from a table

func (*DB) Sql 

func (r *DB) Sql() *sql.DB

Sql returns DB struct

func (*DB) Sum 

func (r *DB) Sum(column string) (sum float64, err error)

Sum calculates sum for specified column

func (*DB) Table 

func (r *DB) Table(table string) *DB

Table appends table name to sql query

func (*DB) Truncate 

func (r *DB) Truncate(tables string) (sql.Result, error)

Truncate clears >=1 tables

func (*DB) Union 

func (r *DB) Union() *DB

Union joins multiple queries omitting duplicate records

func (*DB) UnionAll 

func (r *DB) UnionAll() *DB

UnionAll joins multiple queries to select all rows from both tables with duplicate

func (*DB) Update 

func (r *DB) Update(data any) (int64, error)

Update builds an UPDATE sql stmt with corresponding where/from clauses if stated returning affected rows

func (*DB) Value 

func (r *DB) Value(src any, column string) error

Value gets the value of column in first query resulting row

func (*DB) Where 

func (r *DB) Where(operand, operator string, val any) *DB

Where accepts left operand-operator-right operand to apply them to where clause

func (*DB) WhereBetween 

func (r *DB) WhereBetween(col string, val1, val2 any) *DB

WhereBetween sets the clause BETWEEN 2 values

func (*DB) WhereExists 

func (r *DB) WhereExists(rr *DB) *DB

WhereExists constructs one builder from another to implement WHERE EXISTS sql/dml clause

func (*DB) WhereIn 

func (r *DB) WhereIn(field string, in any) *DB

WhereIn appends IN (val1, val2, val3...) stmt to WHERE clause

func (*DB) WhereNotBetween 

func (r *DB) WhereNotBetween(col string, val1, val2 any) *DB

WhereNotBetween sets the clause NOT BETWEEN 2 values

func (*DB) WhereNotExists 

func (r *DB) WhereNotExists(rr *DB) *DB

WhereNotExists constructs one builder from another to implement WHERE NOT EXISTS sql/dml clause

func (*DB) WhereNotIn 

func (r *DB) WhereNotIn(field string, in any) *DB

WhereNotIn appends NOT IN (val1, val2, val3...) stmt to WHERE clause

func (*DB) WhereNotNull 

func (r *DB) WhereNotNull(field string) *DB

WhereNotNull appends fieldName IS NOT NULL stmt to WHERE clause

func (*DB) WhereNull 

func (r *DB) WhereNull(field string) *DB

WhereNull appends fieldName IS NULL stmt to WHERE clause

func (*DB) WhereRaw 

func (r *DB) WhereRaw(raw string) *DB

WhereRaw accepts custom string to apply it to where clause

type EachToStructFunc added in v1.0.0 

type EachToStructFunc func(rows *sql.Rows) error

type Table 

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

Table is the type for operations on table schema

func (*Table) BigIncrements 

func (t *Table) BigIncrements(colNm string) *Table

BigIncrements creates auto incremented primary key big integer column

func (*Table) BigInt 

func (t *Table) BigInt(colNm string) *Table

BigInt creates big integer column

func (*Table) Boolean added in v0.7.7 

func (t *Table) Boolean(colNm string) *Table

Boolean creates boolean type column

func (*Table) Change 

func (t *Table) Change()

Change the column type/length/nullable etc options

func (*Table) Char 

func (t *Table) Char(colNm string, len uint64) *Table

Char creates char(len) column

func (*Table) Collation 

func (t *Table) Collation(coll string) *Table

Collation sets the last column to specified collation

func (*Table) Comment 

func (t *Table) Comment(cmt string) *Table

Comment sets the column comment

func (*Table) Concurrently added in v0.8.1 

func (t *Table) Concurrently() *Table

func (*Table) Date 

func (t *Table) Date(colNm string, isDefault bool) *Table

Date creates date column with an ability to set current_date as default value

func (*Table) DateTime 

func (t *Table) DateTime(colNm string, isDefault bool) *Table

DateTime creates datetime column with an ability to set NOW() as default value

func (*Table) DateTimeTz 

func (t *Table) DateTimeTz(colNm string, isDefault bool) *Table

DateTimeTz creates datetime column with an ability to set NOW() as default value + time zone support

func (*Table) DblPrecision 

func (t *Table) DblPrecision(colNm string) *Table

DblPrecision creates dbl precision type column

func (*Table) Decimal 

func (t *Table) Decimal(colNm string, precision, scale uint64) *Table

Decimal alias for Numeric as for PostgreSQL they are the same

func (*Table) Default 

func (t *Table) Default(val interface{}) *Table

Default sets the default column value

func (*Table) DropColumn 

func (t *Table) DropColumn(colNm string) *Table

DropColumn the column named colNm in this table context

func (*Table) DropIndex 

func (t *Table) DropIndex(idxNm string) *Table

DropIndex the column named idxNm in this table context

func (*Table) ForeignKey 

func (t *Table) ForeignKey(idxName, rfcTbl, onCol string) *Table

ForeignKey sets the last column to reference rfcTbl on onCol with idxName foreign key index

func (*Table) IfExists added in v0.8.0 

func (t *Table) IfExists() *Table

IfExists drop column/index if exists

func (*Table) IfNotExists added in v0.8.0 

func (t *Table) IfNotExists() *Table

IfNotExists add column/index if not exists

func (*Table) Include added in v0.8.2 

func (t *Table) Include(columns ...string) *Table

func (*Table) Increments 

func (t *Table) Increments(colNm string) *Table

Increments creates auto incremented primary key integer column

func (*Table) Index 

func (t *Table) Index(idxName string) *Table

Index sets the last column to btree index

func (*Table) Integer 

func (t *Table) Integer(colNm string) *Table

Integer creates an integer column

func (*Table) Json 

func (t *Table) Json(colNm string) *Table

Json creates json text typed column

func (*Table) Jsonb 

func (t *Table) Jsonb(colNm string) *Table

Jsonb creates jsonb typed column

func (*Table) NotNull 

func (t *Table) NotNull() *Table

NotNull sets the last column to not null

func (*Table) Numeric 

func (t *Table) Numeric(colNm string, precision, scale uint64) *Table

Numeric creates exact, user-specified precision number

func (*Table) Point 

func (t *Table) Point(colNm string) *Table

Point creates point geometry typed column

func (*Table) Polygon 

func (t *Table) Polygon(colNm string) *Table

Polygon creates point geometry typed column

func (*Table) Rename 

func (t *Table) Rename(from, to string) *Table

Rename the column "from" to the "to"

func (*Table) RenameIndex added in v1.0.6 

func (t *Table) RenameIndex(idxName, newName string) *Table

RenameIndex changes the name of a particular index

func (*Table) SmallInt 

func (t *Table) SmallInt(colNm string) *Table

SmallInt creates small integer column

func (*Table) String 

func (t *Table) String(colNm string, len uint64) *Table

String creates varchar(len) column

func (*Table) TableComment 

func (t *Table) TableComment(cmt string)

TableComment sets the comment for table

func (*Table) Text 

func (t *Table) Text(colNm string) *Table

Text creates text type column

func (*Table) Time 

func (t *Table) Time(colNm string, isDefault bool) *Table

Time creates time column with an ability to set current_time as default value

func (*Table) TsQuery 

func (t *Table) TsQuery(colNm string) *Table

TsQuery creates tsquery typed column

func (*Table) TsVector 

func (t *Table) TsVector(colNm string) *Table

TsVector creates tsvector typed column

func (*Table) Unique 

func (t *Table) Unique(idxName string) *Table

Unique sets the last column to unique index

type Txn added in v0.8.9 

type Txn struct {
	Tx      *sql.Tx
	Builder *builder
}

func (*Txn) Delete added in v0.8.9 

func (r *Txn) Delete() (int64, error)

Delete builds a DELETE stmt with corresponding where clause if stated returning affected rows

func (*Txn) Insert added in v0.8.9 

func (r *Txn) Insert(data any) error

Insert inserts one row with param bindings from struct in transaction context

func (*Txn) InsertGetId added in v0.8.9 

func (r *Txn) InsertGetId(data any) (uint64, error)

InsertGetId inserts one row with param bindings and returning id in transaction context

func (*Txn) Replace added in v0.8.9 

func (r *Txn) Replace(data any, conflict string) (int64, error)

Replace inserts data if conflicting row hasn't been found, else it will update an existing one

func (*Txn) Update added in v0.8.9 

func (r *Txn) Update(data any) (int64, error)

Update builds an UPDATE sql stmt with corresponding where/from clauses if stated returning affected rows

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.