JSON Request Format

JSON Request Format

The Generic SQL API Framework uses a JSON-based request structure to execute SQL queries. This document explains each property available in a request.

---

Basic Request

The simplest request contains only the query name.

{
    "query": "customers"
}

---

Request Structure

{
    "query": "customers",
    "parameters": {},
    "pagination": {},
    "sorting": {},
    "filters": {}
}

---

Properties

query

Specifies the SQL file to execute.

Example:

{
    "query": "customers"
}

This loads:

queries/customers.sql

---

parameters

Passes values into parameterized SQL queries.

Example:

{
    "query": "customer_by_id",
    "parameters": {
        "CustomerID": 101
    }
}

SQL:

SELECT *
FROM CustomerTable
WHERE CustomerID = :CustomerID;

---

pagination

Limits the number of returned records.

Example:

{
    "query": "customers",
    "pagination": {
        "page": 1,
        "pageSize": 50
    }
}

---

sorting

Sorts the returned records.

Example:

{
    "query": "customers",
    "sorting": {
        "column": "CustomerName",
        "direction": "ASC"
    }
}

Supported directions:

• ASC
• DESC

---

filters

Applies additional filtering conditions.

Example:

{
    "query": "customers",
    "filters": {
        "City": "Bangalore",
        "Status": "Active"
    }
}

---

Complete Example

{
    "query": "customers",
    "parameters": {
        "CustomerType": "Retail"
    },
    "pagination": {
        "page": 2,
        "pageSize": 25
    },
    "sorting": {
        "column": "CustomerName",
        "direction": "ASC"
    },
    "filters": {
        "City": "Bangalore"
    }
}

---

Validation Rules

The framework validates every request before execution.

Validation includes:

• Required properties
• Parameter existence
• Supported pagination values
• Valid sorting direction
• Allowed filter fields
• SQL file availability

---

Successful Response

{
    "success": true,
    "rows": [
        {
            "CustomerID": 1,
            "CustomerName": "John Doe"
        }
    ]
}

---

Error Response

{
    "success": false,
    "message": "Validation failed."
}

---

Best Practices

• Always use parameterized queries.
• Keep request payloads small.
• Validate input before sending requests.
• Use pagination for large datasets.
• Avoid exposing unnecessary database fields.

---

Next Steps

Learn how SQL files are organized and executed by reading the SQL Query Files guide.