SheetDB REST API Server

A FastAPI-based REST API server that exposes SheetDB as a universal backend for any frontend framework.

Overview

The SheetDB REST API server provides HTTP endpoints for accessing Google Sheets as a relational database. It's perfect for:

Building frontend applications (React, Vue, Angular, etc.)
Mobile app backends
Microservices architecture
Cross-platform data access

Features

✅ RESTful API - Standard HTTP endpoints
✅ CRUD Operations - Create, Read, Update, Delete
✅ Query Filtering - Filter, sort, paginate results
✅ Authentication - API key support
✅ CORS Support - Enable cross-origin requests
✅ Auto Documentation - Swagger/OpenAPI docs
✅ Fast - Built on FastAPI and DuckDB caching

Installation

Prerequisites

# Install core SheetDB library first
pip install sheetdb

Install Server Dependencies

pip install fastapi>=0.104.0
pip install uvicorn[standard]>=0.24.0

From Source (Current)

# Clone the monorepo
git clone https://github.com/yourusername/sheetdb.git
cd sheetdb

# Install dependencies
pip install -e .
pip install fastapi uvicorn[standard]

Future (Separate Package)

Once published as a separate package:

pip install sheetdb-server

Quick Start

1. Configure Environment

Create .env file:

# Operating mode
MODE=authenticated

# For authenticated mode
CREDENTIALS_PATH=path/to/credentials.json
SPREADSHEET_ID=your_spreadsheet_id

# For public mode
SHEET_URL=https://docs.google.com/spreadsheets/d/YOUR_ID/

# Schema
SCHEMA_PATH=schema.yaml

# Cache
CACHE_MODE=aggressive

2. Start Server

cd sheetdb_server
python server.py

Or use uvicorn directly:

uvicorn sheetdb_server.app:app --reload

3. Access API

API Base: http://localhost:8000/api
Documentation: http://localhost:8000/docs
Health Check: http://localhost:8000/health

API Endpoints

List Tables

GET /api/tables

Returns all available tables with their schemas.

Query Rows

GET /api/tables/{table}/rows

Query Parameters:

limit - Maximum number of rows
offset - Number of rows to skip
order_by - Column to sort by (prefix with - for descending)
fields - Comma-separated list of fields to return
{field} - Exact match filter
{field}__gte - Greater than or equal
{field}__lte - Less than or equal
{field}__contains - String contains

Examples:

# Get all monsters
curl http://localhost:8000/api/tables/monsters/rows

# Filter by danger level
curl http://localhost:8000/api/tables/monsters/rows?danger_level__gte=7

# Pagination and sorting
curl http://localhost:8000/api/tables/monsters/rows?limit=10&offset=0&order_by=-danger_level

# Select specific fields
curl http://localhost:8000/api/tables/monsters/rows?fields=id,name,danger_level

Create Row

POST /api/tables/{table}/rows
Content-Type: application/json

{
  "data": {
    "name": "Vampire",
    "danger_level": 8,
    "habitat": "Castle"
  }
}

Update Row

PUT /api/tables/{table}/rows/{id}
Content-Type: application/json

{
  "data": {
    "danger_level": 9
  }
}

Delete Row

DELETE /api/tables/{table}/rows/{id}

Trigger Sync

POST /api/sync

Refreshes the DuckDB cache from Google Sheets.

Check Sync Status

GET /api/sync/status

Returns information about the last sync operation.

Configuration

Programmatic Configuration

from sheetdb_server.app import create_app

app = create_app(
    credentials="credentials.json",
    spreadsheet_id="YOUR_ID",
    schema_path="schema.yaml",
    cache_mode="aggressive",
    allowed_origins=["http://localhost:3000"],
    api_keys=["your-secret-key"]
)

Environment Variables

# Required
CREDENTIALS_PATH=path/to/credentials.json
SPREADSHEET_ID=your_spreadsheet_id
SCHEMA_PATH=schema.yaml

# Optional
CACHE_MODE=aggressive  # aggressive, smart, lazy, none
CACHE_TTL=300          # seconds (for smart mode)
API_KEYS=key1,key2     # comma-separated
ALLOWED_ORIGINS=*      # CORS origins

Authentication

API Key Authentication

Add API keys to your configuration:

app = create_app(
    ...,
    api_keys=["secret-key-1", "secret-key-2"]
)

Include the API key in requests:

curl -H "X-API-Key: secret-key-1" \
  http://localhost:8000/api/tables/monsters/rows

No Authentication

If no API keys are configured, all requests are allowed (useful for development).

CORS Configuration

Enable CORS for frontend access:

app = create_app(
    ...,
    allowed_origins=[
        "http://localhost:3000",
        "https://myapp.com"
    ]
)

Or allow all origins (development only):

app = create_app(
    ...,
    allowed_origins=["*"]
)

Deployment

Docker

FROM python:3.11-slim

WORKDIR /app

# Install dependencies
RUN pip install sheetdb fastapi uvicorn[standard]

# Copy server code
COPY sheetdb_server/ ./sheetdb_server/
COPY schema.yaml credentials.json ./

# Run server
CMD ["uvicorn", "sheetdb_server.app:app", "--host", "0.0.0.0", "--port", "8000"]

Build and run:

docker build -t sheetdb-server .
docker run -p 8000:8000 sheetdb-server

Production

Use a production ASGI server:

# Install production server
pip install gunicorn

# Run with gunicorn
gunicorn sheetdb_server.app:app \
  --workers 4 \
  --worker-class uvicorn.workers.UvicornWorker \
  --bind 0.0.0.0:8000

Examples

JavaScript/Fetch

// Query data
const response = await fetch(
  "http://localhost:8000/api/tables/monsters/rows?danger_level__gte=7"
);
const data = await response.json();
console.log(data);

// Create row
await fetch("http://localhost:8000/api/tables/monsters/rows", {
  method: "POST",
  headers: {
    "Content-Type": "application/json",
    "X-API-Key": "your-key",
  },
  body: JSON.stringify({
    data: { name: "Vampire", danger_level: 8 },
  }),
});

Python/Requests

import requests

# Query data
response = requests.get(
    'http://localhost:8000/api/tables/monsters/rows',
    params={'danger_level__gte': 7}
)
monsters = response.json()

# Create row
requests.post(
    'http://localhost:8000/api/tables/monsters/rows',
    json={'data': {'name': 'Vampire', 'danger_level': 8}},
    headers={'X-API-Key': 'your-key'}
)

cURL

# Query with filters
curl "http://localhost:8000/api/tables/monsters/rows?danger_level__gte=7&limit=10"

# Create row
curl -X POST http://localhost:8000/api/tables/monsters/rows \
  -H "Content-Type: application/json" \
  -H "X-API-Key: your-key" \
  -d '{"data": {"name": "Vampire", "danger_level": 8}}'

# Update row
curl -X PUT http://localhost:8000/api/tables/monsters/rows/1 \
  -H "Content-Type: application/json" \
  -d '{"data": {"danger_level": 9}}'

Architecture

Frontend (React/Vue/etc)
    ↓ HTTP
REST API Server (FastAPI)
    ↓
SheetDB Core Library
    ↓
Google Sheets + DuckDB Cache

Performance

Query Speed: ~1-10ms (cached queries)
Sync Speed: ~2s for 5 tables × 1000 rows
Throughput: 1000+ requests/second (cached)

Troubleshooting

Server won't start

# Check if port is in use
lsof -i :8000

# Use different port
uvicorn sheetdb_server.app:app --port 8001

CORS errors

Add your frontend origin to allowed_origins:

app = create_app(
    ...,
    allowed_origins=["http://localhost:3000"]
)

Authentication errors

Make sure API key is included in headers:

curl -H "X-API-Key: your-key" http://localhost:8000/api/tables/monsters/rows

Future Plans

This component will be split into a separate repository and package:

Repository: sheetdb-server
Package: pip install sheetdb-server
CLI: sheetdb-server start

Contributing

See the main SheetDB repository for contribution guidelines.

License

MIT

Part of the SheetDB project - See main documentation

Name		Name	Last commit message	Last commit date
Latest commit History 1 Commit
__pycache__		__pycache__
.env.example		.env.example
Dockerfile		Dockerfile
README.md		README.md
__init__.py		__init__.py
app.py		app.py
requirements.txt		requirements.txt
server.py		server.py

whitebumblebee/sheetdb-server

Folders and files

Latest commit

History

Repository files navigation