Sqids PLpgSQL

Sqids (pronounced "squids") is a small library that lets you generate unique IDs from numbers. It's good for link shortening, fast & URL-safe ID generation and decoding back into numbers for quicker database lookups.

Features:

• Encode multiple numbers - generate short IDs from one or several non-negative numbers
• Quick decoding - easily decode IDs back into numbers
• Unique IDs - generate unique IDs by shuffling the alphabet once
• ID padding - provide minimum length to make IDs more uniform
• URL safe - auto-generated IDs do not contain common profanity
• Randomized output - Sequential input provides nonconsecutive IDs
• Many implementations - Support for 40+ programming languages

🧰 Use-cases

Good for:

• Generating IDs for public URLs (eg: link shortening)
• Generating IDs for internal systems (eg: event tracking)
• Decoding for quicker database lookups (eg: by primary keys)

Not good for:

• Sensitive data (this is not an encryption library)
• User IDs (can be decoded revealing user count)

🚀 Getting started

Important notes

Note 🚧 The src/install.sql file is idempotent but destructive. It will DROP SCHEMA sqids so be sure you aren't using a schema with that name!

The blocklist is stored in a table. sqids.encode reads that table, so Postgres treats it as STABLE. If you need it to somehow be dynamic per-call, you can likely use transactions, but I have not tested it.

sqids.encodeImmutable does not read sqids.blocklist, which is why it can be used in generated columns. With three arguments it applies the compiled-in default blocklist. Pass a TEXT[] as the fourth argument for a static custom list:

select sqids.encodeImmutable(array[1, 2, 3], 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 0, ARRAY['foo']::text[]);

Table rows only affect sqids.encode. encodeNumbers with a blocklist argument is the post-shuffle core used by those wrappers — call encode / encodeImmutable instead of using it in generated columns.

Numbers must be non-negative. Negative values and NULL elements raise an exception (they used to hang the backend).

Compatibility

Written & tested on Postgres 15.6. The functions used are pretty simple - it will likely work on 9+ (definitely not earlier). Be sure to install & run tests!

Installation

Simply run src/install.sql on your database.

👩‍💻 Examples

After install, use encode & decode:

encode takes an array of BIGINT, an alphabet, and an optional minLength.

select sqids.encode(array[123, 456, 789], 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 12); -- EBDQWDLPCTHG

decode requires the id and alphabet. It returns an array of BIGINT.

select sqids.decode('EBDQWDLPCTHG', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'); -- {123,456,789}

With default alphabet & min length (0)

select sqids.encode(array[123, 456, 789]); --eVH6til6J

With default alphabet & custom min length

select sqids.encode(array[123, 456, 789], 12); --eVH6til6J03E

Decode with default alphabet

select sqids.decode('eVH6til6J03E'); -- {123,456,789}

Generated columns

Use encodeImmutable when the ID should be stored as a generated column. encode is not allowed there because it reads sqids.blocklist.

CREATE TABLE orders (
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  external_id text UNIQUE NOT NULL GENERATED ALWAYS AS (
    sqids.encodeImmutable(ARRAY[id]::BIGINT[], 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 5)
  ) STORED
);

A custom static list is a constant array in the expression (not the table):

external_id text GENERATED ALWAYS AS (
  sqids.encodeImmutable(ARRAY[id]::BIGINT[], 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 5, ARRAY['foo']::text[])
) STORED

🧪 Testing

Run the sql files in tests dir to install.

Then run:

select sqids.alphabet_test();
select sqids.blocklist_test();
select sqids.encoding_test();
select sqids.minlength_test();
select sqids.immutable_test();

📝 License

MIT