SQLite REST

A lightweight, high-performance REST API for SQLite databases. Expose CRUD operations and database metadata over HTTP with minimal configuration.

Features

Full CRUD Operations: Create, read, update, and delete records via REST endpoints
Metadata API: Explore database structure, table schemas, and relationships
SQL Execution: Run arbitrary SQL queries with security controls
Filtering & Pagination: Filter records with SQL or JSON syntax, with pagination support
Authentication: Basic auth support for securing your API
Cross-Platform: Available for Windows, macOS, and Linux (including ARM support)
Docker Support: Easy deployment with Docker
Minimal Footprint: Small binary size and low memory usage

Quick Start

# Download and run (Linux/macOS)
curl -L https://github.com/paradoxe35/sqlite-rest/releases/latest/download/sqlite-rest_v1.1.0_linux-amd64.tar.gz | tar xz
./sqlite-rest

# Or with Docker
docker run -p 8080:8080 -v "$(pwd)"/data.sqlite:/app/data/data.sqlite:rw ghcr.io/paradoxe35/sqlite-rest

Installation

From releases page

Download the binary for your platform from the releases page.

# Linux/macOS
curl -L https://github.com/paradoxe35/sqlite-rest/releases/latest/download/sqlite-rest_v1.1.0_linux-amd64.tar.gz | tar xz
chmod +x sqlite-rest
./sqlite-rest

# Windows (PowerShell)
Invoke-WebRequest -Uri "https://github.com/paradoxe35/sqlite-rest/releases/latest/download/sqlite-rest_v1.1.0_windows-amd64.zip" -OutFile "sqlite-rest.zip"
Expand-Archive -Path "sqlite-rest.zip" -DestinationPath "."
.\sqlite-rest.exe

From source

# Clone the repository
git clone https://github.com/paradoxe35/sqlite-rest.git
cd sqlite-rest

# Build the binary
go build -o sqlite-rest ./cmd/sqlite-rest.go

# Run the server
./sqlite-rest

From Docker

# Pull and run the image
docker run -p 8080:8080 -v "$(pwd)"/data.sqlite:/app/data/data.sqlite:rw ghcr.io/paradoxe35/sqlite-rest

# Or with docker-compose
docker-compose up -d

CLI Usage

# Show help
sqlite-rest --help

# Show version
sqlite-rest --version

# Run with custom port and database path
sqlite-rest -p 3000 -f ./path/to/database.sqlite

# Run with default settings (port 8080, database at ./data/data.sqlite)
sqlite-rest

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:

Create a docker-compose.yml file:

version: "3.7"

services:
  sqlite-rest:
    image: ghcr.io/paradoxe35/sqlite-rest
    container_name: sqlite-rest
    restart: unless-stopped
    ports:
      - "8080:8080"
    volumes:
      - sqlite-data:/app/data:rw  # Mount a directory for the database
    environment:
      - SQLITE_REST_USERNAME=admin
      - SQLITE_REST_PASSWORD=secret
      # Optional: Customize dangerous operations
      - SQLITE_REST_DANGEROUS_OPS=DROP TABLE,DELETE FROM
    healthcheck:
      test: ["CMD", "wget", "--spider", "-q", "http://localhost:8080/__/health"]
      interval: 30s
      timeout: 10s
      retries: 3
      start_period: 5s

volumes:
  sqlite-data:
    driver: local

Then run:

docker-compose up -d

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
ATTACH DATABASE
DETACH DATABASE

Note: PRAGMA queries are now allowed by default as they can return useful data.

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.

Example of using PRAGMA to get table information:

$ curl -X OPTIONS -H "Content-Type: application/json" -d '{"query": "PRAGMA table_info(cats)"}' localhost:8080/__/exec

{
  "status": "success",
  "type": "pragma",
  "rows": [
    {
      "cid": 0,
      "name": "id",
      "type": "INTEGER",
      "notnull": 0,
      "dflt_value": null,
      "pk": 1
    },
    {
      "cid": 1,
      "name": "name",
      "type": "TEXT",
      "notnull": 0,
      "dflt_value": null,
      "pk": 0
    },
    {
      "cid": 2,
      "name": "paw",
      "type": "INTEGER",
      "notnull": 0,
      "dflt_value": null,
      "pk": 0
    }
  ],
  "count": 3
}

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.1.0"
}

Credits

This project was inspired by and builds upon jonamat/sqlite-rest. See CREDITS.md for more details.

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Fork the repository
Create your feature branch (git checkout -b feature/amazing-feature)
Commit your changes (git commit -m 'Add some amazing feature')
Push to the branch (git push origin feature/amazing-feature)
Open a Pull Request

License

This project is licensed under the MIT License - see the LICENSE file for details.

Name		Name	Last commit message	Last commit date
Latest commit History 27 Commits
.github/workflows		.github/workflows
cmd		cmd
pkg		pkg
scripts		scripts
.dockerignore		.dockerignore
.gitignore		.gitignore
CHANGELOG.md		CHANGELOG.md
CREDITS.md		CREDITS.md
Dockerfile		Dockerfile
LICENSE		LICENSE
Makefile		Makefile
README.md		README.md
go.mod		go.mod
go.sum		go.sum
test_exec.go		test_exec.go

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Repository files navigation

SQLite REST

Features

Quick Start

Installation

From releases page

From source

From Docker

CLI Usage

Authentication

API

Core API

Metadata API

Utility API

Search all records

Get record by id

Create record

Update record

Delete record

Execute arbitrary query

List all tables

Get table schema

Get foreign keys

Get database info

Health check

API version

Credits

Contributing

License

About

Uh oh!

Releases 1

Packages

Uh oh!

Uh oh!

Languages

License

paradoxe35/sqlite-rest

Folders and files

Latest commit

History

Repository files navigation

SQLite REST

Features

Quick Start

Installation

From releases page

From source

From Docker

CLI Usage

Authentication

API

Core API

Metadata API

Utility API

Search all records

Get record by id

Create record

Update record

Delete record

Execute arbitrary query

List all tables

Get table schema

Get foreign keys

Get database info

Health check

API version

Credits

Contributing

License

About

Topics

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases 1

Packages 0

Uh oh!

Uh oh!

Languages

Packages