Endpoints Overview

Endpoints are the heart of flAPI. Each endpoint defines a REST API that serves data from your sources. This guide covers everything you need to know about creating, configuring, and managing endpoints.

What is an Endpoint?

An endpoint is a combination of:

URL path - Where clients access your API (e.g., /customers/, /orders/)
SQL template - The query that retrieves and transforms data
Configuration - Parameters, validation, caching, authentication

File Structure

Every endpoint consists of two files in the sqls/ directory:

project/
├── flapi.yaml           # Main configuration
└── sqls/
    ├── customers.yaml   # Endpoint configuration
    ├── customers.sql    # SQL template
    ├── orders.yaml
    └── orders.sql

Minimal Endpoint

The simplest possible endpoint:

sqls/hello.yaml:

url-path: /hello/
template-source: hello.sql
connection:
  - my-database

sqls/hello.sql:

SELECT 'Hello, World!' as message

That's it! Start flAPI and access http://localhost:8080/hello/

Complete Endpoint Configuration

Here's a full-featured endpoint with all options:

# URL configuration
url-path: /customers/

# Request parameters
request:
  - field-name: id
    field-in: query
    description: Customer ID to filter by
    required: false
    validators:
      - type: int
        min: 1
  
  - field-name: segment
    field-in: query
    description: Market segment
    required: false
    validators:
      - type: string
        enum: ['AUTOMOTIVE', 'BUILDING', 'FURNITURE']

# SQL template
template-source: customers.sql

# Data connection
connection:
  - customers-db

# Caching configuration
cache:
  enabled: true
  table: customers_cache
  schema: analytics
  schedule: 60m
  template_file: customers_cache.sql

# Authentication
authentication:
  required: true
  methods: ['jwt', 'api-key']

# Rate limiting
rate-limit:
  requests: 1000
  window: 60s
  per: user

# MCP tool for AI agents
mcp-tool:
  name: get_customers
  description: Retrieve customer data filtered by segment or ID

URL Paths

Basic Paths

url-path: /customers/
url-path: /products/search/
url-path: /analytics/revenue/

Rules:

Must start with /
Should end with / (convention)
Use lowercase with hyphens: /product-categories/
No spaces or special characters

Path Parameters

Include dynamic segments in your URL:

url-path: /customers/{id}/

request:
  - field-name: id
    field-in: path
    required: true
    validators:
      - type: int

Examples:

/customers/123/ → id = 123
/orders/{order_id}/items/ → Order items API
/users/{user_id}/permissions/ → User permissions

Request Parameters

Parameters can come from different sources:

Query Parameters

Most common for filtering and search:

# /customers?segment=AUTO&active=true
request:
  - field-name: segment
    field-in: query
  - field-name: active
    field-in: query

Path Parameters

For resource identification:

# /customers/123/
request:
  - field-name: id
    field-in: path
    required: true

Header Parameters

For authentication and metadata:

# Header: X-User-Region: US
request:
  - field-name: user_region
    field-in: header

Body Parameters (POST/PUT)

For data submission:

# POST /customers with JSON body
request:
  - field-name: customer_data
    field-in: body
    required: true

SQL Templates

Connect your endpoint to a SQL template:

template-source: customers.sql

The template uses Mustache syntax for dynamic queries:

-- sqls/customers.sql
SELECT
  c_custkey as id,
  c_name as name,
  c_mktsegment as segment
FROM customers
WHERE 1=1
{{#params.segment}}
  AND c_mktsegment = '{{{params.segment}}}'
{{/params.segment}}
{{#params.id}}
  AND c_custkey = {{{params.id}}}
{{/params.id}}

See SQL Templating Guide for details.

Data Connections

Specify which connection(s) to use:

Single Connection

connection:
  - customers-db

Multiple Connections

Join data across sources:

connection:
  - bigquery-warehouse
  - customers-parquet

-- Join BigQuery orders with local customer data
SELECT
  o.*,
  c.name as customer_name
FROM bigquery_scan('project.dataset.orders') o
LEFT JOIN '{{{conn.customers-parquet.path}}}' c
  ON o.customer_id = c.id

Response Format

flAPI returns JSON by default:

{
  "data": [
    {"id": 1, "name": "Customer A"},
    {"id": 2, "name": "Customer B"}
  ],
  "metadata": {
    "count": 2,
    "execution_time_ms": 1.2
  }
}

Configure response format:

response:
  format: json  # or: csv, parquet
  include_metadata: true
  pagination:
    enabled: true
    max_limit: 1000

Endpoint Discovery

List All Endpoints

$ flapii endpoints list

Available Endpoints:
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Path                  Method  Cached
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
/customers/           GET     Yes
/orders/              GET     Yes
/products/search/     GET     No
/analytics/revenue/   GET     Yes
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Describe Endpoint

$ flapii endpoints describe /customers/

Endpoint: /customers/
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
URL Path:      /customers/
Template:      sqls/customers.sql
Connection:    customers-db
Cache:         Enabled (60m refresh)
Auth:          Required (JWT)
Rate Limit:    1000 req/min per user

Parameters:
  • segment (query, optional)
    Type: string
    Values: AUTOMOTIVE, BUILDING, FURNITURE
  
  • id (query, optional)
    Type: integer
    Min: 1

MCP Tool:      get_customers

OpenAPI Documentation

flAPI automatically generates OpenAPI (Swagger) documentation:

Access at: http://localhost:8080/docs

Features:

✅ Interactive API testing
✅ Request/response examples
✅ Parameter documentation
✅ Authentication setup
✅ Export OpenAPI spec

Testing Endpoints

Validate Configuration

$ flapii endpoints validate /customers/

✓ Configuration valid
✓ SQL template found: sqls/customers.sql
✓ Connection 'customers-db' exists
✓ Parameters configured correctly
✓ Validators configured correctly

Test Query

$ flapii query run /customers/ \
  --params '{"segment": "AUTOMOTIVE"}' \
  --limit 5

Results (5 rows):
┌──────┬─────────────────┬─────────────┐
│  id  │ name            │ segment     │
├──────┼─────────────────┼─────────────┤
│ 101  │ Customer A      │ AUTOMOTIVE  │
│ 102  │ Customer B      │ AUTOMOTIVE  │
...

Call API Directly

$ curl http://localhost:8080/customers/?segment=AUTOMOTIVE

{
  "data": [
    {
      "id": 101,
      "name": "Customer A",
      "segment": "AUTOMOTIVE"
    }
  ]
}

Common Patterns

Pagination

request:
  - field-name: limit
    field-in: query
    validators:
      - type: int
        min: 1
        max: 100
  - field-name: offset
    field-in: query
    validators:
      - type: int
        min: 0

SELECT * FROM products
LIMIT {{{params.limit|100}}}
OFFSET {{{params.offset|0}}}

Search

request:
  - field-name: search
    field-in: query
    validators:
      - type: string
        max_length: 100

SELECT * FROM products
WHERE name ILIKE '%{{{params.search}}}%'

Date Ranges

request:
  - field-name: start_date
    field-in: query
    validators:
      - type: string
        pattern: '^\d{4}-\d{2}-\d{2}$'
  - field-name: end_date
    field-in: query
    validators:
      - type: string
        pattern: '^\d{4}-\d{2}-\d{2}$'

SELECT * FROM orders
WHERE order_date BETWEEN '{{{params.start_date}}}' 
  AND '{{{params.end_date}}}'

Best Practices

1. Use Descriptive URL Paths

# ✅ Good
url-path: /customers/by-segment/
url-path: /orders/recent/
url-path: /analytics/monthly-revenue/

# ❌ Bad
url-path: /api1/
url-path: /data/
url-path: /query/

2. Document Parameters

- field-name: status
  field-in: query
  description: Filter orders by status (pending, completed, cancelled)
  required: false

3. Always Validate Input

validators:
  - type: string
    enum: ['active', 'inactive']
  - type: int
    min: 1
    max: 10000

4. Use Caching for Read-Heavy Endpoints

cache:
  enabled: true
  schedule: 60m  # Hourly refresh

5. Add Rate Limiting

rate-limit:
  requests: 1000
  window: 60s

sqls/
├── customers/
│   ├── list.yaml
│   ├── list.sql
│   ├── detail.yaml
│   └── detail.sql
└── orders/
    ├── list.yaml
    └── list.sql

Troubleshooting

Endpoint Not Found

Error: 404 - Endpoint not found

Check:

File exists: sqls/endpoint.yaml
URL path matches: url-path: /endpoint/
flAPI server restarted after changes

Template Error

Error: Template expansion failed

Check:

SQL file exists: template-source: endpoint.sql
Mustache syntax correct
Test with: flapii templates expand /endpoint/

Connection Error

Error: Connection 'my-db' not found

Check:

Connection defined in flapi.yaml
Connection name matches: connection: [my-db]
Connection initialized properly

Next Steps

YAML Syntax: Complete endpoint configuration guide
Parameters: Learn about parameter types
Validation: Master input validation
SQL Templating: Dynamic query patterns and Mustache syntax
Response Format: Configure API responses
Authentication: Secure your APIs
Caching: Optimize performance and reduce costs

What is an Endpoint?​

File Structure​

Minimal Endpoint​

Complete Endpoint Configuration​

URL Paths​

Basic Paths​

Path Parameters​

Request Parameters​

Query Parameters​

Path Parameters​

Header Parameters​

Body Parameters (POST/PUT)​

SQL Templates​

Data Connections​

Single Connection​

Multiple Connections​

Response Format​

Endpoint Discovery​

List All Endpoints​

Describe Endpoint​

OpenAPI Documentation​

Testing Endpoints​

Validate Configuration​

Test Query​

Call API Directly​

Common Patterns​

Pagination​

Search​

Date Ranges​

Best Practices​

1. Use Descriptive URL Paths​

2. Document Parameters​

3. Always Validate Input​

4. Use Caching for Read-Heavy Endpoints​

5. Add Rate Limiting​

6. Group Related Endpoints​

Troubleshooting​

Endpoint Not Found​

Template Error​

Connection Error​

Next Steps​

What is an Endpoint?

File Structure

Minimal Endpoint

Complete Endpoint Configuration

URL Paths

Basic Paths

Path Parameters

Request Parameters

Query Parameters

Path Parameters

Header Parameters

Body Parameters (POST/PUT)

SQL Templates

Data Connections

Single Connection

Multiple Connections

Response Format

Endpoint Discovery

List All Endpoints

Describe Endpoint

OpenAPI Documentation

Testing Endpoints

Validate Configuration

Test Query

Call API Directly

Common Patterns

Pagination

Search

Date Ranges

Best Practices

1. Use Descriptive URL Paths

2. Document Parameters

3. Always Validate Input

4. Use Caching for Read-Heavy Endpoints

5. Add Rate Limiting

6. Group Related Endpoints

Troubleshooting

Endpoint Not Found

Template Error

Connection Error

Next Steps