SQLite Cloud

Weblite

Weblite consists of an autogenerated HTTP/JSON REST API for programmatically interacting with SQLite Cloud.

It is the simplest way to add a robust database backend to your application.

Overview

First, navigate to the Weblite panel from the left-hand navigation menu.

From here, you’ll find a list of APIs you can use to interact with your SQLite Cloud instance, including:

  • Services: Endpoints for health checks, metrics, and more.
  • Weblite: Endpoints for executing SQLiteCloudArrayType, and interacting with databases and tables.
  • Functions: Endpoints for executing SQLite functions.
  • Webhooks: Endpoints for creating and managing webhooks.
  • Files: Endpoints for uploading and downloading files.

Services

Services are endpoints for health checks, server information, and more.

Health Check

Example request:

curl -X 'GET' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/health' \
  -H 'accept: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'

Example response:

{
  "data": {
    "name": "@sqlitecloud/gateway",
    "version": "x.x.x",
    "project": "xxxxxxxxxx",
    "node": "xxxxxxxxxx",
    "hostname": "xxxxxxxxxx",
    "started": "YYYY-MM-DDTHH:mm:ss.sssZ",
    "uptime": "XXh:XXm:XXs"
  },
  "metadata": {
    "connectedMs": "XX",
    "executedMs": "X",
    "elapsedMs": "XX"
  }
}

Info

Example request:

curl -X 'GET' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/info' \
  -H 'accept: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'

Example response:

{
  "data": {
    "name": "@sqlitecloud/gateway",
    "version": "x.x.x",
    "project": "xxxxxxxxxx",
    "node": "xxxxxxxxxx",
    "hostname": "xxxxxxxxxx",
    "started": "YYYY-MM-DDTHH:mm:ss.sssZ",
    "uptime": "XXh:XXm:XXs",
    "drivers": {
      "name": "@sqlitecloud/drivers",
      "version": "x.x.x"
    },
    "runtime": {
      "name": "xxxxxxx",
      "version": "x.x.x",
      "path": "/path/to/runtime",
      "main": "/path/to/main/file"
    },
    "environment": {
      "events": true,
      "settings": true,
      "stats": true,
      "logs": true
    },
    "metrics": {
      "js_heap_size": 00000000,
      "js_heap_capacity": 00000000,
      "js_heap_object_count": 000000,
      "cpu_user": 00.0,
      "cpu_system": 0.00,
      "http_requests": 00000,
      "http_get": 00000,
      "http_completed": 00000,
      "http_options": 00,
      "ws_requests": 00,
      "ws_get": 00,
      "ws_completed": 00,
      "http_patch": 0,
      "http_post": 0
    },
    "internetAccess": true
  },
  "metadata": {
    "connectedMs": 00,
    "executedMs": 00,
    "elapsedMs": 00
  }
}

Stats

Example request:

curl -X 'GET' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/stats' \
  -H 'accept: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'

Example response:

{
  "data": {
    "physicalMemory": 0000000000,
    "bytesIn": 00000,
    "bytesOut": 000000,
    "cpuLoad": 0.0,
    "currentClients": 0,
    "currentMemory": 0000000,
    "maxClients": 0,
    "maxMemory": 0000000,
    "numCommands": 000,
    "numReads": 00,
    "numWrites": 0
  },
  "metadata": {
    "connectedMs": 00,
    "executedMs": 00,
    "elapsedMs": 00
  }
}

Weblite

Weblite are endpoints for executing SQLiteCloudArrayType, and interacting with databases and tables.

Run SQL queries on the node - GET

Example request:

sql_query="SELECT * FROM artists LIMIT 3"

encoded_query=$(printf '%s' "$sql_query" | jq -sRr @uri)

curl -X 'GET' \
  "https://<your-project-id>.sqlite.cloud:8090/v2/weblite/sql?sql=$<encoded_query>&database=<database-name>" \
  -H 'accept: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'

Example response:

{
  "data": [
    { "ArtistId": 1, "Name": "AC/DC" },
    { "ArtistId": 2, "Name": "Accept" },
    { "ArtistId": 3, "Name": "Aerosmith" }
  ],
  "metadata": {
    "connectedMs": "X",
    "executedMs": "XX",
    "elapsedMs": "XX",
    "database": "chinook.sqlite",
    "sql": "SELECT * FROM artists LIMIT 3",
    "version": "X",
    "numberOfRows": "X",
    "numberOfColumns": "X",
    "columns": [
      {
        "name": "ArtistId",
        "type": "INTEGER",
        "database": "main",
        "table": "artists",
        "column": "ArtistId",
        "notNull": 1,
        "primaryKey": 1,
        "autoIncrement": 1
      },
      {
        "name": "Name",
        "type": "NVARCHAR(120)",
        "database": "main",
        "table": "artists",
        "column": "Name",
        "notNull": 0,
        "primaryKey": 0,
        "autoIncrement": 0
      }
    ]
  }
}

Run SQL queries on the node - POST

Example request:

sql="SELECT * FROM albums LIMIT 5"

curl -X 'POST' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/weblite/sql \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'
  -d "{\"sql\":\"$sql\", \"database\": \"chinook.sqlite\"}"

Example response:

{
  "data": [
    {
      "AlbumId": 1,
      "Title": "For Those About To Rock We Salute You",
      "ArtistId": 1
    },
    { "AlbumId": 2, "Title": "Balls to the Wall", "ArtistId": 2 },
    { "AlbumId": 3, "Title": "Restless and Wild", "ArtistId": 2 },
    { "AlbumId": 4, "Title": "Let There Be Rock", "ArtistId": 1 },
    { "AlbumId": 5, "Title": "Big Ones", "ArtistId": 3 }
  ],
  "metadata": {
    "connectedMs": "XX",
    "executedMs": "XX",
    "elapsedMs": "XX",
    "database": "chinook.sqlite",
    "sql": "SELECT * FROM albums LIMIT 5",
    "version": "X",
    "numberOfRows": "X",
    "numberOfColumns": "X",
    "columns": [
      {
        "name": "AlbumId",
        "type": "INTEGER",
        "database": "main",
        "table": "albums",
        "column": "AlbumId",
        "notNull": 1,
        "primaryKey": 1,
        "autoIncrement": 1
      },
      {
        "name": "Title",
        "type": "NVARCHAR(160)",
        "database": "main",
        "table": "albums",
        "column": "Title",
        "notNull": 1,
        "primaryKey": 0,
        "autoIncrement": 0
      },
      {
        "name": "ArtistId",
        "type": "INTEGER",
        "database": "main",
        "table": "albums",
        "column": "ArtistId",
        "notNull": 1,
        "primaryKey": 0,
        "autoIncrement": 0
      }
    ]
  }
}

List databases on the node

Example request:

curl -X 'GET' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/weblite/databases' \
  -H 'accept: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'

Example response:

{
  "data": [
    {
      "type": "database",
      "name": "chinook.sqlite",
      "size": "XXXXXX",
      "connections": "X",
      "encryption": null,
      "backup": "X",
      "nread": "X",
      "nwrite": "X",
      "inbytes": "X",
      "outbytes": "X",
      "fragmentation": "X.XX",
      "pagesize": "XXXX",
      "encoding": "UTF-8",
      "status": "X"
    },
  ],
  "metadata": {
    "connectedMs": "XX",
    "executedMs": "X",
    "elapsedMs": "XX"
  }
}

List all tables in a database

Example request:

curl -X 'GET' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/weblite/<database-name>/tables' \
  -H 'accept: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'

Example response:

{
  "data": [
    {
      "type": "table",
      "name": "albums"
    },
    {
      "type": "table",
      "name": "artists"
    }
  ],
  "metadata": {
    "connectedMs": "XX",
    "executedMs": "X",
    "elapsedMs": "XX"
  }
}

List all columns in a table

Example request:

curl -X 'GET' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/weblite/<database-name>/<table-name>/columns' \
  -H 'accept: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'

Example response:

{
  "data": [
    {
      "cid": 0,
      "name": "AlbumId",
      "type": "INTEGER",
      "notnull": 1,
      "dflt_value": null,
      "pk": 1
    },
    {
      "cid": 1,
      "name": "Title",
      "type": "NVARCHAR(160)",
      "notnull": 1,
      "dflt_value": null,
      "pk": 0
    },
    {
      "cid": 2,
      "name": "ArtistId",
      "type": "INTEGER",
      "notnull": 1,
      "dflt_value": null,
      "pk": 0
    }
  ],
  "metadata": {
    "connectedMs": "XX",
    "executedMs": "X",
    "elapsedMs": "XX"
  }
}

Download database from the node

Example request:

curl -X 'GET' -o chinook.sqlite \
  'https://<your-project-id>.sqlite.cloud:8090/v2/weblite/<database-name>' \
  -H 'accept: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'

Example response:

A binary file representing the database, eg.chinook.sqlite.

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  866k  100  866k    0     0  1146k      0 --:--:-- --:--:-- --:--:-- 1145k

Upload new database to the node

Example request:

curl -X 'POST' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/weblite/<new-database-name>' \
  -H 'Content-Type: application/octet-stream'  \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'
  --data-binary @<path-to-file>

Example response:

{
  "data": {
    "name": "newchinook.sqlite",
    "size": "XXXXXX",
    "connections": "X",
    "encryption": null,
    "backup": "X",
    "nread": "X",
    "nwrite": "X",
    "inbytes": "X",
    "outbytes": "X",
    "fragmentation": "X.XX",
    "pagesize": "XXXX",
    "encoding": "UTF-8",
    "status": "X"
  },
  "metadata": {
    "connectedMs": "XX",
    "executedMs": "X",
    "elapsedMs": "XX"
  }
}

Replace existing database on the node

Example request:

curl -X 'PATCH' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/weblite/<database-name>' \
  -H 'Content-Type: application/octet-stream'  \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'
  --data-binary @<path-to-file>

Example response:

{
  "data": {
    "name": "chinook.sqlite",
    "size": "XXXXXX",
    "connections": "X",
    "encryption": null,
    "backup": "X",
    "nread": "X",
    "nwrite": "X",
    "inbytes": "X",
    "outbytes": "X",
    "fragmentation": "X.XX",
    "pagesize": "XXXX",
    "encoding": "UTF-8",
    "status": "X"
  },
  "metadata": {
    "connectedMs": "XX",
    "executedMs": "X",
    "elapsedMs": "XX"
  }
}

Delete database from the node

Example request:

curl -X 'DELETE' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/weblite/<database-name>' \
  -H 'accept: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'

Example response:

{
  "data": "OK",
  "metadata": {
    "connectedMs": "XX",
    "executedMs": "X",
    "elapsedMs": "XX"
  }
}

Select all rows from a table

Example request:

curl -X 'GET' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/weblite/<database-name>/<table-name>' \
  -H 'accept: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'

Example response:

{
  "data": [
    {
      "AlbumId": 1,
      "Title": "For Those About To Rock We Salute You",
      "ArtistId": 1
    },
    {
      "AlbumId": 2,
      "Title": "Balls to the Wall",
      "ArtistId": 2
    }
  ],
  "metadata": {
    "connectedMs": "XX",
    "executedMs": "X",
    "elapsedMs": "XX"
  }
}

Insert one or more rows into a table

Example request:

curl -X 'POST' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/weblite/<database-name>/<table-name>' \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'
  -d '[{"Name": "Il Divo"}, {"Name": "Natalia LaFourcade"}]'

Example response:

{
  "data": "OK",
  "metadata": {
    "connectedMs": "XX",
    "executedMs": "X",
    "elapsedMs": "XX"
  }
}

Delete all rows in a table (or only those rows specified in search string parameters)

Example request:

curl -X 'DELETE' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/weblite/<database-name>/<table-name>' \
  -H 'accept: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'

Example response:

{
  "data": "OK",
  "metadata": {
    "connectedMs": "XX",
    "executedMs": "X",
    "elapsedMs": "XX"
  }
}

Select single row by row id

Example request:

curl -X 'GET' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/weblite/<database-name>/<table-name>/<row-id>' \
  -H 'accept: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'

Example response:

{
  "data": {
    "ArtistId": 10,
    "Name": "Billy Cobham"
  },
  "metadata": {
    "connectedMs": "XX",
    "executedMs": "X",
    "elapsedMs": "XX"
  }
}

Insert specific single row into a table

Example request:

curl -X 'POST' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/weblite/<database-name>/<table-name>/<row-id>' \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'
  -d '{"Name": "Alessandro Safina"}'

Example response:

{
  "data": {
    "type": "XX",
    "index": "X",
    "lastID": "XXX",
    "changes": 1,
    "totalChanges": 1,
    "finalized": 1,
    "rowId": "X"
  },
  "metadata": {
    "connectedMs": "XX",
    "executedMs": "X",
    "elapsedMs": "XX"
  }
}

Update specific row by row id

Example request:

curl -X 'PATCH' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/weblite/<database-name>/<table-name>/<row-id>' \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'
  -d '{"title": "TEST"}'

Example response:

{
  "data": {
    "type": "XX",
    "index": "X",
    "lastID": "XXX",
    "changes": 1,
    "totalChanges": 1,
    "finalized": 1,
    "rowId": "X"
  },
  "metadata": {
    "connectedMs": "XX",
    "executedMs": "X",
    "elapsedMs": "XX"
  }
}

Delete specific row in a table

Example request:

curl -X 'DELETE' \
  'https://<your-project-id>.sqlite.cloud:8090/v2/weblite/<database-name>/<table-name>/<row-id>' \
  -H 'accept: application/json' \
  -H 'Authorization: Bearer sqlitecloud://<your-project-id>.sqlite.cloud:8860?apikey=<your-api-key>'

Example response:

{
  "data": {
    "type": "XX",
    "index": "X",
    "lastID": "X",
    "changes": 1,
    "totalChanges": 1,
    "finalized": 1,
    "rowId": "X"
  },
  "metadata": {
    "connectedMs": "XX",
    "executedMs": "X",
    "elapsedMs": "XX"
  }
}