web-app8 min

Listing Records from a Database

Most pages show a list — articles, products, users, messages. The pattern is always the same: get the count, fetch the rows, iterate from 0 to count minus one, render each one. Learn this pattern once and you can build any list.

Step 1: get the count. Step 2: fetch all rows.

    GET "/posts" :\n        string count_res = db.query("SELECT CAST(COUNT(*) AS CHAR) as cnt FROM posts WHERE published = 1", "[]")\n        integer post_count = json.get(count_res, "data.rows.0.cnt").toInteger()\n\n        string posts_res = db.query("SELECT title, slug, excerpt FROM posts WHERE published = 1 ORDER BY created_at DESC", "[]")\n        string cards = ""

Always get the count first — you need it to know the iterate range. CAST(COUNT(*) AS CHAR) returns the count as a string, then .toInteger() converts it. Two queries run sequentially: count first, then the full data fetch.

Step 3: iterate through the rows and build HTML.

        iterate i in 0 to post_count - 1\n            string idx = i.toString()\n            string post_title = json.get(posts_res, "data.rows." + idx + ".title")\n            string post_slug = json.get(posts_res, "data.rows." + idx + ".slug")\n            string post_excerpt = json.get(posts_res, "data.rows." + idx + ".excerpt")\n            cards = cards + post_card(post_title, post_slug, post_excerpt)\n\n        return http.respond(200, "text/html", cards)

GET /posts → HTML list of all published posts

Build the json.get path dynamically: "data.rows." + idx + ".column". This works for any number of rows. The cards string grows with each iteration — concatenation is how you assemble a list of components.

Quick recap

Count rows first with COUNT(*), then fetch the full result set
CAST(COUNT(*) AS CHAR) + .toInteger() converts the count to an integer
iterate i in 0 to count - 1 loops through every row
Access each row with json.get(result, "data.rows." + i.toString() + ".column")