Database
This section requires authentication.
Please refer to the Authentication section for more details.
Overview
The Database API lets mobile SDK consumers execute SQL queries against a SQLite-compatible database linked to their app via the AppAmbit dashboard. Each app can have one linked database at a time; a single database can be shared across multiple apps belonging to the same team.
Authentication
All endpoints require a consumer Bearer token obtained from GET /api/consumer/token. The token is scoped to an app via the app_key claim.
| Header | Value |
|---|---|
| Authorization | Bearer {YOUR_AUTH_KEY} |
| Content-Type | application/json |
| Accept | application/json |
Base URL
/api/db
Execute Single Query
Execute a single SQL statement against the app's linked database.
POST/api/db/query
Body Parameters
Required Parameters
| Field | Type | Description |
|---|---|---|
sql |
string |
The SQL statement to execute |
Optional Parameters
| Field | Type | Description |
|---|---|---|
params |
array |
Positional or named parameters for the statement |
Example Request
{
"sql": "SELECT id, name FROM users WHERE active = ?",
"params": [1]
}
Example Response
{
"results": [
{
"columns": ["id", "name"],
"rows": [[1, "Alice"], [2, "Bob"]],
"rows_read": 2,
"rows_written": 0
}
],
"request_id": "550e8400-e29b-41d4-a716-446655440000"
}
Response Fields
Top-level:
| Field | Type | Description |
|---|---|---|
results |
array |
Array with one result object per executed statement |
request_id |
string (UUID) |
Unique identifier for this request, useful for tracing |
Result object:
| Field | Type | Description |
|---|---|---|
columns |
string[] |
Column names in result order |
rows |
array[] |
Array of row arrays; values are typed (integer, float, string, or null) |
rows_read |
integer |
Number of rows read from storage |
rows_written |
integer |
Number of rows written to storage |
error |
string |
Present only when the statement produced a database-level error; HTTP status remains 200 |
Execute Batch Statements
Execute multiple SQL statements in a single request. Optionally wraps them in a transaction.
POST/api/db/batch
Body Parameters
Required Parameters
| Field | Type | Description |
|---|---|---|
statements |
array |
Array of statement objects (minimum 1) |
statements[].sql |
string |
The SQL statement |
Optional Parameters
| Field | Type | Description |
|---|---|---|
statements[].params |
array |
Positional or named parameters for this statement |
transaction |
boolean |
When true, wraps all statements in BEGIN / COMMIT. Default: false |
Example Request
{
"statements": [
{
"sql": "INSERT INTO users (name, email) VALUES (?, ?)",
"params": ["Alice", "alice@example.com"]
},
{
"sql": "UPDATE stats SET user_count = user_count + 1"
}
],
"transaction": true
}
Example Response
{
"results": [
{
"columns": [],
"rows": [],
"rows_read": 0,
"rows_written": 1
},
{
"columns": [],
"rows": [],
"rows_read": 1,
"rows_written": 1
}
],
"request_id": "550e8400-e29b-41d4-a716-446655440001"
}
Using transaction: true
BEGIN and COMMIT are injected server-side — do not include them manually in statements. If any statement contains a forbidden query, the entire batch is rejected before any statement reaches the database.
Error Responses
All errors return a JSON object with error (machine-readable code) and message (human-readable description).
403 Forbidden — Database Not Linked
The app has no database linked. Link one from the AppAmbit dashboard.
{
"error": "database_not_linked",
"message": "This app isn't linked to a database. Link it in your AppAmbit dashboard."
}
503 Service Unavailable — Database Unavailable
The linked database exists but is not yet active (e.g., still provisioning).
{
"error": "database_unavailable",
"message": "The database is not yet available. Please try again shortly."
}
403 Forbidden — Forbidden SQL
The SQL statement contains a blocked keyword or does not start with an allowed prefix.
{
"error": "forbidden_query",
"message": "Forbidden SQL statement: ATTACH DATABASE \"evil.db\" AS evil"
}
500 Internal Server Error — Query Execution Error
An unexpected error occurred when communicating with the database backend.
{
"error": "query_error",
"message": "<error detail from the database>"
}
422 Unprocessable Entity — Validation Error
Missing required fields. Standard Laravel validation error format.
{
"message": "The sql field is required.",
"errors": {
"sql": ["The sql field is required."]
}
}
SQL Restrictions
The API enforces a server-side allowlist and blocklist before forwarding SQL to the database.
Allowed Statement Prefixes
SELECT, INSERT, UPDATE, DELETE,
CREATE TABLE, CREATE INDEX,
ALTER TABLE,
DROP TABLE, DROP INDEX,
BEGIN, COMMIT, ROLLBACK
Blocked Keywords
The following keywords are blocked anywhere in the statement:
ATTACH, DETACH, PRAGMA, VACUUM,
LOAD_EXTENSION, SAVEPOINT, RELEASE
Statements not starting with an allowed prefix, or containing a blocked keyword, return 403 forbidden_query without touching the database.
Data Types
The database uses SQLite's type system. The type you declare for a column sets its type affinity, which is the kind of value SQLite tries to store in that column. Every value is stored as one of five storage classes: NULL, INTEGER, REAL, TEXT, or BLOB.
Column Types
When you create a table or add a column in Database Studio in the AppAmbit dashboard, you pick one of these five types. Use the same names in CREATE TABLE and ALTER TABLE statements.
| Type | Stores | Use it for |
|---|---|---|
INTEGER |
Signed whole numbers, up to 8 bytes | IDs, counts, booleans, Unix timestamps |
REAL |
8-byte floating-point numbers | Prices, ratings, measurements, coordinates |
TEXT |
UTF-8 strings | Names, descriptions, ISO 8601 dates, JSON documents |
NUMERIC |
Integers or reals; numeric text such as '42' is converted to a number |
Values that can be either whole or fractional |
BLOB |
Bytes, stored exactly as written | Binary data written directly in SQL |
Any column without NOT NULL can also hold NULL. Database Studio defaults new columns to TEXT and marks them nullable.
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
price REAL NOT NULL,
active INTEGER NOT NULL DEFAULT 1,
attributes TEXT,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
)
Primary Keys
A column declared exactly INTEGER PRIMARY KEY gets its value automatically: leave it out of an INSERT and SQLite assigns one more than the largest existing ID. Other spellings such as INT PRIMARY KEY or BIGINT PRIMARY KEY don't get automatic IDs.
The Auto-increment toggle in Database Studio adds AUTOINCREMENT, which also guarantees that IDs of deleted rows are never reused. It applies only when the table has a single primary key column of type INTEGER; otherwise the toggle has no effect.
Other Type Names
In SQL you can declare any type name. SQLite maps it to an affinity by checking these rules in order:
| If the declared type contains | Affinity | Examples |
|---|---|---|
INT |
INTEGER |
INT, BIGINT, SMALLINT, TINYINT |
CHAR, CLOB, or TEXT |
TEXT |
VARCHAR(255), CHARACTER(20), CLOB |
BLOB, or no type at all |
BLOB |
BLOB, a column declared without a type |
REAL, FLOA, or DOUB |
REAL |
DOUBLE, DOUBLE PRECISION, FLOAT |
| None of the above | NUMERIC |
DECIMAL(10,2), BOOLEAN, DATE, DATETIME |
The rules match fragments of the name, not whole words: FLOA matches FLOAT, and DOUB matches DOUBLE. Because the INT rule is checked first, a name like FLOATING POINT gets INTEGER affinity, since it contains INT.
Lengths and precision are ignored: VARCHAR(10) accepts longer strings, and DECIMAL(10,2) doesn't round values. BOOLEAN, DATE, and DATETIME are accepted as names but aren't separate types, so follow the conventions below.
Booleans, Dates, and JSON
SQLite has no dedicated boolean, date, or JSON type. Store these values with the following conventions:
| Data | Column type | Store as |
|---|---|---|
| Boolean | INTEGER |
1 for true and 0 for false. SQL also accepts the keywords TRUE and FALSE, which are stored as 1 and 0. |
| Date and time | TEXT |
An ISO 8601 string in UTC, such as 2026-10-01 14:30:00 |
| Date and time (alternative) | INTEGER |
Unix time in seconds |
| JSON object or array | TEXT |
The serialized JSON string. Read fields with json_extract(). |
| Currency that must add up exactly | INTEGER |
The amount in the smallest unit, such as cents. REAL and NUMERIC use floating point. |
| Binary data sent from your app | TEXT |
A Base64-encoded string, because requests and responses are JSON, which has no binary type |
Use one date format everywhere so sorting and comparisons stay correct. CURRENT_TIMESTAMP and datetime('now') both produce YYYY-MM-DD HH:MM:SS in UTC, and ISO 8601 strings in that format sort in chronological order.
SELECT name, json_extract(attributes, '$.color') AS color
FROM products
WHERE active = 1
AND created_at >= datetime('now', '-7 days')
Default values in Database Studio
Database Studio saves the Default value you type as a literal. INTEGER columns convert it to a whole number, REAL and NUMERIC columns convert it to a decimal number, and the other types store it as text. Typing CURRENT_TIMESTAMP stores the text CURRENT_TIMESTAMP, not the current time. To default a column to the current time, create the table with SQL and declare DEFAULT CURRENT_TIMESTAMP, as in the products example above.
Values in Responses
Each value in rows is returned according to how that value is stored, not the column's declared type:
| Stored as | Returned as |
|---|---|
INTEGER |
JSON number without a fraction, such as 42 |
REAL |
JSON number, such as 9.99 |
TEXT |
JSON string |
NULL |
JSON null |
SQLite converts a value to the column's affinity when it can do so without losing information, so '42' written to an INTEGER column comes back as the number 42. A value it can't convert keeps its original type: 'abc' in an INTEGER column comes back as the string "abc".
Booleans come back as 1 or 0, and dates come back as strings or numbers. Convert them in your app.
INTEGER values can be as large as 263 − 1, but JavaScript numbers are exact only up to 253 − 1 (9,007,199,254,740,991). If you read the database from JavaScript, as in React Native or Cloud Code, store IDs that can exceed that limit as TEXT.
Strict Tables
By default, SQLite keeps a value it can't convert instead of rejecting it. To make the database reject values of the wrong type, add STRICT after the column list:
CREATE TABLE scores (
id INTEGER PRIMARY KEY,
player TEXT NOT NULL,
points INTEGER NOT NULL
) STRICT
In a strict table, every column needs a type, and the only allowed types are INT, INTEGER, REAL, TEXT, BLOB, and ANY. NUMERIC isn't allowed. An insert or update with a value that can't be converted to the column's type fails with a database error. See Per-Statement Errors.
Database Lifecycle
A database linked to an app transitions through the following statuses:
| Status | Queryable | Description |
|---|---|---|
pending |
No | Provisioning requested, not yet started |
provisioning |
No | Being created in the backend |
active |
Yes | Ready to accept queries |
suspended |
No | Temporarily suspended |
deleting |
No | Deletion in progress |
deleted |
No | Permanently removed |
Only active databases accept queries. All other statuses return 503 database_unavailable.
Per-Statement Errors
Database-level errors (e.g., syntax errors, constraint violations) are surfaced inside the results array with HTTP 200. Always inspect results[n].error on every result when doing error-sensitive work.
{
"results": [
{
"columns": [],
"rows": [],
"rows_read": 0,
"rows_written": 0,
"error": "syntax error near \"SELECT\""
}
],
"request_id": "550e8400-e29b-41d4-a716-446655440002"
}
Notes
- The database uses SQLite-compatible syntax. Follow the SQLite SQL dialect when writing queries.
- One database can be shared across multiple apps belonging to the same team; each app can have only one linked database at a time.
- Row values come back as JSON numbers, strings, or
null, depending on how each value is stored. See Values in Responses.