siftgo

package module
v0.1.0 Latest Latest
Warning

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

 Go to latest Published: Mar 24, 2025 License: MIT Imports: 4 Imported by: 0

README

siftgo

Go Reference Go Report Card

SiftGo is a lightweight and elegant package for Go that allows you to apply filters to SQL queries based on HTTP request parameters. The package works with any SQL client and supports various databases.

Installation

go get github.com/xdevspo/siftgo

Features

  • Simple and intuitive API, inspired by Laravel Query Filters
  • Works with any SQL client (database/sql, pgx, gorm, etc.)
  • Support for different databases (MySQL, PostgreSQL, etc.)
  • Automatic application of filters based on method names
  • Safe SQL queries with parameterization to protect against SQL injection
  • Minimal dependencies

Quick Start

1. Create a filter
// UserFilter defines filters for users
type UserFilter struct {
    siftgo.BaseFilter
}

// Default applies default filters
func (f *UserFilter) Default(query siftgo.Query) siftgo.Query {
    return query.WhereEqual("active", true)
}

// Search filters users by name or email
func (f *UserFilter) Search(query siftgo.Query, value string) siftgo.Query {
    return query.GroupWhere(func(q siftgo.Query) siftgo.Query {
        return q.WhereLike("first_name", value).
            OrWhereLike("last_name", value).
            OrWhereLike("email", value)
    })
}

// Role filters users by role
func (f *UserFilter) Role(query siftgo.Query, role string) siftgo.Query {
    return query.WhereEqual("role", role)
}
2. Set the placeholder format for your database
// For MySQL
siftgo.SetPlaceholderFormat(siftgo.MySQLPlaceholder) // ?

// For PostgreSQL (default)
siftgo.SetPlaceholderFormat(siftgo.PostgreSQLPlaceholder) // $1, $2, ...

// For MS SQL Server
siftgo.SetPlaceholderFormat(siftgo.MSSQLPlaceholder) // @p1, @p2, ...

// For Oracle
siftgo.SetPlaceholderFormat(siftgo.OraclePlaceholder) // :1, :2, ...
3. Convert HTTP request parameters
func getUsersHandler(w http.ResponseWriter, r *http.Request) {
    // Get request parameters
    query := r.URL.Query()
    
    // Convert to a map for filters (automatically determines types)
    inputs := siftgo.ExtractQueryParams(query, "page", "per_page")
    
    // Create a filter
    filter := &UserFilter{}
    
    // Base SQL query
    baseSQL := "SELECT id, name, email, role FROM users"
    
    // Apply filters
    filteredQuery := siftgo.ApplyFilters(baseSQL, filter, inputs, true)
    
    // For counting total records
    countQuery := siftgo.BuildCountQuery(filteredQuery)
    countSQL, countArgs := countQuery.GetSQL()
    
    var total int
    db.QueryRow(countSQL, countArgs...).Scan(&total)
    
    // Add sorting and pagination
    page := 1
    perPage := 15
    
    // ... get page and perPage from the request ...
    
    filteredQuery = filteredQuery.
        OrderBy("id", "ASC").
        Limit(perPage).
        Offset((page - 1) * perPage)
    
    // Get final SQL and arguments
    sql, args := filteredQuery.GetSQL()
    
    // Execute the query with your SQL client
    rows, err := db.Query(sql, args...)
    // ... process results ...
}

How it works

siftgo automatically looks for methods in your filter that correspond to HTTP request parameters:

  1. When a user makes a request, for example, /api/users?search=john&role=admin, siftgo looks for the Search and Role methods in the filter.
  2. If the methods are found, they are called with the values from the request.
  3. Each method adds conditions to the SQL query and returns the modified query.
  4. In the end, you get a ready SQL query and parameters that can be used with any SQL client.

Supported conditions

siftgo provides many methods for building SQL queries:

// Basic WHERE conditions
query.Where("age > ?", 18)
query.WhereEqual("status", "active")
query.WhereNotEqual("deleted", true)
query.WhereIn("role", "admin", "editor")
query.WhereNotIn("category", "archived", "deleted")
query.WhereNull("deleted_at")
query.WhereNotNull("email")
query.WhereLike("name", "John") // LIKE '%John%'
query.WhereILike("email", "gmail") // ILIKE '%gmail%' (PostgreSQL)

// OR conditions
query.OrWhere("role = ?", "guest")
query.OrWhereEqual("department", "sales")
query.OrWhereIn("category", "new", "featured")

// Grouping conditions
query.GroupWhere(func(q siftgo.Query) siftgo.Query {
    return q.Where("status = ?", "active").Where("role = ?", "admin")
})

query.OrGroupWhere(func(q siftgo.Query) siftgo.Query {
    return q.WhereEqual("department", "sales").WhereEqual("manager_id", 5)
})

// Sorting, grouping, limits
query.OrderBy("created_at", "DESC")
query.GroupBy("department_id")
query.Having("COUNT(*) > ?", 5)
query.Limit(10)
query.Offset(20)

// Table joins
query.Join("roles", "users.role_id = roles.id")
query.LeftJoin("profiles", "users.id = profiles.user_id")
query.RightJoin("departments", "users.department_id = departments.id")

Usage examples

Example with MySQL
package main

import (
    "database/sql"
    "fmt"
    "net/http"
    
    _ "github.com/go-sql-driver/mysql"
    "github.com/yourusername/siftgo"
)

func init() {
    // Set the placeholder format for MySQL
    siftgo.SetPlaceholderFormat(siftgo.MySQLPlaceholder)
}

func main() {
    db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/mydb")
    if err != nil {
        panic(err)
    }
    defer db.Close()
    
    http.HandleFunc("/api/users", func(w http.ResponseWriter, r *http.Request) {
        // Get request parameters
        inputs := siftgo.ExtractQueryParams(r.URL.Query(), "page", "per_page")
        
        // Apply filters
        filter := &UserFilter{}
        query := siftgo.ApplyFilters("SELECT * FROM users", filter, inputs, true)
        
        // Get SQL and arguments
        sql, args := query.GetSQL()
        
        // Execute the query
        rows, err := db.Query(sql, args...)
        if err != nil {
            http.Error(w, err.Error(), http.StatusInternalServerError)
            return
        }
        defer rows.Close()
        
        // Process results...
    })
    
    http.ListenAndServe(":8080", nil)
}
Example with PostgreSQL (pgx)
package main

import (
    "context"
    "fmt"
    "net/http"
    
    "github.com/jackc/pgx/v5/pgxpool"
    "github.com/yourusername/siftgo"
)

func main() {
    ctx := context.Background()
    pool, err := pgxpool.New(ctx, "postgres://user:password@localhost:5432/mydb")
    if err != nil {
        panic(err)
    }
    defer pool.Close()
    
    http.HandleFunc("/api/users", func(w http.ResponseWriter, r *http.Request) {
        // Get request parameters
        inputs := siftgo.ExtractQueryParams(r.URL.Query(), "page", "per_page")
        
        // Apply filters
        filter := &UserFilter{}
        query := siftgo.ApplyFilters("SELECT * FROM users", filter, inputs, true)
        
        // Get SQL and arguments
        sql, args := query.GetSQL()
        
        // Execute the query
        rows, err := pool.Query(ctx, sql, args...)
        if err != nil {
            http.Error(w, err.Error(), http.StatusInternalServerError)
            return
        }
        defer rows.Close()
        
        // Process results...
    })
    
    http.ListenAndServe(":8080", nil)
}

Advanced features

Complex filters with nested conditions
// RequestFilter defines filters for requests/tickets
type RequestFilter struct {
    siftgo.BaseFilter
}

// Types filters requests by types
func (f *RequestFilter) Types(query siftgo.Query, types []string) siftgo.Query {
    if len(types) == 0 {
        return query
    }
    
    // Convert strings to interfaces
    values := make([]interface{}, len(types))
    for i, v := range types {
        values[i] = v
    }
    
    // Apply filter with OR
    return query.GroupWhere(func(q siftgo.Query) siftgo.Query {
        q = q.WhereIn("request_type", values...)
        q = q.OrWhereIn("request_subtype", values...)
        return q
    })
}

// Department filters by department with a subquery
func (f *RequestFilter) Department(query siftgo.Query, deptId int) siftgo.Query {
    return query.GroupWhere(func(q siftgo.Query) siftgo.Query {
        q = q.WhereEqual("department_id", deptId)
        q = q.OrWhere("department_id IN (SELECT id FROM departments WHERE parent_id = ?)", deptId)
        return q
    })
}
Using with models
// Model represents a base data model
type Model interface {
    TableName() string
}

// User model
type User struct {
    ID    int
    Name  string
    Email string
}

func (u *User) TableName() string {
    return "users"
}

// Repository for working with models
type Repository struct {
    DB *sql.DB
}

func (r *Repository) GetAll(model Model, filter siftgo.QueryFilter, inputs map[string]interface{}) ([]interface{}, error) {
    // Get the table name from the model
    tableName := model.TableName()
    
    // Form the base SQL query
    baseSQL := fmt.Sprintf("SELECT * FROM %s", tableName)
    
    // Apply filters
    query := siftgo.ApplyFilters(baseSQL, filter, inputs, true)
    
    // Get SQL and arguments
    sql, args := query.GetSQL()
    
    // Execute the query
    // ...
}

License

MIT

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func ExtractQueryParams 

func ExtractQueryParams(queryParams map[string][]string, skip ...string) map[string]interface{}

ExtractQueryParams converts URL parameters to a map for filters

func MSSQLPlaceholder 

func MSSQLPlaceholder(position int) string

MSSQLPlaceholder returns a placeholder for MS SQL Server (@p1, @p2, ...)

func MySQLPlaceholder 

func MySQLPlaceholder(_ int) string

MySQLPlaceholder returns a placeholder for MySQL (?)

func OraclePlaceholder 

func OraclePlaceholder(position int) string

OraclePlaceholder returns a placeholder for Oracle (:1, :2, ...)

func PostgreSQLPlaceholder 

func PostgreSQLPlaceholder(position int) string

PostgreSQLPlaceholder returns a placeholder for PostgreSQL ($n)

func RemoveOrderLimitOffset 

func RemoveOrderLimitOffset(sql string) string

RemoveOrderLimitOffset removes ORDER BY, LIMIT, OFFSET from an SQL query Useful for counting total records

func SetPlaceholderFormat 

func SetPlaceholderFormat(format func(int) string)

SetPlaceholderFormat sets the placeholder format For example, for MySQL: ? For PostgreSQL: $1, $2, ...

Types

type BaseFilter 

type BaseFilter struct{}

BaseFilter is the base implementation of a filter

func (*BaseFilter) Apply 

func (f *BaseFilter) Apply(query Query, inputs map[string]interface{}, withDefault bool) Query

Apply applies filters to the query

func (*BaseFilter) Default 

func (f *BaseFilter) Default(query Query) Query

Default applies default filters This method must be overridden in specific filter implementations

type Query 

type Query struct {
	SQL  string
	Args []interface{}
}

Query represents an SQL query with arguments

func ApplyFilters 

func ApplyFilters(sql string, filterInstance QueryFilter, inputs map[string]interface{}, withDefault bool) Query

ApplyFilters applies filters to the SQL query sql - the base SQL query filterInstance - an instance of a filter implementing the QueryFilter interface inputs - a map of input parameters where keys correspond to filter method names withDefault - a flag for applying default filters

func BuildCountQuery 

func BuildCountQuery(query Query) Query

BuildCountQuery creates a query for counting total rows

func NewQuery 

func NewQuery(sql string, args ...interface{}) Query

NewQuery creates a new query

func (Query) GetSQL 

func (q Query) GetSQL() (string, []interface{})

GetSQL returns the SQL query and parameters

func (Query) GroupBy 

func (q Query) GroupBy(fields ...string) Query

GroupBy adds grouping

func (Query) GroupWhere 

func (q Query) GroupWhere(fn func(Query) Query) Query

GroupWhere adds a group of conditions with AND

func (Query) Having 

func (q Query) Having(condition string, args ...interface{}) Query

Having adds a HAVING condition

func (Query) Join 

func (q Query) Join(table string, condition string) Query

Join adds a JOIN

func (Query) LeftJoin 

func (q Query) LeftJoin(table string, condition string) Query

LeftJoin adds a LEFT JOIN

func (Query) Limit 

func (q Query) Limit(limit int) Query

Limit adds a limit on the number of rows

func (Query) Offset 

func (q Query) Offset(offset int) Query

Offset adds an offset

func (Query) OrGroupWhere 

func (q Query) OrGroupWhere(fn func(Query) Query) Query

OrGroupWhere adds a group of conditions with OR

func (Query) OrWhere 

func (q Query) OrWhere(condition string, args ...interface{}) Query

OrWhere adds an OR WHERE condition

func (Query) OrWhereEqual 

func (q Query) OrWhereEqual(field string, value interface{}) Query

OrWhereEqual adds an OR field = value condition

func (Query) OrWhereILike 

func (q Query) OrWhereILike(field string, pattern string) Query

OrWhereILike adds an OR ILIKE (case-insensitive) condition

func (Query) OrWhereIn 

func (q Query) OrWhereIn(field string, values ...interface{}) Query

OrWhereIn adds an OR field IN (...) condition

func (Query) OrWhereLike 

func (q Query) OrWhereLike(field string, pattern string) Query

OrWhereLike adds an OR LIKE condition

func (Query) OrderBy 

func (q Query) OrderBy(field string, direction string) Query

OrderBy adds sorting

func (Query) RightJoin 

func (q Query) RightJoin(table string, condition string) Query

RightJoin adds a RIGHT JOIN

func (Query) Where 

func (q Query) Where(condition string, args ...interface{}) Query

Where adds a WHERE condition to the query

func (Query) WhereEqual 

func (q Query) WhereEqual(field string, value interface{}) Query

WhereEqual adds an equality condition

func (Query) WhereILike 

func (q Query) WhereILike(field string, pattern string) Query

WhereILike adds an ILIKE (case-insensitive) condition

func (Query) WhereIn 

func (q Query) WhereIn(field string, values ...interface{}) Query

WhereIn adds an IN condition

func (Query) WhereLike 

func (q Query) WhereLike(field string, pattern string) Query

WhereLike adds a LIKE condition

func (Query) WhereNotEqual 

func (q Query) WhereNotEqual(field string, value interface{}) Query

WhereNotEqual adds an inequality condition

func (Query) WhereNotIn 

func (q Query) WhereNotIn(field string, values ...interface{}) Query

WhereNotIn adds a NOT IN condition

func (Query) WhereNotNull 

func (q Query) WhereNotNull(field string) Query

WhereNotNull adds an IS NOT NULL condition

func (Query) WhereNull 

func (q Query) WhereNull(field string) Query

WhereNull adds an IS NULL condition

func (Query) WhereRaw 

func (q Query) WhereRaw(condition string) Query

WhereRaw adds a raw WHERE condition

type QueryFilter 

type QueryFilter interface {
	// Apply applies filters to the query
	Apply(query Query, inputs map[string]interface{}, withDefault bool) Query
	// Default applies default filters
	Default(query Query) Query
}

QueryFilter is the base interface for all filters

Source Files

View all Source files

Directories

Path Synopsis
examples
models command
mysql command
postgres command

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.