db

package module
v1.1.0 Latest Latest
Warning

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

 Go to latest Published: Aug 24, 2024 License: MIT Imports: 19 Imported by: 5

README

gFly Database

Copyright © 2023, gFly
https://www.gfly.dev
All rights reserved.

Fluent Model - flexible and powerful Data-Access Layer. Build on top of Fluent SQL

Usage

Install

go get -u github.com/gflydev/db@v1.0.0

# PostgreSQL
go get -u github.com/gflydev/db/psql@v1.0.0

# MySQL
go get -u github.com/gflydev/db/mysql@v1.0.0

Quick usage main.go

import (
    mb "github.com/gflydev/db" 
    // PostgreSQL
    _ "github.com/gflydev/db/psql"
    // MySQL
    //_ "github.com/gflydev/db/mysql"
)

func main() {
    // Load Model builder
    mb.Load()
}

func queryUser() {
    try.Perform(func() {
        dbInstance := mb.Instance()
        if dbInstance == nil {
            panic("Database Model is NULL")
        }

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

        var user models.User
        err := dbInstance.First(&user)
        if err != nil {
            log.Fatal(err)
        }
        log.Printf("User %v\n", user)
    }).Catch(func(e try.E) {
        log.Printf("Error %v\n", e)
    })
}
More using FluentSQL and FluentModel
import (
    mb "github.com/gflydev/db" // 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 *mb.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", qb.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", qb.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(qb.InnerJoin, "user_details", qb.Condition{
        Field: "users.id",
        Opt:   qb.Eq,
        Value: qb.ValueField("user_details.user_id"),
    }).
    Where("users.name", qb.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", qb.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", qb.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 Connect 

func Connect(connURL, driver string) (*sqlx.DB, error)

Connect create a connection to Database.

func CreateModel 

func CreateModel[T any](m *T) error

CreateModel a query that creating a model by given model's data

func DeleteModel 

func DeleteModel[T any](m *T) error

DeleteModel a query that deleting a model by given model data.

func GetModel 

func GetModel[T any](m *T, field string, value any, filters ...Filter) error

GetModel takes a model, query field, and condition value. query for getting one model by a specific field condition

func ListModels 

func ListModels[T any](page, limit int, field string, value any, filters ...Filter) ([]*T, int, error)

ListModels is a query that returns slice of models, paginate by limit and offset

func Load 

func Load()

Load func for opening database connection.

func Register 

func Register(driver IDatabase)

Register assign DB provider type fluentsql.PostgreSQL, fluentsql.MySQL,...

func UpdateModel 

func UpdateModel[T any](m *T) error

UpdateModel a query that updating a model by given model's data

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

func (*DBModel) Delete 

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

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

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

func (*DBModel) Get 

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

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

func (*DBModel) Update 

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

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

func (*DBModel) When 

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 Filter 

type Filter core.Data

type GetOne 

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

type IDatabase 

type IDatabase interface {
	Load() (*sqlx.DB, error)
}

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

Directories

Path Synopsis
mysql module
psql module

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.