Database

Getting Started

gFly encourages the use of both Service for processing logic for a certain function and Repository to operate on each corresponding Model.

Model

Note

IMPORTANT!
The Model file should be placed in directory app/domain/models

// ================================================================================
//                                     Model User
// ================================================================================
// TableUser Table name
const TableUser = "users"

// User struct to describe a user object.
type User struct {
    ID           uuid.UUID    `db:"id" json:"id" validate:"required,uuid"`
    Email        string       `db:"email" json:"email" validate:"required,email,lte=255"`
    PasswordHash string       `db:"password_hash" json:"-" validate:"required,gte=6"`
    Fullname     string       `db:"fullname" json:"fullname" validate:"lte=255"`
    Phone        string       `db:"phone" json:"phone" validate:"lte=20"`
    Token        string       `db:"token" json:"-" validate:"lte=100"`
    UserStatus   int          `db:"user_status" json:"user_status" validate:"required,len=1"`
    CreatedAt    time.Time    `db:"created_at" json:"created_at" validate:"required"`
    UpdatedAt    time.Time    `db:"updated_at" json:"updated_at" validate:"required"`
    VerifiedAt   sql.NullTime `db:"verified_at" json:"verified_at"`
    BlockedAt    sql.NullTime `db:"blocked_at" json:"blocked_at"`
    DeletedAt    sql.NullTime `db:"deleted_at" json:"deleted_at"`
    LastAccessAt sql.NullTime `db:"last_access_at" json:"last_access_at"`
}

Repository

Note

IMPORTANT!
The Repository file should be placed in directory app/domain/repository. We don’t necessarily have to create a corresponding repository for each model if it’s not really necessary. Because you can also do basic CRUD operations by using the methods directly.

Create a simple repository user_repository.go for model User which some functions

package repository

import (
    "gfly/app/domain/models"
    mb "github.com/gflydev/db" // Model builder
)

// ====================================================================
// ======================= Repository Interface =======================
// ====================================================================

// IUserRepository represents the interface for managing user-related data in the repository.
// It provides methods for CRUD operations and additional utility functions.
//
// Methods:
//   - GetUserByEmail(email string) *models.User: Retrieves a user by their email address.
//   - GetUserByToken(token string) *models.User: Retrieves a user by their authentication token.
//   - SelectUser(page, limit int) ([]*models.User, int, error): Retrieves a paginated list of users with the total count.
type IUserRepository interface {
    // GetUserByEmail retrieves a user by their email address.
    // Parameters:
    //   - email (string): The email address of the user to retrieve.
    //
    // Returns:
    //   - (*models.User): The user associated with the given email address, or nil if not found.
    GetUserByEmail(email string) *models.User

    // GetUserByToken retrieves a user based on their authentication token.
    // Parameters:
    //   - token (string): The authentication token of the user to retrieve.
    //
    // Returns:
    //   - (*models.User): The user associated with the given token, or nil if not found.
    GetUserByToken(token string) *models.User
}

// ====================================================================
// ====================== Repository Implement ========================
// ====================================================================

// userRepository is a repository type for accessing and managing user data.
type userRepository struct {
}

func (r *userRepository) getBy(field string, value any) *models.User {
    user, err := mb.GetModelBy[models.User](field, value)
    if err != nil {
        return nil
    }

    return user
}

// GetUserByEmail retrieves a user by their email address.
//
// Parameters:
//   - email (string): The email address of the user to retrieve.
//
// Returns:
//   - (*models.User): The user associated with the given email address, or nil if not found.
func (r *userRepository) GetUserByEmail(email string) *models.User {
    return r.getBy("email", email)
}

// GetUserByToken retrieves a user based on their authentication token.
//
// Parameters:
//   - token (string): The authentication token of the user to retrieve.
//
// Returns:
//   - (*models.User): The user associated with the given token, or nil if not found.
func (r *userRepository) GetUserByToken(token string) *models.User {
    return r.getBy("token", token)
}

package repository

// ====================================================================
// ======================== Repository factory ========================
// ====================================================================

// Repositories struct for collect all app repositories.
type Repositories struct {
    IRoleRepository
    IUserRepository
}

// Pool a repository pool to store all
var Pool = &Repositories{
    &roleRepository{},
    &userRepository{},
}

Note

IMPORTANT!
We don’t necessarily have to create a corresponding repository for each model if it’s not really necessary. Because you can also do basic CRUD operations by using Generic DAO.

Let view CRUD examples by using Model builder mb alias:

Get list entity of a specific model type mb.FindModels()
Get entity by ID mb.GetModelByID()
Create entity mb.CreateModel()
Update entity mb.UpdateModel()
Delete entity mb.DeleteModel()

import (
    mb "github.com/gflydev/db" // Model builder
    qb "github.com/jivegroup/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)

Configuration

Parameters

# NOTE: Database settings:
DB_DEBUG=false
DB_HOST="localhost"
DB_PORT=5432
DB_NAME="gfly"
DB_USERNAME="user"
DB_PASSWORD="secret"
DB_SSL_MODE="disable"
DB_MAX_CONNECTION=100
DB_MAX_IDLE_CONNECTION=10
DB_MAX_LIFETIME_CONNECTION=30
DB_MAX_IDLE_TIME_CONNECTION=3

Libraries

gFly use below libraries to handle database

Database `jmoiron/sqlx`

Library "github.com/jmoiron/sqlx"

sqlx is a library which provides a set of extensions on go’s standard database/sql library. The sqlx versions of sql.DB, sql.TX, sql.Stmt, et al. all leave the underlying interfaces untouched, so that their interfaces are a superset on the standard ones. This makes it relatively painless to integrate existing codebases using database/sql with sqlx.

MySQL Driver `go-sql-driver/mysql` (Not testing yet)

MySQL "github.com/go-sql-driver/mysql"

Go MySQL Driver is a MySQL driver for Go’s (golang) database/sql package

PostgreSQL Driver `jackc/pgx`

PostgreSQL "github.com/jackc/pgx"

pgx is a pure Go driver and toolkit for PostgreSQL.

The pgx driver is a low-level, high performance interface that exposes PostgreSQL-specific features such as LISTEN / NOTIFY and COPY. It also includes an adapter for the standard database/sql interface.

The toolkit component is a related set of packages that implement PostgreSQL functionality such as parsing the wire protocol and type mapping between PostgreSQL and Go. These underlying packages can be used to implement alternative drivers, proxies, load balancers, logical replication clients, etc.