[MyNextHome] Simple Backend Server (Supabase) with Express JS

Last weekend, I've added a Express Backend to serve the data (open homes) to my app! Data hosted on Supabase - keep one table for now. This article will explain to set it up.

What is Supabase?

Supabase is an open-source Firebase alternative that provides:

• PostgreSQL Database - A powerful, open-source relational database
• Real-time Subscriptions - Listen to database changes in real-time

For this project, we're using Supabase primarily as our PostgreSQL database layer, which provides a robust and scalable solution for storing property listing data.

Project Overview

Our backend application fetches open home listings from the TradeMe API and stores them in Supabase. It then exposes REST endpoints to query this data efficiently.

Step 1: Setting Up Supabase Configuration

First, we need to create a Supabase client instance. Here's our configuration file:

// config/supabase.js
const { createClient } = require('@supabase/supabase-js');
require('dotenv').config();

const supabaseUrl = process.env.SUPABASE_URL;
const supabaseKey = process.env.SUPABASE_ANON_KEY;

if (!supabaseUrl || !supabaseKey) {
    console.error('ERROR: SUPABASE_URL and SUPABASE_ANON_KEY must be set in .env file');
    throw new Error('Supabase configuration is missing. Please set SUPABASE_URL and SUPABASE_ANON_KEY in your .env file');
}

const supabase = createClient(supabaseUrl, supabaseKey);

module.exports = supabase;

Key points:

• Uses environment variables for secure credential management
• Validates configuration before creating the client
• Exports a singleton client instance for reuse across the application

Environment variables (.env):

SUPABASE_URL=https://your-project.supabase.co
SUPABASE_ANON_KEY=your-anon-key-here

Step 2: Database Schema Design

The open_homes table stores property listings with the following structure:

CREATE TABLE open_homes (
  id BIGSERIAL PRIMARY KEY,
  listing_id TEXT UNIQUE NOT NULL,
  title TEXT,
  location TEXT,
  bedrooms INTEGER DEFAULT 0,
  bathrooms INTEGER DEFAULT 0,
  open_home_time TIMESTAMPTZ,
  price TEXT,
  picture_href TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_listing_id ON open_homes(listing_id);
CREATE INDEX idx_open_home_time ON open_homes(open_home_time);

• listing_id is unique to prevent duplicates
• Indexes on listing_id and open_home_time for faster queries
• Timestamps for tracking record creation and updates

Step 3: Implementing Database Functions

Now let's create reusable database functions in lib/database.js:

Saving Data with Upsert

// lib/database.js
const supabase = require('../config/supabase');

async function saveOpenHomesToDB(openHomes) {
    try {
        // Transform data to match database schema
        const homesToSave = openHomes.map(home => ({
            listing_id: home.listingId?.toString() || home.id?.toString(),
            title: home.title,
            location: home.location,
            bedrooms: home.bedrooms || 0,
            bathrooms: home.bathrooms || 0,
            open_home_time: home.openHomeTime ? new Date(home.openHomeTime) : null,
            price: home.price,
            picture_href: home.pictureHref
        }));

        // Upsert (insert or update) based on listing_id
        const { data, error } = await supabase
            .from('open_homes')
            .upsert(homesToSave, { onConflict: 'listing_id' });

        if (error) {
            console.error('Error saving to Supabase:', error);
            throw error;
        }

        console.log(`Saved ${homesToSave.length} open homes to Supabase`);
        return data;
    } catch (error) {
        console.error('Failed to save open homes to Supabase:', error.message);
        throw error;
    }
}

Why upsert?

• Prevents duplicate entries when re-fetching data
• Updates existing records if data has changed
• Uses listing_id as the conflict resolution key

Fetching All Open Homes

async function getOpenHomesFromDB() {
    try {
        const { data, error } = await supabase
            .from('open_homes')
            .select('*')
            .order('open_home_time', { ascending: true });

        if (error) {
            console.error('Error fetching from Supabase:', error);
            throw error;
        }

        // Transform back to match your API response format
        return data.map(home => ({
            id: home.id,
            listingId: home.listing_id,
            title: home.title,
            location: home.location,
            bedrooms: home.bedrooms,
            bathrooms: home.bathrooms,
            openHomeTime: home.open_home_time,
            price: home.price,
            pictureHref: home.picture_href
        }));
    } catch (error) {
        console.error('Failed to fetch open homes from Supabase:', error.message);
        throw error;
    }
}

Step 4: Integrating with Express API

Now let's integrate these functions into our Express routes:

// index.js
const express = require("express");
const { saveOpenHomesToDB, getOpenHomesFromDB, getOpenHomeById } = require("./lib/database");

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

// GET: list all open homes
app.get("/api/open-homes", async (req, res) => {
  try {
    const openHomes = await getOpenHomesFromDB();
    res.json(openHomes);
  } catch (error) {
    res.status(500).json({
      message: "Failed to fetch open homes from database",
      error: error.message
    });
  }
});

// GET: single open home by ID
app.get("/api/open-homes/:id", async (req, res) => {
  try {
    const home = await getOpenHomeById(req.params.id);

    if (home) {
      return res.json(home);
    }

    // Fallback: fetch from TradeMe API if not in database
    const openHomes = await fetchOpenHomes();
    const foundHome = openHomes.find((item) =>
      item.listingId?.toString() === req.params.id ||
      item.id?.toString() === req.params.id
    );

    if (!foundHome) {
      return res.status(404).json({ message: "Home not found" });
    }

    res.json(foundHome);
  } catch (error) {
    res.status(500).json({
      message: "Failed to fetch open home",
      error: error.message
    });
  }
});

Best Practices Implemented

1. Error Handling: Comprehensive try-catch blocks with meaningful error messages
2. Data Validation: Checks for required fields before database operations
3. Type Conversion: Properly converts dates and ensures consistent data types
4. Separation of Concerns: Database logic separated from API route handlers
5. Upsert Pattern: Prevents duplicates and handles updates gracefully
6. Environment Variables: Keeps credentials secure and out of code

Code Repository Structure

househunt-backend/
├── config/
│   └── supabase.js          # Supabase client configuration
├── lib/
│   └── database.js          # Database operation functions
├── index.js                 # Express API routes
└── package.json             # Dependencies including @supabase/supabase-js

Installation

npm install @supabase/supabase-js dotenv express cors