Database Identifiers Roadmap: UUIDs, ULIDs, and Choosing the Right ORM

Every database ids and orm roadmap has to start below the ORM layer, at the identifier itself, because every later decision assumes you already understand what “unique” costs. UUID v4 is the natural first stop: 122 bits of randomness, a collision probability so low it's not the practical risk people assume it is, and zero coordination required between services generating ids independently. That last property — no central counter, no round trip to ask “what's the next id” — is the entire reason random ids exist as a category, and it's worth internalizing before you touch a schema file.

The skill at this stage is narrow but foundational: know what “random” buys you (no coordination, no leaked sequence, safe to generate on the client) and what it doesn't (no ordering, no sortability, a slightly larger on-disk footprint than an integer). Choosing a database id without understanding this trade-off first means every later stage is guesswork.

• UUID Generator — the pillar guide covers the version differences (v1, v4, v7) and where each is actually appropriate.
• UUID v4 Generator goes deep on the collision math and randomness source specifically — read this before you generate a single production id.

This is the stage most engineers skip, and it's the one that causes real production incidents — which is exactly why it comes second, not first. A random UUID v4 as a clustered primary key is a B-tree's worst case: every insert lands at a random point in the index rather than the rightmost edge, forcing constant page splits, fragmenting the index on disk, and dragging write throughput down as the table grows. You can't appreciate why this matters until you've internalized Stage 1's point that v4 ids carry zero ordering information — the randomness that makes them collision-resistant is precisely what makes them expensive as a clustered key.

The fix isn't abandoning randomness — it's using an id format that's time-sortable at the front and random at the back, so inserts still land at the end of the index (like an auto-increment integer) while remaining safe to generate without coordination (like a UUID). That's the entire pitch behind ULID and UUID v7, and understanding the difference is a prerequisite for choosing a database id for any table that will see meaningful write volume.

ULID Generator walks through the ULID-vs-UUID comparison directly — timestamp prefix, lexicographic sort order, and the B-tree implications above, in enough depth to make the primary-key decision for your own schema.

Once your identifier strategy is settled, the next practical skill is generating data to actually put behind it. Hand-written fixtures — three rows of { id: 1, name: "Test User" }copy-pasted across a test suite — don't exercise the id format decisions from Stages 1 and 2 at all, and they rot the moment a schema field changes. This stage comes before the ORM stage deliberately: you want to be seeding realistic rows against a schema you understand well enough to already have opinions about, not generating placeholder data for a schema you haven't designed yet.

Reproducible mock data — seeded so the same run produces the same rows, varied enough to catch edge cases real users would hit — is the difference between a test suite that gives you confidence and one that quietly passes against data too clean to be useful. It also gives you something concrete to migrate when you move from a hand-rolled connection to an ORM in the next stage.

Mock Data Generator produces exactly this: reproducible, schema-shaped seed data for development and testing, without hand- writing fixtures row by row.

With an identifier strategy settled and realistic data to test against, the actual TypeScript-ORM decision becomes tractable instead of a coin flip. The real fork in the road for an orm for typescript projects isn't “which one is popular” — it's schema-DSL-plus-codegen (define your schema in a dedicated file, generate a typed client) versus schema-in-TypeScript-with-no-codegen (define your schema as TypeScript itself, get types for free). Prisma and Drizzle are the two concrete answers to that fork, and the right choice depends on how much you value a generated client's ergonomics against a build step you don't have to run.

This stage sits after ids and seed data on purpose: an ORM decision made before you know your primary-key format tends to get revisited painfully once you discover your ORM's default id strategy fights the one you actually need. And whichever DSL you land on, the schema file itself becomes a piece of code your whole team edits — which means it needs the same formatting discipline as everything else you check in.

• Prisma vs. Drizzle breaks down the codegen-vs-no-codegen trade-off directly, with the concrete cases where each wins.
• Prisma Formatter Guide covers keeping a growing schema.prisma file consistent once more than one person is editing it.

The last stage is the one every earlier decision was quietly built on top of: relational versus document. It's deliberately last because it's the hardest to answer in the abstract — you need Stage 2's understanding of primary-key behavior and Stage 4's ORM experience to actually feel the difference between a schema that enforces relationships at write time and one that defers structure to read time. A rigid, deeply relational domain (accounts, invoices, permissions with real foreign-key constraints) argues for PostgreSQL; a domain that's naturally document-shaped and evolves fast (event logs, flexible content, per-tenant custom fields) argues for MongoDB.

There's no universally correct answer here, which is exactly why it's the capstone rather than the starting point of this roadmap — by the time you're equipped to make this call well, you've already made every smaller decision that determines how painful the wrong answer would be.

MongoDB vs. PostgreSQL lays out the concrete cases for each, so this decision is made deliberately rather than by default.