sqlingo

package module
v0.12.1 Latest Latest
Warning

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

 Go to latest Published: Aug 31, 2023 License: MIT Imports: 14 Imported by: 0

README

Mentioned in Awesome Go go.dev Travis CI Go Report Card codecov MIT license last commit

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

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

Variables

This section is empty.

Functions

This section is empty.

Types

type Alias 

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

Alias is the interface of an table/column alias.

type ArrayDimension added in v0.12.0 

type ArrayDimension struct {
	Length     int32
	LowerBound int32
}

type ArrayExpression added in v0.12.0 

type ArrayExpression interface {
	Expression
}

type ArrayField added in v0.12.0 

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 added in v0.11.5 

func False() BooleanExpression

func Or 

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

Or creates an expression with OR operator.

func True added in v0.11.5 

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 added in v0.11.2 

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
	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 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 added in v0.10.12 

type WellKnownBinary []byte

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

type WellKnownBinaryExpression added in v0.10.12 

type WellKnownBinaryExpression interface {
	Expression
	STAsText() StringExpression
}

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

func STGeomFromText added in v0.10.12 

func STGeomFromText(text interface{}) WellKnownBinaryExpression

func STGeomFromTextf added in v0.10.12 

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

type WellKnownBinaryField added in v0.10.12 

type WellKnownBinaryField interface {
	WellKnownBinaryExpression
	GetTable() Table
}

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

func NewWellKnownBinaryField added in v0.10.12 

func NewWellKnownBinaryField(table Table, fieldName string) WellKnownBinaryField

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

Source Files

View all Source files

Directories

Path Synopsis

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.