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

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.