Build Your Blog Backend with Supabase: Set Up Tables and Relationships

What This Series Covers#

This is Part 1 of a multi-part series on building a complete blog with Supabase, Astro, and Cloudflare.

• Part 1: Getting Started with Supabase, Setting up your project, creating tables, establishing relationships
• Part 2: Fine-Tuning Your Database, Row-Level Security, functions, and triggers
• Part 3: Setting Up Your Admin Interface, Building the editorial backend

Setting Up a Supabase Project#

First things first: if you haven't heard of Supabase yet, think of it as Firebase but with a cool Postgres backbone. It gives you the database, authentication, and storage all in one slick package. Perfect for a blog.

Step 1: Sign Up and Create a Project#

Head over to Supabase and get yourself signed up. Once you're in:

• Create a New Project: Give it a name, set a strong password, and choose a region close to where your audience hangs out.
• Project Settings: After your project is ready, jot down your API keys and URL. You'll need those later when you hook up the backend to your Astro frontend.

Step 2: Dive into the SQL Editor#

Supabase gives you a neat SQL editor right in the dashboard. This is where we set up our database schema.

Creating the UserProfile Table#

Now let's make sure your authors have some personality. The UserProfile table stores the user's name, bio, and other profile-related details.

CREATE TABLE UserProfile (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
    name text NOT NULL,
    bio text,
    email text NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);

What Each Field Does#

• id: A unique identifier for each profile, auto-generated as a UUID.
• user_id: Links back to the user's ID in auth.users, connecting profiles to Supabase authentication.
• name: The author's name, required.
• bio: A field for author biography, optional.
• email: The author's email, copied from auth.users and required.
• created_at and updated_at: Timestamps that track when the profile was created and last modified.

Creating the Posts Table#

Every blog needs a place to store its posts. This table holds everything from the post title to the content, images, and the author's ID.

CREATE TABLE Posts (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    title text NOT NULL,
    featured_images jsonb DEFAULT '[]'::jsonb,
    slug text NOT NULL,
    unique_id text NOT NULL UNIQUE,
    excerpt text,
    content text,
    author uuid REFERENCES auth.users(id) ON DELETE SET NULL,
    author_profile uuid REFERENCES UserProfile(id) ON DELETE SET NULL,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    deleted_at timestamp with time zone,
    publish_date timestamp with time zone DEFAULT now()
);

What's Happening Here#

• id: Every post gets a unique ID, thanks to UUIDs.
• featured_images: Stored as JSON objects, perfect for multiple images per post.
• slug: The URL-friendly identifier for your post.
• unique_id: A unique identifier that must be different for every post.
• author: A reference to auth.users, keeping track of who wrote what.
• author_profile: A reference to the UserProfile table for quick access to author details.
• publish_date: When the post should go live.

Establishing Relationships Between Tables#

Now that you have your tables, it's time to make them talk to each other. We've already set up foreign keys in both tables. These are the bridges that connect your data.

• Foreign Keys: The author field in Posts and user_id in UserProfile link your tables together.
• Joins: When you query data, these relationships let you pull in the author's name and bio whenever you grab a post. Everything stays connected, and Supabase makes it super easy to keep everything in sync.

Wrapping Up Part 1#

Boom. You've laid the foundation of your blog. Your Supabase project is up, your tables are ready, and the relationships are set. In the next part, we'll dive into locking down access to content using Supabase's Row-Level Security. Stay tuned, and keep that terminal warm because we're just getting started.