expr2sql

Translator of Expr-lang expressions to SQL allowing the execution of expressions in the database for efficient dynamic data filtering. Current SQL support includes PostgreSQL dialect.

The project consists of:

• Golang library
• Autocomplete filter builder component (Typescript)

Features

• 🗨️ expressive
  • most Expr-lang language operators are translated to SQL equivalents
• 🦺 type-safe
  • columns which can appear in the expression can be configured, with respective types
  • types of expression constants are resolved
  • each operation is validated for type compatibility
• 🔒 secure
  • input guarded by the Expr-lang parser (i.e. no injections)
    • only legitimate expressions within its grammar are allowed
  • output validated by the translator
    • resulting SQL must be an expression with a boolean result for the WHERE clause
• 🌳 JSON support
  • allows for simple expressions on JSON columns
  • nesting supported

Usage

Supported literal types

• boolean
• integer
• float
• string
  • if in RFC3339 format, then the type is a timestamp
• nil

Supported column types

• boolean
• integer
• float
• string
• timestamp
• JSON

Supported operators:

Type	Operators
Arithmetic	+, -, *, /, % (modulus), ^ or ** (exponent)
Comparison	==, !=, <, >, <=, >=
Logical	not or !, and or &&, or or ||
Membership	[], .
String	contains, startsWith, endsWith
Regex	matches

Getting started

Get latest library release:

go get github.com/happening-oss/expr2sql

Run example:

package main
import (
"fmt""github.com/happening-oss/expr2sql/pkg/filter"
)
funcmain() {
identifiers:= []filter.Identifier{
{ExprName: "intField", Type: filter.IdentifierTypeInt},
{ExprName: "intField2", Type: filter.IdentifierTypeInt},
{ExprName: "stringField", Type: filter.IdentifierTypeString},
{ExprName: "floatField", Type: filter.IdentifierTypeFloat},
{ExprName: "timestampField", Type: filter.IdentifierTypeTimestamp},
{ExprName: "boolField1", Type: filter.IdentifierTypeBool},
{ExprName: "boolField2", Type: filter.IdentifierTypeBool, DBName: "bool_field2"},
{ExprName: "jsonField", Type: filter.IdentifierTypeJSON, JSONSpec: filter.JSONTree{
"stringProp": filter.JSONLeaf(filter.IdentifierTypeString),
"boolProp": filter.JSONLeaf(filter.IdentifierTypeBool),
}},
}
translator:=filter.NewTranslator(identifiers, filter.TranslatorDialectPostgres)
for_, expr:=range []string{
`intField > 42 and stringField == 'stringValue'`,
`floatField > 2.71828 or timestampField > '2024-12-01T00:00:00Z'`,
`intField > intField2 * 2 and boolField1 or boolField2`,
`jsonField.stringProp == "stringValue" or jsonField.boolProp`,
} {
translated, err:=translator.Translate(expr)
iferr!=nil {
panic(err)
}
fmt.Println(translated)
}
}

Output:

Expr-lang	PostgreSQL
intField > 42 and stringField == 'stringValue'	((intField > 42) and (stringField = 'stringValue'))
floatField > 2.71828 or timestampField > '2024-12-01T00:00:00Z'	((floatField > 2.71828) or (timestampField > '2024-12-01T00:00:00Z'))
intField > intField2 * 2 and boolField1 or boolField2	(((intField > (intField2 * 2)) and boolField1) or bool_field2)
jsonField.stringProp == "stringValue" or jsonField.boolProp	((jsonField ->> 'stringProp' = 'stringValue') or cast(jsonField ->> 'boolProp' as boolean))

Live demo

Autocomplete filter builder - expr2sql-editor

This library is accompanied by an useful filter builder component you can use to build expressions in the browser.