Checking for duplicate posts in a supabase database

15/04/2024

After I got to MVP (minimum viable product) on the blogging/article posting function of DVLN.xyz, I quickly ran into an issue with creating duplicate posts when doing further testing.

Slugification

Post are displayed on the posts page by querying my Supabase database by slug. These slugs are created dynamically from the post title the first time a new post is saved.

For example, if I created a post with the title "How to train a dragon", it would automatically get saved to the database with the slug how-to-train-your-dragon. The "slugification" (it's a word), is done like this

const slug = title
      .replace(/[^\w\s\']|_/g, "")
      .replace(/\s+/g, " ")
      .replaceAll(" ", "-")
      .toLowerCase();

.replace(/[^\w\s\']|_/g, "") removes any character that is not a word, whitespace character, apostrophe, or underscore. .replace(/\s+/g, " ") then replaces any remaining whitespaces with a single space (this is to catch any double spaces or non-space whitespace characters). .replaceAll(" ", "-") replaces all spaces with dashes, and finally .toLowerCase() converts the whole string to lowercase. What this gives me is a valid slug to work with, free from punctuation, capital letters, weird characters, and errant spaces.

Checking for duplicates

It turns out, when you're testing a new blog CRUD function, you end up creating a lot of posts titled:

  • Blog post
  • Another Blog Post
  • Hello World
  • asdfasdfasdf

At a certain point, I tried to create a post with the exact same title as an existing one. As I've told Supabase I need the slug field to be UNIQUE, it started yelling at me in the console.

To solve this, I wrote a function that checks whether a post already exists in the database at the point when I click "Save" on a new post.

const { count } = await supabase
      .from("posts")
      .select("*", { count: "exact", head: true })
      .eq("slug", slug);

    if (count) {
      return fail(400, { slug, slugExists: true });
    }

This uses a Supabase feature called count to check for the presence of a post with the same slug as the one just created from the title I've chosen for my new post. It does this without actually returning any data from Supabase (the head: true parameter), which I'm sure is somehow better for performance or something.

Then, if count is truthy I return a fail, and set slugExists to true to be caught in a form action.

Telling the user

Now that I can't actually create a post with the same title, I need this new information to have an effect on the frontend to tell the user (me).

{#if form?.slugExists}
  <div class="alert alert-warning" role="alert">
    A post with the same slug already exists. Please pick a different title.
  </div>
{/if}

This hooks into the Sveltekit form action that I've defined for creating and updating blog posts. It checks the form for a "slugExists" error, and if it finds one, renders a little Bootstrap-styled error message.

References