Creating intuitive and user-friendly URLs is a cornerstone of good web design and SEO. One of the best ways to achieve this is through the use of “slugs,” which are human-readable identifiers that replace the cryptic URLs often generated by databases. In this post, we’ll explore how PostgreSQL, a powerful open-source relational database, can be used to automate the creation of unique slugs, ensuring that each URL is not only readable but also unique.

Thanks to this article by Broadlume Product, we already have a good starting point for generating slugs. The article provides a simple function that takes a string and returns a slug. However, the function does not guarantee that the slug is unique, which is a critical requirement for any web application. In this post, we’ll build on this function to ensure that the slugs generated are unique.

Basic Implementation

CREATE EXTENSION IF NOT EXISTS "unaccent";

CREATE OR REPLACE FUNCTION slugify("value" TEXT)
RETURNS TEXT AS $$
BEGIN
  RETURN regexp_replace(
           regexp_replace(
             lower(unaccent("value")), -- Lowercase and remove accents in one step
             '[^a-z0-9\\-_]+', '-', 'gi' -- Replace non-alphanumeric characters with hyphens
           ),
           '(^-+|-+$)', '', 'g' -- Remove leading and trailing hyphens
         );
END
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

Ensuring Uniqueness

To ensure that the slugs generated are unique, we can use a combination of the slugify function and a recursive query. The recursive query will append a number to the slug if it already exists in the database. Here’s how we can achieve this:

CREATE OR REPLACE FUNCTION public.set_slug_from_name() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    base_slug TEXT;
    final_slug TEXT;
    counter INTEGER := 1;
BEGIN
    -- Generate the initial slug based on the 'name' field
    base_slug := slugify(NEW.name);
    final_slug := base_slug;

    -- Loop to ensure uniqueness of the slug
    LOOP
        -- Check if the slug already exists in the table
        IF EXISTS (SELECT 1 FROM "my_table_name" WHERE slug = final_slug AND id != COALESCE(NEW.id, 0)) THEN
            -- If it exists, append a numeric suffix and increment the counter
            final_slug := base_slug || '-' || counter;
            counter := counter + 1;
        ELSE
            -- If it's unique, exit the loop
            EXIT;
        END IF;
    END LOOP;

    -- Set the unique slug to the 'slug' field of the NEW record
    NEW.slug := final_slug;
    RETURN NEW;
END
$$;

In this function, we first generate the base slug using the slugify function. We then enter a loop to check if the slug already exists in the table. If it does, we append a numeric suffix to the slug and increment the counter. We continue this process until we find a unique slug. Finally, we set the unique slug to the slug field of the NEW record and return it.

To use this function, we need to create a trigger that calls it before inserting or updating a record in the table. Here’s how we can create the trigger:

CREATE TRIGGER set_slug_from_name
BEFORE INSERT OR UPDATE
ON "my_table_name"
FOR EACH ROW
EXECUTE FUNCTION public.set_slug_from_name();

Conclusion

By leveraging PostgreSQL’s advanced features, developers can automate the creation of unique, SEO-friendly slugs, enhancing the user experience and potentially boosting search engine rankings. This approach not only simplifies the development process but also ensures consistency and uniqueness across all slugs generated by your application.

References