Building Stripe-like Public IDs in Postgres with Nano ID

Ever notice how Stripe IDs look like cus_dnh9ixB5QT or pi_3MqO8sL? That's not by accident.

These prefixed IDs aren't just a technical choice - they're a UX win. When you're debugging at 2am, team_ZsIylPM6qgNa tells you exactly what you're looking at. When users paste IDs in support tickets, you instantly know which table to query. When comparing two IDs side by side, the prefix makes it obvious if they're the same type.

I recently implemented this pattern in LocalCan when adding team support - two years after launch. In this post, I'll show you exactly how I did it, share the actual schema I use in production, and give you a copy-paste SQL function that works on any Postgres database. No extensions needed.

Why You Need Public IDs

Your database probably uses auto-incrementing integers or UUIDs for primary keys. Both work great internally. But exposing them directly causes problems.

Auto-incrementing IDs leak information:

• Competitors can guess your growth (order_145 vs order_52847)
• Users can enumerate resources (/users/1, /users/2, /users/3)
• You expose internal ordering data

Raw UUIDs are ugly:

• Hard to read: 550e8400-e29b-41d4-a716-446655440000
• No type context (is this a user? an order? a team?)
• Copy-paste errors are hard to spot

Stripe-style IDs solve both problems:

• cus_dnh9ixB5QT - clearly a customer
• ord_8M0kXqY - clearly an order
• team_ZsIylPM6qgMa - clearly a team

The prefix tells you what you're looking at. The random suffix reveals nothing about your data.

Comparing ID Types

Before picking an approach, let's compare what's available.

UUIDv4 is the classic choice. Built into Postgres, universally unique, 122 bits of randomness. But it's ugly in URLs and not sortable, which hurts index performance on large tables.

UUIDv7 (Postgres 18+) adds sortability by embedding a timestamp. Better for indexes, but reveals when records were created. Still 36 characters.

ULID is like UUIDv7 with a custom encoding. Shorter (26 chars), sortable, case insensitive. But requires an extension that many managed Postgres providers don't allow.

Snowflake ID is compact (just a bigint) and sortable. Used by Twitter and Discord. Can be encoded as Base62 (2TxocQeKa), Base36 (kf12a0p4s), or Base16/hex (1565e1d8b6e31c00) for shorter URLs. But it reveals creation order and requires worker ID coordination for distributed systems.

Nano ID is pure randomness with a customizable alphabet and length. The official default↗ uses 64 characters (A-Za-z0-9_-), but you can use alphanumeric only (62 chars) for cleaner prefixed IDs. No extensions needed - just a SQL function. The trade-off: not sortable (read on, we'll solve that!).

Why I Recommend Nano ID

For public IDs, Nano ID wins. Here's why:

No information leakage. UUIDv7, ULID, and Snowflake all encode time. Someone can figure out when records were created, estimate growth rates, or predict future IDs. Nano ID is pure randomness.

No extensions needed. Many managed Postgres providers (Railway, Supabase, Neon, etc.) don't let you install extensions. Nano ID works as a plain PL/pgSQL function.

Customizable. Want only alphanumeric? Shorter IDs? Longer for more uniqueness? Just change the parameters.

Collision probability is manageable. With a 62-character alphanumeric alphabet (approximate values based on birthday paradox↗):

At 21 characters, you get collision resistance comparable to UUIDv4.

The Dual ID Strategy

Here's the pattern I use: UUIDv4 internally, Nano ID publicly.

Your internal primary key is still a UUID:

• Best JOIN performance with native UUID type
• Works with ORMs and existing tooling
• Foreign key relationships stay simple

The public ID is a separate column:

• Only used in URLs, APIs, webhooks
• Indexed for lookups
• Can be added to existing tables later

Queries work like this:

-- API receives public_id
SELECT * FROM teams WHERE public_id = 'team_ZsIylPM6qgMa';

-- Internally, use the real ID for joins
SELECT l.* FROM licenses l
JOIN teams t ON t.team_id = l.team_id
WHERE t.team_id = '550e8400-e29b-41d4-a716-446655440000';

The public ID lookup happens once. Then you use the real UUID for all related queries. Minimal overhead.

How I Implemented This in LocalCan

Two years after launching LocalCan↗, I needed to add teams. Users could now share licenses with their team members. Each team needed a URL-safe identifier for the dashboard.

I wanted URLs like:

https://www.localcan.com/dashboard/team_ZsIylPM6qgMa/licenses

Not:

https://www.localcan.com/dashboard/550e8400-e29b-41d4-a716-446655440000/licenses

Here's my actual schema:

create table teams (
    team_id uuid default uuid_generate_v4(),
    public_id text unique not null default (
      'team_' || nanoid(12, '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')
    ),
    name text not null,
    created_at timestamp with time zone default now(),
    primary key (team_id)
);

create index idx_teams_public_id on teams (public_id);

The team_id (UUID) handles all internal relationships: foreign keys from team_members, licenses, and stripe_orders. All JOINs use it.

The public_id is only for external use: dashboard URLs, API responses, webhook payloads.

One nice thing: I added this pattern to an existing product. The public_id column was new. I didn't have to touch any of the existing foreign key relationships.

The Nano ID Function for Postgres

Here's the function. Copy-paste it into your database:

create extension if not exists pgcrypto;

create or replace function nanoid(
  size int default 21,
  -- Using alphanumeric alphabet (62 chars). The official Nano ID default
  -- includes _- (64 chars), but alphanumeric is cleaner for prefixed IDs.
  alphabet text default '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
) returns text
language plpgsql volatile as $$
declare
  id text := '';
  i int := 0;
  urlAlphabet text := alphabet;
  alphabetLength int := length(alphabet);
  bytes bytea := gen_random_bytes(size);
  byte int;
  pos int;
begin
  while i < size loop
    byte := get_byte(bytes, i);
    pos := (byte & 63) + 1;
    if pos <= alphabetLength then
      id := id || substr(urlAlphabet, pos, 1);
      i := i + 1;
    else
      bytes := gen_random_bytes(size);
      i := 0;
      id := '';
    end if;
  end loop;
  return id;
end
$$;

This version works for most cases. There is also an optimized version (higher performance, but with trade-offs) with high throughput. Use the optimized version from viascom/nanoid-postgres↗.

Note: pgcrypto provides gen_random_bytes() for cryptographic randomness. This extension is available on most managed Postgres providers - it's pre-installed on Supabase↗ and available on Neon↗ via CREATE EXTENSION.

Usage Examples

Basic usage with default settings:

SELECT nanoid();
-- Returns: V1StGXR8_Z5jdHi6BmyTx

Custom length (12 characters):

SELECT nanoid(12);
-- Returns: FzT9hX3qLmWp

Numbers only:

SELECT nanoid(8, '0123456789');
-- Returns: 48392715

Prefixed ID in a table:

create table customers (
    id uuid default uuid_generate_v4() primary key,
    public_id text unique not null default ('cus_' || nanoid(12)),
    email text not null
);

Multiple prefixes in one database:

-- Customers
'cus_' || nanoid(12)  -- cus_FzT9hX3qLmWp

-- Orders
'ord_' || nanoid(10)  -- ord_8M0kXqY2nP

-- Invoices
'inv_' || nanoid(14)  -- inv_Xt7K9mPqR2WjYn

-- Teams
'team_' || nanoid(12) -- team_ZsIylPM6qgMa

Generate Nano IDs Anywhere

The beauty of Nano ID is that it's not Postgres-specific. You can generate the same style of IDs in:

• JavaScript/TypeScript: nanoid npm package
• Go: gonanoid
• Python: nanoid
• Ruby: nanoid
• Rust: nanoid

This means you can generate IDs in your application code too. Useful for creating IDs before inserting to the database, generating IDs for external resources, or testing without database access.

Real-World Example: My Dashboard URLs

Looking at my LocalCan dashboard, here's what the URLs look like:

/dashboard/team_ZsIylPM6qgMa/licenses
/dashboard/team_ZsIylPM6qgMa/members
/dashboard/team_ZsIylPM6qgMa/billing

When someone shares a URL in Slack or email, you immediately know it's a team-related link. When debugging, you can tell if you're looking at a team ID or something else.

Compare that to:

/dashboard/550e8400-e29b-41d4-a716-446655440000/licenses

Which would you rather read in a support ticket?

Migration Tips

Adding public IDs to existing tables is straightforward:

-- UNIQUE creates an index automatically, DEFAULT backfills existing rows
ALTER TABLE customers
ADD COLUMN public_id text unique not null default ('cus_' || nanoid(12));

That's it. One line.

Your existing foreign key relationships stay unchanged. Internal queries keep using the UUID primary key.

Summary

For public-facing IDs in Postgres:

1. Use UUIDs internally for primary keys and foreign keys
2. Add a public_id column with prefixed Nano IDs for external use
3. The nanoid() function works without extensions on any Postgres
4. Prefixes make IDs self-documenting (cus_, ord_, team_)
5. 12 characters is enough for most applications

The pattern is simple to add to existing projects. I added it to LocalCan two years after launch with zero downtime and no changes to internal queries.

Your URLs will be cleaner. Your API responses will be more readable. And you won't leak information about your database internals.

FAQ

Q: Why not just use UUIDs as public IDs?
A: UUIDs work, but they're 36 characters long and give no context. When you see 550e8400-e29b-41d4-a716-446655440000 in a log, you don't know if it's a user, order, or team. With team_ZsIylPM6qgMa, you know instantly.

Q: What prefix length should I use?
A: Keep prefixes short but descriptive: 3-5 characters. Examples: cus_, ord_, inv_, team_, proj_. Avoid generic prefixes like id_ or obj_.

Q: Is 12 characters enough for the random part?
A: For most applications, yes. With a 62-character alphabet, 12 characters gives you a 1% collision probability after ~100 million IDs. If you're generating billions of IDs, use 16+ characters. The nice thing is you can increase the length later if your app takes off - old 12-char IDs and new 16-char IDs can coexist.

Q: Can I use this with an ORM?
A: Yes. The public_id is just a text column with a default value. Your ORM handles it like any other field. Use the UUID primary key for relationships, and expose the public_id in your API serializers.

Q: What if I need sortable IDs?
A: If sorting by ID is critical, consider UUIDv7 (Postgres 18+) or Snowflake IDs for your primary key. But in most cases, sorting by created_at is cleaner and more explicit.

Q: Should I index the public_id column?
A: Yes. Add a unique index: CREATE UNIQUE INDEX idx_table_public_id ON table (public_id);. This makes lookups fast and enforces uniqueness.

Q: Can I change the prefix later?
A: Technically yes, but avoid it. Prefixes often appear in URLs, logs, and external systems. Changing them breaks bookmarks and cached references. Pick good prefixes upfront.

For those looking for local development tools, check out LocalCan↗ - local domains and public URLs for your dev environment. It helps you test webhooks and APIs locally without complex ngrok setups.