In web development, creating clean and SEO-friendly URLs is crucial. One way to achieve this is by using slugs human-readable, URL-safe versions of your content titles. In this blog post, we'll explore how to create unique slugs on Supabase using PostgreSQL functions and triggers.

What is a Slug?

A slug is a URL-friendly version of a string, typically used to create readable and meaningful URLs. For example, if you have a blog post titled "10 Tips for Better Sleep", its slug might be "10-tips-for-better-sleep".

The Challenge

Creating slugs is straightforward, but ensuring their uniqueness can be tricky. What if you have two blog posts with the same title? Or what if you want to update a title without changing its existing slug? This is where our PostgreSQL function and trigger come in handy.

The Solution

We'll create a PostgreSQL function to generate slugs and a trigger to automatically create unique slugs when inserting new records. On the Supabase dashboard navigate to SQL Editor, and follow the steps below:

1. Create a function to generate slugs
2. Create a function to ensure slug uniqueness
3. Set up a trigger to automatically generate unique slugs on insert

Let's break it down step by step.

Step 1: Create a Slugify Function

First, we'll create a function that converts a string into a slug:

CREATE EXTENSION IF NOT EXISTS "unaccent";

CREATE OR REPLACE FUNCTION slugify("value" TEXT)
RETURNS TEXT AS $$
-- removes accents (diacritic signs) from a given string --
  WITH "unaccented" AS (
    SELECT unaccent("value") AS "value"
  ),
-- lowercases the string
  "lowercase" AS (
    SELECT lower("value") AS "value"
    FROM "unaccented"
  ),
-- remove single and double quotes
  "removed_quotes" AS (
    SELECT regexp_replace("value", '[''"]+', '', 'gi') AS "value"
    FROM "lowercase"
  ),
-- replaces anything that's not a letter, number, hyphen('-'), or underscore('_') with a hyphen('-')
  "hyphenated" AS (
    SELECT regexp_replace("value", '[^a-z0-9\\-_]+', '-', 'gi') AS "value"
    FROM "removed_quotes"
  ),
-- trims hyphens('-') if they exist on the head or tail of the string
  "trimmed" AS (
    SELECT regexp_replace(regexp_replace("value", '\-+$', ''), '^\-', '') AS "value"
    FROM "hyphenated"
  )
  SELECT "value" FROM "trimmed";
$$ LANGUAGE SQL STRICT IMMUTABLE;

This function does the following:

• Removes accents
• Converts to lowercase
• Removes quotes
• Replaces non-alphanumeric characters with hyphens
• Trims leading and trailing hyphens

Step 2: Create a Function for Unique Slug Generation

Next, we'll create a function that ensures the uniqueness of our slugs:

CREATE OR REPLACE FUNCTION public.set_unique_slug_from_name() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    base_slug TEXT;
    new_slug TEXT;
    counter INTEGER := 1;
BEGIN
-- Generate the base slug
    base_slug := slugify(NEW.name);
    new_slug := base_slug;

-- Check if the slug already exists
    WHILE EXISTS (SELECT 1 FROM place WHERE slug = new_slug) LOOP
-- If it exists, append a number and increment
        new_slug := base_slug || '-' || counter;
        counter := counter + 1;
    END LOOP;

    NEW.slug := new_slug;
    RETURN NEW;
END
$$;

This function:

• Generates a base slug using our slugify function
• Checks if the slug already exists in the table
• If it exists, appends a number to make it unique

Step 3: Create a Trigger

Finally, we'll create a trigger that automatically generates a unique slug when a new record is inserted:

CREATE OR REPLACE TRIGGER "t_place_insert" BEFORE INSERT ON "place" FOR EACH ROW WHEN (NEW.name IS NOT NULL AND NEW.slug IS NULL)
EXECUTE PROCEDURE set_unique_slug_from_name();

This trigger fires before an insert on the "place" table, but only when a name is provided and no slug is specified. Remember to click in the "Run” button after each step or after copy and paste all steps together.

Putting It All Together

With these functions and trigger in place, you can now insert records into your table without worrying about slug creation or uniqueness. For example:

INSERT INTO place (name) VALUES ('My Awesome Place');
INSERT INTO place (name) VALUES ('My Awesome Place');

The first insert will create a slug "my-awesome-place", and the second will automatically create "my-awesome-place-1".

By leveraging PostgreSQL's powerful functions and triggers, we've created a robust system for generating unique slugs in Supabase. This approach ensures that your URLs remain clean and SEO-friendly, while also handling potential conflicts automatically.

Remember, while this solution works well for many cases, you might need to adjust it based on your specific requirements, such as handling updates to existing records or implementing custom slug formats.

Happy coding!