Build a REST API with Node.js and Supabase · Part 2

Add CRUD Endpoints with Supabase

Published 2026-06-14

What you'll build

In this part you'll connect your Express API to a Supabase PostgreSQL database and build a full set of CRUD endpoints for products and categories.

By the end of this part your API will support:

GET /products — list all products with optional category filter and pagination
GET /products/:id — get a single product by ID
POST /products — create a product (validates body with Zod)
PUT /products/:id — update a product
DELETE /products/:id — delete a product
GET /categories and POST /categories

Set up Supabase

Go to app.supabase.com, create a project, and run the following in the SQL Editor:

create table categories (
  id          uuid primary key default gen_random_uuid(),
  name        text not null unique,
  slug        text not null unique,
  created_at  timestamptz default now()
);

create table products (
  id           uuid primary key default gen_random_uuid(),
  name         text not null,
  description  text,
  price_ghs    numeric(10,2) not null,
  sku          text unique,
  stock        integer not null default 0,
  category_id  uuid references categories(id) on delete set null,
  images       text[] default '{}',
  is_active    boolean default true,
  created_at   timestamptz default now(),
  updated_at   timestamptz default now()
);

-- Keep updated_at current automatically
create or replace function update_updated_at()
returns trigger as $$
begin new.updated_at = now(); return new; end;
$$ language plpgsql;

create trigger products_updated_at
  before update on products
  for each row execute function update_updated_at();

Seed some test data:

insert into categories (name, slug) values
  ('Electronics', 'electronics'),
  ('Clothing', 'clothing'),
  ('Food & Drink', 'food-drink');

insert into products (name, description, price_ghs, sku, stock, category_id)
select 'Bluetooth Speaker', 'Portable waterproof speaker with 12h battery', 250.00, 'SPK-001', 15, id
from categories where slug = 'electronics';

Copy your Project URL and service_role key from Settings → API into .env.

Install the client:

npm install @supabase/supabase-js

Build the Supabase client

BlocWeave prompt

Create src/lib/supabase.ts. Initialise a Supabase client with SUPABASE_URL and SUPABASE_SERVICE_KEY from process.env. Throw a clear error at startup if either is missing. Export the client as supabase.

Build the products router

BlocWeave prompt

Create the products CRUD layer for the product catalog API. 1. src/types.ts — define and export: - ProductRow: { id: string; name: string; description: string | null; price_ghs: number; sku: string | null; stock: number; category_id: string | null; images: string[]; is_active: boolean; created_at: string; updated_at: string } - CategoryRow: { id: string; name: string; slug: string; created_at: string } 2. src/controllers/products.controller.ts — export async functions: - listProducts(req, res, next): query supabase.from("products").select("*, categories(name, slug)"). Support ?category= filter (join on categories.slug). Support ?page and ?limit (default limit 20, max 100). Return { data: products, meta: { total, page, limit } }. - getProduct(req, res, next): fetch single product by req.params.id. Return 404 { error: "Product not found" } if missing. - createProduct(req, res, next): insert validated body into products table. Return 201 with the created product. - updateProduct(req, res, next): update by req.params.id. Return 404 if not found. - deleteProduct(req, res, next): delete by req.params.id. Return 204 on success. 3. src/schemas/product.schema.ts — Zod schemas: - CreateProductSchema: { name: string (min 1), description: string optional, price_ghs: number (positive), sku: string optional, stock: number (int, ≥ 0) default 0, category_id: string uuid optional, images: string array optional default [] } - UpdateProductSchema: same fields all optional (use .partial()) 4. src/routes/products.router.ts — mount all five routes using the controller functions and validate() middleware on POST/PUT.

Build the categories router

BlocWeave prompt

Create src/controllers/categories.controller.ts with listCategories and createCategory. Create src/schemas/category.schema.ts with CreateCategorySchema: { name: string (min 1), slug: string (lowercase, no spaces — use .regex(/^[a-z0-9-]+$/)) }. Create src/routes/categories.router.ts. Mount both routers in src/index.ts under /products and /categories.

Test all endpoints

Start the server (npm run dev) and test:

# List products
curl http://localhost:3000/products

# Get one product (replace ID with an actual UUID from the list)
curl http://localhost:3000/products/<id>

# Create a product
curl -X POST http://localhost:3000/products \
  -H "Content-Type: application/json" \
  -d '{"name":"Test Item","price_ghs":99.99,"stock":10}'

# Filter by category
curl "http://localhost:3000/products?category=electronics"

# Validation error (missing required field)
curl -X POST http://localhost:3000/products \
  -H "Content-Type: application/json" \
  -d '{"description":"no name provided"}'

The last command should return a 400 with { "error": "Validation failed", "details": [...] }.

Project structure after Part 2

src/
  controllers/
    products.controller.ts
    categories.controller.ts
  lib/
    supabase.ts
  middleware/
    errorHandler.ts
    validate.ts
  routes/
    health.router.ts
    products.router.ts
    categories.router.ts
  schemas/
    product.schema.ts
    category.schema.ts
  types.ts
  index.ts

What's next

In Part 3 we add user registration and login with hashed passwords and JWT tokens. Protected routes (create, update, delete) will require a valid token in the Authorization header.

← Previous Next →