skills.rest
azlekov
azlekovCOMMUNITY
Explore all skills by this author →
𝕏@azlekovGitHub

postgres-nanoid

Generate prefixed Nanoid public identifiers for PostgreSQL with pgcrypto.

1|Updated Dec 30, 2025
Search Tags:#postgresql#migration#database-design#IDs#nanoid#URL-safe#prefix
One-click install
npx skills add https://github.com/azlekov/my-claude-code --skill postgres-nanoid
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgres-nanoid
Source: https://github.com/azlekov/my-claude-code/tree/main/skills/postgres-nanoid
Command: npx skills add https://github.com/azlekov/my-claude-code --skill postgres-nanoid

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

PostgreSQL often stores internal UUIDs that should not be exposed in public APIs or URLs. This skill provides a standardized approach to generate and use prefixed Nanoid identifiers (public_id) that map to internal keys while remaining URL-safe and human-friendly.

Core Features & Use Cases

  • Hybrid ID pattern: internal UUIDs use for database references; public_id uses nanoid with a prefix for API responses and external links.
  • Prefix conventions: consistent 17-character random suffix after a short, readable prefix (e.g., usr_, ord_, prd_).
  • Use cases: API responses, URL slugs, data exports, and safe migrations to add public_id columns.
  • Migration guidance: add public_id column with default nanoid(prefix_), backfill existing rows, and enforce format with CHECK constraints and indexing.

Quick Start

  • Create the nanoid helper function in your database (or apply the provided migration).
  • Generate a sample prefixed ID: SELECT nanoid('usr_');
  • Use as a default for a column: ALTER TABLE public.orders ADD COLUMN public_id TEXT DEFAULT nanoid('ord_');
  • Validate format with a regex and create an index for efficient lookups.

Frequently Asked Questions about postgres-nanoid

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I generate URL-safe public IDs in PostgreSQL instead of exposing internal UUIDs?▼

Generate prefixed nanoid identifiers in PostgreSQL by creating a custom nanoid function to use as a column default. This produces URL-safe public IDs with prefixes like usr_ or ord_, keeping internal UUIDs hidden from API responses.

What is the hybrid ID pattern for PostgreSQL database design?▼

The hybrid ID pattern uses internal UUIDs for database references while exposing a public_id column with prefixed nanoid values for external use. This decouples internal database keys from API responses and URL slugs.

How do I add a nanoid public_id column to an existing PostgreSQL table?▼

Add a public_id column using ALTER TABLE with a default nanoid(prefix) value, then backfill existing rows. Enforce the nanoid format with CHECK constraints using regex validation and create an index for efficient lookups.

Does generating nanoid IDs in PostgreSQL require the pgcrypto extension?▼

Yes, generating nanoid IDs in PostgreSQL relies on the pgcrypto extension. You must enable this extension before creating the user-defined nanoid helper function to ensure proper random string generation for prefixed public identifiers.

What's the best way to enforce consistent prefix conventions for IDs in a PostgreSQL database?▼

The best way to enforce prefix conventions is to use a standardized nanoid function with defined prefixes like usr_ or ord_ followed by a 17-character random suffix. Apply CHECK constraints with regex validation to maintain format integrity.

When should I avoid using prefixed nanoid IDs in PostgreSQL?▼

Avoid using prefixed nanoid IDs if your application requires direct exposure of internal UUIDs or lacks support for the pgcrypto extension. Also avoid them if your workflow cannot enforce format validation via CHECK constraints.

Related Skills

Explore More Software Engineering Skills→
GDSDNGDSDN
3

db-supabase-setup

Guide interactive Supabase project setup with PostgreSQL extensions and schema initialization.

Community
Advanced
liuzhengdongfortestliuzhengdongfortest
1.1k

cs-libdoc

Generates API references from source code for libraries and frameworks.

Community
Advanced
mgkyawzayyamgkyawzayya
1

database-change-management

Plan and implement reversible Laravel/Eloquent database schema migrations with backfills.

Community
Advanced
ThinkOodleThinkOodle
5

uuid-primary-keys

Implement UUID primary keys in Ruby on Rails with PostgreSQL and SQLite.

Official
Advanced