Docs Ordnary Web Services Ordnary Serverlite
Query API
The HTTPS API your app uses: authentication, single statements, batches, raw rows, the response and errors.
Every request goes to https://serverlite.ordnary.com/v1, carries an API token and sends JSON. A database can be named by its name or its ID in the path.
Authentication
Authorization: Bearer osq_...
Content-Type: application/jsonCreate tokens under Ordnary Serverlite, API tokens. See API tokens.
Run a statement
/v1/databases/{database}/queryRuns SQL and returns rows as objects keyed by column name.
sqlstringRequired- One statement. Use
?(or?1,?2) as placeholders. paramsarray- Values for the placeholders, in order: strings, numbers, booleans or null.
batcharray- Instead of
sql: up to 100 objects of{ "sql", "params" }, run in order in one transaction.
/v1/databases/production/query{
"sql": "SELECT id, email FROM users WHERE id = ?",
"params": [1]
}{
"results": [
{
"rows": [{ "id": 1, "email": "ada@example.com" }],
"meta": { "duration": 0.18, "changes": 0, "lastRowId": 0, "rowsRead": 1, "rowsWritten": 0, "sizeAfter": 16384 }
}
]
}Batches
A batch runs its statements in order, in one transaction: if one fails, none of them take effect. Use it for writes that belong together, such as an order and its lines.
{
"batch": [
{ "sql": "INSERT INTO orders (user_id, total_cents) VALUES (?, ?)", "params": [1, 4999] },
{ "sql": "INSERT INTO order_lines (order_id, sku) VALUES (last_insert_rowid(), ?)", "params": ["TSHIRT-M"] },
{ "sql": "SELECT * FROM orders WHERE user_id = ?", "params": [1] }
]
}results then holds one entry per statement, in the same order.
Raw rows
/v1/databases/{database}/rawThe same request, with rows as arrays and the column names listed once.
Use raw rows for large results, or when two columns share a name (SELECT a.id, b.id ...), which objects cannot hold.
{
"results": [
{
"columns": ["id", "email"],
"rows": [[1, "ada@example.com"], [2, "linus@example.com"]],
"meta": { "duration": 0.2, "changes": 0, "lastRowId": 0, "rowsRead": 2, "rowsWritten": 0, "sizeAfter": 16384 }
}
]
}List databases
/v1/databasesThe databases the token can reach: every database of the account, or the one it is limited to.
The meta object
-
durationnumberTime the database spent on the statement, in milliseconds.
-
changesintegerRows inserted, updated or deleted.
-
lastRowIdintegerThe rowid of the last inserted row.
-
rowsReadintegerRows the database read, which is what you are billed for.
-
rowsWrittenintegerRows the database wrote, including index updates.
-
sizeAfterintegerThe database size in bytes after the statement.
Errors
{
"error": {
"code": "query_failed",
"message": "no such table: user: SQLITE_ERROR"
}
}- 400
invalid_request: the body is not valid JSON,sqlis missing, or there are more than 100 statements.query_failedwith a SQL message: the statement itself failed.- 401
unauthorized: no token, or the token is not valid (revoked, or mistyped).- 403
read_only: a read-only token sent a statement that would change data or schema.- 404
not_found: the database does not exist, or the token is limited to another database.- 429
- Too many requests in a short time. Wait a moment and retry with backoff.
- 502
unavailable: the database could not be reached. Safe to retry reads; check before retrying writes.
CORS
The API answers browsers from any origin, so a page can query it directly. Only do that with a read-only token limited to one database: anything sent to a browser can be read by its user.