Your First API

Your First API

In this guide, you'll create your first API using the Generic SQL API Framework.

---

Step 1: Create a SQL Query

Create a new SQL file inside the queries directory.

Example:

queries/customers.sql

SELECT
    CustomerID,
    CustomerName,
    City,
    MobileNumber
FROM CustomerTable;

Save the file.

---

Step 2: Create the JSON Request

Send the following JSON request to the Query API.

{
    "query": "customers"
}

The framework automatically loads:

queries/customers.sql

and executes the SQL query.

---

Step 3: Send the Request

Example:

POST /api/query

Content-Type: application/json

Request Body:

{
    "query": "customers"
}

---

Step 4: Execute the Query

The framework performs the following steps automatically:

1. Receives the JSON request.
2. Validates the request.
3. Locates the SQL file.
4. Builds the SQL statement.
5. Executes the query.
6. Returns the results as JSON.

---

Step 5: View the Response

Example Response:

{
    "success": true,
    "rows": [
        {
            "CustomerID": 1,
            "CustomerName": "John Doe",
            "City": "Bangalore",
            "MobileNumber": "9876543210"
        }
    ]
}

---

Using Parameters

Suppose you want to retrieve a specific customer.

SQL:

SELECT *
FROM CustomerTable
WHERE CustomerID = :CustomerID;

JSON Request:

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

---

Filtering Multiple Parameters

SQL:

SELECT *
FROM CustomerTable
WHERE City = :City
AND Status = :Status;

JSON:

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

---

Error Handling

If the SQL file does not exist:

{
    "success": false,
    "message": "Query file not found."
}

If a required parameter is missing:

{
    "success": false,
    "message": "Missing required parameter: CustomerID"
}

---

Best Practices

• Keep one SQL query per file.
• Use descriptive query names.
• Use parameterized queries to prevent SQL injection.
• Organize SQL files into folders for larger projects.
• Test queries directly in SQL Server before using them in the framework.

---

Next Steps

Now that you've created your first API, continue with:

• JSON Request Format
• SQL Query Files
• Validation Engine
• Query Examples
• Advanced Features