Skip to main content

Basic Example

Let's Start​

Let's build the same Books API we built with MongoDB and PostgreSQL with TypeORM, this time with Supabase. Comparing the three versions side by side is a good way to see what each tool does for you.

Project Setup​

Follow the setup doc to create the Supabase project, the books table, and the .env file. Then install the dependencies:

npm install express @supabase/supabase-js dotenv
npm install -D nodemon
package.json
{
  "name": "express-with-supabase",
  "version": "1.0.0",
  "type": "module",
  "scripts": {
    "start": "node index.js",
    "dev": "nodemon index.js"
  }
}

Folder Structure​

📂 express-with-supabase
├── 📂 config
│   └── 📄 supabase.js
├── 📂 routes
│   └── 📄 book.js
├── 📂 utils
│   └── 📄 supabase-error.js
├── 📄 .env
├── 📄 index.js
└── 📄 package.json

config/supabase.js​

config/supabase.js
import "dotenv/config";
import { createClient } from "@supabase/supabase-js";

const { SUPABASE_URL, SUPABASE_SECRET_KEY } = process.env;

if (!SUPABASE_URL || !SUPABASE_SECRET_KEY) {
  throw new Error("SUPABASE_URL and SUPABASE_SECRET_KEY must be set");
}

export const supabase = createClient(SUPABASE_URL, SUPABASE_SECRET_KEY, {
  auth: { persistSession: false, autoRefreshToken: false },
});

utils/supabase-error.js​

Turns Postgres error codes into HTTP status codes, so a duplicate book name returns 409 instead of a generic 500:

utils/supabase-error.js
const STATUS_BY_CODE = {
  23505: 409,
  23514: 400,
  23502: 400,
  "22P02": 400,
  PGRST116: 404,
};

export function sendSupabaseError(res, error) {
  const status = STATUS_BY_CODE[error.code] ?? 500;
  // Hide database internals from clients on unexpected errors
  const message = status === 500 ? "Something went wrong" : error.message;

  if (status === 500) console.error(error);
  res.status(status).json({ message });
}

index.js​

index.js
import express from "express";
import { supabase } from "./config/supabase.js";
import bookRouter from "./routes/book.js";

const app = express();
app.use(express.json());

app.use("/book", bookRouter);

const { error } = await supabase.from("books").select("id").limit(1);

if (error) {
  console.error("Supabase connection failed:", error.message);
  process.exit(1);
}

console.log("Supabase Connected ~");
app.listen(9999, () => console.log("Server running on port 9999"));

routes/book.js​

  • GET /book gets books, with pagination
  • GET /book/:id gets a single book
  • POST /book creates a book
  • PATCH /book/:id updates a book
  • DELETE /book/:id deletes a book
routes/book.js
import { Router } from "express";
import { supabase } from "../config/supabase.js";
import { sendSupabaseError } from "../utils/supabase-error.js";

const router = Router();

const EDITABLE_FIELDS = ["name", "author", "price", "stock"];

// Only copy known columns from the body, so clients cannot set id or created_at
function pickBookFields(body) {
  return Object.fromEntries(
    Object.entries(body).filter(([key]) => EDITABLE_FIELDS.includes(key))
  );
}

router.get("/", async (req, res) => {
  const page = Math.max(parseInt(req.query.page) || 1, 1);
  const limit = Math.min(parseInt(req.query.limit) || 10, 100);
  const from = (page - 1) * limit;

  const { data, count, error } = await supabase
    .from("books")
    .select("*", { count: "exact" })
    .order("id")
    .range(from, from + limit - 1);

  if (error) return sendSupabaseError(res, error);

  res.json({
    data,
    meta: { page, limit, total_items: count, total_pages: Math.ceil(count / limit) },
  });
});

router.get("/:id", async (req, res) => {
  const { data, error } = await supabase
    .from("books")
    .select("*")
    .eq("id", req.params.id)
    .maybeSingle();

  if (error) return sendSupabaseError(res, error);
  if (!data) return res.status(404).json({ message: "Book not found" });

  res.json(data);
});

router.post("/", async (req, res) => {
  const { data, error } = await supabase
    .from("books")
    .insert(pickBookFields(req.body))
    .select()
    .single();

  if (error) return sendSupabaseError(res, error);

  res.status(201).json(data);
});

router.patch("/:id", async (req, res) => {
  const changes = pickBookFields(req.body);

  if (Object.keys(changes).length === 0) {
    return res.status(400).json({ message: "No valid fields to update" });
  }

  const { data, error } = await supabase
    .from("books")
    .update({ ...changes, updated_at: new Date().toISOString() })
    .eq("id", req.params.id)
    .select()
    .maybeSingle();

  if (error) return sendSupabaseError(res, error);
  if (!data) return res.status(404).json({ message: "Book not found" });

  res.json(data);
});

router.delete("/:id", async (req, res) => {
  const { data, error } = await supabase
    .from("books")
    .delete()
    .eq("id", req.params.id)
    .select();

  if (error) return sendSupabaseError(res, error);
  if (data.length === 0) return res.status(404).json({ message: "Book not found" });

  res.json({ message: "Book deleted" });
});

export default router;

A few things to notice in the routes:

  • No try/catch: supabase-js returns errors instead of throwing them, so we check error after every query. A network failure can still throw, so in a real project add the async error handler as well.
  • pickBookFields(): the secret key bypasses RLS, so the database will accept any column you send. Whitelisting fields stops a client from overwriting id or created_at. For full validation, use Joi.
  • .maybeSingle(): returns data: null for a missing row, which we turn into a 404.
  • delete().select(): returns the deleted rows, so an empty array means the ID did not exist.

Running the App​

npm run dev

You should see:

Supabase Connected ~
Server running on port 9999

Testing the API​

Create a book:

POST http://localhost:9999/book
Content-Type: application/json

{
  "name": "Clean Code",
  "author": "Robert C. Martin",
  "price": 29.99,
  "stock": 50
}

Send the same request again and you get 409 Conflict, because name is unique. Send "price": -5 and you get 400, because of the check (price > 0) rule.

Get books, page 1:

GET http://localhost:9999/book?page=1&limit=10

Get one book:

GET http://localhost:9999/book/1

Update a book:

PATCH http://localhost:9999/book/1
Content-Type: application/json

{
  "price": 24.99
}

Delete a book:

DELETE http://localhost:9999/book/1

You can also open Table Editor in the Supabase dashboard and watch the rows change as you send requests.

Conclusion​

In this article, we built a Books CRUD API with Express and Supabase. Compared to the TypeORM version, there are no entities or DataSource: the schema lives in SQL, and queries go over HTTP through supabase-js. In the next doc, we will add authentication with Supabase Auth.

Last updated on