Supabase PostgreSQL

From a browser preference to shared records

LocalStorage belongs to one browser origin and profile. A hosted PostgreSQL database stores records beyond one browser so your product can load them again after a reload or on another device. The database is a separate service: it does not disappear when you stop npm run dev.

Tables have named columns and rows. A product needs an ID to identify it, a name to display and a numeric price to calculate with. An ID is not the row's current position in a list; sorting the list should not change which product it identifies.

This course uses the Supabase client in your own practice project. That is separate from your StackFoundry account. Use a disposable free-tier project and non-sensitive sample data; check current service limits and do not buy a plan for this rehearsal.

Create one safe-to-discard practice table

In your own project, open its SQL editor and understand this schema before running it. It creates a public learning playground, not a secure shop or private account store:

CREATE TABLE public.products (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  price numeric NOT NULL CHECK (price >= 0)
);

REVOKE ALL ON public.products FROM PUBLIC, anon, authenticated;
GRANT SELECT, INSERT ON public.products TO anon, authenticated;
GRANT ALL ON public.products TO service_role;
ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Read practice products"
ON public.products FOR SELECT TO anon, authenticated
USING (true);

CREATE POLICY "Add practice products"
ON public.products FOR INSERT TO anon, authenticated
WITH CHECK (true);

The ID is generated automatically. NOT NULL rejects missing values; the price check rejects negative stored prices. A primary key uniquely identifies each row. REVOKE clears any default ordinary-client grants before the explicit read/insert grants are applied. The policies permit those operations on this practice table. No UPDATE or DELETE access is given to ordinary clients.

Everyone who can reach this playground can read and insert sample rows. Do not put personal data, authoritative prices, purchases, passwords or official scores here. Do not publicly promote it as a production service. Use it briefly for the exercise and remove the practice insert policy or retire the project afterward. For private products, first implement sign-in and ownership-based policies from the Auth & RLS lesson; do not simply copy public write rules into a real product.

Read SQL as a request

Insert a harmless notebook and pen, then read them:

INSERT INTO public.products (name, price)
VALUES ('Practice notebook', 12), ('Practice pen', 3);

SELECT id, name, price
FROM public.products
ORDER BY name;

INSERT adds rows; SELECT reads chosen columns. A result with two rows means two stored records, not two copies inside your JavaScript file. Reload the database editor and confirm the rows remain.

WHERE narrows a query. SELECT name, price FROM public.products WHERE price >= 10; returns the notebook in this dataset. UPDATE changes existing rows; DELETE removes them. A missing WHERE can affect every row. Do not practise a broad UPDATE/DELETE on anything important. Those commands are concepts here, not requirements for your read-and-insert activity.

Connect the SvelteKit project

From the folder containing package.json:

npm install @supabase/supabase-js

Add a root .env file using the editor, with the variable names below and your project's public configuration. The bracketed values are instructions to replace, not usable credentials:

PUBLIC_SUPABASE_URL=[your practice project URL]
PUBLIC_SUPABASE_PUBLISHABLE_KEY=[your project's publishable key]

A legacy anon key can serve the documented public-client purpose where the service still uses one; never use a service-role/secret key here. PUBLIC_ means the framework may include the value in browser code. These two values identify the project; RLS and grants still decide access. A variable being in .env does not keep it private if it is exported to the browser.

Add .env to .gitignore before staging; check with git check-ignore .env and review git status. Restart the practice dev server after changing environment files. Never paste keys into course submissions.

Create src/lib/database.ts:

import { createClient } from '@supabase/supabase-js';
import {
  PUBLIC_SUPABASE_URL,
  PUBLIC_SUPABASE_PUBLISHABLE_KEY
} from '$env/static/public';

export const database = createClient(
  PUBLIC_SUPABASE_URL,
  PUBLIC_SUPABASE_PUBLISHABLE_KEY
);

This is the practice public client only. It cannot magically act as a signed-in user on a server. An account-based app needs the framework's documented session-aware client design.

Read a result and an error, not just a promise

Study a small read helper:

import { database } from '$lib/database';

export async function readPracticeProducts() {
  const { data, error } = await database
    .from('products')
    .select('id, name, price')
    .order('name');

  if (error) throw new Error('Could not load practice products.');
  return data ?? [];
}

The client returns a result with data and error. Many database failures arrive as an error result rather than a thrown JavaScript exception. Check it explicitly. An empty successful array is a normal empty state; it is not the same as a permission or connection failure.

In a Svelte component, call the helper when the page is ready using onMount from svelte, assign the array to $state, and render an {#each} block. For example, a single row's markup can display {product.name} and {product.price}. Use the stable product ID as the key, not its list index. Display a loading message before the read, a useful empty message for zero rows and a friendly error if it fails. Adapt the patterns from the earlier async/UI lessons rather than silently ignoring a rejected promise.

Write, then read back

A small generic insert pattern is:

const { error } = await database.from('products').insert({
  name: 'Practice pencil',
  price: 2
});
if (error) throw new Error('Could not save practice product.');

Use your own form values and convert the price from the input's string to a finite, non-negative number. Browser validation improves feedback but cannot secure the table; the validated server endpoint arrives in the next lesson.

Disable the submit control while a write is pending so an accidental double-click does not create duplicates. Only clear the form and announce success after checking error. Then call the read helper again so the newly stored row appears. A local variable appended to a list is not evidence of a hosted write: reload and verify the database row.

Guided rehearsal

1. Read the two SQL-inserted rows in your app and compare their names/prices with the table.
2. Add one differently named harmless product through your app. Confirm it in the table editor.
3. Reload the browser and find it again. If possible, open a second browser window and read the same row.
4. Temporarily use a misspelled table name in your practice read. Confirm a helpful failure, not a false empty state; restore the name afterward.
5. Inspect Git before committing: package.json, lockfile and source belong in the commit; .env and node_modules do not.

Diagnose the layer

Assessment readiness and cleanup

Activity 4.2 needs an installed client, real select and insert code, and a row that can be read back—not only a package dependency. Explain the table's columns, how the public client is configured, how errors are handled and why this public playground is not a secure production database. After practising, close its public write access or retire it; apply signed-in ownership rules before storing real user data.