Query Languages

Discover available query languages, templates, and language-specific features.

Query Language Overview

Informer supports multiple query languages, each with unique capabilities:

• SQL - Direct SQL queries with parameter support
• i5-QL - Visual query builder language
• Flow - Multi-step transformation pipelines

Each language has its own payload structure, validation rules, and execution engine.

GET /api/query-languages

Get all available query languages.

Authentication: Required

Response:

[
  {
    "id": "sql",
    "name": "SQL",
    "description": "Direct SQL queries with parameter substitution",
    "icon": "database",
    "capabilities": {
      "parameters": true,
      "joins": true,
      "aggregations": true,
      "subqueries": true,
      "functions": true
    },
    "payloadSchema": {
      "type": "object",
      "properties": {
        "sql": {
          "type": "string",
          "description": "SQL query text"
        },
        "fields": {
          "type": "array",
          "description": "Field definitions"
        }
      },
      "required": ["sql"]
    }
  },
  {
    "id": "i5-ql",
    "name": "Informer QL",
    "description": "Visual query language with drag-and-drop interface",
    "icon": "magic",
    "capabilities": {
      "parameters": true,
      "joins": true,
      "aggregations": true,
      "visualBuilder": true
    }
  },
  {
    "id": "flow",
    "name": "Flow Builder",
    "description": "Multi-step data transformation pipeline",
    "icon": "flow",
    "capabilities": {
      "parameters": true,
      "transformations": true,
      "branching": true
    }
  }
]

---

GET /api/query-languages/{id}

Get details for a specific query language.

Authentication: Required

Path Parameters:

Parameter	Type	Description
id	string	Language identifier (e.g., sql, i5-ql)

Response:

{
  "id": "sql",
  "name": "SQL",
  "description": "Direct SQL queries with parameter substitution",
  "version": "1.0.0",
  "icon": "database",
  "capabilities": {
    "parameters": true,
    "joins": true,
    "aggregations": true,
    "subqueries": true,
    "functions": true,
    "windowFunctions": true,
    "cte": true
  },
  "payloadSchema": {
    "type": "object",
    "properties": {
      "sql": {
        "type": "string",
        "description": "SQL query text with parameter placeholders (:param)"
      },
      "fields": {
        "type": "array",
        "items": {
          "type": "object",
          "properties": {
            "id": { "type": "string" },
            "name": { "type": "string" },
            "type": { "type": "string" }
          }
        }
      }
    },
    "required": ["sql"]
  },
  "parameterSyntax": {
    "placeholder": ":paramName",
    "example": "SELECT * FROM sales WHERE date >= :startDate"
  },
  "documentation": "https://docs.entrinsik.com/query-languages/sql"
}

---

GET /api/query-templates

Get available query templates.

Authentication: Required

Response:

{
  "_links": {
    "self": { "href": "/api/query-templates" }
  },
  "_embedded": {
    "inf:query-template": [
      {
        "id": "simple-select",
        "name": "Simple SELECT",
        "description": "Basic SELECT query template",
        "language": "sql",
        "category": "starter",
        "payload": {
          "sql": "SELECT * FROM table_name WHERE column = :value"
        },
        "inputs": {
          "value": {
            "type": "string",
            "label": "Filter Value"
          }
        }
      },
      {
        "id": "aggregation",
        "name": "Aggregation Query",
        "description": "GROUP BY with aggregations",
        "language": "sql",
        "category": "analysis",
        "payload": {
          "sql": "SELECT category, COUNT(*) as count, AVG(amount) as avg_amount FROM sales GROUP BY category"
        }
      },
      {
        "id": "date-range",
        "name": "Date Range Filter",
        "description": "Filter by date range with parameters",
        "language": "sql",
        "category": "common",
        "payload": {
          "sql": "SELECT * FROM events WHERE date BETWEEN :startDate AND :endDate"
        },
        "inputs": {
          "startDate": { "type": "date", "label": "Start Date" },
          "endDate": { "type": "date", "label": "End Date" }
        }
      },
      {
        "id": "top-n",
        "name": "Top N Records",
        "description": "Get top N records by value",
        "language": "sql",
        "category": "analysis",
        "payload": {
          "sql": "SELECT * FROM products ORDER BY sales DESC LIMIT :limit"
        },
        "inputs": {
          "limit": { "type": "number", "label": "Number of Records", "default": 10 }
        }
      }
    ]
  },
  "start": 0,
  "count": 4,
  "total": 5
}

Template Properties:

Property	Description
id	Template identifier
name	Display name
description	Template purpose
language	Query language
category	Template category (starter, common, analysis, etc.)
payload	Pre-filled query payload
inputs	Parameter definitions

---

Language-Specific Features

SQL Language

Parameter Syntax:

SELECT * FROM sales WHERE date >= :startDate AND region = :region

Supported Parameters:

• :paramName - Named parameters
• Automatically typed based on inputs definition

Capabilities:

• Standard SQL SELECT, JOIN, WHERE, GROUP BY, ORDER BY
• Subqueries and CTEs (WITH clauses)
• Window functions
• Database-specific functions (depending on datasource)

Example Payload:

{
  "language": "sql",
  "payload": {
    "sql": "SELECT region, SUM(amount) as total FROM sales WHERE date >= :startDate GROUP BY region",
    "fields": [
      { "id": "region", "name": "Region", "type": "string" },
      { "id": "total", "name": "Total Sales", "type": "number" }
    ]
  },
  "inputs": {
    "startDate": { "type": "date", "label": "Start Date", "default": "2024-01-01" }
  }
}

---

i5-QL Language

Visual Query Builder:

The i5-QL language provides a structured, programmatic query definition:

{
  "language": "i5-ql",
  "payload": {
    "source": {
      "datasourceId": "mysql-prod",
      "schemaId": null,
      "mappingId": "sales"
    },
    "fields": [
      { "id": "date", "fieldId": "sale_date", "name": "Date", "type": "field" },
      { "id": "amount", "fieldId": "amount", "name": "Amount", "type": "field" },
      { "id": "region", "fieldId": "region", "name": "Region", "type": "field" }
    ],
    "filters": [
      {
        "field": "date",
        "operator": ">=",
        "value": { "param": "startDate" }
      }
    ],
    "aggregations": [],
    "sorts": [
      { "field": "date", "direction": "desc" }
    ],
    "linkRefs": []
  },
  "inputs": {
    "startDate": { "type": "date", "label": "Start Date" }
  }
}

Key Features:

• Field-based selection (no raw SQL)
• Visual filter builder
• Automatic join management
• Type-safe operations

---

Flow Language

Multi-Step Pipelines:

{
  "language": "flow",
  "flow": [
    {
      "type": "source",
      "datasourceId": "mysql-prod",
      "query": "SELECT * FROM sales"
    },
    {
      "type": "filter",
      "condition": { "field": "amount", "operator": ">", "value": 1000 }
    },
    {
      "type": "transform",
      "fields": [
        { "name": "year", "expression": "YEAR(date)" }
      ]
    },
    {
      "type": "aggregate",
      "groupBy": ["year", "region"],
      "aggregations": [
        { "field": "amount", "function": "sum", "alias": "total" }
      ]
    }
  ]
}

Step Types:

• source - Data source
• filter - Filter rows
• transform - Add/modify fields
• aggregate - Group and aggregate
• join - Join with another source
• union - Combine datasets

---

GET /api/queries/{id}/query-string

Get the compiled query string for a query (SQL representation).

Authentication: Required

Path Parameters:

Parameter	Type	Description
id	string	Query ID (UUID) or natural ID

Response:

{
  "queryString": "SELECT region, SUM(amount) as total FROM sales WHERE date >= '2024-01-01' GROUP BY region ORDER BY total DESC",
  "parameters": {
    "startDate": "2024-01-01"
  },
  "language": "sql"
}

Query Inspection

Use this endpoint to see the actual SQL that will be executed, with parameters interpolated. Useful for debugging and optimization.

---

GET /api/queries/{id}/discover

Discover available fields and schema information for a query's datasource.

Authentication: Required

Path Parameters:

Parameter	Type	Description
id	string	Query ID (UUID) or natural ID

Response:

Returns datasource schema, mappings (tables), and fields available for the query.

{
  "datasource": {
    "id": "mysql-prod",
    "name": "Production MySQL",
    "type": "mysql"
  },
  "schemas": [
    {
      "id": "sales_db",
      "name": "sales_db",
      "mappings": [
        {
          "id": "sales",
          "name": "sales",
          "fields": [
            { "id": "sale_date", "name": "sale_date", "type": "date" },
            { "id": "amount", "name": "amount", "type": "decimal" },
            { "id": "region", "name": "region", "type": "varchar" }
          ]
        }
      ]
    }
  ]
}

Discovery Scope

Field discovery is scoped to the query's datasource. Switch datasources to discover fields from different sources.

---

Language Selection Guide

Use Case	Recommended Language
Complex SQL, optimization needed	SQL
Visual query building	i5-QL
Multi-step transformations	Flow
Existing SQL queries	SQL
Non-technical users	i5-QL
Data pipeline workflows	Flow

---

Best Practices

Language Choice

1. SQL - Use for maximum control and performance tuning
2. i5-QL - Use for user-friendly query building
3. Flow - Use for complex ETL-style workflows

Parameter Design

// Good: Descriptive parameter names with defaults
{
  "inputs": {
    "fiscalYear": {
      "type": "number",
      "label": "Fiscal Year",
      "default": 2024,
      "description": "Fiscal year for reporting"
    }
  }
}

// Bad: Generic names, no defaults
{
  "inputs": {
    "param1": { "type": "number" }
  }
}

Template Usage

1. Start with a template for common patterns
2. Customize payload to your needs
3. Save as new query
4. Test with sample data
5. Add to favorites or tag for reuse