datasette-transactions 0.1a0

API for executing multiple queries within a transaction

datasette-transactions

A Datasette plugin providing an API for executing multiple SQL commands within a single transaction, with support for SQLite authorization callbacks to control table-level read/write access.

Installation

Install this plugin in the same environment as Datasette.

datasette install datasette-transactions

Usage

Begin a Transaction

POST /-/transactions/begin/<database>
Content-Type: application/json

{
  "read": ["table1", "table2"],
  "write": ["table1"],
  "timeout_ms": 5000
}

Parameters:

• read: List of tables the transaction can read from
• write: List of tables the transaction can write to
• timeout_ms: Optional timeout in milliseconds (transaction auto-rolls back after this time)

Response:

{
  "ok": true,
  "transaction_id": "550e8400-e29b-41d4-a716-446655440000"
}

Required Permissions:

• execute-sql on the database
• view-table on each table in the read list
• insert-row and update-row on each table in the write list

Execute SQL

POST /-/transactions/<transaction_id>
Content-Type: application/json

{
  "sql": "INSERT INTO table1 (name) VALUES (:name)",
  "params": {"name": "Alice"}
}

Response:

{
  "ok": true,
  "rows": [],
  "columns": [],
  "truncated": false
}

For SELECT queries, rows contains the results as a list of objects.

Create a Savepoint

POST /-/transactions/<transaction_id>/savepoint
Content-Type: application/json

{
  "name": "before_batch"
}

Response:

{
  "ok": true,
  "savepoint": "before_batch"
}

Release a Savepoint

POST /-/transactions/<transaction_id>/release
Content-Type: application/json

{
  "name": "before_batch"
}

Rollback to a Savepoint

POST /-/transactions/<transaction_id>/rollback-to
Content-Type: application/json

{
  "name": "before_batch"
}

Commit a Transaction

POST /-/transactions/commit/<transaction_id>

Response:

{
  "ok": true
}

Rollback a Transaction

POST /-/transactions/rollback/<transaction_id>

Response:

{
  "ok": true
}

Error Codes

Status	Meaning
400	Bad request (invalid JSON, missing parameters, SQL error)
403	Permission denied (missing required permissions, table access denied)
404	Transaction or savepoint not found
405	Method not allowed (use POST)
410	Transaction expired (timed out)
429	Too many concurrent transactions (max 5 per database)

Authorization

The plugin uses SQLite's set_authorizer() callback to enforce table-level permissions:

• Reading from tables not in read list is blocked
• Writing to tables not in write list is blocked
• ATTACH DATABASE is always blocked

Concurrency

• Maximum 5 concurrent transactions per database
• Read-only transactions use BEGIN DEFERRED (allows concurrent reads)
• Write transactions use BEGIN IMMEDIATE (exclusive lock)

Example: Batch Insert with Rollback on Error

import httpx

# Begin transaction
response = httpx.post(
    "http://localhost:8001/-/transactions/begin/mydb",
    json={"read": ["users"], "write": ["users"]}
)
tx_id = response.json()["transaction_id"]

try:
    # Insert multiple rows
    for user in users:
        httpx.post(
            f"http://localhost:8001/-/transactions/{tx_id}",
            json={
                "sql": "INSERT INTO users (name, email) VALUES (:name, :email)",
                "params": user
            }
        )

    # Commit if all succeeded
    httpx.post(f"http://localhost:8001/-/transactions/commit/{tx_id}")
except Exception:
    # Rollback on error
    httpx.post(f"http://localhost:8001/-/transactions/rollback/{tx_id}")
    raise

Development

To set up this plugin locally, first checkout the code:

cd datasette-transactions
uv run pytest  # Run tests

This project follows TDD (Test-Driven Development). See CLAUDE.md for development guidelines.