sqlingo

README

sqlingo is a SQL DSL (a.k.a. SQL Builder or ORM) library in Go. It generates code from the database and lets you write SQL queries in an elegant way.

Features

• Auto-generating DSL objects and model structs from the database so you don't need to manually keep things in sync
• SQL DML (SELECT / INSERT / UPDATE / DELETE) with some advanced SQL query syntaxes
• Many common errors could be detected at compile time
• Your can use the features in your editor / IDE, such as autocompleting the fields and queries, or finding the usage of a field or a table
• Context support
• Transaction support
• Interceptor support

Database Support Status

Database	Status
MySQL	stable
PostgreSQL	experimental
SQLite	experimental

Tutorial

Install and use sqlingo code generator

The first step is to generate code from the database. In order to generate code, sqlingo requires your tables are already created in the database.

$ go install github.com/Ficoto/sqlingo/sqlingo-gen-mysql
$ mkdir -p generated/sqlingo
$ sqlingo-gen-mysql -dbc root:123456@/database_name -o generated/sqlingo

Write your application

Here's a demonstration of some simple & advanced usage of sqlingo.

package main

import (
    "github.com/lqs/sqlingo"
    . "./generated/sqlingo"
)

func main() {
    db, err := sqlingo.Open("mysql", "root:123456@/database_name")
    if err != nil {
        panic(err)
    }

    // a simple query
    var customers []*CustomerModel
    db.SelectFrom(Customer).
        Where(Customer.Id.In(1, 2)).
    	OrderBy(Customer.Name.Desc()).
        FetchAll(&customers)

    // query from multiple tables
    var customerId int64
    var orderId int64
    err = db.Select(Customer.Id, Order.Id).
        From(Customer, Order).
        Where(Customer.Id.Equals(Order.CustomerId), Order.Id.Equals(1)).
        FetchFirst(&customerId, &orderId)
    
    // subquery and count
    count, err := db.SelectFrom(Order)
        Where(Order.CustomerId.In(db.Select(Customer.Id).
            From(Customer).
            Where(Customer.Name.Equals("Customer One")))).
    	Count()
        
    // group-by with auto conversion to map
    var customerIdToOrderCount map[int64]int64
    err = db.Select(Order.CustomerId, f.Count(1)).
    	From(Order).
    	GroupBy(Order.CustomerId).
    	FetchAll(&customerIdToOrderCount)
    if err != nil {
    	println(err)
    }
    
    // insert some rows
    customer1 := &CustomerModel{name: "Customer One"}
    customer2 := &CustomerModel{name: "Customer Two"}
    _, err = db.InsertInto(Customer).
        Models(customer1, customer2).
        Execute()
    
    // insert with on-duplicate-key-update
    _, err = db.InsertInto(Customer).
    	Fields(Customer.Id, Customer.Name).
    	Values(42, "Universe").
    	OnDuplicateKeyUpdate().
    	Set(Customer.Name, Customer.Name.Concat(" 2")).
    	Execute()
}

Documentation

Index

• Constants
• type Alias
• type Assignment
• type BooleanExpression
  • func And(expressions ...BooleanExpression) (result BooleanExpression)
  • func Or(expressions ...BooleanExpression) (result BooleanExpression)
• type BooleanField
  • func NewBooleanField(table Table, fieldName string) BooleanField
• type CaseExpression
  • func Case() CaseExpression
• type CaseExpressionWithElse
• type Cursor
• type Database
  • func Open(driverName string, dataSourceName string) (db Database, err error)
  • func Use(driverName string, sqlDB *sql.DB) Database
• type Expression
  • func Function(name string, args ...interface{}) Expression
• type Field
• type InterceptorFunc
• type InvokerFunc
• type Model
• type NumberExpression
  • func Count(arg interface{}) NumberExpression
  • func Length(arg interface{}) NumberExpression
  • func Sum(arg interface{}) NumberExpression
• type NumberField
  • func NewNumberField(table Table, fieldName string) NumberField
• type OrderBy
• type StringExpression
  • func Concat(args ...interface{}) StringExpression
• type StringField
  • func NewStringField(table Table, fieldName string) StringField
• type Table
  • func NewTable(name string) Table
• type Transaction
• type UnknownExpression
  • func If(predicate Expression, trueValue interface{}, falseValue interface{}) (result UnknownExpression)
  • func Raw(sql string) UnknownExpression
• type WellKnownBinary
• type WellKnownBinaryExpression
  • func STGeomFromText(text interface{}) WellKnownBinaryExpression
  • func STGeomFromTextf(format string, a ...interface{}) WellKnownBinaryExpression
• type WellKnownBinaryField
  • func NewWellKnownBinaryField(table Table, fieldName string) WellKnownBinaryField

Constants

const (
	// SqlingoRuntimeVersion is the the runtime version of sqlingo
	SqlingoRuntimeVersion = 2
)

Types

type Alias

type Alias interface {
	GetSQL(scope scope) (string, error)
}

Alias is the interface of an table/column alias.

type Assignment

type Assignment interface {
	GetSQL(scope scope) (string, error)
}

Assignment is an assignment statement

type BooleanExpression

type BooleanExpression interface {
	Expression
	And(other interface{}) BooleanExpression
	Or(other interface{}) BooleanExpression
	Xor(other interface{}) BooleanExpression
	Not() BooleanExpression
}

BooleanExpression is the interface of an SQL expression with boolean value.

func And

func And(expressions ...BooleanExpression) (result BooleanExpression)

And creates an expression with AND operator.

func Or

func Or(expressions ...BooleanExpression) (result BooleanExpression)

Or creates an expression with OR operator.

type BooleanField

type BooleanField interface {
	BooleanExpression
	GetTable() Table
}

BooleanField is the interface of a generated field of boolean type.

func NewBooleanField

func NewBooleanField(table Table, fieldName string) BooleanField

NewBooleanField creates a reference to a boolean field. It should only be called from generated code.

type CaseExpression

type CaseExpression interface {
	WhenThen(when BooleanExpression, then interface{}) CaseExpression
	Else(value interface{}) CaseExpressionWithElse
	End() Expression
}

CaseExpression indicates the status in a CASE statement

func Case

func Case() CaseExpression

Case initiates a CASE statement

type CaseExpressionWithElse

type CaseExpressionWithElse interface {
	End() Expression
}

CaseExpressionWithElse indicates the status in CASE ... ELSE ... statement

type Cursor

type Cursor interface {
	Next() bool
	Scan(dest ...interface{}) error
	GetMap() (map[string]value, error)
	Close() error
}

Cursor is the interface of a row cursor.

type Database

type Database interface {
	// Get the underlying sql.DB object of the database
	GetDB() *sql.DB
	BeginTx(ctx context.Context, opts *sql.TxOptions, f func(tx Transaction) error) error
	// Executes a query and return the cursor
	Query(sql string) (Cursor, error)
	// Executes a query with context and return the cursor
	QueryContext(ctx context.Context, sqlString string) (Cursor, error)
	// Executes a statement
	Execute(sql string) (sql.Result, error)
	// Executes a statement with context
	ExecuteContext(ctx context.Context, sql string) (sql.Result, error)
	// Set the logger function
	SetLogger(logger func(sql string, durationNano int64))
	// Set the retry policy function.
	// The retry policy function returns true if needs retry.
	SetRetryPolicy(retryPolicy func(err error) bool)
	// enable or disable caller info
	EnableCallerInfo(enableCallerInfo bool)
	// Set a interceptor function
	SetInterceptor(interceptor InterceptorFunc)

	// Initiate a SELECT statement
	Select(fields ...interface{}) selectWithFields
	// Initiate a SELECT DISTINCT statement
	SelectDistinct(fields ...interface{}) selectWithFields
	// Initiate a SELECT * FROM statement
	SelectFrom(tables ...Table) selectWithTables
	// Initiate a INSERT INTO statement
	InsertInto(table Table) insertWithTable
	// Initiate a REPLACE INTO statement
	ReplaceInto(table Table) insertWithTable
	// Initiate a UPDATE statement
	Update(table Table) updateWithSet
	// Initiate a DELETE FROM statement
	DeleteFrom(table Table) deleteWithTable
}

Database is the interface of a database with underlying sql.DB object.

func Open

func Open(driverName string, dataSourceName string) (db Database, err error)

Open a database, similar to sql.Open

func Use

func Use(driverName string, sqlDB *sql.DB) Database

Use an existing *sql.DB handle

type Expression

type Expression interface {
	// get the SQL string
	GetSQL(scope scope) (string, error)

	// <> operator
	NotEquals(other interface{}) BooleanExpression
	// == operator
	Equals(other interface{}) BooleanExpression
	// < operator
	LessThan(other interface{}) BooleanExpression
	// <= operator
	LessThanOrEquals(other interface{}) BooleanExpression
	// > operator
	GreaterThan(other interface{}) BooleanExpression
	// >= operator
	GreaterThanOrEquals(other interface{}) BooleanExpression

	IsNull() BooleanExpression
	IsNotNull() BooleanExpression
	In(values ...interface{}) BooleanExpression
	NotIn(values ...interface{}) BooleanExpression
	Between(min interface{}, max interface{}) BooleanExpression
	NotBetween(min interface{}, max interface{}) BooleanExpression
	Desc() OrderBy

	As(alias string) Alias

	IfNull(altValue interface{}) Expression
	// contains filtered or unexported methods
}

Expression is the interface of an SQL expression.

func Function

func Function(name string, args ...interface{}) Expression

Function creates an expression of the call to specified function.

type Field

type Field interface {
	Expression
	GetTable() Table
}

Field is the interface of a generated field.

type InterceptorFunc

type InterceptorFunc = func(ctx context.Context, sql string, invoker InvokerFunc) error

InterceptorFunc is the function type of an interceptor. An interceptor should implement this function to fulfill it's purpose.

type InvokerFunc

type InvokerFunc = func(ctx context.Context, sql string) error

InvokerFunc is the function type of the actual invoker. It should be called in an interceptor.

type Model

type Model interface {
	GetTable() Table
	GetValues() []interface{}
}

Model is the interface of generated model struct

type NumberExpression

type NumberExpression interface {
	Expression
	Add(other interface{}) NumberExpression
	Sub(other interface{}) NumberExpression
	Mul(other interface{}) NumberExpression
	Div(other interface{}) NumberExpression
	IntDiv(other interface{}) NumberExpression
	Mod(other interface{}) NumberExpression

	Sum() NumberExpression
	Avg() NumberExpression
	Min() UnknownExpression
	Max() UnknownExpression
}

NumberExpression is the interface of an SQL expression with number value.

func Count

func Count(arg interface{}) NumberExpression

Count creates an expression of COUNT aggregator.

func Length

func Length(arg interface{}) NumberExpression

Length creates an expression of LENGTH function.

func Sum

func Sum(arg interface{}) NumberExpression

Sum creates an expression of SUM aggregator.

type NumberField

type NumberField interface {
	NumberExpression
	GetTable() Table
}

NumberField is the interface of a generated field of number type.

func NewNumberField

func NewNumberField(table Table, fieldName string) NumberField

NewNumberField creates a reference to a number field. It should only be called from generated code.

type OrderBy

type OrderBy interface {
	GetSQL(scope scope) (string, error)
}

OrderBy indicates the ORDER BY column and the status of descending order.

type StringExpression

type StringExpression interface {
	Expression
	Min() UnknownExpression
	Max() UnknownExpression
	Like(other interface{}) BooleanExpression
	Contains(substring string) BooleanExpression
	Concat(other interface{}) StringExpression
	IfEmpty(altValue interface{}) StringExpression
	IsEmpty() BooleanExpression
}

StringExpression is the interface of an SQL expression with string value.

func Concat

func Concat(args ...interface{}) StringExpression

Concat creates an expression of CONCAT function.

type StringField

type StringField interface {
	StringExpression
	GetTable() Table
}

StringField is the interface of a generated field of string type.

func NewStringField

func NewStringField(table Table, fieldName string) StringField

NewStringField creates a reference to a string field. It should only be called from generated code.

type Table

type Table interface {
	GetName() string
	GetSQL(scope scope) string
	GetFields() []Field
}

Table is the interface of a generated table.

func NewTable

func NewTable(name string) Table

NewTable creates a reference to a table. It should only be called from generated code.

type Transaction

type Transaction interface {
	GetDB() *sql.DB
	GetTx() *sql.Tx
	Query(sql string) (Cursor, error)
	Execute(sql string) (sql.Result, error)

	Select(fields ...interface{}) selectWithFields
	SelectDistinct(fields ...interface{}) selectWithFields
	SelectFrom(tables ...Table) selectWithTables
	InsertInto(table Table) insertWithTable
	Update(table Table) updateWithSet
	DeleteFrom(table Table) deleteWithTable
}

Transaction is the interface of a transaction with underlying sql.Tx object.

type UnknownExpression

type UnknownExpression interface {
	Expression
	And(other interface{}) BooleanExpression
	Or(other interface{}) BooleanExpression
	Xor(other interface{}) BooleanExpression
	Not() BooleanExpression
	Add(other interface{}) NumberExpression
	Sub(other interface{}) NumberExpression
	Mul(other interface{}) NumberExpression
	Div(other interface{}) NumberExpression
	IntDiv(other interface{}) NumberExpression
	Mod(other interface{}) NumberExpression

	Sum() NumberExpression
	Avg() NumberExpression
	Min() UnknownExpression
	Max() UnknownExpression

	Like(other interface{}) BooleanExpression
	Contains(substring string) BooleanExpression
	Concat(other interface{}) StringExpression
	IfEmpty(altValue interface{}) StringExpression
	IsEmpty() BooleanExpression
}

UnknownExpression is the interface of an SQL expression with unknown value.

func If

func If(predicate Expression, trueValue interface{}, falseValue interface{}) (result UnknownExpression)

If creates an expression of IF function.

func Raw

func Raw(sql string) UnknownExpression

Raw create a raw SQL statement

type WellKnownBinary

type WellKnownBinary []byte

WellKnownBinary is the type of geometry well-known binary (WKB) field.

type WellKnownBinaryExpression

type WellKnownBinaryExpression interface {
	Expression
	STAsText() StringExpression
}

WellKnownBinaryExpression is the interface of an SQL expression with binary geometry (WKB) value.

func STGeomFromText

func STGeomFromText(text interface{}) WellKnownBinaryExpression

func STGeomFromTextf

func STGeomFromTextf(format string, a ...interface{}) WellKnownBinaryExpression

type WellKnownBinaryField

type WellKnownBinaryField interface {
	WellKnownBinaryExpression
	GetTable() Table
}

WellKnownBinaryField is the interface of a generated field of binary geometry (WKB) type.

func NewWellKnownBinaryField

func NewWellKnownBinaryField(table Table, fieldName string) WellKnownBinaryField

NewWellKnownBinaryField creates a reference to a geometry WKB field. It should only be called from generated code.