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
• Golang time.Time is supported now, but you can still use the string type by adding -timeAsString when generating the model

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/lqs/sqlingo/sqlingo-gen-mysql@latest
$ mkdir -p generated/sqlingo
$ sqlingo-gen-mysql root:123456@/database_name >generated/sqlingo/database_name.dsl.go

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 ArrayDimension
• type ArrayExpression
• type ArrayField
• type Assignment
• type BooleanExpression
  • func And(expressions ...BooleanExpression) (result BooleanExpression)
  • func False() BooleanExpression
  • func Or(expressions ...BooleanExpression) (result BooleanExpression)
  • func True() 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 DateExpression
• type DateField
  • func NewDateField(table Table, fieldName string) DateField
• type 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 Function(name string, args ...interface{}) 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 ArrayDimension

type ArrayDimension struct {
	Length     int32
	LowerBound int32
}

type ArrayExpression

type ArrayExpression interface {
	Expression
}

type ArrayField

type ArrayField interface {
	ArrayExpression
	IndexOf(index int) Field
	GetTable() Table
}

ArrayField is the interface of a generated field of array type.

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 False

func False() BooleanExpression

func Or

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

Or creates an expression with OR operator.

func True

func True() BooleanExpression

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 DateExpression

type DateExpression interface {
	Expression
}

type DateField

type DateField interface {
	DateExpression
	GetTable() Table
}

func NewDateField

func NewDateField(table Table, fieldName string) DateField

NewDateField creates a reference to a time.Time field. It should only be called from generated code.

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
	IsTrue() BooleanExpression
	IsNotTrue() BooleanExpression
	IsFalse() BooleanExpression
	IsNotFalse() 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

	If(trueValue interface{}, falseValue interface{}) UnknownExpression
	IfNull(altValue interface{}) UnknownExpression
	// contains filtered or unexported methods
}

Expression is the interface of an SQL expression.

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
	Lower() StringExpression
	Upper() StringExpression
}

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
	Lower() StringExpression
	Upper() StringExpression
}

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

func Function

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

Function creates an expression of the call to specified function.

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.