Database ID Design: Choosing ID Methods and Primary Key Strategies

Source: Dev.to

Considerations for ID Design

Choosing a primary key (ID) for your database is a surprisingly deep topic. While some frameworks have defaults, you’ll often need to make this choice yourself. In PostgreSQL, auto‑increment is implemented using the SERIAL type (internally a SEQUENCE).

ID methods like UUID and CUID2 combine timestamps and random values to generate unique IDs without central management, allowing distributed systems to create data without worrying about collisions.

A helpful video that organizes the decision criteria for ID selection:
https://www.youtube.com/watch?v=pmqRaEcDxl4

Comparison of Major ID Methods

Selection Criteria

• Will IDs be exposed in URLs? → Avoid sequential IDs if exposed.
• Need time‑based sorting? → Use UUID v7 or ULID.
• Is write performance critical? → Sequential IDs for large datasets.
• Is ID length important? → NanoID or CUID2 for URLs.

Adoption Strategy for My Indie Project

Default: CUID2

I use CUID2 for content IDs (pages, tables, dashboards, etc.).

Why CUID2:

• Short: 24 characters (vs. 36 for UUID v4).
• URL‑safe: No hyphens, lowercase alphanumeric only.
• Double‑click selectable: No hyphens means you can select the entire ID.
• Secure: SHA‑3 based, hard to guess.

// id-generator.ts
import { init } from '@paralleldrive/cuid2';

// Initialize with fixed 24‑character length
const createCuid = init({ length: 24 });

export function generateContentId(): string {
  return createCuid();
}

// Example: "clhqr8x9z0001abc123def45"

Exception: UUID v7 for Bulk Processing Tables

For tables that may receive bulk inserts (e.g., table_rows), I use UUID v7.

Why UUID v7:

• Insert performance: Time‑ordered IDs are efficient for B‑tree indexes.
• PostgreSQL compatible: Can be stored as the native UUID type.
• RFC standard: Compliant with RFC 9562 (established 2024).

import { v7 as uuidv7 } from 'uuid';

export function generateRowId(): string {
  return uuidv7();
}

// Example: "018c1234-5678-7abc-9def-0123456789ab"

Selection Criteria Summary

Composite Primary Key Design

Choosing between a single primary key and a composite primary key is another important design decision, especially for multi‑tenant SaaS where data isolation and search efficiency matter.

Single vs Composite Primary Key

-- Single primary key
CREATE TABLE contents (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  ...
);

-- Composite primary key
CREATE TABLE contents (
  tenant_id TEXT NOT NULL,
  content_id TEXT NOT NULL,
  ...
  PRIMARY KEY (tenant_id, content_id)
);

Benefits of Composite Primary Keys

• Index efficiency: Fast tenant‑scoped searches (tenant_id is at the front of the index).
• Data isolation: Prevents cross‑tenant data access.
• Uniqueness guarantee: Ensures uniqueness through the combination of tenant_id and content_id.

Definition with Drizzle ORM

import { primaryKey, text } from 'drizzle-orm/pg-core';

export const contents = appContent.table(
  'contents',
  {
    tenant_id: text('tenant_id').notNull(),
    content_id: text('content_id').notNull(),
    title: text('title').notNull(),
    // ...
  },
  table => ({
    pk: primaryKey({ columns: [table.tenant_id, table.content_id] }),
  })
);

Practical Tips

Prepare ID Validation Functions

Having validation functions helps prevent errors from invalid IDs.

export function validateCuid2(id: string): void {
  const cuid2Regex = /^[a-z0-9]{24}$/;
  if (!cuid2Regex.test(id)) {
    throw new Error('Invalid CUID2 format');
  }
}

export function validateUuidV7(id: string): void {
  const uuidRegex = /^[0-9a-f]{8}-[0-9a-f]{4}-7[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i;
  if (!uuidRegex.test(id)) {
    throw new Error('Invalid UUID v7 format');
  }
}

Summary

What’s working well

• Short, easy‑to‑handle URLs with CUID2.
• Bulk‑processing performance with UUID v7.
• Multi‑tenant data isolation with composite primary keys.

Things to be careful about

• The optimal ID varies by requirements (there’s no single right answer).
• Plan carefully when migrating from existing data.
• Match the ID format of external dependencies like auth libraries.

As concluded in the video, the optimal ID depends on your project’s requirements. Choose what fits your use case.

Tomorrow I’ll explain “Database Migration Best Practices: How to Safely Apply Changes.”

Other articles in this series

• 12/6: Schema Design with Supabase – Table Partitioning and Normalization in Practice
• 12/8: Database Migration Best Practices – How to Safely Apply Changes