restqlite 0.1.0

PostgREST-inspired SQLite HTTP REST API using Python stdlib

restqlite
=========

PostgREST-inspired HTTP REST API for SQLite, Python stdlib only.
No dependencies. Python 3.9+, compatible with 3.14 free-threaded.


INSTALL
-------

    pip install restqlite


CLI
---

    python -m restqlite mydb.sqlite3
    python -m restqlite mydb.sqlite3 --port 5000
    python -m restqlite mydb.sqlite3 --host 127.0.0.1 --read-only
    python -m restqlite mydb.sqlite3 --log-level WARNING


USAGE
-----

    import restqlite

    # blocking
    restqlite.serve('mydb.sqlite3', host='', port=8000)

    # custom server (e.g. alongside other handlers)
    import http.server
    handler = restqlite.make_handler('mydb.sqlite3')
    with http.server.ThreadingHTTPServer(('', 8000), handler) as srv:
        srv.serve_forever()


serve(db_path, host='', port=8000, read_only=False,
      before_request=None, routes=None,
      allowed_tables=None, max_rows=None, log_level='INFO')

make_handler(db_path, read_only=False,
             before_request=None, routes=None,
             allowed_tables=None, max_rows=None, log_level='INFO')

    db_path         Path to SQLite file, or ':memory:'.
    read_only       If True, only GET and HEAD are accepted.
    before_request  Callable(handler) run before every request; return False
                    to signal the response has already been sent.
    routes          Dict mapping path or 'METHOD /path' to a callable.
                    Matched before restqlite routing. Example:
                        {'GET /health': lambda h: _send(h, 200, {'ok': True})}
    allowed_tables  Collection of table/view names to expose, or a
                    Callable(handler) returning one. None exposes all (default).
    max_rows        Maximum rows returned per GET request. Clamps both explicit
                    ?limit= values and requests with no limit. None = no cap.
    log_level       Logging level string ('DEBUG', 'INFO', 'WARNING', …).
                    Sets the 'restqlite' logger level. None = don't change.
                    Default 'INFO'.


API REFERENCE
-------------

All tables and views are auto-discovered from the database schema.
Requests and responses use JSON by default. Column types are inferred
at query time (SQLite dynamic typing).


GET /
    List tables and views.

    Response:
        {"tables": ["orders", "users"], "views": ["active_users"]}

    When allowed_tables is set, only listed tables/views are returned.


GET /metrics
    Prometheus exposition format metrics:
        restqlite_requests_total{method="GET"} 42
        restqlite_db_bytes 16384
    Falls through to a table named "metrics" if one exists.
    Use with Prometheus + grafana_dashboard.json (included in repo).


GET /<table>
    Fetch rows from a table or view.

    Query parameters:
        select=col1,col2        return only named columns
        col=op.value            filter rows (see OPERATORS)
        order=col.asc           sort; append .desc for descending
        order=col1.asc,col2.desc  multi-column sort
        order=col.asc.nullsfirst  nullsfirst/nullslast suffix accepted (ignored)
        limit=N                 page size
        offset=N                skip N rows
        Prefer: count=none      skip COUNT(*); omits Content-Range header

    Response header:
        Content-Range: 0-9/42   (first-last/total; omitted with count=none)
        Content-Range: */0      (empty result set)

    Example:
        GET /orders?status=eq.open&order=created.desc&limit=20

HEAD /<table>
    Same as GET but returns headers only (no body).
    Content-Range header is still included.


POST /<table>
    Insert one row (JSON object) or many rows (JSON array).

    Headers:
        Prefer: return=representation   return inserted row(s) in response

    Response (without Prefer header):
        {"count": 1}

    Response (with Prefer: return=representation):
        {inserted row object}   for a single row
        [{...}, {...}]          for multiple rows

    Example:
        POST /orders
        {"product": "widget", "qty": 3, "status": "open"}


PUT /<table>
    Upsert one row (JSON object) or many rows (JSON array).
    Uses INSERT OR REPLACE semantics: existing rows matched by primary key
    are replaced; new rows are inserted.

    Headers:
        Prefer: return=representation   return upserted row(s) in response

    Response (without Prefer header):
        {"count": 1}

    Response (with Prefer: return=representation):
        {row object}   for a single row
        [{...}, {...}] for multiple rows

    Example:
        PUT /orders
        {"id": 42, "product": "widget", "qty": 5, "status": "open"}


PATCH /<table>[?filters]
    Update columns on matching rows. Body is a JSON object of columns
    to set. Filters follow the same syntax as GET.

    Headers:
        Prefer: return=representation   return updated rows in response

    Response (without Prefer header):
        {"count": 2}

    Response (with Prefer: return=representation):
        [{updated row}, ...]

    Example:
        PATCH /orders?status=eq.open
        {"status": "closed"}


DELETE /<table>[?filters]
    Delete matching rows. No filters deletes all rows.

    Headers:
        Prefer: return=representation   return deleted rows in response

    Response (without Prefer header):
        {"count": 5}

    Response (with Prefer: return=representation):
        [{deleted row}, ...]

    Example:
        DELETE /orders?status=eq.closed


OPERATORS
---------

Filters take the form ?column=op.value.
Prefix with not. to negate: ?col=not.eq.foo

    eq      equal               ?name=eq.alice
    neq     not equal           ?name=neq.bob
    lt      less than           ?price=lt.100
    lte     less than or equal  ?price=lte.100
    gt      greater than        ?price=gt.0
    gte     greater than/equal  ?price=gte.0
    like    pattern match       ?name=like.*ali*  (* is wildcard; case-sensitive)
    ilike   case-insensitive    ?name=ilike.*ALI*  (ASCII only)
    is      null/bool check     ?deleted=is.null
                                ?active=is.true
                                ?active=is.false
                                ?col=is.unknown  (alias for null)
    in      set membership      ?id=in.(1,2,3)  (parentheses required)
    fts     FTS5 full-text      ?body=fts.hello+world  (requires FTS5 table)
            FTS5 binary NOT:    ?body=fts.hello+NOT+world


ALLOWED TABLES
--------------

allowed_tables controls which tables and views are exposed. Tables not
in the allowed set return 404 (not 403, to avoid leaking schema info).

Static list:
    restqlite.serve('mydb.sqlite3', allowed_tables=['orders', 'products'])

Callable (dynamic, e.g. DB-stored permissions):
    def tables(handler):
        # return a collection or None to allow all
        role = handler.headers.get('X-Role', 'guest')
        if role == 'admin':
            return None  # unrestricted
        return ['products', 'categories']

    restqlite.serve('mydb.sqlite3', allowed_tables=tables)

None (default) exposes all tables and views.


EXTENSIBILITY
-------------

before_request -- run before every request:

    def auth_check(handler):
        if handler.headers.get('Authorization') != 'Bearer secret':
            handler.send_response(401)
            handler.send_header('Content-Length', '0')
            handler.end_headers()
            return False  # response already sent; restqlite must not respond

    restqlite.serve('mydb.sqlite3', before_request=auth_check)

routes -- custom endpoints (bypass restqlite routing entirely):

    import json

    def health(handler):
        body = json.dumps({'status': 'ok'}).encode()
        handler.send_response(200)
        handler.send_header('Content-Type', 'application/json')
        handler.send_header('Content-Length', str(len(body)))
        handler.end_headers()
        handler.wfile.write(body)

    restqlite.serve('mydb.sqlite3', routes={
        'GET /health': health,
        '/ping': health,          # matches any method
    })


MULTI-TENANCY
-------------

Isolate tenants with separate SQLite database files (one file per tenant).
Use routes or before_request to select the appropriate db_path and
dispatch to a per-tenant handler class.


ERROR RESPONSES
---------------

All errors return JSON:
    {"error": "description"}

Status codes:
    400  bad request (invalid filter, unknown column, SQLite error)
    404  table or view not found (also returned for allowed_tables misses)
    405  method not allowed (write on read-only server, or unsupported method)


THREADING
---------

Uses ThreadingHTTPServer (one thread per connection). HTTP/1.1 keep-alive
allows a single thread to serve multiple sequential requests on one
connection, and each thread holds its own SQLite connection via
threading.local. WAL journal mode is enabled for concurrent reads.

The sqlite3 module has threadsafety level 1 (threads may share the
module but not connections). Per-thread connections satisfy this.
Safe under Python 3.14 free-threaded (no-GIL) builds because no
mutable state is shared between threads.


SCHEMA NOTES
------------

No column types in CREATE TABLE statements (SQLite dynamic typing):

    CREATE TABLE orders (
        id      INTEGER PRIMARY KEY,
        product,
        qty,
        status,
        created
    );

PRAGMA foreign_keys=ON is set on every connection.


MAX ROWS
--------

Prevent unbounded queries by setting a server-level row cap:

    restqlite.serve('mydb.sqlite3', max_rows=1000)

The cap clamps both explicit ?limit= and queries with no limit parameter.
Clients cannot exceed it regardless of what they request.


LOGGING
-------

restqlite emits structured JSON logs to stdout via Python's logging module.
The logger is named 'restqlite'. Each log record is a JSON object:

    {"timestamp": "...", "level": "INFO", "message": "...", "logger": "restqlite"}

Access logs (one per request) include additional fields:

    {"timestamp": "...", "level": "INFO", "message": "GET /orders 200",
     "logger": "restqlite",
     "client_ip": "127.0.0.1", "method": "GET", "path": "/orders",
     "status": 200, "duration_ms": 1.23}

A startup log is emitted when serve() binds the socket:

    {"message": "restqlite listening on 0.0.0.0:8000", ...,
     "db": "mydb.sqlite3", "host": "0.0.0.0", "port": 8000}

To suppress access logs, set log_level='WARNING'. To integrate with an
existing logging setup, configure logging.getLogger('restqlite') directly
and pass log_level=None to avoid overriding the level.


LIMITATIONS
-----------

- No bulk PATCH with per-row values
- Single SQLite file per server instance (use separate instances for tenants)