---
title: "Bookmark Shelf"
description: "Bookmark manager API that derives domains and tag counts as computed columns, served with FastAPI"
url: "https://www.pixeltable.com/recipes/bookmark-shelf"
source: "https://github.com/pierrebrunelle/pixeltable-bookmark-shelf"
---

# Bookmark Shelf

Bookmark manager API that derives domains and tag counts as computed columns, served with FastAPI

One `app.py` with 1 table, 4 computed columns, 5 HTTP routes. Needs `pixeltable[serve]>=0.7.14`.

## Tables

### bookmarks (table)

| Column | Kind | Type or expression |
| --- | --- | --- |
| id | primary key | `pxtf.uuid.uuid7()` |
| url | stored | String |
| title | stored | String |
| tags | stored | String (optional) |
| notes | stored | String (optional) |
| domain | computed on insert | `extract_domain(url)` |
| title_upper | computed on insert | `pxtf.string.upper(title)` |
| n_tags | computed on insert | `tag_count(tags)` |
| blurb | computed on insert | `title_blurb(title)` |

## Routes (service `bookmarks_api`)

- `POST /bookmarks`: inserts into `bookmarks` (takes url, title, tags, notes; returns id, domain, title_upper, n_tags, blurb)
- `POST /bookmarks/update`: updates `bookmarks` (takes title, tags; returns id, n_tags, blurb)
- `POST /bookmarks/delete`: deletes from `bookmarks`
- `POST /domain`: computes `bookmarks` columns without storing (takes url; returns domain)
- `GET /shelf`: runs the query `shelf` (takes domain)

## Run it locally

```sh
bash <<'PXT_APP'
set -euo pipefail
git clone https://github.com/pierrebrunelle/pixeltable-bookmark-shelf
cd pixeltable-bookmark-shelf
git checkout db0e7d8433bd2560f171ec3ca8f089e13d8c59b0
uv sync
uv run pxt schema update app.py shelf
uv run python seed.py shelf
uv run pxt service run app.py shelf --port 8000
PXT_APP
```

## Run it on Pixeltable Cloud

```sh
bash <<'PXT_CLOUD'
set -euo pipefail
# In the app folder, with its Python environment active (Codespaces installs the dependencies).
trial=$(pxt new --json)    # a free database for 48 hours, no account
uri=$(printf '%s' "$trial" | python -c 'import json, sys; d = json.load(sys.stdin); print("Claim this trial: " + d["claim_url"], file=sys.stderr) if d["claim_url"] else None; print("pxt://" + d["org"] + ":" + d["db"])')
trap 'printf "Database: %s\n" "$uri" >&2' EXIT
# Keep the example project's second database entry in step with this trial, including on retries.
python - "$uri" <<'PY'
import re, sys
from pathlib import Path
path = Path("pixeltable.toml")
sections = path.read_text().split("[[pixeltable.database]]")
if len(sections) != 3:
    sys.exit("Expected the example project to have a local and a hosted database entry")
sections[2], count = re.subn(r"(?m)^name\s*=.*$", "name = " + repr(sys.argv[1]), sections[2], count=1)
if count != 1:
    sys.exit("Expected a name in the hosted database entry")
path.write_text("[[pixeltable.database]]".join(sections))
PY
state() { pxt db status "$uri" --json | python -c 'import json, sys; print(json.load(sys.stdin)["current"]["state"])'; }
for i in $(seq 60); do
  s=$(state)
  [ "$s" = AVAILABLE ] && break
  case "$s" in FAILED | STOPPED) echo "$uri is $s" >&2; exit 1 ;; esac
  sleep 10
done
[ "$s" = AVAILABLE ] || { echo "Database provisioning timed out" >&2; exit 1; }
pxt db update "$uri" -f
pxt schema update app.py "$uri/shelf" -f
pxt service update app.py "$uri/shelf" -f
# Tables start empty. Service routes require an X-api-key; pxt new keeps the trial key private.
# Claim the trial, then run pxt login and pxt key create app-client to obtain your own key for requests.
PXT_CLOUD
```

## app.py

At commit `db0e7d84` (https://github.com/pierrebrunelle/pixeltable-bookmark-shelf/blob/db0e7d8433bd2560f171ec3ca8f089e13d8c59b0/app.py):

```python
"""Bookmark Shelf API built with Pixeltable.

    pxt schema update app.py shelf
    pxt service run app.py shelf
"""
import pixeltable as pxt
import pixeltable.functions as pxtf
from pixeltable.serving import FastAPIRouter

from udfs import extract_domain, tag_count, title_blurb

# ---- tables ----
TableModel = pxt.model_base()


class Bookmarks(TableModel, name='bookmarks'):
    id = pxt.Column(value=pxtf.uuid.uuid7(), primary_key=True)
    url: pxt.String
    title: pxt.String
    tags: pxt.String | None
    notes: pxt.String | None

    domain = extract_domain(url)
    title_upper = pxtf.string.upper(title)
    n_tags = tag_count(tags)
    blurb = title_blurb(title)


# ---- queries ----
@pxt.query
def shelf(domain: str):
    """Everything saved from one domain."""
    return Bookmarks.where(Bookmarks.domain == domain).select(
        Bookmarks.id, Bookmarks.blurb, Bookmarks.url, Bookmarks.n_tags
    ).order_by(Bookmarks.title)


# ---- routes ----
bookmarks_api = FastAPIRouter(name='bookmarks_api')
bookmarks_api.add_insert_route(
    Bookmarks, path='/bookmarks',
    inputs=[Bookmarks.url, Bookmarks.title, Bookmarks.tags, Bookmarks.notes],
    outputs=[Bookmarks.id, Bookmarks.domain, Bookmarks.title_upper, Bookmarks.n_tags, Bookmarks.blurb],
)
bookmarks_api.add_update_route(
    Bookmarks, path='/bookmarks/update',
    inputs=[Bookmarks.title, Bookmarks.tags],
    outputs=[Bookmarks.id, Bookmarks.n_tags, Bookmarks.blurb],
)
bookmarks_api.add_delete_route(Bookmarks, path='/bookmarks/delete')
bookmarks_api.add_compute_route(Bookmarks, path='/domain', inputs=[Bookmarks.url], outputs=[Bookmarks.domain])
bookmarks_api.add_query_route(path='/shelf', query=shelf, method='get')
```