Supabase + Python

A minimal setup to run Supabase locally and interact with it from Python: authentication (sign up / sign in) and a Postgres table protected by Row Level Security (RLS).

About .env in this repo

This is a sample project, so the .env file is committed to the repo. That makes it easy to clone and run without extra setup (you only need to paste the local anon key after the first npm run dev). In real applications you should not commit .env — keep secrets in environment variables or a secrets manager and add .env to .gitignore.

Prerequisites

• Node.js 18+ (for npm and Supabase CLI via npx)
• Python 3.10+
• Poetry (install)
• Docker (Docker Desktop, Colima, OrbStack, or compatible) — required for local Supabase

Quick start

1. Install dependencies

# Node (Supabase CLI is used via npx)
npm install

# Python (Supabase client + dev tools)
poetry install

2. Start Supabase locally

In a first terminal, run (and leave it running):

npm run dev

This runs npx supabase start. The first time it will pull Docker images and can take a few minutes. Wait until you see "Started supabase local development setup" and the API URL — do not press Ctrl+C during "Starting database..." or you’ll leave the stack in a broken state. Keep this terminal open; Supabase must be running for the app to connect. When it finishes, it prints something like:

Started supabase local development setup.

         API URL: http://127.0.0.1:54321
          DB URL: postgresql://postgres:postgres@127.0.0.1:54322/postgres
      Studio URL: http://127.0.0.1:54323
        anon key: eyJhbGci...
service_role key: eyJhbGci...

• API URL — REST, Auth, Realtime, Storage (e.g. http://127.0.0.1:54321)
• Studio — Web UI for database and auth: http://127.0.0.1:54323
• anon key — Used by the Python app; copy it into .env (see below)

3. Configure environment

A .env file is already in the repo (see About .env in this repo above). If SUPABASE_ANON_KEY is empty, run npm run dev once, then paste the printed anon key into .env. SUPABASE_URL is already set for local use.

4. Run the Python app (auth + DB)

In a second terminal (with Supabase still running in the first), run:

npm run test

If you see "Connection refused" or "Cannot reach Supabase": (1) In the other terminal, wait until you see "Started supabase local development setup" and the API URL table — "Starting database..." or "Starting database from backup..." can take 1–2 minutes. (2) Then run npm run test again. To confirm the stack is up: npx supabase status.

"supabase start is already running" but container "unhealthy" or "exited"?
Startup was likely interrupted (e.g. Ctrl+C during "Starting database..."). Do a clean reset: run npm run stop, then npm run dev again and wait until you see "Started supabase local development setup".

Or:

poetry run python app.py

The script will:

1. Authenticate — sign in (or sign up) with a demo user (demo@example.com / demo-password-123).
2. Insert a row into the user_notes table (allowed by RLS for the authenticated user).
3. Select that row back and print it.

So you get a full flow: Auth API + Postgres with authentication.

5. Apply migrations (if needed)

If the user_notes table is missing (e.g. after a restore from backup), with Supabase running run:

npm run migrate

Then run npm run test again.

Example: successful run

With Supabase already started, running migrate then test should look like this:

Terminal 1 (Supabase running):

npm run migrate

Resetting local database...
Recreating database...
Initialising schema...
Seeding globals from roles.sql...
Applying migration 20240308120000_create_user_notes.sql...
WARN: no files matched pattern: supabase/seed.sql
Restarting containers...
Finished supabase db reset on branch main.

Terminal 2:

npm run test

Signed up: demo@example.com
Stored note with id: 35d3abb2-1178-4c93-b61b-d940f3af5521
Retrieved note: Hello from app.py - This note was stored and will be retrieved.
Done. Authentication and database flow OK.

Authentication and database flow are working when you see that output.

---

NPM scripts

Script	Command	Description
npm run build	ruff check + ruff format --check	Run Python linters (no formatting changes).
npm run dev	npx supabase start	Start local Supabase (API, Auth, Postgres, Studio).
npm run stop	npx supabase stop	Stop local Supabase.
npm run migrate	npx supabase db reset	Reset DB and apply migrations (creates user_notes). Run with Supabase up.
npm run test	poetry run python app.py	Run the demo script (auth + insert + select).

---

Local Supabase services

Started with npm run dev:

Service	URL / usage
API	http://127.0.0.1:54321 — REST (PostgREST), Realtime, Storage
Auth	http://127.0.0.1:54321/auth/v1 — sign up, sign in, JWTs
Postgres	postgresql://postgres:postgres@127.0.0.1:54322/postgres — direct DB (e.g. psql)
Studio	http://127.0.0.1:54323 — DB tables, SQL, Auth users
Inbucket (email)	http://127.0.0.1:54324 — catch emails in local dev

Auth is configured in supabase/config.toml (e.g. email confirmations off for local dev). The database is created and migrated from supabase/migrations/ on first start; the user_notes table and its RLS policies are defined there. If the table is missing (e.g. after restoring from backup), run npm run migrate with Supabase running.

---

Project layout

.
├── app.py                 # Demo: auth, insert note, select note
├── package.json           # npm scripts (build, dev, test)
├── pyproject.toml         # Poetry + ruff config
├── .env.example           # Template for SUPABASE_URL and SUPABASE_ANON_KEY
├── .env                   # Your local keys (gitignored)
└── supabase/
    ├── config.toml        # Local Supabase config (API, DB, Auth, etc.)
    └── migrations/
        └── 20240308120000_create_user_notes.sql   # user_notes table + RLS

---

Python stack

• supabase — official client (auth, PostgREST, optional Realtime/Storage).
• python-dotenv — load SUPABASE_URL and SUPABASE_ANON_KEY from .env.
• ruff — lint and format check (used in npm run build).

All managed via Poetry; run commands with poetry run or activate the venv and run directly.

---

Troubleshooting

Symptom	What to do
"Connection refused" or "Cannot reach Supabase" when running npm run test	In the other terminal, Supabase must be fully started. Wait for "Started supabase local development setup" and the API URL table (not just "Starting database..."). Then run npm run test again. Check with npx supabase status.
"supabase start is already running" and "container is not ready: unhealthy" or "exited"	Startup was interrupted (e.g. Ctrl+C). Run npm run stop, then npm run dev again. Wait for the full startup message; don’t interrupt during "Starting database...".
"Could not find the table 'public.user_notes'" (PGRST205)	The migration didn’t run (e.g. DB was restored from backup). With Supabase running, run npm run migrate to apply migrations, then npm run test again.

---

Stopping Supabase

npm run stop

This runs npx supabase stop. Use npx supabase stop --no-backup to remove data (e.g. before a clean re-run of migrations).

---

Summary

• npm run build — Python linters (ruff).
• npm run dev — Start Supabase (Auth API + Postgres + Studio).
• npm run stop — Stop local Supabase.
• npm run migrate — Reset DB and apply migrations (creates user_notes).
• npm run test — Run app.py (authenticate, store and retrieve a note).
• Auth and DB access are wired so that only the authenticated user can read/write their own rows in user_notes, as defined in the migration.