Having deployed a fair few web apps, APIs and utilities, I’ve grown more familiar and comfortable with PostgreSQL. As that familiarity has grown, I’ve started moving more functionality into the database; particularly using triggers/functions on UPDATE/INSERT as well as pre-creating JSON(B) objects that can effectively skip the application layer logic and be sent straight to the client.
Slug generation is low hanging fruit where PostgreSQL can excel. Moving this functionality to the database gives you the guarantee that no matter how rows are inserted/updated, they will get a consistent slug.
What is a slug?!
If you’re reading this, you probably know, but a slug is a URL/SEO friendly representation of a string. If for example you wrote an article:
The World’s “Best” Cafés!
You’d want a slug function to create a representation that looks a bit like:
Granted browsers can probably handle the accented “e” but it’s good to be over conservative for backwards compatibility.
PostgreSQL slug function
The most comprehensive slug function I’ve seen for Postgres (and in general) is by
ianks on Github. Slug functions are one of those things that never seems to have a definitive “best and final” version, but this has been very reliable for me.
It’s verbose so you can feel comfortable modifying it, it’s pretty robust and it leverages the
unaccent extension in Postgres to help with transcoding/transliterating non-ASCII characters (i.e. it’ll convert
é => e for you).
One limitation of this function, though, is that it expands quotes (single and double) into a hyphen which - for me - is overkill. I’ve subsequently tweaked the function:
Now you can run the earlier example:
And you get:
| slugify | |-----------------------| | the-worlds-best-cafes |
Using the slug function
Typically you will only want to create a slug when a record is created. If you update the title of something, you probably want to preserve the slug as it may well be part of your websites’ URL structure. You could update your slug each time, but you’d need an audit table to redirect people from the previous slug(s) to the new ones.
Depending on your schema, you can have a function and trigger for each table that requires a slug, OR if you have consistent column names over tables you can create a generic function that faithfully assumes you’ve got a
title and a
This function returns a trigger, whilst the
slugify function returns text. The
NEW keyword above is effectively referencing the table row we are updating.
Note that the above function will happily generate duplicate slugs. You could append an ID,
hashid or some other bit of random text to shoot for uniqueness.
And finally, to add this trigger to your table(s)…
You can now
news a new row with a
title set and when you commit, you’ll see a slug appear that is a URL safe, sensible representation of the title.
I’ve forked the original gist and put it here.