web-app8 min

Reading from a Database

Most web apps store data in a database. With frame.data, you write SQL directly in your Clean Language code and get results back as JSON — no ORM layer, no magic, just plain SQL with safe parameterization.

Add frame.data to plugins and use db.query to run SQL:

plugins:\n    frame.server\n    frame.data\n\nendpoints server:\n    GET "/status" :\n        string result = db.query("SELECT CAST(COUNT(*) AS CHAR) as cnt FROM users", "[]")\n        string count = json.get(result, "data.rows.0.cnt")\n        return http.respond(200, "text/plain", "We have " + count + " users")

GET /status → We have 42 users

db.query() takes a SQL string and a JSON array of parameters. "[]" means no parameters. Results come back as JSON with a data.rows array. json.get() with a dot path reads the first row's field.

Use ? placeholders to safely pass URL values into the query:

    GET "/users/:username" :\n        string username = req.param("username")\n        string params = "[\\"" + username + "\\"]"\n        string result = db.query("SELECT email FROM users WHERE username = ? LIMIT 1", params)\n        string email = json.get(result, "data.rows.0.email")\n        if email == ""\n            return http.respond(404, "text/plain", "User not found")\n        return http.respond(200, "text/plain", "Email: " + email)

GET /users/alice  → Email: alice@example.com\nGET /users/nobody → 404 User not found

Always use ? placeholders for values — never concatenate user input into SQL strings. Build params as a JSON array string where each value is wrapped in escaped quotes. CAST(... AS CHAR) ensures numeric results come back as strings.

Quick recap

Add frame.data to plugins: to enable database access
db.query(sql, params) — sql uses ? placeholders, params is a JSON array string
json.get(result, "data.rows.0.column") reads the first row's field
NEVER concatenate user input into SQL — always use ? placeholders