How to Wire React to Supabase CRUD (Using the pg Client)

The CRUD shape Supabase mirrors

Supabase's PostgREST methods (supabase.from('profiles').select(), .insert(), .update(), .delete()) each map directly to a SQL statement your pg client can issue. Building the layer yourself first means you understand exactly what the abstraction does - and swapping clients later is just changing the connector, not the logic.

For a profiles table (id INT PK, name TEXT, bio TEXT), the four functions are:

const { Client } = require("pg");

async function _client() {
  const c = new Client({ host: "127.0.0.1", database: "app", user: "postgres" });
  await c.connect();
  return c;
}

async function createProfile(id, name, bio) {
  const c = await _client();
  try {
    await c.query(
      "INSERT INTO profiles (id, name, bio) VALUES ($1, $2, $3)",
      [id, name, bio]
    );
  } finally { await c.end(); }
}

async function getProfile(id) {
  const c = await _client();
  try {
    const r = await c.query("SELECT * FROM profiles WHERE id = $1", [id]);
    return r.rows[0] || null;
  } finally { await c.end(); }
}

async function updateProfile(id, name, bio) {
  const c = await _client();
  try {
    const r = await c.query(
      "UPDATE profiles SET name=$2, bio=$3 WHERE id=$1 RETURNING *",
      [id, name, bio]
    );
    return r.rows[0] || null;
  } finally { await c.end(); }
}

async function deleteProfile(id) {
  const c = await _client();
  try {
    await c.query("DELETE FROM profiles WHERE id = $1", [id]);
  } finally { await c.end(); }
}

module.exports = { createProfile, getProfile, updateProfile, deleteProfile };

Why parameterized queries ($1, $2, ...)

Never build SQL by concatenating user input. $1, $2 placeholders pass values separately from the query string - the driver handles escaping, so SQL injection is structurally impossible regardless of what the caller passes in.

Supabase's client enforces this automatically; rolling your own layer means you have to enforce it yourself. Every query above uses placeholders.

RETURNING * on UPDATE

Adding RETURNING * to the UPDATE statement gets the full updated row back in a single round trip, so the caller doesn't need a second SELECT. This mirrors what Supabase returns from .update().select().

Swap path to @supabase/supabase-js

Once the pg layer works, replacing it with the Supabase client is mechanical:

import { createClient } from "@supabase/supabase-js";
const supabase = createClient(SUPABASE_URL, SUPABASE_ANON_KEY);

// getProfile equivalent
const { data } = await supabase.from("profiles").select("*").eq("id", id).single();

The query logic stays the same - only the client changes. Production also layers Row-Level Security policies so each user only reads their own rows, and realtime channels to push changes to React state as they happen.