sqlite-rest

sqlite-rest

Expose CRUD operations for SQLite database over HTTP via REST API.

Installation

From releases page

Download the binary for your platform from the releases page

From source

$ go get github.com/paradoxe35/sqlite-rest

From Docker

Authentication

SQLite REST supports Basic Authentication. To enable it, set the following environment variables:

• SQLITE_REST_USERNAME: The username for Basic Authentication
• SQLITE_REST_PASSWORD: The password for Basic Authentication

If both variables are set, Basic Authentication will be enabled. If either variable is not set, authentication will be disabled.

Example with Docker:

$ docker run -p 8080:8080 -v "$(pwd)"/data.sqlite:/app/data/data.sqlite:rw \
  -e SQLITE_REST_USERNAME=admin \
  -e SQLITE_REST_PASSWORD=secret \
  ghcr.io/paradoxe35/sqlite-rest

Example with Docker Compose:

version: "3.7"

services:
  sqlite-rest:
    image: ghcr.io/paradoxe35/sqlite-rest
    ports:
      - "8080:8080"
    volumes:
      - ./data.sqlite:/app/data/data.sqlite:rw # OR sqlite-data:/app/data
    environment:
      - SQLITE_REST_USERNAME=admin
      - SQLITE_REST_PASSWORD=secret

# volumes:
#   sqlite-data:
#     driver: local

API

Core API

Search all records - GET /:table
Get record by id - GET /:table/:id
Create record - POST /:table
Update record by id - PATCH /:table/:id
Delete record by id - DELETE /:table/:id
Execute arbitrary query - OPTIONS /__/exec

Metadata API

List all tables - GET /__/tables
Get table schema - GET /__/tables/:table
Get foreign keys - GET /__/tables/:table/foreign-keys
Get database info - GET /__/db

Utility API

Health check - GET /__/health
API version - GET /__/version

Search all records

Get all record in a table.

Request: GET /:table

Basic example:

$ curl localhost:8080/cats

{
  "data": [
    { "id": 1, "name": "Tequila", "paw": 4 },
    { "id": 2, "name": "Whisky", "paw": 3 }
  ],
  "limit": null,
  "offset": null,
  "total_rows": 2
}

Optional parameters:

• offset: Offset the number of records returned. Default: 0
• limit: Limit the number of records returned. Default: not set
• order_by: Order the records by a column. Default: id
• order_dir: Order the records by a column. Default: asc
• columns: Select only the specified columns. Default: *
• filters_raw: Filter the records by a raw SQL query. Must be URIescaped.
• filters: Filter the records by a JSON object. Must be URIescaped.

Filters:

Can be passed as a JSON object or as a raw WHERE clause. The JSON object is more convenient to use, the raw query is more flexible. Both must be URIescaped. Cannot be used together. Filters provided by filters param are joined with AND operator.

Example with filters_raw parameter in cURL:

$ curl "localhost:8080/cats?offset=10&limit=2&order_by=name&order_dir=desc&filters_raw=paw%20%3D%204%20OR%20name%20LIKE%20'%25Tequila%25'"

{
  "data": [
    { "id": 10, "name": "Tequila", "paw": 4 },
    { "id": 11, "name": "Cognac", "paw": 4 }
  ],
  "limit": 2,
  "offset": 10,
  "total_rows": 2
}

Example with filters_raw parameter in JS:

const filters = "paw = 4 OR name LIKE '%Tequila%'"

fetch(`http://localhost:8080/cats?filters_raw=${encodeURIComponent(filters)}`)

Example with filters parameter in JS:

const filters = [
  {
    "column": "paw",
    "operator": "=",
    "value": 4
  },
  {
    "column": "name",
    "operator": "LIKE",
    "value": "%Tequila%"
  }
]

fetch(`http://localhost:8080/cats?filters=${encodeURIComponent(JSON.stringify(filters))}`)

Get record by id

Get a record by its id in a table.

Request: GET /:table/:id

Example:

$ curl localhost:8080/cats/1

{
  "id": 1,
  "name": "Tequila",
  "paw": 4
}

Optional parameters:

• columns: Select only the specified columns. Default: *

Example with parameters:

$ curl localhost:8080/cats/1?columns=name,paw

{
  "name": "Tequila",
  "paw": 4
}

Create record

Create a record in a table.

Request: POST /:table

Example:

$ curl -X POST -H "Content-Type: application/json" -d '{"name": "Tequila", "paw": 4}' localhost:8080/cats

{
  "id": 1,
}

Update record

Update a record in a table.
⚠️ The update is a PATCH, not a PUT. Only the fields passed in the body will be updated. The other fields will be left untouched.

Request: PATCH /:table/:id

Example:

$ curl -X PATCH -H "Content-Type: application/json" -d '{"name": "Tequila", "paw": 4}' localhost:8080/cats/1

{
  "id": 1,
}

Delete record

Delete a record in a table.

Request: DELETE /:table/:id

Example:

$ curl -X DELETE localhost:8080/cats/1

{
  "id": 1,
}

Execute arbitrary query

Execute an arbitrary query. ⚠️ Experimental

Request: OPTIONS /__/exec

This endpoint is protected by authentication when enabled. It allows executing SQL queries and returns the results.

For security reasons, the following operations are blocked by default:

• DROP TABLE
• DROP DATABASE
• DELETE FROM
• TRUNCATE TABLE
• ALTER TABLE
• PRAGMA
• ATTACH DATABASE
• DETACH DATABASE

You can customize the list of dangerous operations by setting the SQLITE_REST_DANGEROUS_OPS environment variable. This should be a comma-separated list of SQL operations to block. For example:

SQLITE_REST_DANGEROUS_OPS="DROP TABLE,DELETE FROM"

To allow all operations (use with caution), set the variable to an empty string:

SQLITE_REST_DANGEROUS_OPS=""

Example of creating a table:

$ curl -X OPTIONS -H "Content-Type: application/json" -d '{"query": "CREATE TABLE cats (id INTEGER PRIMARY KEY, name TEXT, paw INTEGER)"}' localhost:8080/__/exec

{
  "status": "success",
  "type": "create",
  "rows_affected": 0
}

Example of inserting data:

$ curl -X OPTIONS -H "Content-Type: application/json" -d '{"query": "INSERT INTO cats (name, paw) VALUES (\"Tequila\", 4)"}' localhost:8080/__/exec

{
  "status": "success",
  "type": "insert",
  "rows_affected": 1
}

Example of selecting data:

$ curl -X OPTIONS -H "Content-Type: application/json" -d '{"query": "SELECT * FROM cats"}' localhost:8080/__/exec

{
  "status": "success",
  "type": "select",
  "rows": [
    {
      "id": 1,
      "name": "Tequila",
      "paw": 4
    }
  ],
  "count": 1
}

Example of listing tables:

$ curl -X OPTIONS -H "Content-Type: application/json" -d '{"query": "SHOW TABLES"}' localhost:8080/__/exec

{
  "status": "success",
  "type": "show_tables",
  "tables": ["cats", "dogs", "birds"],
  "rows": [
    {"table_name": "cats"},
    {"table_name": "dogs"},
    {"table_name": "birds"}
  ],
  "count": 3
}

You can also use LIST TABLES as an alternative to SHOW TABLES.

List all tables

Get a list of all tables in the database.

Request: GET /__/tables

Example:

$ curl localhost:8080/__/tables

{
  "status": "success",
  "tables": ["cats", "dogs", "birds"],
  "count": 3
}

Get table schema

Get the schema of a specific table.

Request: GET /__/tables/:table

Example:

$ curl localhost:8080/__/tables/cats

{
  "status": "success",
  "table": "cats",
  "schema": [
    {
      "cid": 0,
      "name": "id",
      "type": "INTEGER",
      "notnull": false,
      "default_val": null,
      "pk": 1
    },
    {
      "cid": 1,
      "name": "name",
      "type": "TEXT",
      "notnull": false,
      "default_val": null,
      "pk": 0
    },
    {
      "cid": 2,
      "name": "paw",
      "type": "INTEGER",
      "notnull": false,
      "default_val": null,
      "pk": 0
    }
  ]
}

Get foreign keys

Get the foreign key relationships for a specific table.

Request: GET /__/tables/:table/foreign-keys

Example:

$ curl localhost:8080/__/tables/cats/foreign-keys

{
  "status": "success",
  "table": "cats",
  "foreign_keys": [
    {
      "id": 0,
      "seq": 0,
      "table": "owners",
      "from": "owner_id",
      "to": "id",
      "on_update": "NO ACTION",
      "on_delete": "NO ACTION",
      "match": "NONE"
    }
  ]
}

Get database info

Get general information about the database.

Request: GET /__/db

Example:

$ curl localhost:8080/__/db

{
  "status": "success",
  "sqlite_version": "3.36.0",
  "table_count": 3,
  "tables": ["cats", "dogs", "birds"],
  "database_size": 16384,
  "database_path": "./data/data.sqlite"
}

Health check

Check if the API is healthy.

Request: GET /__/health

Example:

$ curl localhost:8080/__/health

{
  "status": "success",
  "message": "API is healthy"
}

API version

Get the API version.

Request: GET /__/version

Example:

$ curl localhost:8080/__/version

{
  "status": "success",
  "version": "1.0.0"
}

License

MIT