Porting Sqids to PostgreSQL using Rust

Sqids is a small open-source project that converts numbers into unique IDs.

It has over 40 implementations for whatever programming languages your heart desires, all created and maintained by volunteers.

I’ve been in awe of how quickly people have been contributing in all kinds of ways to the project, but one repo has been evading everyone’s time - the PostgreSQL extension .

The project is structured so unimplemented repos are sitting there empty, and people can star them to indicate their demand. This empty repo has accumulated 67 stars at the time, and a healthy discussion of people waiting for somebody to implement it.

Having zero experience writing a database extension, naturally, I thought I was a good fit 🫠😅

My first problem was: I don’t know C [well]. It’s been forever since I’ve dabbled in it, and I had no idea where to even begin.

Thankfully, it turns out you can write PostgreSQL extensions in Rust !

This is not a technical dive into pgrx or how to get going with it (there are plenty of those). Just a few notes as I was making my first PostgreSQL extension:

Refreshingly easy to get going

The project’s README is structured so well that:

1. I didn’t have to mess around with PostgreSQL on my local machine
2. I didn’t even have to open PostgreSQL docs
3. Within 5 minutes, I had a working Hello World example

The project has a few commands to get you going:

cargo pgrx init
cargo pgrx run

After that, you modify examples to fit your needs.

Function overloading ftw

Sqids is a simple project. It contains two functions: encode and decode. The problem is that the instance is configurable and that’s where complexity slips in.

This is easy to implement in most programming languages, but I had to wrap my head around how to provide similar functionality in SQL.

For example, one option was to implement something like this:

select sqids_encode(1, 2, 3);
select sqids_encode_with_alphabet('alph4bet', 1, 2, 3);

With several configurable parameters (this particular project has 3), that would have been a lot of functions.

Rust doesn’t support function overloading, but PostgreSQL does. pgrx solves this well:

#[pg_extern(name = "sqids_encode")]
fn sqids_encode(numbers: VariadicArray<i64>) -> Result<String, PgError> {
	// @TODO
}

#[pg_extern(name = "sqids_encode")]
fn sqids_encode_with_alphabet(
	alphabet: &str,
	numbers: VariadicArray<i64>,
) -> Result<String, PgError> {
	// @TODO
}

So, I ended up implementing just 2 SQL functions: sqids_encode and sqids_decode (with support for multiple parameters).

This is what the API looks like now:

select sqids_encode(1, 2, 3);
select sqids_encode('alph4bet', 1, 2, 3); -- notice the same function name

select sqids_decode('86Rf07');
select sqids_decode('alph4bet', '86Rf07');

Maintainer support 🥇

I stumbled on an issue where I didn’t know how to structure my arrays and tests . The boilerplate code generated a test function with sample usage, but I wasn’t sure how to:

1. Move it out to a dedicated test file so it doesn’t get messy
2. Have my tests in SQL instead of Rust

The README didn’t give too many clues to the above, so I created a Github issue and the developer guided me to a solution in a very timely manner.

There’s tons to be said about open-source projects where devs do unpaid support, but that’s another topic. I appreciate anyone dedicating their time to helping others (if that’s you - bravo 👏).

Testing is easy(-ish)

You can see the tests here .

Apparently, you can also have them in Rust if that’s what you prefer. I haven’t figured out how to move them to a separate test file, but that’s fine for this small project.

I do wonder how bigger pgrx projects handle this…

The repo is available here and improvements are welcome.