Live data

A page usually shows a snapshot of the database from when it rendered. With live(), a page variable follows the database instead: when a table it reads changes, every open page showing it gets the new rows. No polling, channels or handlers to write.

from pyweb import App, live, server
from pyweb.db import connect

app = App(title="Orders")
db = connect("sqlite:///shop.db")
db.execute("create table if not exists orders (id integer primary key, item text, status text default 'new')")


@server
def place(item: str) -> None:
    db.execute("insert into orders (item) values (?)", (item,))


@server
def ship(order_id: int) -> None:
    db.execute("update orders set status = 'shipped' where id = ?", (order_id,))


@app.page("/")
def Orders():
    orders = live(db, "select id, item, status from orders order by id desc limit 50")
    waiting = live(db, "select count(*) as n from orders where status = 'new'")
    item = ""

    def order():
        place(item)
        item = ""

    <h1>Orders ({waiting[0]["n"]} waiting)</h1>
    <form onsubmit={order}><input bind={item} /><button>Order</button></form>
    <ul>
        for o in orders:
            <li>{o["item"]}: {o["status"]}
                if o["status"] == "new":
                    <button onclick={lambda: ship(o["id"])}>Ship</button>
            </li>
    </ul>

// Orders.js
function Orders($s) {
  const orders = $signal("orders" in $s ? $s["orders"] : null);
  const waiting = $signal("waiting" in $s ? $s["waiting"] : null);
  const item = $signal("item" in $s ? $s["item"] : "");
  async function order() {
    (await $rpc("place", {"item": item()}));
    item("");
  }
  $live(orders, $s["$live:orders"]);
  $live(waiting, $s["$live:waiting"]);
  return [
    $h("h1", null, () => [
      $t("Orders ("),
      $dyn(() => $py.at($py.at(waiting(), 0), "n")),
      $t(" waiting)")
    ]),
    $h("form", {"onsubmit": order}, () => [
      $h("input", {"$bind": item}),
      $h("button", null, () => [$t("Order")])
    ]),
    $h("ul", null, () => [$list(() => orders(), (o) => [$h("li", null, () => [
          $t($py.text($py.at(o, "item"))),
          $t(": "),
          $t($py.text($py.at(o, "status"))),
          $when(() => ($py.at(o, "status") === "new"), () => [$h("button", {"onclick": (async () => ((await $rpc("ship", {"order_id": $py.at(o, "id")}))))}, () => [$t("Ship")])])
        ])])])
  ];
}
$mount("Orders", Orders);

page Orders  route=/
  browser  orders         reactive state: a live query: the server sends new rows when its tables change; initial value computed on the server and sent
  browser  waiting        reactive state: a live query: the server sends new rows when its tables change; initial value computed on the server and sent
  browser  item           reactive state: bound to an input (line 30); literal initial value
  browser  order          event handler (compiled to JavaScript)
rpc POST /__pyweb/rpc/place  (item: str) -> None
rpc POST /__pyweb/rpc/ship  (order_id: int) -> None

<h1>Orders ( waiting)</h1>
<form>
<input value=""><button>Order</button>
</form><ul>
</ul>

Open the page in two windows: an order placed or shipped in one appears in both at once.

#How it works

• live(db, sql, params) runs the query while the page renders, like db.execute(sql, params).dicts(), so the first paint has the data and needs no extra request. In the browser the variable is reactive state.
• Writes announce themselves. Every INSERT, UPDATE, DELETE, REPLACE or TRUNCATE made through pyweb.db (and so through pyweb.models) announces its table once it's committed. Inside with db.transaction(): the announcements wait for the commit, and a rollback sends none.
• Queries are shared. Each distinct query (same database, SQL and parameters) is re-run once per change, however many pages show it, and only sends rows when they actually changed. A burst of writes is coalesced into one re-run.
• Pages follow a signed feed, like channel(): only visitors who were served the page can listen, and nothing written after the render is missed.

The tables to watch are the ones after FROM and JOIN. Pass tables=["orders", "customers"] when that isn't enough (views, functions, subqueries).

#Writes PyWeb doesn't see

If another program, a cron job or a raw driver connection writes to the database, tell the live queries:

db.notify("orders")            # after the other write has committed

#Several processes and servers

With more than one worker process or server, share the realtime bus through Redis (the same setting live updates use):

from pyweb.realtime import RedisBus, use_bus

use_bus(RedisBus("redis://localhost:6379/0"))

Then a write in any process reaches pages connected to any other. A process that didn't render a page (the browser's connection landed on another worker, or the worker restarted) takes over re-running its query from the page's signed query description.

#Limits

• Live queries are for what a page shows: keep them small with LIMIT. A live query that returns more than 10,000 rows is an error.
• Every change sends the query's full result. For large lists, page them (limit ? offset ?) or show counts.
• Rows are sent to every page showing the query, so filter per user in SQL (where owner = ?, with the id from session.user()), never in the browser.
• A query nobody has rendered or watched for ten minutes is forgotten until a page renders it again.