DSQLEX

A SQL-like DSL (Domain Specific Language) for evaluating dynamic calculations in Elixir.

DSQLEX allows users to define calculation expressions using familiar SQL syntax, which can be stored, validated, and evaluated at runtime against structured data.

Features

• 🧮 Arithmetic operations: +, -, *, / with Decimal precision
• 🔍 Comparisons: =, !=, <, >, <=, >=
• 🔗 Logical operators: AND, OR with same-operator chaining
• 🔀 Conditional logic: CASE WHEN ... THEN ... ELSE ... END
• 📦 Built-in functions: ROUND(), COALESCE(), UPPER(), LOWER(), ABS(), CONCAT(), LEAST(), GREATEST(), and list aggregation with SUM(), COUNT(), ANY(), ALL()
• ✅ Validation: Parse expressions before storing to catch syntax errors early
• 🎯 Unambiguous syntax: Parentheses required for complex expressions
• 💬 Comments: SQL-style line comments (-- ..., # ...) and block comments (/* ... */)

Installation

Add dsqlex to your list of dependencies in mix.exs:

def deps do
  [
    {:dsqlex, "~> 0.1.0"}
  ]
end

Quick Start

# Define your data context
context = %{
  "price" => Decimal.new("500.00"),
  "quantity" => Decimal.new("4"),
  "category" => "B",
  "rate" => Decimal.new("5.00")
}

# Evaluate a simple expression (SELECT is optional)
{:ok, result} = Dsqlex.eval("price / rate", context)
# => {:ok, Decimal.new("100")}

# Evaluate a conditional expression
{:ok, result} = Dsqlex.eval("""
  CASE 
    WHEN category = 'A' THEN quantity 
    WHEN category != 'A' THEN (price / rate) 
  END
""", context)
# => {:ok, Decimal.new("100")}

API

Dsqlex.eval(expression, context)

Evaluates an expression against a context and returns the result. The SELECT keyword is optional.

{:ok, result} = Dsqlex.eval("x * 2", %{"x" => Decimal.new("50")})
# => {:ok, Decimal.new("100")}

{:error, reason} = Dsqlex.eval("unknown_field", %{})
# => {:error, "Unknown field: unknown_field"}

Dsqlex.parse(expression)

Parses an expression and returns the AST without evaluating. Useful for validating expressions before storing them in a database.

{:ok, ast} = Dsqlex.parse("x / y")
# => {:ok, {:select, {:binary_op, :divide, {:identifier, "x"}, {:identifier, "y"}}}}

{:ok, ast} = Dsqlex.parse("1 + 2 + 3")
# => {:ok, {:select, {:binary_op, :plus, {:binary_op, :plus, {:number, "1"}, {:number, "2"}}, {:number, "3"}}}}


{:error, reason} = Dsqlex.parse("1 + 2 * 3")
# => {:error, "Ambiguous expression: mixing +/- and *// requires parentheses"}

Dsqlex.tokenize(expression)

Tokenizes an expression without parsing. Useful for debugging.

{:ok, tokens} = Dsqlex.tokenize("1 + 2")
# => {:ok, [number: "1", operator: :plus, number: "2"]}

Supported Syntax

Literals

Comments

Comments are stripped at the lexer stage and never reach the parser, so they may appear anywhere whitespace is allowed and may contain any characters (including non-ASCII text) in their bodies.

status_id NOT IN (
  1,  -- pending review
  2,  -- archived – soft-deleted
  3   -- naïve test
)

Arithmetic Operators

Comparison Operators

Logical Operators

Note: You can chain the same operator (a AND b AND c), but mixing AND/OR requires parentheses.

CASE/WHEN

CASE 
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

Functions

List Aggregation

SUM, COUNT, ANY and ALL evaluate their second argument once per element of a list of maps. Bare identifiers read the element; ROOT.<path> reads the context the expression was evaluated against (still the outermost context inside nested aggregations). A nil element result in SUM is an error that names the element index, so return 0 for elements that contribute nothing.

context = %{
  "cutoff_date" => "2024-03-10",
  "items" => [
    %{"amount" => Decimal.new("100"), "shipped_on" => "2024-03-01"},
    %{"amount" => Decimal.new("40"), "shipped_on" => "2024-03-15"}
  ]
}

{:ok, result} = Dsqlex.eval("SUM(items, CASE WHEN shipped_on <= ROOT.cutoff_date THEN amount ELSE 0 END)", context)
# => {:ok, Decimal.new("100")}

By default a dot path through a list (items.amount) sums the element values when they are all numeric and returns the list otherwise. Pass strict_lists: true to Dsqlex.eval/3 to turn that, and any list value used where a scalar is expected, into an error that points at the list functions.

The Parentheses Rule

To eliminate ambiguity and ensure calculation correctness, DSQLEX requires parentheses when mixing operator groups:

# ✅ Valid - single operation
"SELECT a + b"
"SELECT a = 1"

# ✅ Valid - chaining the same operator group
"SELECT a + b + c"        # additive chain, left-associative: (a+b)+c
"SELECT a - b + c"        # additive chain
"SELECT a * b / c"        # multiplicative chain, left-associative: (a*b)/c

# ✅ Valid - parentheses make intent clear
"SELECT (a + b) * c"
"SELECT (a = 1 AND b = 2) OR c = 3"

# ✅ Valid - same logical operator can chain
"SELECT a = 1 AND b = 2 AND c = 3"
"SELECT a = 1 OR b = 2 OR c = 3"

# ❌ Invalid - mixing operator groups requires parentheses
"SELECT a + b * c"        # mixing additive and multiplicative
"SELECT a = 1 AND b = 2 OR c = 3"  # mixing AND/OR

This design choice prioritizes correctness over convenience — mixed-precedence expressions must use parentheses to make the intended order of operations explicit.

Examples

Conditional Selection

expression = """
  CASE 
    WHEN category = 'A' THEN x 
    WHEN category != 'A' THEN (y / z) 
  END
"""

context = %{
  "x" => Decimal.new("100.00"),
  "y" => Decimal.new("500.00"),
  "category" => "B",
  "z" => Decimal.new("5.00")
}

{:ok, result} = Dsqlex.eval(expression, context)
# => {:ok, Decimal.new("100")}

Tiered Calculation

expression = """
  CASE 
    WHEN x > 1000 THEN ROUND(x * 0.02, 2)
    WHEN x > 100 THEN ROUND(x * 0.03, 2)
    ELSE ROUND(x * 0.05, 2)
  END
"""

{:ok, result} = Dsqlex.eval(expression, %{"x" => Decimal.new("500")})
# => {:ok, Decimal.new("15.00")}

Null Handling

expression = "ROUND(COALESCE(x, 0) + y, 2)"

context = %{
  "x" => nil,
  "y" => Decimal.new("99.99")
}

{:ok, result} = Dsqlex.eval(expression, context)
# => {:ok, Decimal.new("99.99")}

Conditional Text

expression = """
  CASE 
    WHEN status = 'active' THEN UPPER(label)
    ELSE 'INACTIVE'
  END
"""

{:ok, result} = Dsqlex.eval(expression, %{"status" => "active", "label" => "hello world"})
# => {:ok, "HELLO WORLD"}

Architecture

DSQLEX uses a classic three-stage pipeline:

┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│   String    │ ──► │   Tokens    │ ──► │     AST     │ ──► Result
│             │     │             │     │             │
│  "SELECT    │     │ [{:keyword, │     │ {:select,   │
│   a + b"    │     │   :select}, │     │  {:binary_  │
│             │     │  ...]       │     │   op, ...}} │
└─────────────┘     └─────────────┘     └─────────────┘
     Lexer              Parser            Evaluator

• Lexer (Dsqlex.Lexer): Converts string to tokens
• Parser (Dsqlex.Parser): Converts tokens to AST
• Evaluator (Dsqlex.Evaluator): Walks AST with context to produce result

Testing

mix test

The test suite includes 162 tests covering:

• Lexer token generation
• Parser AST construction
• Evaluator computations
• End-to-end integration tests
• Error handling at every stage

License

MIT