db

func (Condition) ToQBCondition ¶

func (c Condition) ToQBCondition() qb.Condition

ToQBCondition converts a Condition to qb.Condition. This helper function recursively converts the local Condition struct to the fluentsql qb.Condition struct, including any nested Group conditions.

Returns:

• qb.Condition: The converted condition ready for use with fluentsql

Example:

condition := Condition{
	Field: "user_id",
	Opt:   Eq,
	Value: 123,
	AndOr: And,
}
qbCondition := condition.ToQBCondition()

func (*DBModel) Begin ¶

func (db *DBModel) Begin() *DBModel

Begin starts a new database transaction.

Returns:

• *DBModel: The DBModel instance with an active transaction.

func (*DBModel) Commit ¶

func (db *DBModel) Commit() error

Commit commits the current database transaction.

Returns:

• error: An error, if any, that occurred during the commit process.

func (*DBModel) Create ¶

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

Create inserts new data into a database table using various model types. This method provides a flexible interface for inserting data by automatically detecting the input type and routing to the appropriate insertion strategy. It supports raw SQL queries, map-based insertion, batch insertion via slices, and single record insertion via structs.

Parameters:

• model (any): The data to be inserted. Supported types:
• Raw SQL: When db.raw.sqlStr is set, uses raw SQL insertion
• map[string]any: Creates a record using key-value pairs from the map
• []Struct or []*Struct: Batch insertion of multiple records
• Struct or *Struct: Single record insertion using struct fields

Returns:

• error: Returns an error if the insertion fails, model type is unsupported, or if there are database connectivity issues. Returns nil on success.

Examples:

// Single struct insertion
user := User{Name: "John", Email: "john@example.com"}
err := db.Model(&User{}).Create(user)

// Pointer to struct insertion
user := &User{Name: "Jane", Email: "jane@example.com"}
err := db.Model(&User{}).Create(user)

// Batch insertion with slice
users := []User{
    {Name: "Alice", Email: "alice@example.com"},
    {Name: "Bob", Email: "bob@example.com"},
}
err := db.Model(&User{}).Create(users)

// Map-based insertion
userData := map[string]any{
    "name": "Charlie",
    "email": "charlie@example.com",
    "age": 30,
}
err := db.Model(&User{}).Create(userData)

// Raw SQL insertion
err := db.Model(&User{}).Raw("INSERT INTO users (name, email) VALUES (?, ?)", "David", "david@example.com").Create(nil)

Note:

• Primary key fields are automatically handled and populated after insertion
• The method respects Select() and Omit() clauses for column filtering
• For batch operations, if one record fails, the entire operation may fail
• The model is reset after the operation completes

func (*DBModel) Delete ¶

func (db *DBModel) Delete(model any) error

Delete removes records from the database table based on the provided model and conditions. This method provides a flexible approach to data deletion by supporting both primary key-based deletion and conditional deletion using WHERE clauses. It automatically constructs DELETE SQL statements based on the model's primary keys and any additional conditions specified through the fluent interface. The method ensures safe deletion by requiring at least one WHERE condition to prevent accidental deletion of all table data.

Parameters:

• model (any): The model defining the target table and deletion criteria. Supported types:
• Struct: Direct struct value representing the table model (e.g., User{ID: 1})
• *Struct: Pointer to struct representing the table model (e.g., &User{ID: 1})
• The struct should have appropriate field tags for database mapping
• Primary key fields in the model are used to build WHERE conditions automatically
• Non-nil primary key values are included in the deletion criteria

Returns:

• error: Returns an error if:
• The model cannot be processed or reflected (invalid struct type)
• Table metadata extraction fails
• No WHERE conditions are present (safety requirement)
• Raw SQL execution fails (when using Raw() method)
• DELETE statement construction fails
• Database execution fails
• Database connectivity issues occur Returns nil on successful deletion.

Examples:

// Delete by primary key
user := User{ID: 123}
err := db.Model(&User{}).Delete(user)
// Executes: DELETE FROM users WHERE id = 123

// Delete with pointer to struct
user := &User{ID: 456}
err := db.Model(&User{}).Delete(user)
// Executes: DELETE FROM users WHERE id = 456

// Delete with additional WHERE conditions
user := User{}
err := db.Model(&User{}).Where("status", "inactive").Delete(user)
// Executes: DELETE FROM users WHERE status = 'inactive'

// Delete with multiple conditions
user := User{ID: 789}
err := db.Model(&User{}).Where("created_at < ?", time.Now().AddDate(-1, 0, 0)).Delete(user)
// Executes: DELETE FROM users WHERE id = 789 AND created_at < '2023-01-01'

// Delete with OR conditions
user := User{}
err := db.Model(&User{}).Where("status", "inactive").Or("last_login < ?", oldDate).Delete(user)
// Executes: DELETE FROM users WHERE status = 'inactive' OR last_login < '2023-01-01'

// Delete with grouped conditions
user := User{}
err := db.Model(&User{}).Where("status", "inactive").
    Where(func(db *DBModel) *DBModel {
        return db.Where("role", "guest").Or("role", "temp")
    }).Delete(user)
// Executes: DELETE FROM users WHERE status = 'inactive' AND (role = 'guest' OR role = 'temp')

// Raw SQL deletion
user := User{}
err := db.Model(&User{}).Raw("DELETE FROM users WHERE created_at < NOW() - INTERVAL 1 YEAR").Delete(user)
// Executes the raw SQL directly

// Composite primary key deletion
orderItem := OrderItem{OrderID: 100, ProductID: 200}
err := db.Model(&OrderItem{}).Delete(orderItem)
// Executes: DELETE FROM order_items WHERE order_id = 100 AND product_id = 200

Safety Features:

• Requires at least one WHERE condition to prevent accidental mass deletion
• Automatically includes non-nil primary key values as WHERE conditions
• Validates model structure before executing deletion
• Supports transaction rollback through proper error handling

Note:

• Primary key fields with nil/zero values are ignored in WHERE conditions
• The method respects all WHERE conditions set via the fluent interface
• Raw SQL takes precedence over model-based deletion when both are present
• The fluent model builder is automatically reset after the operation
• For mass deletion, use Raw() method with appropriate WHERE clauses
• Composite primary keys are fully supported with AND logic
• The operation is atomic and will either succeed completely or fail without changes

func (*DBModel) Fetch ¶

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

Fetch adds a FETCH clause to the query.

Parameters:

• offset (int): The offset for fetching rows.
• fetch (int): The number of rows to fetch.

Returns:

• *DBModel: A reference to the DBModel instance for chaining.

func (*DBModel) Find ¶

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

Find searches for multiple rows in the database based on query criteria.

Parameters:

• model (any): A pointer to the slice where the retrieved rows will be stored.

Returns:

• total (int): The total number of rows matching the query criteria.
• err (error): An error object if any issues occur during the retrieval process; nil otherwise.

func (*DBModel) First ¶

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

First retrieves the first record ordered by primary key.

Parameters:

• model (any): A pointer to the model where the result will be stored.

Returns:

• err (error): An error object if any issues occur during the retrieval process; nil otherwise.

func (*DBModel) Get ¶

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

Get retrieves a single record from the database based on the specified strategy.

Parameters:

• model (any): A pointer to the model where the retrieved record will be stored.
• getType (GetOne): The strategy for selecting the record. Possible values are:
• GetFirst: Retrieve the first record ordered by primary key in ascending order.
• GetLast: Retrieve the last record ordered by primary key in descending order.
• TakeOne: Retrieve a random record.

Returns:

• err (error): An error object if any issues occur during the retrieval process; nil otherwise.

func (*DBModel) GroupBy ¶

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

GroupBy adds GROUP BY fields to the query.

Parameters:

• fields (...string): The fields to group by.

Returns:

• *DBModel: A reference to the DBModel instance for chaining.

func (*DBModel) Having ¶

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

Having adds a HAVING condition to the query.

Parameters:

• field (any): The field or column to filter.
• opt (qb.WhereOpt): The operator to use.
• value (any): The value to compare against.

Returns:

• *DBModel: A reference to the DBModel instance for chaining.

func (*DBModel) Join ¶

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

Join adds a JOIN clause to the query.

Parameters:

• join (qb.JoinType): The type of join (e.g., INNER, LEFT).
• table (string): The name of the table to join.
• condition (qb.Condition): The condition for the join.

Returns:

• *DBModel: A reference to the DBModel instance for chaining.

func (*DBModel) Last ¶

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

Last retrieves the last record ordered by primary key in descending order.

Parameters:

• model (any): A pointer to the model where the result will be stored.

Returns:

• err (error): An error object if any issues occur during the retrieval process; nil otherwise.

func (*DBModel) Limit ¶

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

Limit adds a LIMIT clause to the query.

Parameters:

• limit (int): The maximum number of rows to return.
• offset (int): The number of rows to skip.

Returns:

• *DBModel: A reference to the DBModel instance for chaining.

func (*DBModel) Model ¶

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

Model associates a struct model with the DBModel instance for ORM operations. This method is fundamental to the ORM functionality as it defines the target table structure, column mappings, and data types for database operations. The model struct should have appropriate database tags to define table and column mappings. This method must be called before most ORM operations to establish the context.

Parameters:

• model (any): The model struct that defines the database table structure. Supported types:
• Struct: Direct struct value (e.g., User{})
• *Struct: Pointer to struct (e.g., &User{}) - recommended for most operations
• The struct should have database tags for proper column mapping:
• `db:"column_name"` for column name mapping
• `db:"column_name,primary"` for primary key designation
• `db:"-"` to exclude fields from database operations

Returns:

• *DBModel: The same DBModel instance for method chaining, now configured with the model.

Examples:

// Basic model association
type User struct {
    ID    int    `db:"id,primary"`
    Name  string `db:"name"`
    Email string `db:"email"`
    Age   int    `db:"age"`
}

db := Instance().Model(&User{})

// Query operations after model association
var users []User
err := db.Find(&users)

// Create operations
user := User{Name: "John", Email: "john@example.com", Age: 30}
err := db.Create(&user) // user.ID will be populated

// Update operations
user.Age = 31
err := db.Where("id", Eq, user.ID).Update(&user)

// Delete operations
err := db.Where("id", Eq, user.ID).Delete(&user)

// Complex model with relationships
type Order struct {
    ID       int       `db:"id,primary"`
    UserID   int       `db:"user_id"`
    Total    float64   `db:"total"`
    Status   string    `db:"status"`
    Created  time.Time `db:"created_at"`
    Updated  time.Time `db:"updated_at"`
    Internal string    `db:"-"` // Excluded from database operations
}

db := Instance().Model(&Order{})

// Model with custom table name (if different from struct name)
type UserProfile struct {
    ID     int    `db:"id,primary"`
    UserID int    `db:"user_id"`
    Bio    string `db:"biography"`
} // Maps to "user_profiles" table by convention

// Multiple models for different operations
userDB := Instance().Model(&User{})
orderDB := Instance().Model(&Order{})
// Each instance maintains separate model context

// Model reuse with different instances
db1 := Instance().Model(&User{}).Where("status", Eq, "active")
db2 := Instance().Model(&User{}).Where("age", qb.Gt, 18)
// Independent query contexts with same model

Model Requirements:

• Struct fields should be exported (capitalized)
• Use appropriate database tags for column mapping
• Primary key fields should be tagged with "primary"
• Time fields should use time.Time type for proper handling
• Nullable fields can use sql.NullString, sql.NullInt64, etc.

Common Patterns:

• Always use pointer to struct: Model(&User{}) instead of Model(User{})
• Set model before any ORM operations
• One model per DBModel instance for clarity
• Use consistent naming conventions between struct and database

Note:

• The model defines the target table name (derived from struct name)
• Column mappings are established through struct tags
• Primary key detection is automatic based on "primary" tag
• The model persists until the DBModel instance is reset
• Required for Create, Update, Delete, Find, and other ORM operations
• Raw SQL operations can work without a model but lose ORM benefits

func (*DBModel) Omit ¶

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

Omit excludes specific columns from retrieval.

Parameters:

• columns (...any): Variadic list of columns to omit.

Returns:

• *DBModel: A reference to the DBModel instance for chaining.

func (*DBModel) OrderBy ¶

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

OrderBy adds an ORDER BY clause to the query.

Parameters:

• field (string): The field to sort by.
• dir (qb.OrderByDir): The sorting direction (e.g., ASC or DESC).

Returns:

• *DBModel: A reference to the DBModel instance for chaining.

func (*DBModel) Raw ¶

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

Raw configures the DBModel to execute a custom SQL query with parameters. This method allows bypassing the ORM's query builder to execute hand-crafted SQL for complex operations, database-specific features, or performance optimization. When Raw is used, it takes precedence over all other query building methods, and the provided SQL will be executed directly against the database.

Parameters:

• sqlStr (string): The raw SQL query string with parameter placeholders. Supports database-specific placeholder syntax:
• MySQL/SQLite: Use ? for parameters (e.g., "SELECT * FROM users WHERE id = ?")
• PostgreSQL: Use $1, $2, etc. for parameters (e.g., "SELECT * FROM users WHERE id = $1")
• args (...any): Variadic arguments that correspond to the parameter placeholders. Arguments are applied in order and should match the placeholder count and expected types. Supports all Go types that can be converted to SQL types.

Returns:

• *DBModel: The same DBModel instance for method chaining, configured for raw SQL execution.

Examples:

// Simple SELECT with parameters
var users []User
err := Instance().Raw("SELECT * FROM users WHERE age > ? AND status = ?", 18, "active").Find(&users)

// Complex query with joins and subqueries
var results []UserStats
query := `
    SELECT u.name, COUNT(o.id) as order_count, AVG(o.total) as avg_order
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.created_at > ?
    GROUP BY u.id, u.name
    HAVING COUNT(o.id) > ?
    ORDER BY avg_order DESC
`
err := Instance().Raw(query, time.Now().AddDate(-1, 0, 0), 5).Find(&results)

// Database-specific functions
var count int
err := Instance().Raw("SELECT COUNT(*) FROM users WHERE MATCH(name) AGAINST(?)", "john").Get(&count)

// INSERT with RETURNING (PostgreSQL)
var newID int
err := Instance().Raw("INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id", "John", "john@example.com").Get(&newID)

// UPDATE with complex conditions
err := Instance().Raw("UPDATE products SET price = price * 0.9 WHERE category_id IN (SELECT id FROM categories WHERE name = ?)", "electronics").Exec()

// DELETE with joins
err := Instance().Raw("DELETE u FROM users u JOIN user_sessions s ON u.id = s.user_id WHERE s.last_activity < ?", oldDate).Exec()

// Stored procedure call
err := Instance().Raw("CALL update_user_statistics(?)", userID).Exec()

// Common Table Expression (CTE)
query := `
    WITH RECURSIVE category_tree AS (
        SELECT id, name, parent_id, 0 as level FROM categories WHERE parent_id IS NULL
        UNION ALL
        SELECT c.id, c.name, c.parent_id, ct.level + 1
        FROM categories c
        JOIN category_tree ct ON c.parent_id = ct.id
    )
    SELECT * FROM category_tree WHERE level <= ?
`
var categories []Category
err := Instance().Raw(query, 3).Find(&categories)

Use Cases:

• Complex analytical queries with window functions
• Database-specific optimizations and hints
• Stored procedure and function calls
• Bulk operations with custom logic
• Migration and schema manipulation queries
• Performance-critical queries requiring manual optimization

Note:

• Raw SQL takes precedence over all query builder methods
• Parameter placeholders prevent SQL injection when used properly
• The query is executed in the current transaction context if one exists
• Database-specific syntax should match the configured database driver
• Use appropriate execution methods: Get() for single row, Find() for multiple rows, Exec() for non-query operations
• SQL debugging can be enabled via DB_DEBUG environment variable

func (*DBModel) RemoveFetch ¶

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

RemoveFetch removes the FETCH clause from the query.

Returns:

• qb.Fetch: The removed fetch settings.

func (*DBModel) RemoveLimit ¶

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

RemoveLimit removes the LIMIT clause from the query.

Returns:

• qb.Limit: The removed limit settings.

func (*DBModel) Rollback ¶

func (db *DBModel) Rollback() error

Rollback rolls back the current database transaction.

Returns:

• error: An error, if any, that occurred during the rollback process.

func (*DBModel) Select ¶

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

Select specifies the list of columns to retrieve.

Parameters:

• columns (...any): Variadic list of columns to include in the SELECT clause.

Returns:

• *DBModel: A reference to the DBModel instance for chaining.

func (*DBModel) Update ¶

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

Update modifies data for a table using a model of type Struct or *Struct.

Parameters:

• model (any): The data model used for updating the table. It can be of type Struct, *Struct, or a map.

Returns:

• error: Returns an error if the update process fails.

func (*DBModel) When ¶

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

When conditionally applies a WHERE condition if the provided condition is TRUE.

Parameters:

• condition (bool): Determines whether the condition should be applied.
• groupCondition (qb.FnWhereBuilder): The function to build the condition.

Returns:

• *DBModel: A reference to the DBModel instance for chaining.

func (*DBModel) Where ¶

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

Where adds a WHERE condition to the query.

Parameters:

• field (any): The field or column to filter.
• opt (qb.WhereOpt): The operator to use (e.g., equals, greater than).
• value (any): The value to compare against.

Returns:

• *DBModel: A reference to the DBModel instance for chaining.

func (*DBModel) WhereGroup ¶

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

WhereGroup combines multiple WHERE conditions into a group.

Parameters:

• groupCondition (qb.FnWhereBuilder): The function to build grouped conditions.

Returns:

• *DBModel: A reference to the DBModel instance for chaining.

func (*DBModel) WhereOr ¶

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

WhereOr adds an OR condition to the WHERE clause.

Parameters:

• field (any): The field or column to filter.
• opt (qb.WhereOpt): The operator to use.
• value (any): The value to compare against.

Returns:

• *DBModel: A reference to the DBModel instance for chaining.