Build a database-backed API

Create a read-only task-list API using a sandbox SQLite database. The example keeps schema setup separate from request execution and returns a bounded collection.

1. Prepare test data#

Create a dedicated sandbox database under Workflows → Databases. Run this setup once in that database:

CREATE TABLE Tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    status TEXT NOT NULL
);

INSERT INTO Tasks (title, status)
VALUES ('Review the first workflow', 'open');

This is SQLite SQL. Adapt it for another engine rather than assuming the same identity-column and limit syntax applies.

2. Create the action#

Create a controller with path tasks and a GET action with route list. Configure the access required by your app. If the records are private, place authorization before the Database node.

Add a Database node named ListTasks and bind it to the test database. Use:

-- cloudgate:parameters
SELECT id, title, status
FROM Tasks
WHERE status = @status
ORDER BY id DESC
LIMIT 20;

Set its input JSON to:

{"status": "open"}

This initial fixed filter makes it easy to verify the query. To accept a client filter later, validate the value and pass it as a parameter.

3. Confirm the response shape#

Add a final Function named TaskResponse and read ${ListTasks} directly. Normalize the empty-SELECT result into an array:

import json

rows = ${ListTasks}
if rows == "No records found":
    rows = []
if not isinstance(rows, list):
    raise ValueError("Expected a task list")
return json.dumps({"items": rows})

The selector resolves a JSON row array as a Python list. An empty SELECT can return the text No records found; this Function normalizes that result to an empty list. Select JSON as the action response format, then run the complete workflow and confirm the final response contains an items array. Test both the seeded row and an empty result. A table consuming this response uses items as its data input path; if your client adds another response wrapper, include that wrapper in its binding.

4. Prepare production#

Create the production schema through a deliberate migration, select the correct production binding, and keep fixture records out of the live database. Review ownership filters and paging before allowing larger datasets.

Use SQL parameters for dynamic values, backups before destructive schema changes, and go-live checks before release.