effendy/app-test-golang

Template

Fork 0

Files

History

effendy 2bd26d91fb first commit

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

builder.go

first commit

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

README.md

first commit

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

README.md

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