737 lines
16 KiB
Markdown
737 lines
16 KiB
Markdown
# 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
|
|
|
|
```bash
|
|
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
|
|
|
|
```go
|
|
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
|
|
|
|
```go
|
|
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
|
|
|
|
```go
|
|
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
|
|
|
|
```go
|
|
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
|
|
|
|
```go
|
|
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
|
|
|
|
```go
|
|
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
|
|
|
|
```go
|
|
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
|
|
|
|
```go
|
|
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
|
|
|
|
```go
|
|
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
|
|
|
|
```go
|
|
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
|
|
|
|
```go
|
|
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
|
|
|
|
```go
|
|
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
|
|
|
|
1. **Selalu gunakan security checks** saat production:
|
|
```go
|
|
qb.SetSecurityOptions(true, 1000)
|
|
qb.SetAllowedColumns(allowedColumns)
|
|
qb.SetAllowedTables(allowedTables)
|
|
```
|
|
|
|
2. **Gunakan context dengan timeout** untuk query:
|
|
```go
|
|
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
|
|
defer cancel()
|
|
```
|
|
|
|
3. **Validasi input user** sebelum membuat DynamicFilter
|
|
|
|
4. **Gunakan prepared statements** (sudah otomatis di QueryBuilder)
|
|
|
|
5. **Log query** untuk debugging:
|
|
```go
|
|
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 |