fluentmodel

package module
v1.3.4 Latest Latest
Warning

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

 Go to latest Published: Jun 24, 2024 License: MIT Imports: 20 Imported by: 0

README

Fluent Model

Fluent Model - flexible and powerful Data-Access Layer

Build on top of Fluent SQL

Use FluentSQL and FluentModel
import (
    mb "github.com/jiveio/fluentmodel" // Model builder
    qb "github.com/jiveio/fluentsql" // Query builder
)
User model

We have model User is struct type. Every fluent model must have MetaData field to specify some table metadata to link from model to table.

// User model
type User struct {
    // Table meta data
    MetaData fm.MetaData `db:"-" model:"table:users"`

    // Table fields
    Id   int            `db:"id" model:"type:serial,primary"`
    Name sql.NullString `db:"name" model:"type:varchar(255)"`
    Age  uint8          `db:"age" model:"type:numeric"`

	// Extra fields
    TotalAge int `db:"total_age" model:"type:numeric"`
}
Fluent model object

A new ModelBuilder instance to help you perform query, create, update or delete data.

db := mb.Instance()
if db == nil {
    panic("Database Model is NULL")
}

// Defer a rollback in case anything fails.
defer func(db *fluentmodel.DBModel) {
    _ = db.Rollback()
}(db)

var err error

Query data

Get the first ORDER BY id ASC

var user User
err = db.First(&user)
if err != nil {
    log.Fatal(err)
}
log.Printf("User %v\n", user)

Get the last ORDER BY id DESC

var user1 User
err = db.Last(&user1)
if err != nil {
    log.Fatal(err)
}
log.Printf("User %v\n", user1)

Get a random item

// Get a random item
var user2 User
err = db.Take(&user2)
if err != nil {
    log.Fatal(err)
}
log.Printf("User %v\n", user2)

Get first by ID

var user3 User
err = db.First(&user3, 103)
if err != nil {
    log.Fatal(err)
}
log.Printf("User %v\n", user3)

Get first by model ID

var user4 User
user4 = User{Id: 103}
err = db.First(&user4)
if err != nil {
    log.Fatal(err)
}
log.Printf("User %v\n", user4)

Get first by model

var user5 User
err = db.Model(User{Id: 102}).
	First(&user5)
if err != nil {
    log.Fatal(err)
}
log.Printf("User %v\n", user5)

Get first by Where condition

var user6 User
err = db.Where("Id", qb.Eq, 100).
	First(&user6)
if err != nil {
    log.Fatal(err)
}
log.Printf("User %v\n", user6)

Get first by WhereGroup

var user7 User
err = db.Where("Id", fluentsql.Eq, 100).
    WhereGroup(func(query qb.WhereBuilder) *qb.WhereBuilder {
        query.Where("age", qb.Eq, 42).
            WhereOr("age", qb.Eq, 39)

        return &query
    }).First(&user7)
if err != nil {
    log.Fatal(err)
}
log.Printf("User %v\n", user7)

Query all

var users []User
_, err = db.Find(&users)
if err != nil {
    log.Fatal(err)
}
for _, user := range users {
    log.Printf("User %v\n", user)
}

Query by model condition

var users1 []User
_, err = db.Model(&User{Age: 20}).Find(&users1)
if err != nil {
    log.Fatal(err)
}
for _, user := range users1 {
    log.Printf("User %v\n", user)
}

Query by Slice (list of ID)

var users2 []User
_, err = db.Find(&users2, []int{144, 145, 146})
if err != nil {
    log.Fatal(err)
}
for _, user := range users2 {
    log.Printf("User %v\n", user)
}

Query with SELECT|GROUP BY|HAVING

var users3 []UserTotal
_, err = db.Model(&UserTotal{}).
    Select("name, sum(age) as total_age").
    GroupBy("name").
    Having("name", fluentsql.Eq, "vinh").
    Find(&users3)
if err != nil {
    log.Fatal(err)
}
for _, user := range users3 {
    log.Printf("User %v\n", user)
}

Query with JOIN

var users4 []UserJoin
_, err = db.Model(&UserJoin{}).
    Select("name, age, email, phone").
    Join(fluentsql.InnerJoin, "user_details", fluentsql.Condition{
        Field: "users.id",
        Opt:   fluentsql.Eq,
        Value: fluentsql.ValueField("user_details.user_id"),
    }).
    Where("users.name", fluentsql.Eq, "Kite").
    Find(&users4)
if err != nil {
    log.Fatal(err)
}
for _, user := range users4 {
    log.Printf("User %v\n", user)
}

Query with raw SQL

var users5 []User
_, err = db.Raw("SELECT * FROM users WHERE name = ?", "Kite").
    Find(&users5)
if err != nil {
    log.Fatal(err)
}
for _, user := range users5 {
    log.Printf("User %v\n", user)
}

Query with paging info

var (
    users6 []User
    total  int
)
total, err = db.Limit(10, 0).Find(&users6)

if err != nil {
    log.Fatal(err)
}
log.Printf("Total %d\n", total)
for _, user := range users6 {
    log.Printf("User %v\n", user)
}

Create data

Create from a model

user := User{
    Name: sql.NullString{String: "Vinh", Valid: true},
    Age:  42,
}
// Create new row into table `users`
err = db.Create(&user) 
if err != nil {
    log.Fatal(err)
}
log.Printf("User ID: %d", user.Id)

Create from model - Omit a column

userDetail := UserDetail{
    UserId: 1,
    Email:  "vinh@mail.com",
    Phone:  1989831911,
}

// Create new row but skips data of column `phone`
err = db.Omit("phone").
	Create(&userDetail)
if err != nil {
    log.Fatal(err)
}
log.Printf("User detail ID: %d", userDetail.Id)

Create from model - For some specific columns

userDetail = UserDetail{
    UserId: 1,
    Email:  "vinh.vo@gmail.com",
    Phone:  975821086,
}

// Create new row but only data for column `user_id` and `email`
err = db.Select("user_id", "email").
	Create(&userDetail)
if err != nil {
    log.Fatal(err)
}
log.Printf("User detail ID: %d", userDetail.Id)

Create from Slice models

var users []*User
users = append(users, &User{
    Name: sql.NullString{String: "John", Valid: true},
    Age:  39,
})
users = append(users, &User{
    Name: sql.NullString{String: "Kite", Valid: true},
    Age:  42,
})
err = db.Create(users)
if err != nil {
    log.Fatal(err)
}

for _, user := range users {
    log.Printf("User ID: %d", user.Id)
}

Create from Map column keys

user = User{}
err = db.Model(&user).
	Create(map[string]interface{}{
        "Name": "John Lyy",
        "Age":  39,
    })
if err != nil {
    log.Fatal(err)
}
log.Printf("User ID: %d", user.Id)

Update data

Update by model

var user User
err = db.First(&user)
user.Name = sql.NullString{
    String: "Cat John",
    Valid:  true,
}

err = db.Update(user)
if err != nil {
    log.Fatal(err)
}
log.Printf("User %v\n", user)

Update by model and condition

var user1 User
err = db.First(&user1)
user1.Name = sql.NullString{
    String: "Cat John",
    Valid:  true,
}
user1.Age = 100

err = db.
    Where("id", fluentsql.Eq, 1).
    Update(user1)

if err != nil {
    log.Fatal(err)
}
log.Printf("User %v\n", user1)

Update by Map

var user2 User
err = db.First(&user2)
err = db.Model(&user2).
    Omit("Name").
    Update(map[string]interface{}{"Name": "Tah Go Tab x3", "Age": 88})

if err != nil {
    log.Fatal(err)
}
log.Printf("User %v\n", user2)

Delete data

Delete by Model

var user User
err = db.First(&user)
err = db.Delete(user)
if err != nil {
    log.Fatal(err)
}

Delete by ID

err = db.Delete(User{}, 157)
if err != nil {
    log.Fatal(err)
}

Delete by List ID

err = db.Delete(User{}, []int{154, 155, 156})
if err != nil {
    log.Fatal(err)
}

Delete by Where condition

err = db.Where("Id", fluentsql.Eq, 153).
	Delete(&User{})
if err != nil {
    log.Fatal(err)
}

RAW SQLs

// -------- Insert --------
var user User
err = db.Raw("INSERT INTO users(name, age) VALUES($1, $2)", "Kite", 43).
    Create(&user)
if err != nil {
    log.Fatal(err)
}
log.Printf("User %v\n", user)

// -------- Update --------
err = db.Raw("UPDATE users SET name = $1, age = $2 WHERE id= $3", "Kite - Tola", 34, 1).
    Update(&User{})
if err != nil {
    log.Fatal(err)
}

// -------- Get One --------
var user2 User
err = db.Raw("SELECT * FROM users WHERE id=$1", 1).
    First(&user2)
log.Printf("User %v\n", user2)

// -------- Select --------
var userList []User
var total int
total, err = db.Raw("SELECT * FROM users").
    Find(&userList)
log.Printf("Total %v\n", total)

for _, _user := range userList {
    log.Printf("User %v\n", _user)
}

// -------- Delete --------
err = db.Raw("DELETE FROM users WHERE id > $1", 1).
    Delete(&User{})
if err != nil {
    log.Fatal(err)
}

Documentation

Index

Constants

View Source 
const (
	MODEL     = "model"   // Tag `model`
	TABLE     = "table"   // Table name
	TYPE      = "type"    // Column types
	REFERENCE = "ref"     // Column reference
	CASCADE   = "cascade" // Column cascade DELETE, UPDATE
	RELATION  = "rel"     // Column relationship
	NAME      = "name"    // Column name
)

Variables

This section is empty.

Functions

func GetEnv 

func GetEnv[V any](key string, init V) V

GetEnv Get environment parameter (From .env file).

func SetValue 

func SetValue(model any, key string, data any) (err error)

SetValue reflection set value for a key in struct. Support types bool, string, int (Int, Int8, Int16, Int32, Int64), uint (UInt, UInt8, UInt16, UInt32, UInt64), float (float32, float64)

func ToString 

func ToString(data interface{}) (res string)

ToString convert interface{} type to string. Support types bool, string, int (Int, Int8, Int16, Int32, Int64), uint (UInt, UInt8, UInt16, UInt32, UInt64), float (float32, float64)

Types

type Column 

type Column struct {
	Key      string
	Name     string
	Primary  bool
	Types    string
	Ref      string // Reference id to table
	Relation string // Relation to table
	IsZero   bool   // Keep Zero value of type
	HasValue bool
}

Column structure

type DB 

type DB struct {
	*sqlx.DB // Embed sqlx DB.
}

DB the database

type DBModel 

type DBModel struct {
	// contains filtered or unexported fields
}

func Instance 

func Instance() *DBModel

func (*DBModel) Begin 

func (db *DBModel) Begin() *DBModel

Begin new transaction

func (*DBModel) Commit 

func (db *DBModel) Commit() error

Commit transaction

func (*DBModel) Create 

func (db *DBModel) Create(model any) (err error)

Create add new data for table via model type Slice, Struct, *Struct 

------ Insert a user ------

user := &User{
	Name: "Vinh",
	Age:  42,
}
err = db.Create(user)
if err != nil {
	log.Fatal(err)
}
log.Printf("User ID: %d", user.Id)

------ Insert slice (multi) users ------

var users []*User

users = append(users, &User{
	Name: "John",
	Age:  39,
})

users = append(users, &User{
	Name: "Kite",
	Age:  42,
})

err = db.Create(users)

if err != nil {
	log.Fatal(err)
}

for _, user := range users {
	log.Printf("User ID: %d", user.Id)
}

------ Insert Map ------

user = &User{}
err = db.Model(user).Create(map[string]interface{}{
	"Name": "Toi Lest",
	"Age":  39,
})
if err != nil {
	log.Fatal(err)
}
log.Printf("User ID: %d", user.Id)

func (*DBModel) Delete 

func (db *DBModel) Delete(model any, args ...any) (err error)

Delete perform delete data for table via model type Struct, *Struct

-------- Delete via Model -------- 

var user User
err = db.First(&user)
err = db.Delete(user)
if err != nil {
	log.Fatal(err)
}

-------- Delete via ID -------- 

err = db.Delete(User{}, 157)
if err != nil {
	log.Fatal(err)
}

-------- Delete via List ID --------

err = db.Delete(User{}, []int{154, 155, 156})
if err != nil {
	log.Fatal(err)
}

-------- Delete via Where condition -------- 

err = db.Where("Id", fluentsql.Eq, 153).Delete(&User{})
if err != nil {
	log.Fatal(err)
}

func (*DBModel) Fetch 

func (db *DBModel) Fetch(offset, fetch int) *DBModel

Fetch builder

func (*DBModel) Find 

func (db *DBModel) Find(model any, params ...any) (total int, err error)

Find search rows

func (*DBModel) First 

func (db *DBModel) First(model any) (err error)

First get the first record ordered by primary key 

Example

-------- Query a First --------

var user User err = db.First(&user) 

if err != nil {
	log.Fatal(err)
}

log.Printf("User %v\n", user)

-------- Query a First by ID --------

var user3 User err = db.First(&user3, 103) 

if err != nil {
	log.Fatal(err)
}

log.Printf("User %v\n", user3) var user4 User user4 = User{Id: 103} err = db.First(&user4) 

if err != nil {
	log.Fatal(err)
}

log.Printf("User %v\n", user4)

-------- Query a First by Model --------

var user5 User err = db.Model(User{Id: 102}).First(&user5) 

if err != nil {
	log.Fatal(err)
}

log.Printf("User %v\n", user5)

-------- Query a First by Where --------

var user6 User err = db.Where("Id", fluentsql.Eq, 100).First(&user6) 

if err != nil {
	log.Fatal(err)
}

log.Printf("User %v\n", user6)

-------- Query a First by WhereGroup --------

var user7 User err = db.Where("Id", fluentsql.Eq, 100). 

WhereGroup(func(query fluentsql.WhereBuilder) *fluentsql.WhereBuilder {
	query.Where("age", fluentsql.Eq, 42).
		WhereOr("age", fluentsql.Eq, 39)

	return &query
}).First(&user7)

if err != nil {
	log.Fatal(err)
}

log.Printf("User %v\n", user7)

func (*DBModel) Get added in v1.3.1 

func (db *DBModel) Get(model any, getType GetOne) (err error)

Get with specific strategy GetLast | GetFirst | TakeOne

func (*DBModel) GroupBy 

func (db *DBModel) GroupBy(fields ...string) *DBModel

GroupBy fields in a query

func (*DBModel) Having 

func (db *DBModel) Having(field any, opt fluentsql.WhereOpt, value any) *DBModel

Having builder

func (*DBModel) Join 

func (db *DBModel) Join(join fluentsql.JoinType, table string, condition fluentsql.Condition) *DBModel

Join builder

func (*DBModel) Last 

func (db *DBModel) Last(model any) (err error)

Last last record, ordered by primary key desc 

Example

-------- Query a Last --------

var user1 User err = db.Select("name").Last(&user1) 

if err != nil {
	log.Fatal(err)
}

log.Printf("User %v\n", user1)

func (*DBModel) Limit 

func (db *DBModel) Limit(limit, offset int) *DBModel

Limit builder

func (*DBModel) Model 

func (db *DBModel) Model(model any) *DBModel

Model set specific model for builder

func (*DBModel) Omit 

func (db *DBModel) Omit(columns ...any) *DBModel

Omit exclude some columns

func (*DBModel) OrderBy 

func (db *DBModel) OrderBy(field string, dir fluentsql.OrderByDir) *DBModel

OrderBy builder

func (*DBModel) Raw 

func (db *DBModel) Raw(sqlStr string, args ...any) *DBModel

Raw build query from raw SQL

func (*DBModel) RemoveFetch 

func (db *DBModel) RemoveFetch() fluentsql.Fetch

RemoveFetch builder

func (*DBModel) RemoveLimit 

func (db *DBModel) RemoveLimit() fluentsql.Limit

RemoveLimit builder

func (*DBModel) Rollback 

func (db *DBModel) Rollback() error

Rollback transaction

func (*DBModel) Select 

func (db *DBModel) Select(columns ...any) *DBModel

Select List of columns

func (*DBModel) Take 

func (db *DBModel) Take(model any, args ...any) (err error)

Take get one record, no specified order 

Example

-------- Query a Take --------

var user2 User err = db.Take(&user2) 

if err != nil {
	log.Fatal(err)
}

log.Printf("User %v\n", user2)

func (*DBModel) Update 

func (db *DBModel) Update(model any) (err error)

Update modify data for table via model type Struct, *Struct 

// -------- Update from Struct --------

var user User
err = db.First(&user)
user.Name = sql.NullString{
	String: "Cat John",
	Valid:  true,
}

err = db.Update(user)
if err != nil {
	log.Fatal(err)
}
log.Printf("User %v\n", user)

var user1 User
err = db.First(&user1)
user1.Name = sql.NullString{
	String: "Cat John",
	Valid:  true,
}
user1.Age = 100

err = db.
	Where("id", fluentsql.Eq, 1).
	Update(user1)

if err != nil {
	log.Fatal(err)
}
log.Printf("User %v\n", user1)

// -------- Update from Map --------

var user2 User
err = db.First(&user2)
err = db.Model(&user2).
	Omit("Name").
	Update(map[string]interface{}{"Name": "Tah Go Tab x3", "Age": 88})

if err != nil {
	log.Fatal(err)
}
log.Printf("User %v\n", user2)

func (*DBModel) When added in v1.2.1 

func (db *DBModel) When(condition bool, groupCondition fluentsql.FnWhereBuilder) *DBModel

When checking TRUE to build Where condition.

func (*DBModel) Where 

func (db *DBModel) Where(field any, opt fluentsql.WhereOpt, value any) *DBModel

Where add where condition

func (*DBModel) WhereGroup 

func (db *DBModel) WhereGroup(groupCondition fluentsql.FnWhereBuilder) *DBModel

WhereGroup combine multi where conditions into a group.

func (*DBModel) WhereOr 

func (db *DBModel) WhereOr(field any, opt fluentsql.WhereOpt, value any) *DBModel

WhereOr add where condition

type GetOne 

type GetOne int
const (
	GetFirst GetOne = iota
	GetLast
	TakeOne
)

type MetaData 

type MetaData string

MetaData name

type Raw 

type Raw struct {
	// contains filtered or unexported fields
}

Raw struct

type Table 

type Table struct {
	Name      string
	Columns   []Column
	Primaries []Column
	Values    map[string]any
	Relation  []*Table
	HasData   bool
}

Table structure

func ModelData 

func ModelData(model any) (*Table, error)

func NewTable 

func NewTable() *Table

Source Files

View all Source files

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.