Database Operations

Now that the client is set up, let's read and write data. Every query starts with supabase.from("table_name"), followed by an action (select, insert, update, delete) and optional filters.

The `{ data, error }` Result

supabase-js does not throw when a query fails. Every query resolves to an object with data and error, and you must check error yourself:

const { data, error } = await supabase.from("books").select("*");

if (error) {
  // error.message, error.code, error.details
  throw error;
}

console.log(data); // array of rows

danger

If you forget to check error, a failed query looks like an empty result (data is null) and your API silently returns nothing. Always check error first.

Create (Insert)

Insert one row
const { data, error } = await supabase
  .from("books")
  .insert({ name: "Clean Code", author: "Robert C. Martin", price: 29.99 })
  .select()
  .single();

By default insert() does not return the new row. Chaining .select() asks for it back, and .single() turns the one-item array into an object.

Insert many rows
const { data, error } = await supabase
  .from("books")
  .insert([
    { name: "Refactoring", author: "Martin Fowler", price: 39.5 },
    { name: "The Pragmatic Programmer", author: "Andy Hunt", price: 35 },
  ])
  .select();

Upsert

upsert() inserts a row, or updates it if a row with the same unique value already exists:

const { data, error } = await supabase
  .from("books")
  .upsert({ name: "Clean Code", author: "Robert C. Martin", price: 24.99 }, { onConflict: "name" })
  .select()
  .single();

Read (Select)

All Rows

const { data, error } = await supabase.from("books").select("*");

Specific Columns

const { data, error } = await supabase.from("books").select("id, name, price");

One Row

const { data, error } = await supabase
  .from("books")
  .select("*")
  .eq("id", 1)
  .maybeSingle();

Method	When there are no rows	When there is more than one row
`.single()`	Returns an error	Returns an error
`.maybeSingle()`	Returns `data: null`	Returns an error

For a GET /book/:id route, use .maybeSingle() so a missing book becomes data: null, which you turn into a 404.

Filters

Filters are chained after select(). Each one adds a SQL WHERE condition, and they are combined with AND.

const { data, error } = await supabase
  .from("books")
  .select("*")
  .eq("author", "Robert C. Martin")
  .gte("price", 10)
  .lte("price", 50);

supabase-js	SQL	Mongoose equivalent
`.eq("col", v)`	`col = v`	`{ col: v }`
`.neq("col", v)`	`col <> v`	`{ col: { $ne: v } }`
`.gt` / `.gte`	`>` / `>=`	`$gt` / `$gte`
`.lt` / `.lte`	`<` / `<=`	`$lt` / `$lte`
`.in("col", [a, b])`	`col IN (a, b)`	`{ col: { $in: [a, b] } }`
`.ilike("col", "%cod%")`	`col ILIKE '%cod%'`	`{ col: /cod/i }`
`.is("col", null)`	`col IS NULL`	`{ col: null }`

For OR conditions, use .or():

// books by Fowler OR cheaper than 20
const { data, error } = await supabase
  .from("books")
  .select("*")
  .or("author.eq.Martin Fowler,price.lt.20");

caution

Do not build .or() strings directly from user input. A value containing a comma or a dot changes the meaning of the filter. Use the typed methods (.eq(), .ilike(), ...) for user values, and keep .or() for fixed conditions.

Dynamic Filters from Query Parameters

This is the supabase-js version of the filtering doc. Since each filter returns the query, you can add filters conditionally:

routes/book.js
router.get("/", async (req, res) => {
  let query = supabase.from("books").select("*");

  if (req.query.author) {
    query = query.eq("author", req.query.author);
  }

  if (req.query.min_price) {
    query = query.gte("price", Number(req.query.min_price));
  }

  if (req.query.search) {
    query = query.ilike("name", `%${req.query.search}%`);
  }

  const { data, error } = await query;

  if (error) {
    return res.status(500).json({ message: error.message });
  }

  res.json(data);
});

Sorting

const { data, error } = await supabase
  .from("books")
  .select("*")
  .order("price", { ascending: false })
  .order("name");

Pagination with a Total Count

range(from, to) returns rows by position, and both ends are included. Rows 0 to 9 are the first 10 rows. Passing { count: "exact" } to select() also returns the total number of matching rows, so you can calculate the number of pages.

routes/book.js
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 to = from + limit - 1;

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

  if (error) {
    return res.status(500).json({ message: error.message });
  }

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

limit is capped at 100 so a client cannot ask for a million rows in one request.

If books had an author_id column with a foreign key to an authors table, you could load the author in the same query, like populate() in Mongoose or relations in TypeORM:

const { data, error } = await supabase
  .from("books")
  .select("id, name, price, authors ( id, name )");

Supabase finds the relation from the foreign key automatically.

Update

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

// data is null when no book has id 1

danger

update() or delete() without a filter changes every row in the table. Always add a filter like .eq("id", id), and double-check it before running the query.

Delete

const { data, error } = await supabase
  .from("books")
  .delete()
  .eq("id", 1)
  .select();

// data is an array of the deleted rows; empty when nothing matched

To delete all rows on purpose, you need a filter that matches every row:

await supabase.from("books").delete().gt("id", 0);

Handling Database Errors

error.code holds the PostgreSQL error code, so you can return the right HTTP status:

`error.code`	Meaning	HTTP status
`23505`	Unique constraint violated (duplicate)	`409`
`23514`	Check constraint violated (e.g. `price <= 0`)	`400`
`23502`	A `not null` column is missing	`400`
`22P02`	Invalid value for the column type	`400`
`PGRST116`	`.single()` found zero or many rows	`404`

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

export function statusFromSupabaseError(error) {
  return STATUS_BY_CODE[error.code] ?? 500;
}

Transactions with Postgres Functions

supabase-js sends each query as a separate HTTP request, so you cannot wrap several queries in one transaction from Express. If two writes must succeed or fail together (for example, "create an order and reduce the stock"), put them in a Postgres function. A function runs inside a single transaction: if any statement fails, everything is rolled back.

SQL Editor
create table orders (
  id bigint generated always as identity primary key,
  book_id bigint not null references books (id),
  quantity integer not null check (quantity > 0),
  created_at timestamptz not null default now()
);

create function place_order(p_book_id bigint, p_quantity integer)
returns orders
language plpgsql
as $$
declare
  new_order orders;
begin
  update books
  set stock = stock - p_quantity
  where id = p_book_id and stock >= p_quantity;

  if not found then
    raise exception 'Not enough stock for book %', p_book_id;
  end if;

  insert into orders (book_id, quantity)
  values (p_book_id, p_quantity)
  returning * into new_order;

  return new_order;
end;
$$;

Call it from Express with rpc():

routes/order.js
router.post("/", async (req, res) => {
  const { data, error } = await supabase.rpc("place_order", {
    p_book_id: req.body.book_id,
    p_quantity: req.body.quantity,
  });

  if (error) {
    return res.status(400).json({ message: error.message });
  }

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

The where ... and stock >= p_quantity check and the update happen in one statement, so two customers ordering the last copy at the same time cannot both succeed.

Conclusion

In this doc, we learned how to:

Check { data, error } on every query
Insert, upsert, select, update, and delete rows
Filter, sort, and paginate with a total count
Map Postgres error codes to HTTP status codes
Run several writes in one transaction using a Postgres function and rpc()

In the next doc, we will put this together in a complete Books API.

The { data, error } Result​

Create (Insert)​

Upsert​

Read (Select)​

All Rows​

Specific Columns​

One Row​

Filters​

Dynamic Filters from Query Parameters​

Sorting​

Pagination with a Total Count​

Related Tables​

Update​

Delete​

Handling Database Errors​

Transactions with Postgres Functions​

Conclusion​