Build an app on a team Database

A team Database has an HTTP API. A browser or a server reads and writes its tables with no server code of your own. The API follows PostgREST, so supabase-js works for reads and writes. A sign-in service gives your app's people their own accounts. A live channel sends the rows again when they change.

• The database decides what a request may do. A table has no access for the web until you grant it, and row-level security chooses the rows.
• A request runs as a role, the role of its key or token. The roles are anon (a visitor), authenticated (a person who signed in) and service_role (your server; it passes row-level security).
• The address of everything below is https://gizstack.com/db/TEAM/NAME. TEAM is the slug of your team and NAME the name of the Database.

Step 1: Take the keys

Open the Database page and find Data API. Writers of the team see three keys there.

Step 2: Give a table to the web

A new table is closed. Grant what a visitor may do, then say which rows with a policy.

CREATE TABLE notes (id bigint PRIMARY KEY, owner text, body text);
GRANT SELECT, INSERT, UPDATE, DELETE ON notes TO authenticated;
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY own ON notes
  USING (owner = current_setting('request.jwt.claim.sub', true));

Each claim of the token is a setting named request.jwt.claim.NAME. A person's id is the claim sub.

Step 3: Read and write

With supabase-js:

import { createClient } from "@supabase/supabase-js";
const db = createClient("https://gizstack.com/db/TEAM/NAME", ANON_KEY);

const { data } = await db.from("notes").select("id, body").order("id");
await db.from("notes").insert({ id: 1, owner: "me", body: "hello" });
await db.from("notes").update({ body: "changed" }).eq("id", 1);
await db.from("notes").delete().eq("id", 1);

With HTTP:

curl "https://gizstack.com/db/TEAM/NAME/rest/v1/notes?select=id,body&order=id" \
  -H "apikey: $ANON_KEY" -H "authorization: Bearer $TOKEN"

What the API understands

Values are always parameters of the statement. No text of a request becomes SQL.

An error has the shape { "code", "message", "details", "hint" }. code is the SQLSTATE of the database when it gave the error. The status is 401 for a missing or bad key, 403 when a grant or policy refuses, 406 for one object that is not one, and 400 for the rest.

Not supported

• Embedded resources (select=id,author(name)): read the tables with two requests, or make a view.
• The array and text-search filters (cs, cd, ov, fts).

Step 4: Sign people in

The service is at /db/TEAM/NAME/auth/v1, with the shapes of GoTrue. People are rows of your own Database (schema auth), not accounts of the console.

const { data } = await db.auth.signUp({ email: "ann@example.com", password: "correct horse" });
await db.auth.signInWithPassword({ email: "ann@example.com", password: "correct horse" });
const { data: { user } } = await db.auth.getUser();
await db.auth.signOut();

• A session holds an access token (valid one hour) and a refresh token. Use the refresh token once: it gives a new pair. supabase-js does this for you.
• The access token is the role authenticated with sub set to the person's id, so the policy of Step 2 works with no more code. supabase-js sends it with every request after sign-in.
• A password has 8 to 128 characters. It is kept as a salted PBKDF2-SHA-256 hash.
• After 10 wrong passwords for one email in 15 minutes, the next sign-in answers 429 (over_request_rate_limit) until the window passes. A right password clears the count.
• Sign-up needs no confirmation mail. Magic links, OAuth and multi-factor sign-in are not part of the service.

Step 5: Follow changes live

Open one WebSocket to /db/TEAM/NAME/live. The channel is the same one every GizWeb page uses, so one connection carries any number of tables. Browsers cannot set headers on a WebSocket: give the key as ?apikey=.

const live = new WebSocket("wss://gizstack.com/db/TEAM/NAME/live?apikey=" + TOKEN);
live.onopen = () => live.send(JSON.stringify({
  v: "", s: [["notes?select=id,body&order=id", "", ""]],
}));
live.onmessage = (e) => {
  const m = JSON.parse(e.data);
  if (m.r) return console.error("refused", m.r);   // 401 or 403: that topic ends
  render(m.o[0].rows);                              // the rows as this person may see them
};

• A topic is TABLE?QUERY, the query of a read above. Ask for several in the list s.
• The first message holds the rows now. Each change to the Database sends the rows again, read again as the same person, so row-level security decides what each person sees.
• A change made through the Data API or the Databases page tells the channel. A write over the PostgreSQL connection does not yet.
• A lost socket comes back by itself in the GizWeb kernel. In your own client, open it again and send the same message.

Where it is different from Supabase

See also: Use GizDB, Move to GizStack.