Ildar Karymov

Posted on Mar 21

DumbQL: A Simplified Query Language for Go Applications

#showdev #go #programming #tutorial

When building Go applications that interact with databases, developers often face challenges with query construction. Traditional approaches involve either writing raw SQL strings or using complex query builders. DumbQL offers an alternative approach by providing a simplified query language that can be translated to SQL.

The Challenge of Dynamic Queries

Working with user-provided filtering in Go applications presents several challenges:

Dynamically building SQL queries based on user input
Preventing SQL injection while handling user filters
Creating an approachable query interface for various users
Validating input against defined schemas
Maintaining consistency between database queries and in-memory filtering

How DumbQL Works

DumbQL implements a straightforward query syntax that's converted to SQL or used for in-memory filtering:

status:pending and period_months < 4 and (title:"hello world" or name:"John Doe")

This can be more accessible than equivalent SQL, particularly for queries that need to be constructed or modified by users.

Core Functionality

Query Syntax

DumbQL supports various expressions:

Field expressions: age >= 18, name:"John"
Boolean expressions: verified and premium
One-of expressions: status:[active, pending]
Boolean field shorthand: is_active (equivalent to is_active:true)

Schema Validation

DumbQL includes built-in schema validation:

schm := schema.Schema{
    "status": schema.All(
        schema.Is[string](),
        schema.EqualsOneOf("pending", "approved", "rejected"),
    ),
    "period_months": schema.Max(int64(3)),
    "title":         schema.LenInRange(1, 100),
}

const q = `status:pending and period_months:4 and (title:"hello world" or name:"John Doe")`
expr, err := dumbql.Parse(q)
validated, err := expr.Validate(schm)

The validation process identifies invalid fields and provides error details.

SQL Integration

DumbQL works with squirrel or directly with SQL drivers:

const q = `status:pending and period_months < 4 and (title:"hello world" or name:"John Doe")`
expr, _ := dumbql.Parse(q)

sql, args, _ := sq.Select("*").
    From("users").
    Where(expr).
    ToSql()

// Generates: SELECT * FROM users WHERE ((status = ? AND period_months < ?) AND (title = ? OR name = ?))
// With args: [pending 4 hello world John Doe]

Struct Matching

Beyond SQL generation, DumbQL can filter Go structs in memory using the same query language:

q := `(age >= 30 and score > 4.0) or (location:"Los Angeles" and role:"user")`
ast, _ := query.Parse("test", []byte(q))
expr := ast.(query.Expr)

matcher := &match.StructMatcher{}

filtered := make([]User, 0, len(users))
for _, user := range users {
    if expr.Match(&user, matcher) {
        filtered = append(filtered, user)
    }
}

Implementation Contexts

DumbQL can be useful in several scenarios:

Admin interfaces with filtering capabilities
API endpoints with query parameters for filtering
Condition-based alerts or notifications
Report generation with user-defined filters
Search functionality with complex conditions

Usage Basics

To use DumbQL in your project:

go get go.tomakado.io/dumbql

Basic parsing example:

import "go.tomakado.io/dumbql"

func main() {
    const query = `profile.age >= 18 and profile.city = Barcelona`
    ast, err := dumbql.Parse(query)
    if err != nil {
        panic(err)
    }

    // Use with SQL builders, validate, or match against structs
}

Performance Considerations

For struct matching, DumbQL provides two options:

Reflection-based matching (works immediately but has runtime overhead)
Code generation via the dumbqlgen tool (eliminates reflection overhead)

The choice depends on your application's performance requirements.

Conclusion

DumbQL provides a lightweight query language for Go applications that can simplify filtering logic. It bridges the gap between user-friendly query syntax and database operations while offering features like schema validation and struct matching.

If you're working on a Go application that requires filtering capabilities, DumbQL might be worth exploring. Check out the GitHub repository for more details and examples.

Have you implemented similar query abstractions in your projects? What approaches have you found most effective?

DEV Community