Databases & APIs

Topics

• What is a database and why you need one
• Supabase: your backend-as-a-service
• SQL basics: SELECT, INSERT, UPDATE, DELETE
• Database schema design
• API routes in Next.js
• CRUD operations (Create, Read, Update, Delete)
• Row Level Security basics

Activities

• Set up a free Supabase project
• Design a schema for your task manager
• Ask Claude Code to create API routes
• Build forms to add and edit tasks
• Implement all CRUD operations
• Add basic data validation

Skills You'll Gain

Database concepts, Supabase, SQL basics, API routes, CRUD operations

Learning Objectives

By the end of this week, you will be able to:

1. Explain what a database is and why web applications need one
2. Write basic SQL queries: SELECT, INSERT, UPDATE, DELETE
3. Design a simple database schema (tables, columns, data types)
4. Connect a Next.js application to Supabase
5. Implement full CRUD operations so data persists between sessions

Lesson

Why Your Data Disappears

Right now, your task manager from Week 4 has a problem: close the browser tab and all your tasks vanish. That is because the tasks only live in your browser's temporary memory (called "state" in React). When the page closes, the memory is wiped clean.

A database stores your data permanently on a server. It is like the difference between writing notes on a whiteboard (browser memory — erased when someone wipes the board) and writing them in a notebook (database — saved until you explicitly delete them).

What Is a Database?

A database is a super-powered spreadsheet. If you have ever used Google Sheets or Excel, you already understand the basic concept:

Tasks Table (like a spreadsheet tab)
┌────┬──────────────────┬───────────┬────────────┬─────────────────────┐
│ id │ title            │ completed │ priority   │ created_at          │
├────┼──────────────────┼───────────┼────────────┼─────────────────────┤
│  1 │ Buy groceries    │ false     │ high       │ 2026-02-10 09:00:00 │
│  2 │ Read chapter 3   │ true      │ medium     │ 2026-02-10 10:30:00 │
│  3 │ Call dentist     │ false     │ low        │ 2026-02-11 08:00:00 │
│  4 │ Write report     │ false     │ high       │ 2026-02-11 14:00:00 │
└────┴──────────────────┴───────────┴────────────┴─────────────────────┘

Key vocabulary:

• Table = A spreadsheet tab. Each table stores one type of thing (tasks, users, etc.)
• Row = One entry (one task, one user). Also called a "record."
• Column = One property of every entry (title, completed, priority). Also called a "field."
• Schema = The design of your tables — which columns exist, what type of data each holds.

SQL: Talking to Your Database

SQL (Structured Query Language, pronounced "sequel") is the language you use to talk to a database. It has four essential commands, and each one maps to something you do naturally:

SELECT — Reading data ("Show me...")

-- Show me all tasks
SELECT * FROM tasks;

-- Show me only incomplete tasks
SELECT * FROM tasks WHERE completed = false;

-- Show me task titles, sorted by priority
SELECT title, priority FROM tasks ORDER BY priority;

The * means "all columns." WHERE is a filter. ORDER BY sorts the results.

INSERT — Adding data ("Create a new...")

-- Add a new task
INSERT INTO tasks (title, completed, priority)
VALUES ('Buy groceries', false, 'high');

You specify the table, the columns, and the values. The id and created_at are usually generated automatically.

UPDATE — Changing data ("Change this to...")

-- Mark task 1 as completed
UPDATE tasks SET completed = true WHERE id = 1;

-- Change the priority of task 3
UPDATE tasks SET priority = 'high' WHERE id = 3;

Always include WHERE in an UPDATE — without it, you would change every row in the table.

DELETE — Removing data ("Get rid of...")

-- Delete task 4
DELETE FROM tasks WHERE id = 4;

Like UPDATE, always use WHERE. Running DELETE FROM tasks without WHERE deletes everything.

CRUD: The Four Operations

CRUD is an acronym for the four basic things you can do with data:

Every data-driven application does these four things. Your task manager needs all four.

Supabase: Your Database in the Cloud

Supabase is a free service that gives you a database, an API, and authentication — all in one place. Think of it as a pre-built kitchen (from our restaurant analogy) that you can start using immediately, without building anything from scratch.

Setting up Supabase:

1. Go to https://supabase.com and create a free account
2. Click "New Project"
3. Choose a name (e.g., "task-manager") and a strong database password
4. Select a region close to you
5. Wait about 60 seconds for the project to set up

Getting your keys:

In your Supabase dashboard, go to Settings → API. You need two values:

• Project URL — Where your database lives (e.g., https://abcdef.supabase.co)
• Anon Key — A public key that lets your app talk to the database

Add these to your project's .env.local file:

NEXT_PUBLIC_SUPABASE_URL=https://abcdef.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIs...

Designing Your Schema

Before writing code, design your database. For the task manager:

CREATE TABLE tasks (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       TEXT NOT NULL,
  completed   BOOLEAN DEFAULT false,
  priority    TEXT DEFAULT 'medium',
  created_at  TIMESTAMPTZ DEFAULT now()
);

Translation:

• id — A unique number for each task, generated automatically
• title — The task name, required (NOT NULL means it cannot be empty)
• completed — True or false, defaults to false (new tasks start incomplete)
• priority — Text like "high", "medium", "low", defaults to "medium"
• created_at — When the task was created, auto-filled with the current time

You can run this SQL in the Supabase dashboard under "SQL Editor."

API Routes: The Waiter Between Your App and Database

In Next.js, API routes are files in the app/api/ folder that handle requests from your front end. They are the "waiter" carrying orders between the browser and the database.

When a user clicks "Add Task," the flow is:

Browser (click button)
    → API Route (/api/tasks)
        → Database (INSERT INTO tasks...)
        → Response (new task data)
    → Browser (update the list)

Ask Claude Code to create these API routes for you:

Create API routes for my task manager that connect to Supabase.
I need endpoints for:
- GET /api/tasks — list all tasks
- POST /api/tasks — create a new task
- PUT /api/tasks/[id] — update a task (mark complete, change title)
- DELETE /api/tasks/[id] — delete a task
Use the Supabase client library.

Row Level Security

Row Level Security (RLS) is a safety feature that controls who can see and modify which rows. Without RLS, anyone who knows your API URL could read or delete all your data.

For now, you can enable RLS on your tasks table and add a simple policy that allows all operations (since you do not have user authentication yet — that comes in Week 6):

ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Allow all operations" ON tasks
  FOR ALL
  USING (true)
  WITH CHECK (true);

In Week 6, you will replace this with proper user-based policies.

Practice Exercises

Exercise 1 (Guided): Create Your Database Table

1. Open your Supabase project dashboard
2. Go to SQL Editor
3. Run the CREATE TABLE statement from the lesson above
4. Go to Table Editor — you should see an empty "tasks" table
5. Manually insert a row using the Table Editor (click "Insert Row")
6. Run SELECT * FROM tasks; in the SQL Editor — your row should appear

Verification: The tasks table exists with 5 columns and at least 1 test row.

Exercise 2 (Independent): Connect Your App to Supabase

Goal: Modify your Week 4 task manager to load and save tasks from Supabase instead of browser memory.

1. Install the Supabase client: ask Claude "Add Supabase to my project"
2. Create API routes for all CRUD operations
3. Update the front end to fetch tasks from the API on page load
4. Update the "Add Task" button to send a POST request
5. Update the checkbox to send a PUT request
6. Update the delete button to send a DELETE request

Hints:

• Use Plan mode — this is a multi-step change
• Test each operation in the browser after implementing it
• Check the Supabase Table Editor to verify data is actually being saved

Verification: Close the browser, reopen it, and your tasks are still there. Check Supabase Table Editor — you should see all your tasks.

Exercise 3 (Challenge): Add Data Validation

Add validation so users cannot create invalid tasks:

• Task title must not be empty
• Task title must be at least 3 characters
• Priority must be one of: "high", "medium", "low"
• Show a user-friendly error message when validation fails

Implement validation on both the front end (before sending the request) and the API route (before inserting into the database). Why both? Because the front end can be bypassed — someone could send a request directly to your API.

Self-Assessment Quiz

1. What is the difference between a table, a row, and a column in a database?

2. Write a SQL query that selects all tasks where completed is false, sorted by created_at from newest to oldest.

3. What does CRUD stand for, and what SQL command corresponds to each letter?

4. What is an API route in Next.js, and what role does it play?

5. Why is Row Level Security important, even for a simple app?

Answers:

1. A table is like a spreadsheet tab — it stores one type of thing (e.g., tasks). A row is one entry in the table (one specific task). A column is one property that every entry has (e.g., title, completed, priority).

2. SELECT * FROM tasks WHERE completed = false ORDER BY created_at DESC; — The DESC keyword means descending (newest first).

3. CRUD stands for Create (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE). These are the four basic operations you can perform on data.

4. An API route is a file in app/api/ that handles requests from the browser. It acts as the "waiter" between the front end (browser) and the back end (database), processing requests and returning responses.

5. Without Row Level Security, anyone who discovers your API URL could read, modify, or delete all your data. RLS ensures that each user can only access their own data, even if someone bypasses your front-end code.

Cowork: Claude Code for Desktop

• Cowork brings Claude Code's agentic capabilities to the Claude desktop app. Give Claude access to a folder, set a task, and let it work — it loops you in along the way.
• Useful when you want Claude Code-style workflows without opening a terminal.
• Try it at claude.com/download.

Shell & Input Fixes (Feb 2026)

• Shell completion cache files no longer get truncated on exit (v2.1.21)
• Full-width (zenkaku) input from Japanese IME now works correctly in option selection prompts (v2.1.21, v2.1.31)
• Tab key fixed to properly autocomplete instead of queueing slash commands (v2.1.38)