Build a SQL-backed API

Build an API with two endpoints over a SQLite database: GET /report runs an analytics query and returns CSV; POST /update applies a batch of updates in a transaction.

The sql component runs queries against PostgreSQL, MySQL, SQLite, SQL Server, or Oracle. Parameters use $1, $2, ... placeholders - never string interpolation - so input cannot break out of the query. This example uses SQLite so it runs with no database server.

Needs: The sqlite3 CLI (for creating the demo database).

Step 1: create the project and database

mkdir sql-api
cd sql-api
mkdir resources

sqlite3 demo.db <<'SQL'
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, age INTEGER,
                    status TEXT, created_at TEXT);
INSERT INTO users (email, age, status, created_at) VALUES
  ('a@example.com', 31, 'active',   '2024-03-01'),
  ('b@example.com', 27, 'active',   '2024-03-01'),
  ('c@example.com', 44, 'inactive', '2024-03-02');
SQL

Step 2: declare the routes and the connection

Create workflow.yaml:

# workflow.yaml
apiVersion: kdeps.io/v1
kind: Workflow

metadata:
  name: sql-api
  version: "1.0.0"
  targetActionId: results

settings:
  apiServer:
    portNum: 16395
    routes:
      - path: /report
        methods: [GET]
      - path: /update
        methods: [POST]

Resources refer to the database by name (connectionName: demo). The DSN is machine-local, so it lives in ~/.kdeps/config.yaml, not in the workflow:

# ~/.kdeps/config.yaml
sql_connections:
  demo:
    connection: "sqlite://./demo.db"   # relative to the directory you run kdeps from

Or set it for one shell with export KDEPS_SQL_CONNECTIONS_DEMO_CONNECTION="sqlite://./demo.db".

Step 3: the analytics query

Create resources/report.yaml:

# resources/report.yaml
actionId: report
name: User report
validations:
  methods: [GET]
  routes: [/report]
component:
  name: sql
  with:
    connectionName: demo
    query: |
      SELECT status,
             COUNT(*)        AS total,
             ROUND(AVG(age)) AS avg_age
      FROM users
      WHERE created_at >= $1
      GROUP BY status
    params:
      - "{{ get('since', '2024-01-01') }}"     # ?since=... or default
    format: csv                        # csv, json, or table
    maxRows: 100
    timeout: 30s

Step 4: the batch update

Create resources/update.yaml:

# resources/update.yaml
actionId: update
name: Batch status update
validations:
  methods: [POST]
  routes: [/update]
component:
  name: sql
  with:
    connectionName: demo
    transaction: true                  # all or nothing
    queries:
      - query: "UPDATE users SET status = $1 WHERE id = $2"
        paramsBatch: "{{ get('changes') }}"   # a list of [status, id] pairs

paramsBatch runs the query once per element in the list, inside one transaction.

Step 5: combine the results

Create resources/results.yaml:

# resources/results.yaml
actionId: results
name: Combined results
requires: [report, update]
apiResponse:
  success: true
  response:
    report: "{{ get('report') }}"
    update: "{{ get('update') }}"
    at: "{{ info('current_time') }}"

Only the resource matching the request's route runs; the other is skipped.

Step 6: validate and run

kdeps validate .
export KDEPS_API_AUTH_TOKEN=dev-token
export KDEPS_SQL_CONNECTIONS_DEMO_CONNECTION="sqlite://./demo.db"   # skip if set in config.yaml
kdeps run .

Run the report:

curl "http://localhost:16395/report?since=2024-03-01" \
  -H "Authorization: Bearer $KDEPS_API_AUTH_TOKEN"

It returns the CSV inside the combined response:

{"success": true, "data": {"report": "avg_age,status,total\n29,active,2\n44,inactive,1\n", "update": null, "at": "..."}}

Apply updates:

curl -X POST http://localhost:16395/update \
  -H "Authorization: Bearer $KDEPS_API_AUTH_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"changes": [["inactive", 1], ["active", 3]]}'

Confirm the change landed:

sqlite3 demo.db "SELECT id, status FROM users"
# 1|inactive
# 2|active
# 3|active

Next steps

• SQL resource - all connection types, formats, transactions
• SQL examples - joins, pagination, upserts
• Validation and control flow - route and method scoping
• Global config - keeping connection strings out of workflow.yaml