Skip to content
/ mql Public

Model Query Language (mql) is a query language for your database models.

License

Notifications You must be signed in to change notification settings

hashicorp/mql

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

54 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

MQL

Go Reference Go Report Card Go Coverage

The mql (Model Query Language) Go package provides a language that end users can use to query your database models, without them having to learn SQL or exposing your application to SQL injection.

Examples

w, err := mql.Parse(`name="alice" or name="bob"`,User{})
if err != nil {
  return nil, err
}
err = db.Where(w.Condition, w.Args...).Find(&users).Error
w, err := mql.Parse(`name="alice" or name="bob"`,User{}, mql.WithPgPlaceholders())
if err != nil {
  return nil, err
}
q := fmt.Sprintf("select * from users where %s", w.Condition)
rows, err := db.Query(q, w.Args...)
w, err := mql.Parse(`name="alice" or name="bob")`,User{})
if err != nil {
  return nil, err
}
err := rw.SearchWhere(ctx, &users, w.Condition, w.Args)

Some bits about usage

First, you define a model you wish to query as a Go struct and then provide a mql query. The package then uses the query along with a model to generate a parameterized SQL where clause.

Fields in your model can be compared with the following operators: =, !=, >=, <=, <, >, % .

Strings must be quoted. Double quotes ", single quotes ' or backticks ` can be used as delimiters. Users can choose whichever supported delimiter makes it easier to quote their string.

Comparison operators can have optional leading/trailing whitespace.

The % operator allows you to do partial string matching using LIKE "%value%". This matching is case insensitive.

The = equality operator is case insensitive when used with string fields.

Comparisons can be combined using: and, or.

More complex queries can be created using parentheses.

See GRAMMAR.md for a more complete documentation of mql's grammar.

Example query:

name="alice" and age > 11 and (region % 'Boston' or region="south shore")

Date/Time fields

If your model contains a time.Time field, then we'll append ::date to the column name when generating a where clause and the comparison value must be in an ISO-8601 format.

Note: It's possible to compare date-time fields down to the millisecond using ::date and a literal in ISO-8601 format.

Currently, this is the only supported way to compare dates, if you need something different then you'll need to provide your own custom validator/converter via WithConverter(...) when calling mql.Parse(...).

We provide default validation+conversion of fields in a model when parsing and generating a WhereClause. You can provide optional validation+conversion functions for fields in your model via WithConverter(...).

Example date comparison down to the HH::MM using an ISO-8601 format:

name="alice" and created_at>"2023-12-01 14:01"

Note: Expressions with the same level of precedence are evaluated right to left. Example: name="alice" and age > 11 and region = "Boston" is evaluated as: name="alice" and (age > 11 and region = "Boston")

Mapping column names

You can also provide an optional map from query column identifiers to model field names via WithColumnMap(...) if needed.

Example WithColumnMap(...) usage:

type User {
    FullName string
}

// map the column alice to field name FullName
columnMap := map[string]string{
    "name": "FullName",
}

w, err := mql.Parse(
    `name="alice"`,
    User{}, 
    mql.WithColumnMap(columnMap))

if err != nil {
    return nil, err
}

Ignoring fields

If your model (Go struct) has fields you don't want users searching then you can optionally provide a list of columns to be ignored via WithIgnoreFields(...)

Example WithIgnoreFields(...) usage:

type User {
    Name string
    CreatedAt time.Time
    UpdatedAt time.Time
}

// you want to keep users from using queries that include the user fields
// of: created_at updated_at
w, err := mql.Parse(
    `name="alice"`,
    User{}, 
    mql.WithIgnoreFields("CreatedAt", "UpdatedAt"))

if err != nil {
    return nil, err
}

Custom converters/validators

Sometimes the default out-of-the-box bits doesn't fit your needs. If you need to override how expressions (column name, operator and value) is converted and validated during the generation of a WhereClause, then you can optionally provide your own validator/convertor via WithConverter(...)

Example WithConverter(...) usage:

// define a converter for mySQL dates
mySQLDateConverter := func(columnName string, comparisonOp mql.ComparisonOp, value *string) (*mql.WhereClause, error) {
  // you should add some validation of function parameters here.
  return &mql.WhereClause{
    Condition: fmt.Sprintf("%s%sSTR_TO_DATE(?)", columnName, comparisonOp),
    Args:      []any{*value},
  }, nil
}

w, err := mql.Parse(
    `name="alice" and created_at > "2023-06-18"`,
    User{}, 
    mql.WithConverter("CreatedAt", mySqlDateConverter))

if err != nil {
    return nil, err
}

Grammar

See: GRAMMAR.md

Security

Please note: We take security and our users' trust very seriously. If you believe you have found a security issue, please responsibly disclose by contacting us at security@hashicorp.com.

Contributing

Thank you for your interest in contributing! Please refer to CONTRIBUTING.md for guidance.

About

Model Query Language (mql) is a query language for your database models.

Resources

License

Code of conduct

Security policy

Stars

Watchers

Forks

Packages

No packages published