sqlingo

package module
v0.0.0-...-d32d0ea Latest Latest
Warning

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

 Go to latest Published: Jul 25, 2024 License: MIT Imports: 17 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
  • 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

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

Variables

This section is empty.

Functions

func DefaultLogger 

func DefaultLogger(sql string, duration time.Duration, isTx bool, retry bool)

DefaultLogger is sqlingo default logger, which print log to stderr and regard executing time gt 100ms as slow sql.

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 LoggerFunc)
	// Set the retry policy function.
	// The retry policy function returns true if needs retry.
	SetRetryPolicy(retryPolicy func(err error) bool)
	// EnableCallerInfo enable or disable the caller info in the log.
	// Deprecated: use SetLogger instead
	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. `db` using a default logger, which print log to stderr and regard executing time gt 100ms as slow sql. To disable the default logger, use `db.SetLogger(nil)`.

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 LoggerFunc 

type LoggerFunc func(sql string, duration time.Duration, isTx bool, retry bool)

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 Scanner 

type Scanner interface {
	Scan(dest ...interface{}) error
}

Scanner is the interface that wraps the Scan method.

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
	Left(count interface{}) StringExpression
	Right(count interface{}) StringExpression
	Trim() 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. It provides methods to execute DDL and TCL operations.

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
	Left(count interface{}) StringExpression
	Right(count interface{}) StringExpression
	Trim() 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.

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.