exql

package module
v1.9.4 Latest Latest
Warning

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

Go to latest
Published: Jul 13, 2022 License: MIT Imports: 16 Imported by: 0

README

exql

codecov

Safe, Strict and Clear ORM for Go

Usage

Open
package main

import (
	"github.com/apex/log"
	"github.com/loilo-inc/exql"
	"time"
)

func OpenDB() exql.DB {
	db, err := exql.Open(&exql.OpenOptions{
		// MySQL url for sql.Open()
		Url: "user:password@tcp(127.0.0.1:3306)/database?charset=utf8mb4&parseTime=True&loc=Local",
		// Max retry count for database connection failure
		MaxRetryCount: 3,
		RetryInterval: 10 * time.Second,
	})
	if err != nil {
		log.Fatalf("open error: %s", err)
		return nil
	}
	return db
}

Generate Models
package main

import (
	"github.com/loilo-inc/exql"
	"log"
)

func GenerateModels() {
	gen := exql.NewGenerator(db.DB())
	err := gen.Generate(&exql.GenerateOptions{
		// Directory path for result. Default is `model`
		OutDir: "dist",
		// Package name for models. Default is `model`
		Package: "dist",
		// Exclude table names for generation. Default is []
		Exclude: []string{
			"internal",
		},
	})
	if err != nil {
		log.Fatalf(err.Error())
	}
}

Insert
package main

import (
	"github.com/apex/log"
)

func main() {
	// Create user model
	// Primary key (id) is not needed to set. It will be ignored on building insert query.
	user := User{
		Name: "Go",
	}
	// You must pass model as a pointer.
	if result, err := db.Insert(&user); err != nil {
		log.Error(err.Error())
	} else {
		insertedId, _ := result.LastInsertId()
		// Inserted id is inserted into primary key field after insertion, if field is int64/uint64
		if insertedId != user.Id {
			log.Fatalf("impossible")
		}
	}
}

Update
package main

import (
	"github.com/apex/log"
	"github.com/loilo-inc/exql"
)

func Update() {
	// UPDATE `users` SET `name` = `GoGo` WHERE `id` = 1
	_, err := db.Update("users", exql.SET{
		"name": "GoGo",
	}, exql.Where("id = ?", 1))
	if err != nil {
		log.Errorf(err.Error())
	}
}

Map rows
package main

import "github.com/apex/log"

func Map() {
	// select query
	rows, err := db.DB().Query(`SELECT * FROM users WHERE id = ?`, 1)
	if err != nil {
		log.Errorf(err.Error())
	} else {
		// Destination model struct
		var user User
		// Passing destination to Map(). Second argument must be a pointer of model struct.
		if err := db.Map(rows, &user); err != nil {
			log.Error(err.Error())
		}
		log.Infof("%d", user.Id) // -> 1
	}
}

func MapMany() {
	rows, err := db.DB().Query(`SELECT * FROM users LIMIT ?`, 5)
	if err != nil {
		log.Errorf(err.Error())
	} else {
		// Destination model structs.
		// NOTE: It must be slice of pointer of model structure
		var users []*User
		// Passing destination to MapMany().
		// Second argument must be a pointer.
		if err := db.MapMany(rows, &users); err != nil {
			log.Error(err.Error())
		}
		log.Infof("%d", len(users)) // -> 5
	}
}

Map joined rows
package main

import (
	"github.com/apex/log"
	"github.com/loilo-inc/exql"
)

type School struct {
	Id   int64  `exql:"column:id;primary;not null;auto_increment"`
	Name string `exql:"column:name;not null"`
}
type SchoolUsers struct {
	Id       int64 `exql:"column:id;primary;not null;auto_increment"`
	UserId   int64 `exql:"column:user_id;not null"`
	SchoolId int64 `exql:"column:school_id;not null"`
}

/*
	school has many users
	users has many schools
*/
func MapSerial() {
	query := `
	SELECT * FROM users
	JOIN school_users ON school_users.user_id = users.id
	JOIN schools ON schools.id = school_users.id
	WHERE schools.id = ?`
	rows, err := db.DB().Query(query, "goland")
	if err != nil {
		log.Errorf("err")
		return
	}
	defer rows.Close()
	serialMapper := exql.NewSerialMapper(func(i int) string {
		// Each column's separator is `id`
		return "id"
	})
	var users []*User
	for rows.Next() {
		var user User
		var schoolUser SchoolUsers
		var school School
		// Create serial mapper. It will split joined columns by logical tables.
		// In this case, joined table and destination mappings are:
		// |   users   |       school_users       |   school  |
		// + --------- + ------------------------ + --------- +
		// | id | name | id | user_id | school_id | id | name |
		// + --------- + ------------------------ + --------- +
		// |   &user   |       &schoolUser        |  &school  |
		// + --------- + ------------------------ + --------- +
		if err := serialMapper.Map(rows, &user, &schoolUser, &school); err != nil {
			log.Error(err.Error())
			return
		}
		users = append(users, &user)
	}
	// enumerate users...
}

In case of outer join
package main

import (
	"github.com/apex/log"
	"github.com/loilo-inc/exql"
)

func MapSerialOuterJoin() {
	query := `
	SELECT * FROM users
	LEFT JOIN school_users ON school_users.user_id = users.id
	LEFT JOIN schools ON schools.id = school_users.id
	WHERE users.id = ?`
	rows, err := db.DB().Query(query, 1)
	if err != nil {
		log.Errorf("err")
		return
	}
	defer rows.Close()
	serialMapper := exql.NewSerialMapper(func(i int) string {
		// Each column's separator is `id`
		return "id"
	})
	var users []*User
	var schools []*School
	for rows.Next() {
		var user User
		var schoolUser *SchoolUsers // Use *SchoolUsers/*School for outer join so that it can be nil
		var school *School          // when the values of outer joined columns are NULL.
		if err := serialMapper.Map(rows, &user, &schoolUser, &school); err != nil {
			log.Error(err.Error())
			return
		}
		users = append(users, &user)
		schools = append(schools, school) // school = nil when the user does not belong to any school.
	}
	// enumerate users and schools.
}

Transaction
package main

import (
	"context"
	"database/sql"
	"github.com/loilo-inc/exql"
	"github.com/loilo-inc/exql/model"
	"github.com/volatiletech/null"
	"time"
)

func Transaction() {
	timeout, _ := context.WithTimeout(context.Background(), 10*time.Second)
	err := db.TransactionWithContext(timeout, &sql.TxOptions{
		Isolation: sql.LevelDefault,
		ReadOnly:  false,
	}, func(tx exql.Tx) error {
		user := model.Users{
			FirstName: null.String{},
			LastName:  null.String{},
		}
		_, err := tx.Insert(&user)
		return err
	})
	if err != nil {
		// Transaction has been rolled back
	} else {
		// Transaction has been committed
	}
}

Documentation

Index

Constants

View Source
const (
	ClauseTypeWhere = "where"
)

Variables

View Source
var ErrRecordNotFound = errors.New("record not found")

Error returned when record not found

Functions

func IsSafeWhereClause

func IsSafeWhereClause(s string) bool

func NewSaver

func NewSaver(ex Executor) *saver

func ParseTags

func ParseTags(tag string) (map[string]string, error)

Types

type Clause

type Clause interface {
	Query() (string, error)
	Args() []interface{}
	Type() ClauseType
}

func Where

func Where(q string, args ...interface{}) Clause

type ClauseType

type ClauseType string

type Column

type Column struct {
	FieldName    string         `json:"field_name"`
	FieldType    string         `json:"field_type"`
	FieldIndex   int            `json:"field_index"`
	GoFieldType  string         `json:"go_field_type"`
	Nullable     bool           `json:"nullable"`
	DefaultValue sql.NullString `json:"default_value"`
	Key          sql.NullString `json:"key"`
	Extra        sql.NullString `json:"extra"`
}

func (*Column) Field

func (c *Column) Field() string

func (*Column) IsPrimary

func (c *Column) IsPrimary() bool

func (*Column) ParseExtra

func (c *Column) ParseExtra() []string

func (*Column) UpdateField added in v1.9.0

func (c *Column) UpdateField() string

type ColumnSplitter

type ColumnSplitter func(i int) string

type DB

type DB interface {
	Saver
	Mapper
	// Return *sql.DB instance
	DB() *sql.DB
	// Set db object
	SetDB(db *sql.DB)
	// Begin transaction and commit.
	// If error returned from callback, transaction is rolled back.
	// Internally call tx.BeginTx(context.Background(), nil)
	Transaction(callback func(tx Tx) error) error
	// Same as Transaction()
	// Internally call tx.BeginTx(ctx, opts)
	TransactionWithContext(ctx context.Context, opts *sql.TxOptions, callback func(tx Tx) error) error
	// Call db.Close()
	Close() error
}

func NewDB added in v1.4.0

func NewDB(d *sql.DB) DB

func Open

func Open(opts *OpenOptions) (DB, error)

type Executor added in v1.2.0

type Executor interface {
	Exec(query string, args ...interface{}) (sql.Result, error)
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
}

type GenerateOptions

type GenerateOptions struct {
	OutDir  string
	Package string
	Exclude []string
}

type Generator

type Generator interface {
	Generate(opts *GenerateOptions) error
}

func NewGenerator

func NewGenerator(db *sql.DB) Generator

type Mapper

type Mapper interface {
	// Read single row and map columns to destination.
	// pointerOfStruct MUST BE a pointer of struct.
	// It closes rows after mapping regardless error occurred.
	// example:
	// 		var user User
	// 		err := m.Map(rows, &user)
	Map(rows *sql.Rows, pointerOfStruct interface{}) error
	// Read all rows and map columns for each destination.
	// pointerOfSliceOfStruct MUST BE a pointer of slices of pointer of struct.
	// It closes rows after mapping regardless error occurred.
	// example:
	// 		var users []*Users
	// 		m.MapMany(rows, &users)
	MapMany(rows *sql.Rows, pointerOfSliceOfStruct interface{}) error
}

func NewMapper

func NewMapper() Mapper

type OpenOptions

type OpenOptions struct {
	// @default "mysql"
	DriverName string
	Url        string
	// @default 5
	MaxRetryCount int
	// @default 5s
	RetryInterval time.Duration
}

type Parser

type Parser interface {
	ParseTable(db *sql.DB, table string) (*Table, error)
}

func NewParser

func NewParser() Parser

type SET

type SET map[string]interface{}

type SaveQuery

type SaveQuery struct {
	Query              string
	Fields             []string
	Values             []interface{}
	AutoIncrementField *reflect.Value
}

type Saver

type Saver interface {
	Insert(structPtr interface{}) (sql.Result, error)
	InsertContext(ctx context.Context, structPtr interface{}) (sql.Result, error)
	QueryForInsert(structPtr interface{}) (*SaveQuery, error)
	Update(table string, set map[string]interface{}, where Clause) (sql.Result, error)
	UpdateModel(updaterStructPtr interface{}, where Clause) (sql.Result, error)
	UpdateContext(ctx context.Context, table string, set map[string]interface{}, where Clause) (sql.Result, error)
	UpdateModelContext(ctx context.Context, updaterStructPtr interface{}, where Clause) (sql.Result, error)
	QueryForUpdate(table string, set map[string]interface{}, where Clause) (*SaveQuery, error)
	QueryForUpdateModel(updateStructPtr interface{}, where Clause) (*SaveQuery, error)
}

type SerialMapper

type SerialMapper interface {
	// Read joined rows and map columns for each destination serially.
	// pointerOfStruct MUST BE a pointer of struct
	// NOTE: It WON'T close rows automatically. Close rows manually.
	// example:
	// 		var user User
	// 		var favorite UserFavorite
	// 		err := m.Map(rows, &user, &favorite)
	Map(rows *sql.Rows, pointersOfStruct ...interface{}) error
}

func NewSerialMapper

func NewSerialMapper(s ColumnSplitter) SerialMapper

type Table

type Table struct {
	TableName string    `json:"table_name"`
	Columns   []*Column `json:"columns"`
}

func (*Table) Fields

func (t *Table) Fields() []string

func (*Table) HasJsonField added in v1.9.0

func (t *Table) HasJsonField() bool

func (*Table) HasNullField

func (t *Table) HasNullField() bool

func (*Table) HasTimeField

func (t *Table) HasTimeField() bool

type Tx added in v1.2.0

type Tx interface {
	Saver
	Mapper
	Tx() *sql.Tx
}

Directories

Path Synopsis
This file is generated by exql.
This file is generated by exql.
tool

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL