Storing and showing TNIDs

Store TNIDs in a native UUID column, and show the TNID string wherever people or other systems see them: API responses, URLs, logs, admin screens. Convert where TNIDs enter or leave your app.

Form	Value
TNID string	`user.Br2flcNDfF6LYICnT`
UUID form	`d6157337-0ebc-8686-83ab-4075a34cdcde`
u128 (hex)	`0xD61573370EBC868683AB4075A34CDCDE`
Bytes (big-endian)	`D6 15 73 37 0E BC 86 86 83 AB 40 75 A3 4C DC DE`

One 128-bit value in every form; conversion is lossless.

Store as UUID, show as TNID

Postgres, for example, has a uuid type. Converting the TNID from Basic operations:

RustTypeScript

use tnid::Case;

let uuid = user_id.to_uuid_string(Case::Lower); // "d6157337-0ebc-8686-83ab-4075a34cdcde"
let back: Tnid<User> = uuid.parse()?;           // parse accepts either form
println!("{back}");                             // "user.Br2flcNDfF6LYICnT"

const uuid = UserId.toUuidString(userId); // "d6157337-0ebc-8686-83ab-4075a34cdcde"
const back: UserId = UserId.parse(uuid);  // parse accepts either form
console.log(back);                        // "user.Br2flcNDfF6LYICnT"

Don't store the TNID string

Store TNIDs in a native UUID column, and show the TNID string wherever people or other systems see them. Where the database has no UUID type, store the 16 bytes or the 128-bit integer instead. The TNID string is a display form, not the value.

Why:

Less space. A UUID column takes 16 bytes. A TNID string takes 19 to 22 characters of text, plus the text column's own overhead.
Simpler indexes. Index entries are fixed 16-byte values, compared byte by byte, with no collation rules.
No collation problems. A case-insensitive column treats distinct TNIDs as equal, and a locale-aware one sorts them out of creation order. A UUID column has neither problem.
UUID tooling keeps working. Drivers, foreign keys and other tools built for UUIDs take the UUID form unchanged.

If you must store the string anyway, for example in an existing text column, give that column a case-sensitive binary collation. Case matters in a TNID string by design: one exact spelling per TNID is what lets the strings sort and compare as plain text.

Database	Column definition	Docs
PostgreSQL	`id text COLLATE "C"` (`"C"` compares bytes)	Collation Support
MySQL	`id VARCHAR(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin`	Binary collations
SQLite	`id TEXT COLLATE BINARY` (the default)	Collating sequences
Others	Use whatever your database calls a binary or byte-order collation.

Other collations break:

Collation	Example	What breaks
Case-insensitive	MySQL's default	`user.Br2flcNDfF6LYICnT` and `user.br2flcndff6lyicnt` compare as equal. A lookup can return the wrong row, and a unique index can reject a valid new TNID.
Locale-aware	Many Postgres defaults	`-`, `_` and upper and lower case sort differently from byte order, so sorting by the TNID string no longer gives creation order.

For how collations work in general, see PostgreSQL's Collation Support chapter.

Sorting

Time-sortable TNIDv0s with one name sort in creation order in every form. Stored as text, they keep that order only with a binary collation or one-case UUID hex.

UUID form as text

As with any UUID stored as text: UUID hex has upper- and lowercase spellings, and mixed case sorts out of order. Use one case throughout: A and a are the same hex digit but sort differently as characters.

Bit-level detail: Representations.