sqlquery

package module
v1.5.0 Latest Latest
Warning

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

 Go to latest Published: Jan 20, 2026 License: MIT Imports: 3 Imported by: 4

README

sqlquery

Build Status Go Report Card go.dev reference

A fluent, type-safe SQL query builder for Go with support for MySQL, PostgreSQL, and SQLite. Built on top of go-sqlbuilder, sqlquery provides a simplified API for constructing SQL queries with advanced filtering, pagination, and row locking capabilities.

Features

  • Multiple Database Support - Works with MySQL, PostgreSQL, and SQLite
  • Advanced Filtering - Rich filter syntax with operators (IN, NOT IN, >, >=, <, <=, LIKE, IS NULL, etc.)
  • Fluent API - Method chaining for clean, readable query construction
  • Type-Safe - Strongly typed options prevent common SQL building errors
  • Struct Mapping - Build INSERT/UPDATE queries directly from Go structs
  • Pagination Support - Built-in LIMIT and OFFSET with ordering
  • Row Locking - Support for FOR UPDATE with various locking modes
  • Parameterized Queries - Returns SQL with placeholders and args to prevent SQL injection

Installation

go get github.com/allisson/sqlquery

Quick Start

package main

import (
    "database/sql"
    "fmt"
    "log"

    "github.com/allisson/sqlquery"
    _ "github.com/lib/pq" // PostgreSQL driver
)

func main() {
    db, _ := sql.Open("postgres", "postgresql://user:pass@localhost/dbname?sslmode=disable")
    defer db.Close()

    // Build a simple SELECT query
    options := sqlquery.NewFindAllOptions(sqlquery.PostgreSQLFlavor).
        WithFilter("status", "active").
        WithLimit(10).
        WithOrderBy("created_at DESC")
    
    sql, args := sqlquery.FindAllQuery("users", options)
    
    rows, err := db.Query(sql, args...)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    
    // Process results...
}

Table of Contents

Basic Usage

SELECT Queries
Simple SELECT with FindQuery
// SELECT specific fields
options := sqlquery.NewFindOptions(sqlquery.MySQLFlavor).
    WithFields([]string{"id", "name", "email"}).
    WithFilter("status", "active")

sql, args := sqlquery.FindQuery("users", options)
// SQL: SELECT id, name, email FROM users WHERE status = ?
// Args: ["active"]
Paginated SELECT with FindAllQuery
// SELECT with pagination
options := sqlquery.NewFindAllOptions(sqlquery.PostgreSQLFlavor).
    WithFilter("status", "active").
    WithLimit(50).
    WithOffset(100).
    WithOrderBy("created_at DESC")

sql, args := sqlquery.FindAllQuery("users", options)
// SQL: SELECT * FROM users WHERE status = $1 ORDER BY created_at DESC LIMIT 50 OFFSET 100
// Args: ["active"]
INSERT Queries
type User struct {
    ID        int    `db:"id"`
    Name      string `db:"name"`
    Email     string `db:"email"`
    Status    string `db:"status"`
}

user := User{
    ID:     1,
    Name:   "John Doe",
    Email:  "john@example.com",
    Status: "active",
}

sql, args := sqlquery.InsertQuery(
    sqlquery.MySQLFlavor,
    "db",        // struct tag to use
    "users",     // table name
    &user,       // struct with values
)
// SQL: INSERT INTO users (id, name, email, status) VALUES (?, ?, ?, ?)
// Args: [1, "John Doe", "john@example.com", "active"]
UPDATE Queries
Update by ID using struct
type User struct {
    ID    int    `db:"id"`
    Name  string `db:"name" fieldtag:"update"`
    Email string `db:"email" fieldtag:"update"`
}

user := User{
    ID:    1,
    Name:  "Jane Doe",
    Email: "jane@example.com",
}

sql, args := sqlquery.UpdateQuery(
    sqlquery.PostgreSQLFlavor,
    "update",    // use "fieldtag" to determine which fields to update
    "users",
    user.ID,
    &user,
)
// SQL: UPDATE users SET name = $1, email = $2 WHERE id = $3
// Args: ["Jane Doe", "jane@example.com", 1]
Update with custom options
options := sqlquery.NewUpdateOptions(sqlquery.MySQLFlavor).
    WithAssignment("status", "inactive").
    WithAssignment("deactivated_at", time.Now()).
    WithFilter("last_login.lt", time.Now().AddDate(0, -6, 0))

sql, args := sqlquery.UpdateWithOptionsQuery("users", options)
// SQL: UPDATE users SET deactivated_at = ?, status = ? WHERE last_login < ?
// Args: [<timestamp>, "inactive", <6 months ago>]
DELETE Queries
Delete by ID
sql, args := sqlquery.DeleteQuery(sqlquery.SQLiteFlavor, "users", 1)
// SQL: DELETE FROM users WHERE id = ?
// Args: [1]
Delete with filters
options := sqlquery.NewDeleteOptions(sqlquery.PostgreSQLFlavor).
    WithFilter("status", "inactive").
    WithFilter("created_at.lt", time.Now().AddDate(-1, 0, 0))

sql, args := sqlquery.DeleteWithOptionsQuery("users", options)
// SQL: DELETE FROM users WHERE created_at < $1 AND status = $2
// Args: [<1 year ago>, "inactive"]

Advanced Filtering

sqlquery supports a rich filter syntax using dot notation for operators:

Filter Syntax SQL Operator Example
field = WithFilter("status", "active") → status = ?
field.in IN WithFilter("id.in", "1,2,3") → id IN (?, ?, ?)
field.notin NOT IN WithFilter("id.notin", "1,2,3") → id NOT IN (?, ?, ?)
field.not <> WithFilter("status.not", "deleted") → status <> ?
field.gt > WithFilter("age.gt", 18) → age > ?
field.gte >= WithFilter("age.gte", 18) → age >= ?
field.lt < WithFilter("age.lt", 65) → age < ?
field.lte <= WithFilter("age.lte", 65) → age <= ?
field.like LIKE WithFilter("name.like", "%John%") → name LIKE ?
field.null IS NULL / IS NOT NULL WithFilter("deleted_at.null", true) → deleted_at IS NULL
field (nil value) IS NULL WithFilter("deleted_at", nil) → deleted_at IS NULL
Filter Examples
// Equality filter
options := sqlquery.NewFindOptions(sqlquery.MySQLFlavor).
    WithFilter("status", "active")
// WHERE status = ?

// IN filter (comma-separated string)
options := sqlquery.NewFindOptions(sqlquery.MySQLFlavor).
    WithFilter("id.in", "1,2,3,4,5")
// WHERE id IN (?, ?, ?, ?, ?)

// NOT IN filter
options := sqlquery.NewFindOptions(sqlquery.MySQLFlavor).
    WithFilter("status.notin", "deleted,archived")
// WHERE status NOT IN (?, ?)

// Comparison filters
options := sqlquery.NewFindOptions(sqlquery.MySQLFlavor).
    WithFilter("age.gte", 18).
    WithFilter("age.lt", 65)
// WHERE age >= ? AND age < ?

// LIKE filter
options := sqlquery.NewFindOptions(sqlquery.MySQLFlavor).
    WithFilter("email.like", "%@example.com")
// WHERE email LIKE ?

// NULL filters
options := sqlquery.NewFindOptions(sqlquery.MySQLFlavor).
    WithFilter("deleted_at.null", true)
// WHERE deleted_at IS NULL

options := sqlquery.NewFindOptions(sqlquery.MySQLFlavor).
    WithFilter("verified_at.null", false)
// WHERE verified_at IS NOT NULL

// Multiple filters combined
options := sqlquery.NewFindAllOptions(sqlquery.PostgreSQLFlavor).
    WithFilter("status", "active").
    WithFilter("age.gte", 18).
    WithFilter("role.in", "admin,moderator").
    WithFilter("deleted_at.null", true).
    WithLimit(10)
// WHERE status = $1 AND age >= $2 AND role IN ($3, $4) AND deleted_at IS NULL LIMIT 10

Pagination and Ordering

// Basic pagination
options := sqlquery.NewFindAllOptions(sqlquery.PostgreSQLFlavor).
    WithLimit(20).
    WithOffset(0)

sql, args := sqlquery.FindAllQuery("products", options)
// SELECT * FROM products LIMIT 20 OFFSET 0

// Pagination with ordering
options := sqlquery.NewFindAllOptions(sqlquery.MySQLFlavor).
    WithLimit(50).
    WithOffset(100).
    WithOrderBy("created_at DESC, id ASC")

sql, args := sqlquery.FindAllQuery("orders", options)
// SELECT * FROM orders ORDER BY created_at DESC, id ASC LIMIT 50 OFFSET 100

// Combining with filters
options := sqlquery.NewFindAllOptions(sqlquery.PostgreSQLFlavor).
    WithFilter("status", "pending").
    WithFilter("priority.gte", 5).
    WithOrderBy("priority DESC, created_at ASC").
    WithLimit(25).
    WithOffset(0)

sql, args := sqlquery.FindAllQuery("tasks", options)
// SELECT * FROM tasks WHERE priority >= $1 AND status = $2 
// ORDER BY priority DESC, created_at ASC LIMIT 25 OFFSET 0

Row Locking

Support for FOR UPDATE with various locking modes (database-specific):

// Basic FOR UPDATE
options := sqlquery.NewFindOptions(sqlquery.PostgreSQLFlavor).
    WithFilter("id", 1).
    WithForUpdate("")

sql, args := sqlquery.FindQuery("accounts", options)
// SELECT * FROM accounts WHERE id = $1 FOR UPDATE

// FOR UPDATE NOWAIT (PostgreSQL/MySQL)
options := sqlquery.NewFindOptions(sqlquery.PostgreSQLFlavor).
    WithFilter("id", 1).
    WithForUpdate("NOWAIT")

sql, args := sqlquery.FindQuery("accounts", options)
// SELECT * FROM accounts WHERE id = $1 FOR UPDATE NOWAIT

// FOR UPDATE SKIP LOCKED (PostgreSQL/MySQL 8.0+)
options := sqlquery.NewFindAllOptions(sqlquery.MySQLFlavor).
    WithFilter("status", "pending").
    WithLimit(10).
    WithForUpdate("SKIP LOCKED")

sql, args := sqlquery.FindAllQuery("queue_items", options)
// SELECT * FROM queue_items WHERE status = ? LIMIT 10 FOR UPDATE SKIP LOCKED

// Useful for job queues
options := sqlquery.NewFindAllOptions(sqlquery.PostgreSQLFlavor).
    WithFilter("status", "pending").
    WithOrderBy("priority DESC, created_at ASC").
    WithLimit(1).
    WithForUpdate("SKIP LOCKED")

sql, args := sqlquery.FindAllQuery("jobs", options)
// SELECT * FROM jobs WHERE status = $1 ORDER BY priority DESC, created_at ASC 
// LIMIT 1 FOR UPDATE SKIP LOCKED

Struct-Based Queries

Use struct tags to control which fields are included in INSERT and UPDATE operations:

type User struct {
    ID        int       `db:"id" insert:"insert"`
    Name      string    `db:"name" insert:"insert" update:"update"`
    Email     string    `db:"email" insert:"insert" update:"update"`
    Password  string    `db:"password" insert:"insert"`
    CreatedAt time.Time `db:"created_at" insert:"insert"`
    UpdatedAt time.Time `db:"updated_at" update:"update"`
}

// INSERT with "insert" tag
user := User{
    ID:        1,
    Name:      "Alice Smith",
    Email:     "alice@example.com",
    Password:  "hashed_password",
    CreatedAt: time.Now(),
}

sql, args := sqlquery.InsertQuery(
    sqlquery.PostgreSQLFlavor,
    "insert",
    "users",
    &user,
)
// SQL: INSERT INTO users (id, name, email, password, created_at) 
//      VALUES ($1, $2, $3, $4, $5)
// Args: [1, "Alice Smith", "alice@example.com", "hashed_password", <timestamp>]

// UPDATE with "update" tag (only updates name, email, updated_at)
user.Name = "Alice Johnson"
user.Email = "alice.johnson@example.com"
user.UpdatedAt = time.Now()

sql, args := sqlquery.UpdateQuery(
    sqlquery.PostgreSQLFlavor,
    "update",
    "users",
    user.ID,
    &user,
)
// SQL: UPDATE users SET email = $1, name = $2, updated_at = $3 WHERE id = $4
// Args: ["alice.johnson@example.com", "Alice Johnson", <timestamp>, 1]

Database Flavors

sqlquery supports three database flavors with appropriate SQL dialect handling:

// MySQL / MariaDB
sqlquery.MySQLFlavor
// Uses: ? for placeholders, MySQL-specific syntax

// PostgreSQL
sqlquery.PostgreSQLFlavor
// Uses: $1, $2, $3 for placeholders, PostgreSQL-specific syntax

// SQLite
sqlquery.SQLiteFlavor
// Uses: ? for placeholders, SQLite-specific syntax
Flavor Examples
// MySQL
options := sqlquery.NewFindOptions(sqlquery.MySQLFlavor).
    WithFilter("id", 1)
sql, args := sqlquery.FindQuery("users", options)
// SELECT * FROM users WHERE id = ?

// PostgreSQL
options := sqlquery.NewFindOptions(sqlquery.PostgreSQLFlavor).
    WithFilter("id", 1)
sql, args := sqlquery.FindQuery("users", options)
// SELECT * FROM users WHERE id = $1

// SQLite
options := sqlquery.NewFindOptions(sqlquery.SQLiteFlavor).
    WithFilter("id", 1)
sql, args := sqlquery.FindQuery("users", options)
// SELECT * FROM users WHERE id = ?

Complete Examples

Example 1: User Management System
package main

import (
    "database/sql"
    "fmt"
    "time"

    "github.com/allisson/sqlquery"
    _ "github.com/lib/pq"
)

type User struct {
    ID        int       `db:"id"`
    Name      string    `db:"name"`
    Email     string    `db:"email"`
    Status    string    `db:"status"`
    CreatedAt time.Time `db:"created_at"`
}

func main() {
    db, _ := sql.Open("postgres", "postgresql://user:pass@localhost/mydb?sslmode=disable")
    defer db.Close()

    // Find active users with pagination
    options := sqlquery.NewFindAllOptions(sqlquery.PostgreSQLFlavor).
        WithFilter("status", "active").
        WithFilter("created_at.gte", time.Now().AddDate(0, -1, 0)).
        WithOrderBy("created_at DESC").
        WithLimit(20).
        WithOffset(0)

    sql, args := sqlquery.FindAllQuery("users", options)
    rows, _ := db.Query(sql, args...)
    defer rows.Close()

    var users []User
    for rows.Next() {
        var u User
        rows.Scan(&u.ID, &u.Name, &u.Email, &u.Status, &u.CreatedAt)
        users = append(users, u)
    }

    fmt.Printf("Found %d active users\n", len(users))
}
Example 2: E-commerce Order Processing
package main

import (
    "database/sql"
    "time"

    "github.com/allisson/sqlquery"
    _ "github.com/go-sql-driver/mysql"
)

func processOrders(db *sql.DB) error {
    // Start transaction
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    // Lock and fetch pending orders
    options := sqlquery.NewFindAllOptions(sqlquery.MySQLFlavor).
        WithFilter("status", "pending").
        WithFilter("created_at.lt", time.Now().Add(-5*time.Minute)).
        WithOrderBy("priority DESC, created_at ASC").
        WithLimit(10).
        WithForUpdate("SKIP LOCKED")

    sql, args := sqlquery.FindAllQuery("orders", options)
    rows, err := tx.Query(sql, args...)
    if err != nil {
        return err
    }
    defer rows.Close()

    // Process each order
    for rows.Next() {
        var orderID int
        rows.Scan(&orderID)

        // Update order status
        updateOpts := sqlquery.NewUpdateOptions(sqlquery.MySQLFlavor).
            WithAssignment("status", "processing").
            WithAssignment("processed_at", time.Now()).
            WithFilter("id", orderID)

        updateSQL, updateArgs := sqlquery.UpdateWithOptionsQuery("orders", updateOpts)
        _, err = tx.Exec(updateSQL, updateArgs...)
        if err != nil {
            return err
        }
    }

    return tx.Commit()
}
Example 3: Data Cleanup Job
package main

import (
    "database/sql"
    "log"
    "time"

    "github.com/allisson/sqlquery"
    _ "github.com/mattn/go-sqlite3"
)

func cleanupOldRecords(db *sql.DB) error {
    // Delete inactive users older than 2 years
    deleteOpts := sqlquery.NewDeleteOptions(sqlquery.SQLiteFlavor).
        WithFilter("status", "inactive").
        WithFilter("last_login.lt", time.Now().AddDate(-2, 0, 0))

    sql, args := sqlquery.DeleteWithOptionsQuery("users", deleteOpts)
    result, err := db.Exec(sql, args...)
    if err != nil {
        return err
    }

    deleted, _ := result.RowsAffected()
    log.Printf("Deleted %d inactive users\n", deleted)

    // Archive completed orders older than 1 year
    archiveOpts := sqlquery.NewFindAllOptions(sqlquery.SQLiteFlavor).
        WithFilter("status", "completed").
        WithFilter("completed_at.lt", time.Now().AddDate(-1, 0, 0)).
        WithLimit(1000)

    sql, args = sqlquery.FindAllQuery("orders", archiveOpts)
    rows, err := db.Query(sql, args...)
    if err != nil {
        return err
    }
    defer rows.Close()

    // Process archival...

    return nil
}
Example 4: Search with Multiple Filters
package main

import (
    "database/sql"
    "fmt"

    "github.com/allisson/sqlquery"
)

type SearchParams struct {
    Query    string
    Category string
    MinPrice float64
    MaxPrice float64
    InStock  bool
    Page     int
    PageSize int
}

func searchProducts(db *sql.DB, params SearchParams) ([]Product, error) {
    options := sqlquery.NewFindAllOptions(sqlquery.PostgreSQLFlavor)

    // Add filters based on search parameters
    if params.Query != "" {
        options = options.WithFilter("name.like", "%"+params.Query+"%")
    }
    if params.Category != "" {
        options = options.WithFilter("category", params.Category)
    }
    if params.MinPrice > 0 {
        options = options.WithFilter("price.gte", params.MinPrice)
    }
    if params.MaxPrice > 0 {
        options = options.WithFilter("price.lte", params.MaxPrice)
    }
    if params.InStock {
        options = options.WithFilter("stock.gt", 0)
    }

    // Add pagination
    offset := (params.Page - 1) * params.PageSize
    options = options.
        WithLimit(params.PageSize).
        WithOffset(offset).
        WithOrderBy("popularity DESC, name ASC")

    sql, args := sqlquery.FindAllQuery("products", options)
    rows, err := db.Query(sql, args...)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var products []Product
    for rows.Next() {
        var p Product
        // Scan into product...
        products = append(products, p)
    }

    return products, nil
}

API Reference

For complete API documentation, see pkg.go.dev.

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

This project is licensed under the MIT License - see the LICENSE file for details.

Documentation

Overview

Package sqlquery provides a fluent API for building SQL queries with support for MySQL, PostgreSQL, and SQLite.

The package wraps github.com/huandu/go-sqlbuilder and provides simplified query building functions with support for advanced filtering, pagination, and row locking.

Basic Usage

Build a SELECT query with filters: 

options := NewFindAllOptions(MySQLFlavor).
	WithFilter("status", "active").
	WithFilter("age.gte", 18).
	WithLimit(10).
	WithOffset(0).
	WithOrderBy("created_at DESC")
sql, args := FindAllQuery("users", options)

Filter Syntax

The Filters map supports special operators via dot notation: 

"field"          - Equality (field = value)
"field.in"       - IN clause (value must be comma-separated string)
"field.notin"    - NOT IN clause (value must be comma-separated string)
"field.not"      - Not equal (field != value)
"field.gt"       - Greater than (field > value)
"field.gte"      - Greater or equal (field >= value)
"field.lt"       - Less than (field < value)
"field.lte"      - Less or equal (field <= value)
"field.like"     - LIKE pattern matching
"field.null"     - IS NULL / IS NOT NULL (value must be bool)

Supported Database Flavors

Use one of the predefined flavors when creating options: 

MySQLFlavor      - MySQL and MariaDB
PostgreSQLFlavor - PostgreSQL
SQLiteFlavor     - SQLite

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func DeleteQuery 

func DeleteQuery(flavor Flavor, tableName string, id interface{}) (string, []interface{})

DeleteQuery builds a DELETE query and returns the compiled SQL string and arguments.

The function deletes a record identified by its ID.

Parameters:

  • flavor: The SQL dialect (MySQLFlavor, PostgreSQLFlavor, or SQLiteFlavor)
  • tableName: The name of the table to delete from
  • id: The ID value for the WHERE id = ? condition

Returns the compiled SQL string and a slice of arguments for parameterized queries.

Example: 

sql, args := DeleteQuery(MySQLFlavor, "users", 123)
// DELETE FROM users WHERE id = ?

func DeleteWithOptionsQuery added in v1.3.0 

func DeleteWithOptionsQuery(tableName string, options *DeleteOptions) (string, []interface{})

DeleteWithOptionsQuery builds a DELETE query with custom filter conditions.

This function provides more flexibility than DeleteQuery by allowing:

  • Multiple WHERE conditions with filter operators
  • Deleting multiple records matching complex criteria

Parameters:

  • tableName: The name of the table to delete from
  • options: Configuration including flavor and filter conditions

Returns the compiled SQL string and a slice of arguments for parameterized queries.

Example: 

options := NewDeleteOptions(MySQLFlavor).
	WithFilter("status", "inactive").
	WithFilter("created_at.lt", time.Now().AddDate(-1, 0, 0))
sql, args := DeleteWithOptionsQuery("users", options)
// DELETE FROM users WHERE status = ? AND created_at < ?

func FindAllQuery 

func FindAllQuery(tableName string, options *FindAllOptions) (string, []interface{})

FindAllQuery builds a SELECT query with pagination and returns the compiled SQL string and arguments.

This function extends FindQuery with support for LIMIT, OFFSET, and ORDER BY clauses.

Parameters:

  • tableName: The name of the table to query
  • options: Configuration including flavor, fields, filters, limit, offset, ordering, and FOR UPDATE settings

Returns the compiled SQL string and a slice of arguments for parameterized queries.

Example: 

options := NewFindAllOptions(MySQLFlavor).
	WithFilter("status", "active").
	WithLimit(10).
	WithOffset(20).
	WithOrderBy("created_at DESC")
sql, args := FindAllQuery("users", options)
// SELECT * FROM users WHERE status = ? ORDER BY created_at DESC LIMIT 10 OFFSET 20

func FindQuery 

func FindQuery(tableName string, options *FindOptions) (string, []interface{})

FindQuery builds a SELECT query and returns the compiled SQL string and arguments.

The function supports filtering with special operators, field selection, and row locking (FOR UPDATE).

Parameters:

  • tableName: The name of the table to query
  • options: Configuration including flavor, fields, filters, and FOR UPDATE settings

Returns the compiled SQL string and a slice of arguments for parameterized queries.

Example: 

options := NewFindOptions(MySQLFlavor).
	WithFields([]string{"id", "name", "email"}).
	WithFilter("status", "active").
	WithFilter("age.gte", 18)
sql, args := FindQuery("users", options)
// SELECT id, name, email FROM users WHERE status = ? AND age >= ?

func InsertQuery 

func InsertQuery(flavor Flavor, tag, tableName string, structValue interface{}) (string, []interface{})

InsertQuery builds an INSERT query from a struct and returns the compiled SQL string and arguments.

The function uses struct tags to map struct fields to database columns. Fields are extracted based on the specified tag name (e.g., "db", "sql", or custom tags).

Parameters:

  • flavor: The SQL dialect (MySQLFlavor, PostgreSQLFlavor, or SQLiteFlavor)
  • tag: The struct tag name to use for field mapping (e.g., "db", "sql")
  • tableName: The name of the table to insert into
  • structValue: The struct instance containing values to insert

Returns the compiled SQL string and a slice of arguments for parameterized queries.

Example: 

type User struct {
	Name  string `db:"name"`
	Email string `db:"email"`
	Age   int    `db:"age"`
}
user := User{Name: "John", Email: "john@example.com", Age: 30}
sql, args := InsertQuery(MySQLFlavor, "db", "users", user)
// INSERT INTO users (name, email, age) VALUES (?, ?, ?)

func UpdateQuery 

func UpdateQuery(flavor Flavor, tag, tableName string, id interface{}, structValue interface{}) (string, []interface{})

UpdateQuery builds an UPDATE query from a struct and returns the compiled SQL string and arguments.

The function updates a record identified by its ID. Struct tags determine which fields to update.

Parameters:

  • flavor: The SQL dialect (MySQLFlavor, PostgreSQLFlavor, or SQLiteFlavor)
  • tag: The struct tag name to use for field mapping (e.g., "db", "sql")
  • tableName: The name of the table to update
  • id: The ID value for the WHERE id = ? condition
  • structValue: The struct instance containing updated values

Returns the compiled SQL string and a slice of arguments for parameterized queries.

Example: 

type User struct {
	Name  string `db:"name"`
	Email string `db:"email"`
	Age   int    `db:"age"`
}
user := User{Name: "John", Email: "john@example.com", Age: 31}
sql, args := UpdateQuery(MySQLFlavor, "db", "users", 123, user)
// UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?

func UpdateWithOptionsQuery added in v1.3.0 

func UpdateWithOptionsQuery(tableName string, options *UpdateOptions) (string, []interface{})

UpdateWithOptionsQuery builds an UPDATE query with custom field assignments and filters.

This function provides more flexibility than UpdateQuery by allowing:

  • Custom field assignments (not limited to struct fields)
  • Multiple WHERE conditions with filter operators
  • Updating multiple records at once

Parameters:

  • tableName: The name of the table to update
  • options: Configuration including flavor, field assignments, and filter conditions

Returns the compiled SQL string and a slice of arguments for parameterized queries.

Example: 

options := NewUpdateOptions(MySQLFlavor).
	WithAssignment("status", "inactive").
	WithAssignment("updated_at", time.Now()).
	WithFilter("last_login.lt", time.Now().AddDate(0, -6, 0))
sql, args := UpdateWithOptionsQuery("users", options)
// UPDATE users SET status = ?, updated_at = ? WHERE last_login < ?

Types

type DeleteOptions added in v1.3.0 

type DeleteOptions struct {
	Flavor  Flavor
	Filters map[string]interface{}
}

DeleteOptions configures the behavior of DeleteWithOptionsQuery for building DELETE queries.

Fields:

  • Flavor: The SQL dialect (MySQLFlavor, PostgreSQLFlavor, or SQLiteFlavor)
  • Filters: WHERE conditions using field names and optional operators (see package docs for filter syntax)

func NewDeleteOptions added in v1.3.0 

func NewDeleteOptions(flavor Flavor) *DeleteOptions

NewDeleteOptions creates a new DeleteOptions with default values. An empty filters map is initialized.

func (*DeleteOptions) WithFilter added in v1.3.0 

func (d *DeleteOptions) WithFilter(field string, value interface{}) *DeleteOptions

WithFilter returns a new DeleteOptions with an additional filter condition. Supports special operators via dot notation (e.g., "age.gte", "status.in"). See package documentation for complete filter syntax.

type FindAllOptions 

type FindAllOptions struct {
	Flavor        Flavor
	Fields        []string
	Filters       map[string]interface{}
	Limit         int
	Offset        int
	OrderBy       string
	ForUpdate     bool
	ForUpdateMode string
}

FindAllOptions configures the behavior of FindAllQuery for building paginated SELECT queries.

Fields:

  • Flavor: The SQL dialect (MySQLFlavor, PostgreSQLFlavor, or SQLiteFlavor)
  • Fields: Column names to select (defaults to "*" for all columns)
  • Filters: WHERE conditions using field names and optional operators (see package docs for filter syntax)
  • Limit: Maximum number of rows to return
  • Offset: Number of rows to skip before returning results
  • OrderBy: ORDER BY clause (e.g., "created_at DESC", "name ASC, id DESC")
  • ForUpdate: Whether to add FOR UPDATE clause for row locking
  • ForUpdateMode: Optional mode for FOR UPDATE (e.g., "NOWAIT", "SKIP LOCKED")

func NewFindAllOptions 

func NewFindAllOptions(flavor Flavor) *FindAllOptions

NewFindAllOptions creates a new FindAllOptions with default values. The fields list defaults to ["*"] (all columns), and an empty filters map is initialized.

func (*FindAllOptions) WithFields 

func (f *FindAllOptions) WithFields(fields []string) *FindAllOptions

WithFields returns a new FindAllOptions with the specified field list. Use this to select specific columns instead of "*".

func (*FindAllOptions) WithFilter 

func (f *FindAllOptions) WithFilter(field string, value interface{}) *FindAllOptions

WithFilter returns a new FindAllOptions with an additional filter condition. Supports special operators via dot notation (e.g., "age.gte", "status.in"). See package documentation for complete filter syntax.

func (*FindAllOptions) WithForUpdate added in v1.1.0 

func (f *FindAllOptions) WithForUpdate(mode string) *FindAllOptions

WithForUpdate returns a new FindAllOptions with FOR UPDATE clause enabled. The mode parameter can specify locking behavior (e.g., "NOWAIT", "SKIP LOCKED"). Pass an empty string for default FOR UPDATE behavior.

func (*FindAllOptions) WithLimit 

func (f *FindAllOptions) WithLimit(limit int) *FindAllOptions

WithLimit returns a new FindAllOptions with the specified LIMIT value.

func (*FindAllOptions) WithOffset 

func (f *FindAllOptions) WithOffset(offset int) *FindAllOptions

WithOffset returns a new FindAllOptions with the specified OFFSET value.

func (*FindAllOptions) WithOrderBy 

func (f *FindAllOptions) WithOrderBy(orderBy string) *FindAllOptions

WithOrderBy returns a new FindAllOptions with the specified ORDER BY clause. The orderBy parameter should be a valid SQL ORDER BY expression (e.g., "created_at DESC").

type FindOptions 

type FindOptions struct {
	Flavor        Flavor
	Fields        []string
	Filters       map[string]interface{}
	ForUpdate     bool
	ForUpdateMode string
}

FindOptions configures the behavior of FindQuery for building SELECT queries.

Fields:

  • Flavor: The SQL dialect (MySQLFlavor, PostgreSQLFlavor, or SQLiteFlavor)
  • Fields: Column names to select (defaults to "*" for all columns)
  • Filters: WHERE conditions using field names and optional operators (see package docs for filter syntax)
  • ForUpdate: Whether to add FOR UPDATE clause for row locking
  • ForUpdateMode: Optional mode for FOR UPDATE (e.g., "NOWAIT", "SKIP LOCKED")

func NewFindOptions 

func NewFindOptions(flavor Flavor) *FindOptions

NewFindOptions creates a new FindOptions with default values. The fields list defaults to ["*"] (all columns), and an empty filters map is initialized.

func (*FindOptions) WithFields 

func (f *FindOptions) WithFields(fields []string) *FindOptions

WithFields returns a new FindOptions with the specified field list. Use this to select specific columns instead of "*".

func (*FindOptions) WithFilter 

func (f *FindOptions) WithFilter(field string, value interface{}) *FindOptions

WithFilter returns a new FindOptions with an additional filter condition. Supports special operators via dot notation (e.g., "age.gte", "status.in"). See package documentation for complete filter syntax.

func (*FindOptions) WithForUpdate added in v1.4.0 

func (f *FindOptions) WithForUpdate(mode string) *FindOptions

WithForUpdate returns a new FindOptions with FOR UPDATE clause enabled. The mode parameter can specify locking behavior (e.g., "NOWAIT", "SKIP LOCKED"). Pass an empty string for default FOR UPDATE behavior.

type Flavor 

type Flavor int

Flavor represents the SQL dialect used for query compilation. It controls the format of compiled SQL to match database-specific syntax. 

const (
	MySQLFlavor Flavor = iota + 1
	PostgreSQLFlavor
	SQLiteFlavor
)

Supported SQL database flavors for query compilation.

type UpdateOptions added in v1.3.0 

type UpdateOptions struct {
	Flavor      Flavor
	Assignments map[string]interface{}
	Filters     map[string]interface{}
}

UpdateOptions configures the behavior of UpdateWithOptionsQuery for building UPDATE queries.

Fields:

  • Flavor: The SQL dialect (MySQLFlavor, PostgreSQLFlavor, or SQLiteFlavor)
  • Assignments: Field-value pairs to set (e.g., {"status": "active", "updated_at": time.Now()})
  • Filters: WHERE conditions using field names and optional operators (see package docs for filter syntax)

func NewUpdateOptions added in v1.3.0 

func NewUpdateOptions(flavor Flavor) *UpdateOptions

NewUpdateOptions creates a new UpdateOptions with default values. Empty maps are initialized for both assignments and filters.

func (*UpdateOptions) WithAssignment added in v1.3.0 

func (u *UpdateOptions) WithAssignment(field string, value interface{}) *UpdateOptions

WithAssignment returns a new UpdateOptions with an additional field assignment. Use this to specify which fields to update and their new values.

func (*UpdateOptions) WithFilter added in v1.3.0 

func (u *UpdateOptions) WithFilter(field string, value interface{}) *UpdateOptions

WithFilter returns a new UpdateOptions with an additional filter condition. Supports special operators via dot notation (e.g., "age.gte", "status.in"). See package documentation for complete filter syntax.

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.