Don't know of any such either. Typical SQLite, MySQL, or PostgerSQL dump/backup is just bunch of SQL statements that re-create the content, including PKs.

You can imagine a non-native (perhaps DB-agnostic?) DB dump format that would use generated IDs for PK, rather than actual PKs. Why would one do that, I don't know.

Another possible case is using file (or memory) offset as the PK. This gives certain nice properties - one less indirection level, and object identity being pointer identity. Perhaps there's a memory image-based LISP version out there that uses such. Perhaps there's a NoSQL engine like that somewhere.

>Plus, you'd have to hack your slug-generation algorithm to ensure the generated slugs to be unique

...or you put the moderators to the task of ruthlessly stamping out duplicate questions. There was a popular website like that, oh wait, that's the Stack Overflow ;-)

[edit]

I've just remembered that PostgreSQL's ctid and SQLite's ROWID are physical identifiers (offset-like identifiers) of records. They are (to certain extent) exposed to userspace and could be misused as IDs in URLs. As physical identifiers (offset-like), they are naturally subject to change upon dump/restore, vacuum, other maintenance tasks, etc.. Clearly those should not be used as end-user visible IDs in URLs.

The OID is generated from a sequence shared by all databases in the "cluster" i.e. the PostgreSQL server instance. So it is not only non-deterministic based on insert orders to your DB, but also is affected by inserts in other databases on the same server.

As I recall, OIDs only ever came up in practice if you wound up wrapping the 32 bit uint.

The original goal for OID was a bit like UUIDs, but just at the cluster level rather than truly global. Someone steeped in that background and focused on "tuning", where you would consider things like storage consumption and index efficiency for int4 vs int8, might well have baked OIDs into a URL while dismissing the uuid type as extravagantly expensive.

You might not use foreign keys, but still have autoincremented primary key.

Database ids on the URL are also a security concern.

They shouldnt be. Users should only be allowed access to their own records, even if they know the id's of records belonging to other users.

Yes, in fact - even then. There are fascinating historical stories about lay-people who scoffed and then failed to account for the dark art of statistics. https://www.statisticshowto.datasciencecentral.com/german-ta...

What performance cost there is (and in my experience it is not much) is because a UUID is twice as large as a bigint. And there are situations where that matters. But not that many of them.

   postgres[7878][1]=# SELECT amname, pg_indexam_has_property(oid, 'can_unique') FROM pg_am WHERE amtype = 'i';
   ┌────────┬─────────────────────────┐
   │ amname │ pg_indexam_has_property │
   ├────────┼─────────────────────────┤
   │ btree  │ t                       │
   │ hash   │ f                       │
   │ gist   │ f                       │
   │ gin    │ f                       │
   │ spgist │ f                       │
   │ brin   │ f                       │
   └────────┴─────────────────────────┘
   (6 rows)

Looks like I have a conversation to have. Thanks. :)

That sounds like they wanted to migrate to another cluster, and wanted to have both clusters running at the same time (for testing) without risking an ID overlap, so they turned the least significant bit of the ID into a cluster identifier. The older cluster was 0 (even numbers), the new cluster was 1 (odd numbers).

More so than in mark up? If so why?

Should surrogate keys never be exposed to users in any form? Then what's the alternative? Nonces for everything, all the time?

Easy to enumerate, if that's a concern.

1. Mutable data (looks nice but can change)

2. Incrementing data (looks nice but can be guessed and measured)

3. Random looking data (immutable and unguessable but ugly)

Guids aren't the only option for 3 and some of the others may not look as bad, but it still seems the safest option. Any other compelling reasons to not use guids?

There's NO NEED for a primary key to be an INTEGER or any sort of ROWID.

I agree with TFA -if this is what it says- that one should not put INTEGER PRIMARY KEY values in URIs -- it's useless to users. Just make the "slug" a column, make a NOT NULL and UNIQUE, disallow changes, and use that. You don't have to make the slug a PRIMARY KEY -- NOT NULL and UNIQUE has the same semantics as PRIMARY KEY.

Surely TFA wouldn't argue to have no DB key at all in URIs, so I won't bother with that possibility. And yes, I should read TFA...

1. Numeric based Primary Key Lookups are some of the fastest operations on modern databases. 2. "Slugs", are a set of strings, which occupy more memory, space, now you have to come up with some way to make them unique. If you are building a CMS, sure, SEO is important. If you are building a line of business application, no. 3. Just checked, Google and Facebook expose numeric based identifiers. My Facebook Business ID is numeric, my Google Analytics ID is numeric with a prefix on it. 4. There are plenty of libraries out there that will take a numeric value, convert it to a small alphanumeric value with a check digit. 5. No end user cares about URLs (except for SEO). Your business user is not going to navigate your application by going to myapp/settings, they are going to click on the gear icon.

Well, you need what is called a https://en.wikipedia.org/wiki/Surrogate_key.

Surrogate keys need to be made up from whole cloth, apart from the natural data (because if they are derived from any of the natural data, they'll become incorrect when the data changes.) And what's easy to make up from whole cloth? Sequential integers. (Or random numbers, which—if you want to guarantee no collisions across a distributed DB cluster—gives you ≥128-bit random numbers, i.e. UUIDv4s.)

But now the slug in the URL of the published article (about party B winning the election) is still "party-a-wins-election"!

There are thousands of use-cases just like this. For another example, someone might have a real claim on a libel suit if you assert something false about them in a URL slug in a link hosted on your own site, even if it points to a page that says only true things about them! ("People don't always click through to find out what the actual article is about", their lawyer would argue; "sometimes they just read the URL itself." The judge would agree, being just the sort of person who is too busy to read everything that crosses their desk, instead triaging the importance of things [read oneself, delegate to their legal analysts, forget] by just such sloppy criteria as looking at the URL slug.)

This is why, in CMSes, you need to always be able to exert editorial control over every part of a page, including the URL. The slug, just like all other parts of the page, must be mutable. And so you can't use the slug as the primary key.

("But what if you just delete posts with bad slugs and make new posts to replace them?" Not always possible; in a CMS for a publisher with a hard-core editorial flow, often the "post" you see is just a view onto the same work-object that passes through the rest of the CMS process of edits and approvals, and you can't just delete the work-object and make another one, without getting whoever assigned you the job to cancel your assignment and re-assign it to you. Heck, often at time-of-assignment you don't even know what you're going to use as the headline. How should a system assign a slug for that?)

(And, while, yes, you could restructure things so as to decouple content-asset objects from business-domain objects, such that it's the content-asset object that has a slug, and maybe content-assets never change at all, just get new versions of them published with the previous versions hidden, with some router using rules to determine to which content-asset a given slug should route... at that point you're talking about the sort of Event Sourcing architecture which people on HN like to fantasize about but which 99% of businesses refuse to implement for the same reason they don't want to use unpopular programming languages: it makes it far harder to hire Joe Average Java-School Dev to maintain the code.)

A small number in a URL isn't going to confuse anyone.

It's pretty useful to me when scraping/syncing data from a website. I'm also a user.

  IDs not round-tripping properly during backup/restore

(I know this because I once had to fix a database table with no primary key, and some rows where every field was identical. The rowid lets you delete one out of two identical rows. So it's not an entirely useless feature)

If you had an inexperienced developer, and no-one reviewing their work, and they heard the rowid is the fastest way of looking up a row - faster even than the primary key - so they decided to use that in URLs, that would be tremendously inconvenient.

Apparently you can't use AUTOINCREMENT on tables using WITHOUT ROWID either.

[1] https://www.sqlite.org/autoinc.html

https://stackoverflow.com/questions/18692068/will-mysql-reus...

Many applications have meaningful unique identifiers, creating a unique slug is not always a "hack". Any app that displays events that occurred in the past often have unique identifiers. Examples include software logs, email, government records, financial transactions, i.e., anything that displays past events.

These records are inherently "write-once", and the primary key can be something more meaningful than an ID. These may be just a subset of apps out there, but in these cases there is a strong argument for removing any ID in a URL.

Simple example - neo4j. Node id's are reused when deleted and you can't create node with id you want. Documentation mentions this. We've just used unique random strings as keys.

I was relying on this just yesterday. We use graphenedb for our production database and I needed to load a production backup locally for testing. It was really useful that I could query a node by id both locally and on prod and get the same result.