Skip to content

weblite.mdx

Latest commit

 

History

History
752 lines (643 loc) · 15.4 KB

weblite.mdx

File metadata and controls

752 lines (643 loc) · 15.4 KB
title Weblite
description With Weblite, adding robust database capabilities to your site is as simple as adding Google Analytics.
category platform
status publish
slug 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"
  }
}