Use of this endpoint is not typically required for most applications based on the output format of this endpoint. Where we typically see the most usage for our raw query endpoint is when database interfaces such as Outerbase, TablePlus, DBeaver and other tools need additional metadata returned in the response or headers based on the query that was executed.
For example, let’s say we run a SQL statement that returns 0 rows but is correctly trying to pick data from our users table. An interface may want to show the users what columns were in the table users even though zero rows were returned.
The goal of this endpoint is to inform the interface of what belongs where, including columns that should exist even if no information might be returned from them. You may also notice that the response for each row looks slightly odd. Why you may ask? If we query from two different tables, and they both have an id column, we cannot represent two keys in an object with the same string of id – so we need to be able to map it to its column name appropriately still. We do this by mapping its place in the rows item array with the place of the string in the columns array.
curl --location 'https://starbasedb.YOUR-IDENTIFER.workers.dev/query/raw' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: application/json' \
--data '{
"sql": "SELECT * FROM orders;",
"params": []
}'
The result of the above query may look like this:
{
"result": {
"columns": [
"order_id",
"user_id",
"order_date",
"amount"
],
"rows": [
[
1,
1,
"2024-10-01",
50.75
],
[
2,
1,
"2024-10-03",
30
],
[
3,
2,
"2024-10-05",
99.99
],
[
4,
3,
"2024-10-06",
20.49
],
[
5,
1,
"2024-12-01",
50.75
]
],
"meta": {
"rows_read": 5,
"rows_written": 0
}
}
}