sql

README

SQL Package Documentation

The SQL package is a comprehensive Go library that facilitates seamless interaction with relational databases. Leveraging the power of GORM, it provides convenient connectors for MySQL and SQLite databases along with a flexible and expressive clause builder for constructing SQL queries.

Installation

To integrate the SQL package into your Go project, use the following go get command:

go get -u github.com/metadiv-io/sql

Connectors

MySQL Connector

Establishes a connection to a MySQL database and returns a GORM database instance.

func Connector.MySQL(host, port, username, password, database string) (*gorm.DB, error)

SQLite Connector

Connects to an SQLite database using the specified file path.

func Connector.Sqlite(path string) (*gorm.DB, error)

SQLite Memory Connector

Creates an in-memory SQLite database and returns a GORM database instance.

func Connector.SqliteMemory() (*gorm.DB, error)

Clause Builder

The SQL package includes a powerful clause builder for constructing SQL queries. The following functions assist in creating clauses:

• sql.Eq(field string, value interface{}) *Clause

• sql.Neq(field string, value interface{}) *Clause

• sql.Gt(field string, value interface{}) *Clause

• sql.Gte(field string, value interface{}) *Clause

• sql.Lt(field string, value interface{}) *Clause

• sql.Lte(field string, value interface{}) *Clause

• sql.Like(field string, value interface{}) *Clause

• sql.NotLike(field string, value interface{}) *Clause

• sql.Similar(field string, value string) *Clause

• sql.NotSimilar(field string, value string) *Clause

• sql.In(field string, value ...interface{}) *Clause

• sql.NotIn(field string, value ...interface{}) *Clause

• sql.IsNull(field string) *Clause

• sql.IsNotNull(field string) *Clause

• sql.And(children ...*Clause) *Clause

• sql.Or(children ...*Clause) *Clause

Query Functions

The SQL package provides versatile query functions:

• sql.FindOne[T](tx *gorm.DB, clause *Clause) (*T, error)

• sql.FindAll[T](tx *gorm.DB, clause *Clause) ([]T, error)

• sql.Count[T](tx *gorm.DB, clause *Clause) (int64, error)

• sql.FindAllComplex[T](tx *gorm.DB, clause *Clause, sort *Sort, pagination *Pagination) ([]T, *Pagination, error)

Write Functions

For database modification operations, the SQL package offers convenient write functions:

• Save[T](tx *gorm.DB, model *T) (*T, error)

• SaveAll[T](tx *gorm.DB, models []T) ([]T, error)

• Delete[T](tx *gorm.DB, model *T) error

• DeleteAll[T](tx *gorm.DB, models []T) error

• DeleteAllByClause[T](tx *gorm.DB, clause *Clause) error

Documentation

Index

• Constants
• func Connect(silent ...bool) *connector
• func Count[T any](tx *gorm.DB, clause *Clause) (int64, error)
• func Delete[T any](tx *gorm.DB, model *T) error
• func DeleteAll[T any](tx *gorm.DB, models []T) error
• func DeleteAllByClause[T any](tx *gorm.DB, clause *Clause) error
• func FindAll[T any](tx *gorm.DB, clause *Clause) ([]T, error)
• func FindOne[T any](tx *gorm.DB, clause *Clause) (*T, error)
• func Save[T any](tx *gorm.DB, model *T) (*T, error)
• func SaveAll[T any](tx *gorm.DB, models []T) ([]T, error)
• type Clause
  • func And(children ...*Clause) *Clause
  • func Eq(field string, value interface{}) *Clause
  • func Gt(field string, value interface{}) *Clause
  • func Gte(field string, value interface{}) *Clause
  • func In(field string, value ...interface{}) *Clause
  • func IsNotNull(field string) *Clause
  • func IsNull(field string) *Clause
  • func Like(field string, value interface{}) *Clause
  • func Lt(field string, value interface{}) *Clause
  • func Lte(field string, value interface{}) *Clause
  • func Neq(field string, value interface{}) *Clause
  • func NewClause(field, operator string, value interface{}, children ...*Clause) *Clause
  • func NotIn(field string, value ...interface{}) *Clause
  • func NotLike(field string, value interface{}) *Clause
  • func NotSimilar(field string, value string) *Clause
  • func Or(children ...*Clause) *Clause
  • func Similar(field string, value string) *Clause
  • func (c *Clause) Build() (string, []interface{})
  • func (s *Clause) Consume(tx *gorm.DB) *gorm.DB
• type Pagination
  • func FindAllComplex[T any](tx *gorm.DB, clause *Clause, sort *Sort, pagination *Pagination) ([]T, *Pagination, error)
  • func Page(page int, size int) *Pagination
  • func (p *Pagination) Consume(tx *gorm.DB) *gorm.DB
• type Sort
  • func Order(by string, asc bool) *Sort
  • func (s *Sort) Consume(tx *gorm.DB) *gorm.DB

Constants

const (
	OPERATOR_EQUAL         = "="
	OPERATOR_NOT_EQUAL     = "<>"
	OPERATOR_GREATER       = ">"
	OPERATOR_LESS          = "<"
	OPERATOR_GREATER_EQUAL = ">="
	OPERATOR_LESS_EQUAL    = "<="
	OPERATOR_LIKE          = "LIKE"
	OPERATOR_NOT_LIKE      = "NOT LIKE"
	OPERATOR_IN            = "IN"
	OPERATOR_NOT_IN        = "NOT IN"
	OPERATOR_IS_NULL       = "IS NULL"
	OPERATOR_IS_NOT_NULL   = "IS NOT NULL"
	OPERATOR_AND           = "AND"
	OPERATOR_OR            = "OR"
)

Functions

func Connect

func Connect(silent ...bool) *connector

Connect returns a new connector.

func Count

func Count[T any](tx *gorm.DB, clause *Clause) (int64, error)

Count counts the number of records that match the given clause.

func Delete

func Delete[T any](tx *gorm.DB, model *T) error

Delete deletes the given model.

func DeleteAll

func DeleteAll[T any](tx *gorm.DB, models []T) error

DeleteAll deletes all the given models.

func DeleteAllByClause

func DeleteAllByClause[T any](tx *gorm.DB, clause *Clause) error

DeleteAllByClause deletes all the models that match the given clause.

func FindAll

func FindAll[T any](tx *gorm.DB, clause *Clause) ([]T, error)

FindAll finds all records that match the given clause.

func FindOne

func FindOne[T any](tx *gorm.DB, clause *Clause) (*T, error)

FindOne finds one record that matches the given clause.

func Save

func Save[T any](tx *gorm.DB, model *T) (*T, error)

Save saves the given model.

func SaveAll

func SaveAll[T any](tx *gorm.DB, models []T) ([]T, error)

SaveAll saves all the given models.

Types

type Clause

type Clause struct {
	Field    string
	Operator string
	Value    interface{}
	Children []*Clause
}

Clause is a struct that represents a clause in a SQL statement.

func And

func And(children ...*Clause) *Clause

And creates a new Clause with the operator "AND".

func Eq

func Eq(field string, value interface{}) *Clause

Eq creates a new Clause with the operator "=".

func Gt

func Gt(field string, value interface{}) *Clause

Gt creates a new Clause with the operator ">".

func Gte

func Gte(field string, value interface{}) *Clause

Gte creates a new Clause with the operator ">=".

func In

func In(field string, value ...interface{}) *Clause

In creates a new Clause with the operator "IN".

func IsNotNull

func IsNotNull(field string) *Clause

IsNotNull creates a new Clause with the operator "IS NOT NULL".

func IsNull

func IsNull(field string) *Clause

IsNull creates a new Clause with the operator "IS NULL".

func Like

func Like(field string, value interface{}) *Clause

Like creates a new Clause with the operator "LIKE".

func Lt

func Lt(field string, value interface{}) *Clause

Lt creates a new Clause with the operator "<".

func Lte

func Lte(field string, value interface{}) *Clause

Lte creates a new Clause with the operator "<=".

func Neq

func Neq(field string, value interface{}) *Clause

Neq creates a new Clause with the operator "<>".

func NewClause

func NewClause(field, operator string, value interface{}, children ...*Clause) *Clause

NewClause creates a new Clause.

func NotIn

func NotIn(field string, value ...interface{}) *Clause

NotIn creates a new Clause with the operator "NOT IN".

func NotLike

func NotLike(field string, value interface{}) *Clause

NotLike creates a new Clause with the operator "NOT LIKE".

func NotSimilar

func NotSimilar(field string, value string) *Clause

NotSimilar creates a new Clause with the operator "NOT LIKE" and the value "%value%".

func Or

func Or(children ...*Clause) *Clause

Or creates a new Clause with the operator "OR".

func Similar

func Similar(field string, value string) *Clause

Similar creates a new Clause with the operator "LIKE" and the value "%value%".

func (*Clause) Build

func (c *Clause) Build() (string, []interface{})

Build builds the clause into a SQL statement and returns the statement and the values.

func (*Clause) Consume

func (s *Clause) Consume(tx *gorm.DB) *gorm.DB

Consume consumes the clause and applies it to the given transaction.

type Pagination

type Pagination struct {
	Page  int   `form:"page" json:"page"`
	Size  int   `form:"size" json:"size"`
	Total int64 `json:"total"` // this field is used in response, not in query
}

func FindAllComplex

func FindAllComplex[T any](tx *gorm.DB, clause *Clause, sort *Sort, pagination *Pagination) ([]T, *Pagination, error)

FindAllComplex finds all records that match the given clause and applies the given sort and pagination.

func Page

func Page(page int, size int) *Pagination

func (*Pagination) Consume

func (p *Pagination) Consume(tx *gorm.DB) *gorm.DB

type Sort

type Sort struct {
	By  string `form:"by"`
	Asc bool   `form:"asc"`
}

func Order

func Order(by string, asc bool) *Sort

func (*Sort) Consume

func (s *Sort) Consume(tx *gorm.DB) *gorm.DB