db

package module
v1.5.0 Latest Latest
Warning

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

 Go to latest Published: Apr 9, 2025 License: MIT Imports: 19 Imported by: 10

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@latest

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

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

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)
    })
}
Generic DAO

Basic methods to create CRUD actions

import (
    mb "github.com/gflydev/db" // Model builder
    qb "github.com/jiveio/fluentsql" // Query builder
)
...
// ----- GetModelByID -----
user, err := mb.GetModelByID[models.User](1)
if err != nil {
    log.Fatal(err)
}
log.Info("Get \n", user1.Email)

// ----- CreateModel -----
err = mb.CreateModel(&models.User{
    Email:        "john@gmail.com",
    Password:     "02j33ih32i3",
    Fullname:     "John Man",
    Phone:        "0989712353",
    Token:        sql.NullString{},
    Status:       "active",
    CreatedAt:    time.Time{},
    Avatar:       sql.NullString{},
    UpdatedAt:    time.Time{},
    VerifiedAt:   sql.NullTime{},
    BlockedAt:    sql.NullTime{},
    DeletedAt:    sql.NullTime{},
    LastAccessAt: sql.NullTime{},
})
if err != nil {
    log.Fatal(err)
}

// ----- FindModels -----
users, total, err := mb.FindModels[models.User](1, 100, "id", qb.Desc, qb.Condition{
        Field: "id",
        Opt:   qb.NotEq,
        Value: 0,
    })
if err != nil {
    log.Fatal(err)
}
log.Info("Find \n", total)
for _, user := range users {
    log.Info("User\n", user.Email)
}

// ----- UpdateModel -----
user1.Fullname = "Admin"
if err := mb.UpdateModel(user1); err != nil {
    log.Fatal(err)
}
log.Info("Update \n", user1.Fullname)
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 creates a new record of type T in the database. It begins a transaction, attempts to create the record, and commits the transaction. If an error occurs, the transaction is rolled back and the error is returned.

Parameters:

  • m: A pointer to the model to be created.

Returns:

  • error: An error object if an error occurs during the creation process.

func DeleteModel 

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

DeleteModel deletes a record of type T from the database. It begins a transaction, deletes the record, and commits the transaction. If an error occurs, the transaction is rolled back and the error is returned.

Parameters:

  • m: A pointer to the model to be deleted.

Returns:

  • error: An error object if an error occurs during the deletion process.

func FindModels added in v1.5.0 

func FindModels[T any](page, limit int, sortField string, sortDir qb.OrderByDir, conditions ...qb.Condition) ([]T, int, error)

FindModels retrieves a paginated list of records of type T from the database that match the provided conditions.

Parameters:

  • page (int): The current page number (1-based). Defaults to 0 if not provided.
  • limit (int): The number of records to retrieve per page.
  • conditions (...qb.Condition): Variadic list of qb.Condition specifying the field, operator, and value to filter the query.

Returns:

  • ([]T): A slice of records of type T.
  • (int): The total number of records that match the conditions.
  • (error): An error object if an error occurs during the retrieval process.

func GetModel 

func GetModel[T any](conditions ...qb.Condition) (*T, error)

GetModel retrieves the first record of type T from the database that matches the provided conditions.

Parameters:

  • m: A pointer to the model where the result will be stored.
  • conditions: Variadic list of qb.Condition specifying the field, operator, and value to filter the query.

Returns:

  • error: An error object if an error occurs during the retrieval process. Returns nil if the query succeeds. Logs unexpected errors.

func GetModelBy added in v1.5.0 

func GetModelBy[T any](field string, value any) (*T, error)

GetModelBy allows filtering records of type T from the database by specifying a field and its required value.

This is a helper function that makes use of the GetModelWhereEq function to apply an equality condition.

Generic Type:

  • T: The type of the model.

Parameters:

  • field (string): The name of the database field to filter on.
  • value (any): The value the specified field is required to equal.

Returns:

  • *T: A pointer to the first matching record of type T retrieved from the database, or nil if no record is found.
  • error: An error object if an error occurs during the retrieval process.

func GetModelByID added in v1.5.0 

func GetModelByID[T any](value any) (*T, error)

GetModelByID retrieves the first record of type T from the database where the "id" field matches the specified value.

This is a convenience wrapper around GetModelBy for querying records by their unique identifier.

Generic Type:

  • T: The type of the model.

Parameters:

  • value (any): The value of the "id" field to match against.

Returns:

  • *T: A pointer to the retrieved model of type T, or nil if no matching record is found.
  • error: An error object if an error occurs during the retrieval process.

func GetModelWhereEq added in v1.5.0 

func GetModelWhereEq[T any](field string, value any) (*T, error)

GetModelWhereEq retrieves the first record of type T from the database where the specified field equals the given value.

Parameters:

  • m: A pointer to the model where the result will be stored.
  • field: The name of the database field to filter by.
  • value: The value to match the field against.

Returns:

  • error: An error object if an error occurs during the retrieval process. Returns nil if the query succeeds.

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 updates a record of type T in the database. It begins a transaction, updates the record, and commits the transaction. If an error occurs, the transaction is rolled back and the error is returned.

Parameters:

  • m: A pointer to the model to be updated.

Returns:

  • error: An error object if an error occurs during the update process.

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) 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) (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 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) 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 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.