cel2sql

cel2sql

Convert CEL (Common Expression Language) expressions to PostgreSQL SQL

cel2sql makes it easy to build dynamic SQL queries using CEL expressions. Write type-safe, expressive filters in CEL and automatically convert them to PostgreSQL-compatible SQL.

Quick Start

Installation

go get github.com/spandigital/cel2sql/v2

Basic Example

package main

import (
    "fmt"
    "github.com/google/cel-go/cel"
    "github.com/spandigital/cel2sql/v2"
    "github.com/spandigital/cel2sql/v2/pg"
)

func main() {
    // 1. Define your database table schema
    userSchema := pg.Schema{
        {Name: "name", Type: "text"},
        {Name: "age", Type: "integer"},
        {Name: "active", Type: "boolean"},
    }

    // 2. Create CEL environment
    env, _ := cel.NewEnv(
        cel.CustomTypeProvider(pg.NewTypeProvider(map[string]pg.Schema{
            "User": userSchema,
        })),
        cel.Variable("user", cel.ObjectType("User")),
    )

    // 3. Write your filter expression in CEL
    ast, _ := env.Compile(`user.age >= 18 && user.active`)

    // 4. Convert to SQL
    sqlWhere, _ := cel2sql.Convert(ast)

    fmt.Println(sqlWhere)
    // Output: user.age >= 18 AND user.active IS TRUE

    // 5. Use in your query
    query := "SELECT * FROM users WHERE " + sqlWhere
}

Why cel2sql?

✅ Type-Safe: Catch errors at compile time, not runtime ✅ PostgreSQL 17: Fully compatible with the latest PostgreSQL ✅ Rich Features: JSON/JSONB, arrays, regex, timestamps, and more ✅ Well-Tested: 100+ tests including integration tests with real PostgreSQL ✅ Easy to Use: Simple API, comprehensive documentation ✅ Secure by Default: Built-in protections against SQL injection and ReDoS attacks

Security Features

cel2sql includes comprehensive security protections:

• 🛡️ Field Name Validation - Prevents SQL injection via field names
• 🔒 JSON Field Escaping - Automatic quote escaping in JSON paths
• 🚫 ReDoS Protection - Validates regex patterns to prevent catastrophic backtracking
• 🔄 Recursion Depth Limits - Prevents stack overflow from deeply nested expressions (default: 100)
• 📏 SQL Output Length Limits - Prevents memory exhaustion from extremely large SQL queries (default: 50,000 chars)
• ⏱️ Context Timeouts - Optional timeout protection for complex expressions

All security features are enabled by default with zero configuration required.

Advanced Options

cel2sql supports optional advanced features via functional options:

import (
    "context"
    "log/slog"
    "github.com/spandigital/cel2sql/v2"
)

// Basic conversion
sql, err := cel2sql.Convert(ast)

// With schemas for JSON/JSONB support
sql, err := cel2sql.Convert(ast,
    cel2sql.WithSchemas(schemas))

// With context for timeouts
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
sql, err := cel2sql.Convert(ast,
    cel2sql.WithContext(ctx),
    cel2sql.WithSchemas(schemas))

// With logging for observability
logger := slog.New(slog.NewJSONHandler(os.Stdout, nil))
sql, err := cel2sql.Convert(ast,
    cel2sql.WithContext(ctx),
    cel2sql.WithSchemas(schemas),
    cel2sql.WithLogger(logger))

Available Options:

• WithSchemas(map[string]pg.Schema) - Provide table schemas for JSON detection
• WithContext(context.Context) - Enable cancellation and timeouts
• WithLogger(*slog.Logger) - Enable structured logging
• WithMaxDepth(int) - Set custom recursion depth limit (default: 100)

Parameterized Queries

cel2sql supports parameterized queries (prepared statements) for improved performance, security, and monitoring.

Benefits

🚀 Performance - PostgreSQL caches query plans for parameterized queries, enabling plan reuse across executions 🔒 Security - Parameters are passed separately from SQL, providing defense-in-depth SQL injection protection 📊 Monitoring - Same query pattern appears in logs/metrics, making analysis easier

Usage

// Convert to parameterized SQL
result, err := cel2sql.ConvertParameterized(ast)
if err != nil {
    log.Fatal(err)
}

fmt.Println(result.SQL)         // "user.age > $1 AND user.name = $2"
fmt.Println(result.Parameters)  // [18 "John"]

// Execute with database/sql
rows, err := db.Query(
    "SELECT * FROM users WHERE " + result.SQL,
    result.Parameters...,
)

What Gets Parameterized?

Parameterized (values become placeholders):

• ✅ String literals: 'John' → $1
• ✅ Numeric literals: 42, 3.14 → $1, $2
• ✅ Byte literals: b"data" → $1

Kept Inline (for query plan optimization):

• ❌ TRUE, FALSE - Boolean constants
• ❌ NULL - Null values

PostgreSQL's query planner optimizes better when it knows boolean and null values at plan time.

Example Comparison

celExpr := `user.age > 18 && user.active == true && user.name == "John"`
ast, _ := env.Compile(celExpr)

// Non-parameterized (inline values)
sql, _ := cel2sql.Convert(ast)
// SQL: user.age > 18 AND user.active IS TRUE AND user.name = 'John'

// Parameterized (placeholders + parameters)
result, _ := cel2sql.ConvertParameterized(ast)
// SQL: user.age > $1 AND user.active IS TRUE AND user.name = $2
// Parameters: [18 "John"]
// Note: TRUE is kept inline for query plan efficiency

Prepared Statements

For maximum performance with repeated queries, use prepared statements:

result, _ := cel2sql.ConvertParameterized(ast)

// Prepare once
stmt, err := db.Prepare("SELECT * FROM users WHERE " + result.SQL)
defer stmt.Close()

// Execute multiple times with different parameters
rows1, _ := stmt.Query(25)  // age > 25
rows2, _ := stmt.Query(30)  // age > 30
rows3, _ := stmt.Query(35)  // age > 35 (reuses cached plan!)

See the parameterized example for a complete working demo with PostgreSQL integration.

Common Use Cases

1. User Filters

// CEL: Simple comparison
user.age > 21 && user.country == "USA"
// SQL: user.age > 21 AND user.country = 'USA'

2. Text Search

// CEL: String operations
user.email.startsWith("admin") || user.name.contains("John")
// SQL: user.email LIKE 'admin%' OR POSITION('John' IN user.name) > 0

3. Date Filters

// CEL: Date comparisons
user.created_at > timestamp("2024-01-01T00:00:00Z")
// SQL: user.created_at > CAST('2024-01-01T00:00:00Z' AS TIMESTAMP WITH TIME ZONE)

4. JSON/JSONB Fields

// CEL: JSON field access
user.preferences.theme == "dark"
// SQL: user.preferences->>'theme' = 'dark'

5. Array Operations

// CEL: Check if all items match
user.scores.all(s, s >= 60)
// SQL: NOT EXISTS (SELECT 1 FROM UNNEST(user.scores) AS s WHERE NOT (s >= 60))

Documentation

• 📖 Getting Started Guide - Step-by-step tutorial
• 🔧 JSON/JSONB Support - Working with JSON data
• 🎯 Array Comprehensions - Advanced array operations
• 🔍 Regex Matching - Pattern matching with regex
• 🛡️ Security Guide - Security features and best practices
• 📚 Operators Reference - Complete operator list
• 💡 Examples - More code examples

Supported Features

Type Mapping

Dynamic Schema Loading

Load table schemas directly from your PostgreSQL database:

// Connect to database and load schema
provider, _ := pg.NewTypeProviderWithConnection(ctx, "postgres://user:pass@localhost/db")
defer provider.Close()

// Load table schema dynamically
provider.LoadTableSchema(ctx, "users")

// Use with CEL
env, _ := cel.NewEnv(
    cel.CustomTypeProvider(provider),
    cel.Variable("user", cel.ObjectType("users")),
)

See Getting Started Guide for more details.

Requirements

• Go 1.24 or higher
• PostgreSQL 17 (also compatible with PostgreSQL 15+)

Contributing

Contributions are welcome! Please see CONTRIBUTING.md for guidelines.

License

Apache 2.0 - See LICENSE for details.

Related Projects

• CEL-Go - Common Expression Language implementation in Go
• CEL Spec - Common Expression Language specification

Need Help?

• 📖 Check the documentation
• 💬 Open an issue
• 🌟 Star the repo if you find it useful!