effendy/app-test-golang

Template

Fork 0

Files

T

effendy.stim 2bd26d91fb first commit

2025-11-26 07:24:49 +00:00

16 KiB

Raw Permalink Blame History

Dynamic Query Builder

Pustaka Go yang powerful untuk membangun query SQL dan MongoDB secara dinamis dengan dukungan filtering, sorting, pagination, join, CTE, window functions, dan operasi JSON/Array.

Fitur Utama

Multi-Database: Mendukung PostgreSQL, MySQL, SQLite, SQL Server, dan MongoDB
Dynamic Filtering: Berbagai operator filter (_eq, _neq, _like, _in, _between, dll.)
JSON Operations: Dukungan penuh untuk query dan update data JSON
Array Operations: Query array dengan berbagai operator
Security: Built-in proteksi SQL injection dan kontrol akses kolom/tabel
Window Functions: Dukungan ROW_NUMBER, RANK, dll.
CTE & Unions: Support untuk Common Table Expressions dan UNION
Query Parsing: Parse URL query parameters menjadi DynamicQuery

Instalasi

go get github.com/Masterminds/squirrel
go get github.com/jmoiron/sqlx
go get go.mongodb.org/mongo-driver/mongo

Contoh Penggunaan

1. Operator Perbandingan Dasar

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
) 

func main() {
	// Inisialisasi QueryBuilder
	qb := NewQueryBuilder(DBTypePostgreSQL)
	
	// Koneksi ke database
	db, err := sqlx.Connect("postgres", "user=postgres dbname=mydb sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// Equal (_eq)
	query := DynamicQuery{
		From: "users",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "email",
				Operator: OpEqual,
				Value:    "john@example.com",
			}},
			LogicOp: "AND",
		}},
	}
	
	var users []map[string]interface{}
	err = qb.ExecuteQuery(context.Background(), db, query, &users)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("Found %d users\n", len(users))

	// Greater Than (_gt)
	query = DynamicQuery{
		From: "products",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "price",
				Operator: OpGreaterThan,
				Value:    100000,
			}},
			LogicOp: "AND",
		}},
		Limit: 10,
	}
	
	var products []map[string]interface{}
	err = qb.ExecuteQuery(context.Background(), db, query, &products)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("Found %d products with price > 100000\n", len(products))
}

2. Operator Like dan String Matching

func stringMatchingExample(qb *QueryBuilder, db *sqlx.DB) {
	// Case Insensitive Like (_ilike)
	query := DynamicQuery{
		From: "products",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "name",
				Operator: OpILike,
				Value:    "%laptop%",
			}},
			LogicOp: "AND",
		}},
		Limit: 20,
	}
	
	var products []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &products)
	
	// Contains (_contains)
	query = DynamicQuery{
		From: "articles",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "content",
				Operator: OpContains,
				Value:    "golang",
			}},
			LogicOp: "AND",
		}},
	}
	
	var articles []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &articles)
	
	// Starts With (_starts_with)
	query = DynamicQuery{
		From: "users",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "username",
				Operator: OpStartsWith,
				Value:    "admin",
			}},
			LogicOp: "AND",
		}},
	}
	
	var admins []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &admins)
}

3. Operator IN, NOT IN, dan Between

func inBetweenExample(qb *QueryBuilder, db *sqlx.DB) {
	// In (_in)
	query := DynamicQuery{
		From: "products",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "category",
				Operator: OpIn,
				Value:    []string{"electronics", "computers", "gadgets"},
			}},
			LogicOp: "AND",
		}},
		Limit: 50,
	}
	
	var products []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &products)
	
	// Between (_between)
	query = DynamicQuery{
		From: "orders",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "order_date",
				Operator: OpBetween,
				Value:    []interface{}{time.Now().AddDate(0, -1, 0), time.Now()},
			}},
			LogicOp: "AND",
		}},
		Sort: []SortField{{Column: "order_date", Order: "DESC"}},
		Limit: 100,
	}
	
	var orders []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &orders)
}

4. Operator Null dan Exists

func nullExistsExample(qb *QueryBuilder, db *sqlx.DB) {
	// Is Null (_null)
	query := DynamicQuery{
		From: "users",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "deleted_at",
				Operator: OpNull,
				Value:    nil,
			}},
			LogicOp: "AND",
		}},
	}
	
	var activeUsers []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &activeUsers)
	
	// Is Not Null (_nnull)
	query = DynamicQuery{
		From: "products",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "description",
				Operator: OpNotNull,
				Value:    nil,
			}},
			LogicOp: "AND",
		}},
	}
	
	var productsWithDesc []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &productsWithDesc)
}

5. Operator JSON

func jsonExample(qb *QueryBuilder, db *sqlx.DB) {
	// JSON Contains (_json_contains)
	query := DynamicQuery{
		From: "products",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "attributes",
				Operator: OpJsonContains,
				Value:    `{"color": "red"}`,
			}},
			LogicOp: "AND",
		}},
	}
	
	var redProducts []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &redProducts)
	
	// JSON Exists (_json_exists)
	query = DynamicQuery{
		From: "users",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "profile",
				Operator: OpJsonExists,
				Options: map[string]interface{}{
					"path": "$.social_media",
				},
			}},
			LogicOp: "AND",
		}},
	}
	
	var usersWithSocialMedia []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &usersWithSocialMedia)
	
	// JSON Equal (_json_eq)
	query = DynamicQuery{
		From: "settings",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "preferences",
				Operator: OpJsonEqual,
				Value:    "dark_mode",
				Options: map[string]interface{}{
					"path": "$.theme",
				},
			}},
			LogicOp: "AND",
		}},
	}
	
	var darkModeUsers []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &darkModeUsers)
}

6. Operator Array

func arrayExample(qb *QueryBuilder, db *sqlx.DB) {
	// Array Contains (_array_contains)
	query := DynamicQuery{
		From: "products",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "tags",
				Operator: OpArrayContains,
				Value:    "premium",
			}},
			LogicOp: "AND",
		}},
	}
	
	var premiumProducts []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &premiumProducts)
	
	// Array Length (_array_length)
	query = DynamicQuery{
		From: "galleries",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "images",
				Operator: OpArrayLength,
				Options: map[string]interface{}{
					"length": 5,
				},
			}},
			LogicOp: "AND",
		}},
	}
	
	var galleriesWith5Images []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &galleriesWith5Images)
}

7. Complex Query dengan Multiple Filters

func complexQueryExample(qb *QueryBuilder, db *sqlx.DB) {
	// Query kompleks dengan multiple filter groups
	query := DynamicQuery{
		From: "products",
		Fields: []SelectField{
			{Expression: "id", Alias: "product_id"},
			{Expression: "name", Alias: "product_name"},
			{Expression: "price", Alias: "price"},
			{Expression: "category", Alias: "category"},
		},
		Filters: []FilterGroup{
			// Filter Group 1: Active products with price range
			{
				Filters: []DynamicFilter{
					{
						Column:   "status",
						Operator: OpEqual,
						Value:    "active",
					},
					{
						Column:   "price",
						Operator: OpBetween,
						Value:    []interface{}{100000, 500000},
					},
				},
				LogicOp: "AND",
			},
			// Filter Group 2: Category OR name matching
			{
				Filters: []DynamicFilter{
					{
						Column:   "category",
						Operator: OpIn,
						Value:    []string{"electronics", "computers"},
					},
					{
						Column:   "name",
						Operator: OpILike,
						Value:    "%laptop%",
					},
				},
				LogicOp: "OR",
			},
		},
		Sort: []SortField{
			{Column: "price", Order: "ASC"},
			{Column: "name", Order: "ASC"},
		},
		Limit:  20,
		Offset: 0,
	}
	
	var results []map[string]interface{}
	err := qb.ExecuteQuery(context.Background(), db, query, &results)
	if err != nil {
		log.Fatal(err)
	}
	
	fmt.Printf("Found %d products\n", len(results))
	for _, result := range results {
		fmt.Printf("Product: %s, Price: %v\n", result["product_name"], result["price"])
	}
}

8. Query dengan JOIN

func joinExample(qb *QueryBuilder, db *sqlx.DB) {
	query := DynamicQuery{
		From:    "orders",
		Aliases: "o",
		Fields: []SelectField{
			{Expression: "o.id", Alias: "order_id"},
			{Expression: "o.order_date", Alias: "order_date"},
			{Expression: "c.name", Alias: "customer_name"},
			{Expression: "p.name", Alias: "product_name"},
		},
		Joins: []Join{
			{
				Type:  "INNER",
				Table: "customers",
				Alias: "c",
				OnConditions: FilterGroup{
					Filters: []DynamicFilter{
						{
							Column:   "o.customer_id",
							Operator: OpEqual,
							Value:    "c.id",
						},
					},
					LogicOp: "AND",
				},
			},
			{
				Type:  "LEFT",
				Table: "products",
				Alias: "p",
				OnConditions: FilterGroup{
					Filters: []DynamicFilter{
						{
							Column:   "o.product_id",
							Operator: OpEqual,
							Value:    "p.id",
						},
					},
					LogicOp: "AND",
				},
			},
		},
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{
				{
					Column:   "o.order_date",
					Operator: OpGreaterThanEqual,
					Value:    time.Now().AddDate(0, -1, 0),
				},
			},
			LogicOp: "AND",
		}},
		Sort: []SortField{
			{Column: "o.order_date", Order: "DESC"},
		},
		Limit: 50,
	}
	
	var orders []map[string]interface{}
	err := qb.ExecuteQuery(context.Background(), db, query, &orders)
	if err != nil {
		log.Fatal(err)
	}
	
	fmt.Printf("Found %d orders\n", len(orders))
}

9. Query dengan Window Functions

func windowFunctionExample(qb *QueryBuilder, db *sqlx.DB) {
	query := DynamicQuery{
		From: "sales",
		Fields: []SelectField{
			{Expression: "id", Alias: "sale_id"},
			{Expression: "product_name", Alias: "product"},
			{Expression: "amount", Alias: "sale_amount"},
		},
		WindowFunctions: []WindowFunction{
			{
				Function: "ROW_NUMBER",
				Over:     "product_name",
				OrderBy:  "amount DESC",
				Alias:    "rank_in_category",
			},
			{
				Function: "SUM",
				Over:     "product_name",
				OrderBy:  "sale_date",
				Frame:    "ROWS UNBOUNDED PRECEDING",
				Alias:    "running_total",
			},
		},
		Sort: []SortField{
			{Column: "product_name", Order: "ASC"},
			{Column: "amount", Order: "DESC"},
		},
	}
	
	var sales []map[string]interface{}
	err := qb.ExecuteQuery(context.Background(), db, query, &sales)
	if err != nil {
		log.Fatal(err)
	}
	
	fmt.Printf("Found %d sales records\n", len(sales))
}

10. MongoDB Example

import (
	"context"
	"fmt"
	"log"
	"time"

	"go.mongodb.org/mongo-driver/bson"
	"go.mongodb.org/mongo-driver/mongo"
	"go.mongodb.org/mongo-driver/mongo/options"
)

func mongodbExample() {
	// Inisialisasi MongoQueryBuilder
	mqb := NewMongoQueryBuilder()
	
	// Koneksi ke MongoDB
	client, err := mongo.Connect(context.Background(), options.Client().ApplyURI("mongodb://localhost:27017"))
	if err != nil {
		log.Fatal(err)
	}
	defer client.Disconnect(context.Background())
	
	collection := client.Database("mydb").Collection("products")
	
	// Query dengan filter
	query := DynamicQuery{
		From: "products",
		Filters: []FilterGroup{
			{
				Filters: []DynamicFilter{
					{
						Column:   "status",
						Operator: OpEqual,
						Value:    "active",
					},
					{
						Column:   "price",
						Operator: OpBetween,
						Value:    []interface{}{100000, 500000},
					},
				},
				LogicOp: "AND",
			},
			{
				Filters: []DynamicFilter{
					{
						Column:   "category",
						Operator: OpIn,
						Value:    []string{"electronics", "computers"},
					},
					{
						Column:   "name",
						Operator: OpILike,
						Value:    "laptop",
					},
				},
				LogicOp: "OR",
			},
		},
		Sort: []SortField{
			{Column: "price", Order: "ASC"},
			{Column: "name", Order: "ASC"},
		},
		Limit:  20,
		Offset: 0,
	}
	
	var results []map[string]interface{}
	err = mqb.ExecuteFind(context.Background(), collection, query, &results)
	if err != nil {
		log.Fatal(err)
	}
	
	fmt.Printf("Found %d products\n", len(results))
	for _, result := range results {
		fmt.Printf("Product: %s, Price: %v\n", result["name"], result["price"])
	}
}

11. URL Query Parsing

import (
	"net/url"
)

func urlParsingExample() {
	// Parse URL query parameters
	values, _ := url.ParseQuery("filter[status][_eq]=active&filter[price][_gte]=100000&sort=-created_at&limit=20")
	
	parser := NewQueryParser()
	query, err := parser.ParseQuery(values, "products")
	if err != nil {
		log.Fatal(err)
	}
	
	// Query akan berisi:
	// - Filters: status = 'active' AND price >= 100000
	// - Sort: created_at DESC
	// - Limit: 20
	
	qb := NewQueryBuilder(DBTypePostgreSQL)
	db, _ := sqlx.Connect("postgres", "connection-string")
	
	var products []map[string]interface{}
	err = qb.ExecuteQuery(context.Background(), db, query, &products)
	if err != nil {
		log.Fatal(err)
	}
}

Konfigurasi Keamanan

func securityExample() {
	qb := NewQueryBuilder(DBTypePostgreSQL)
	
	// Aktifkan security checks
	qb.SetSecurityOptions(true, 1000) // max 1000 rows
	
	// Tentukan kolom yang diizinkan
	qb.SetAllowedColumns([]string{
		"id", "name", "email", "status", "created_at",
		"price", "category", "description",
	})
	
	// Tentukan tabel yang diizinkan
	qb.SetAllowedTables([]string{
		"users", "products", "orders", "categories",
	})
	
	// Query dengan kolom/tabel tidak diizinkan akan error
	query := DynamicQuery{
		From: "users", // OK
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "password", // ERROR: kolom tidak diizinkan
				Operator: OpEqual,
				Value:    "secret",
			}},
			LogicOp: "AND",
		}},
	}
	
	// Akan mengembalikan error
	_, _, err := qb.BuildQuery(query)
	fmt.Println(err) // "disallowed column: password"
}

Referensi Operator

Operator Perbandingan

_eq - Equal
_neq - Not Equal
_gt - Greater Than
_gte - Greater Than Equal
_lt - Less Than
_lte - Less Than Equal

Operator String

_like - Like (case-sensitive)
_ilike - Like (case-insensitive)
_nlike - Not Like (case-sensitive)
_nilike - Not Like (case-insensitive)
_contains - Contains substring
_ncontains - Not Contains substring
_starts_with - Starts with
_ends_with - Ends with

Operator Set

_in - In list
_nin - Not In list
_between - Between two values
_nbetween - Not Between two values

Operator Null

_null - Is Null
_nnull - Is Not Null

Operator JSON

_json_contains - JSON contains value
_json_ncontains - JSON not contains value
_json_exists - JSON path exists
_json_nexists - JSON path not exists
_json_eq - JSON path equals value
_json_neq - JSON path not equals value

Operator Array

_array_contains - Array contains value
_array_ncontains - Array not contains value
_array_length - Array has specific length

Best Practices

Selalu gunakan security checks saat production:

qb.SetSecurityOptions(true, 1000)
qb.SetAllowedColumns(allowedColumns)
qb.SetAllowedTables(allowedTables)

Gunakan context dengan timeout untuk query:

ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()

Validasi input user sebelum membuat DynamicFilter
Gunakan prepared statements (sudah otomatis di QueryBuilder)
Log query untuk debugging:
```
qb.SetQueryLogging(true)
```

Kontribusi

Pull requests are welcome! For major changes, please open an issue first to discuss what you would like to change.

License

MIT License

16 KiB Raw Permalink Blame History