The json component
For advanced users, allows you to easily build an API over your database. The json component responds to the current HTTP request with a JSON object. This component must appear at the top of your SQL file, before any other data has been sent to the browser.
Introduced in SQLPage v0.9.0.
Top-level parameters
name | required | type | description |
---|---|---|---|
contents | TEXT |
A single JSON payload to send. You can use your database's built-in json functions to build the value to enter here. If not provided, the contents will be taken from the next SQL statements and rendered as a JSON array. | |
type | TEXT |
The type of the JSON payload to send. Defaults to "array" (each query result is rendered as a JSON object in the array). Other possible values are "jsonlines" (each query result is rendered as a JSON object in a new line, without a top-level array) and "sse" (each query result is rendered as a JSON object in a new line, prefixed by "data: ", which allows you to read the results as server-sent events in real-time from javascript). |
No data |
Examples
Send query results as a JSON array
SQL
select 'json' AS component;
select * from users;
Result
[
{"username":"James","userid":1},
{"username":"John","userid":2}
]
Send a single JSON object
SQL
select 'json' AS component, 'jsonlines' AS type;
select * from users where id = $user_id LIMIT 1;
Note the
LIMIT 1
clause. Thejsonlines
type will send one JSON object per result row, separated only by a single newline character (\n). So if your query returns multiple rows, the result will not be a single valid JSON object, like most JSON parsers expect.
Result
{ "username":"James", "userid":1 }
Create a complex API endpoint
You can create an API endpoint that will return a JSON value in any format you want, to implement a complex API.
You should use the json functions provided by your database to form the value you pass to the contents
property.
To build a json array out of rows from the database, you can use:
-
json_group_array()
in SQLite, -
json_agg()
in Postgres, or -
JSON_ARRAYAGG()
in MySQL. -
FOR JSON PATH
in SQL Server.
SELECT 'json' AS component,
JSON_OBJECT(
'users', (
SELECT JSON_GROUP_ARRAY(
JSON_OBJECT(
'username', username,
'userid', id
)
) FROM users
)
) AS contents;
This will return a JSON response that looks like this:
{
"users" : [
{ "username":"James", "userid":1 }
]
}
If you want to handle custom API routes, like POST /api/users/:id
,
you can use
- the
404.sql
file to handle the request despite the URL not matching any file, - the
request_method
function to differentiate between GET and POST requests, - and the
path
function to extract the:id
parameter from the URL.
Access query results in real-time with server-sent events
Using server-sent events, you can stream large query results to the client in real-time, row by row.
This allows building sophisticated dynamic applications that will start processing and displaying the first rows of data in the browser while the database server is still processing the end of the query.
SQL
select 'json' AS component, 'sse' AS type;
select * from users;
JavaScript
const eventSource = new EventSource("users.sql");
eventSource.onmessage = function (event) {
const user = JSON.parse(event.data);
console.log(user.username);
}
eventSource.onerror = function () {
eventSource.close(); // do not reconnect after reading all the data
}