Automating URL Slug Generation in PostgreSQL with Triggers and Functions

When building a web application, one essential feature you’ll need is generating user-friendly URLs—commonly referred to as “slugs”—from database entries, like company names. A slug takes the company’s name and turns it into a URL-safe string, usually by lowercasing it, replacing spaces with dashes, and removing any non-alphanumeric characters.

Thank me by sharing on Twitter 🙏

In my most recent project, I found myself facing a common problem: ensuring that slugs generated from company names were unique, especially for companies with similar names. Manually creating slugs isn’t sustainable in a large system, so I decided to automate this process directly in my PostgreSQL database.

In this post, I’ll walk you through how to set up a trigger in PostgreSQL that automatically generates a unique slug every time a new company is added to the database. We’ll use PostgreSQL’s built-in functions, regular expressions, and triggers to keep everything self-contained within the database. I’ll also show how to handle cases where multiple companies may have similar names, ensuring the slugs remain unique without manual intervention.

Why Automate Slug Generation in PostgreSQL?

When thinking about where to place slug generation logic, you might be tempted to handle it at the application level. That’s perfectly valid, but there are distinct advantages to handling it in the database:

Consistency: All slugs are generated uniformly, no matter how data gets inserted (whether manually, through an admin interface, or via an automated API).
Efficiency: Slug generation happens directly where the data lives, reducing the back-and-forth between the database and the application layer.
Automation: No need to worry about manually setting slugs during data insertion; it’s all automated.

With these advantages in mind, let’s explore how we can use PostgreSQL triggers and functions to streamline slug generation.

HP 910 Cyan, Magenta, Yellow Ink Cartridges | Works with HP OfficeJet 8010, 8020 Series, HP OfficeJet Pro 8020, 8030 Series | Eligible for Instant Ink | 3YN97AN, 3 Count (Pack of 1)

(17415)

$39.89 (as of January 22, 2025 11:32 GMT +00:00 - )

The Game Boy Encyclopedia: Every Game Released for the Nintendo Game Boy and Game Boy Color

(18)

$27.40 (as of January 22, 2025 11:32 GMT +00:00 - )

Elebase USB to USB C Adapter 4 Pack,USBC Female to A Male Car Charger,Type C Converter for iPhone 16 Pro Max,15 14 13 12 11 Plus,Apple Watch iWatch 10 9 8,Airpods,iPad Air Mini 6 7,Samsung Galaxy S24

(51499)

$9.99 (as of January 22, 2025 11:32 GMT +00:00 - )

1. Creating the Slug Generation Logic

First, we need to define the rules for how to convert a company name into a slug. The primary steps are straightforward:

Convert the name to lowercase.
Replace spaces with dashes (-).
Remove any non-alphanumeric characters (except dashes).
Ensure that no two companies have the same slug.

To start, we can encapsulate these steps in a PostgreSQL function. Here’s what our function looks like in plain SQL:

SQL

CREATE OR REPLACE FUNCTION generate_unique_slug()
RETURNS TRIGGER AS $$
DECLARE
    base_slug TEXT;
    final_slug TEXT;
    slug_rank INT;
BEGIN
    -- Generate the initial slug by processing the name
    base_slug := lower(
                      regexp_replace(
                          regexp_replace(
                              regexp_replace(NEW.name, '\s+', '-', 'g'),
                              '[^a-zA-Z0-9\-]', '', 'g'
                          ),
                      '-+', '-', 'g')
                  );

    -- Check if this slug already exists and if so, append a number to ensure uniqueness
    SELECT COUNT(*) INTO slug_rank
    FROM company
    WHERE slug LIKE base_slug || '%';

    IF slug_rank = 0 THEN
        -- No duplicates found, assign base slug
        final_slug := base_slug;
    ELSE
        -- Duplicates found, append the count as a suffix
        final_slug := base_slug || '-' || slug_rank;
    END IF;

    -- Assign the final slug to the new record
    NEW.slug := final_slug;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Explanation of the Function:

Base Slug Generation: The function first converts the company name to lowercase and replaces spaces with dashes. Then, using regular expressions, it removes any non-alphanumeric characters. This gives us the basic slug.
Checking for Duplicates: It checks if the slug already exists in the database. If duplicates are found, the function appends a number to the slug (e.g., company-1 or company-2) to ensure uniqueness.
Returning the Final Slug: Finally, the slug is assigned to the new record being inserted.

2. Creating the Trigger

With the slug generation function in place, we can now set up a trigger that fires whenever a new row is inserted into the company table. The trigger will only generate a slug if the slug field is null, ensuring that pre-existing slugs aren’t overwritten.

Here’s how you can set up the trigger:

SQL

CREATE TRIGGER set_company_slug
BEFORE INSERT ON company
FOR EACH ROW
WHEN (NEW.slug IS NULL)
EXECUTE FUNCTION generate_unique_slug();

How the Trigger Works:

Before Insert: The trigger activates just before a row is inserted into the company table. This ensures the slug is generated before the data gets saved.
When Condition: The trigger only runs if the slug field is NULL. This condition ensures that the slug is only generated for new records that don’t already have a slug value.

With this trigger in place, any time you insert a new company into the company table without specifying a slug, PostgreSQL will automatically generate a unique, URL-safe slug for you.

3. Handling Multiple Inserts and Duplicates

You might be wondering: What happens when two companies have the same or very similar names? That’s where our duplicate handling logic comes in.

Our function includes a mechanism to check if the generated slug already exists in the database. If it finds duplicates, it appends a number to the slug to make it unique.

For example, if you insert two companies with the name “Tech Corp,” the first one might get the slug tech-corp, and the second one will get tech-corp-1. This keeps the slugs unique while maintaining readability.

4. Testing the Trigger

To test this functionality, you can manually insert some data into the company table. Here’s a quick example in TypeScript (assuming you’re using a PostgreSQL client like pg):

TypeScript

import { Client } from 'pg';

const client = new Client({
  user: 'your_username',
  host: 'localhost',
  database: 'your_database',
  password: 'your_password',
  port: 5432,
});

async function insertCompany(name: string) {
  await client.connect();
  const res = await client.query(
    `INSERT INTO company (name, slug) VALUES ($1, NULL) RETURNING *`,
    [name]
  );
  console.log(res.rows[0]);
  await client.end();
}

insertCompany('Tech Corp');
insertCompany('Tech Corp');  // This will trigger slug generation with a suffix

The TypeScript example inserts two companies with the same name, triggering the PostgreSQL slug generation logic. The first company will have a simple slug, while the second will have a suffix to make it unique.

Conclusion

Automating URL slug generation within PostgreSQL is a powerful way to keep your data layer smart and efficient. By leveraging PostgreSQL functions and triggers, you can offload slug creation logic from your application code, ensuring consistency and uniqueness directly in the database.

This approach eliminates the need to manage slugs manually and handles edge cases like duplicates seamlessly. The database takes care of everything, whether the data is inserted through an API, a manual process, or another backend system.

As a result, you’ll have clean, user-friendly URLs for all your companies, and you can rest easy knowing that the database will ensure uniqueness.