Home

Awesome

Nano ID for PostgreSQL

Inspired by the following parent project: ai/nanoid

<img src="./logo.svg" align="right" alt="Nano ID logo by Anton Lovchikov" width="180" height="94">

A tiny, secure, URL-friendly, unique string ID generator for Postgres.

“An amazing level of senseless perfectionism, which is simply impossible not to respect.”

How to use

SELECT nanoid(); -- Simplest way to use this function. Creates an id, with the defaults of the created nanoid() function.
SELECT nanoid(4); -- size parameter set to return 4 digit ids only
SELECT nanoid(3, 'abcdefghij'); -- custom size and alphabet parameters defined. nanoid() generates ids concerning them.
SELECT nanoid(10, '23456789abcdefghijklmnopqrstuvwxyz', 1.85); -- nanoid() could generates ids more performant with a custom defined additional bytes factor.

CREATE TABLE mytable(
    id char(21) DEFAULT nanoid() PRIMARY KEY
);

or

-- To use a custom id size
CREATE TABLE mytable(
    id char(14) DEFAULT nanoid(14) PRIMARY KEY
);

or

-- To use a custom id size and a custom alphabet
CREATE TABLE mytable(
    id char(12) DEFAULT nanoid(12, 'ABC123') PRIMARY KEY
);

Getting Started

Requirements

Execute the file nanoid.sql to create the nanoid() function on your defined schema. The nanoid() function will only be available in the specific database where you run the SQL code provided.

Manually create the function in each database: You can connect to each database and create the function. This function can be created manually or through a script if you have many databases. Remember to manage updates to the function. If you change the function in one database, those changes will only be reflected in the other databases if you update each function.

Adding to the default template database

Use a template database: If you often create databases that need to have the same set of functions, you could create a template database that includes these functions. Then, when you create a new database, you can specify this template, and PostgreSQL will make the new database a copy of the template.

Here's how to do that:

  1. Connect to template1 database:
  2. Then, run your nanoid() function creation code.

If the function is only needed for specific applications, it might be better to create it manually in each database where needed or create a custom template database that includes this function and use that template when creating new databases for these applications.

Also, note that changes to template1 won't affect existing databases, only new ones created after the changes. Existing databases will need to have the function added manually if required.

Reference: Template Databases

Calculating the additional bytes factor for a custom alphabet

If you change the alphabet of the nanoid() function, you could optimize the performance by calculating a new additional bytes factor with the following SQL statement:

WITH input as (SELECT '23456789abcdefghijklmnopqrstuvwxyz' as alphabet)
SELECT round(1 + abs((((2 << cast(floor(log(length(alphabet) - 1) / log(2)) as int)) - 1) - length(alphabet)::numeric) / length(alphabet)), 2) as "Optimal additional bytes factor"
FROM input;

-- The resulting value can then be used f.e. as follows:
SELECT nanoid(10, '23456789abcdefghijklmnopqrstuvwxyz', 1.85);

Utilizing a custom-calculated additional bytes factor in nanoid() enhances string generation performance. This factor determines how many bytes are generated in a single batch, optimizing computational efficiency. Generating an optimal number of bytes per batch minimizes redundant operations and conserves memory.

Usage Guide: nanoid_optimized()

The nanoid_optimized() function is an advanced version of the nanoid() function designed for higher performance and lower memory overhead. While it provides a more efficient mechanism to generate unique identifiers, it assumes that you know precisely how you want to use it.

🚫 Warning: No checks are performed inside nanoid_optimized(). Use it only if you're sure about the parameters you' re passing.

Function Signature

nanoid_optimized(
    size int,
    alphabet text,
    mask int,
    step int
) RETURNS text;

Parameters

Example Usage

Generate a NanoId String of length 10 using the default alphabet set:

SELECT nanoid_optimized(10, '_-0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 63, 16);

Tips:

By following this guide, you can seamlessly integrate the nanoid_optimized() function into your projects and enjoy the benefits of its optimized performance.

LEAKPROOF Setting

Default Configuration

The nanoid() function is configured without the LEAKPROOF attribute by default to ensure compatibility across diverse environments, including cloud platforms and managed services, without the need for superuser privileges.

When to Enable LEAKPROOF

Enabling LEAKPROOF is optional and beneficial in environments that require enhanced security measures, such as those utilizing row-level security (RLS). This setting should be considered if you have superuser access and seek to further restrict information leakage.

Note: To apply the LEAKPROOF attribute, uncomment the LEAKPROOF line in the function definition. This setting is permissible only for superusers due to its implications for database security and operation.

Using MySQL/MariaDB?

If you're using MySQL or MariaDB and you found this library helpful, we have a similar library for MySQL/MariaDB, too! Check out our Nano ID for MySQL/MariaDB repository to use the same capabilities in your MySQL/MariaDB databases.

🌱 Contributors Welcome

All relevant details about the project can be found in this README.

Your active participation 🤝 is a cornerstone of nanoid-postgres. Thank you for joining us on this journey.

🖥️ Authors

See also the list of contributors who participated in this project. 💕

License

This project is licensed under the Apache License 2.0 - see the LICENSE file for details.