Databases have built-in features for this now. What the author is talking about is a regular table.

In reality, that wasn't too unusual to see because frameworks would use that technique because it's a lowest common denominator across RDMS.

Does SQLite have sequences yet?

Sqlite only has automatically assigned row ID. You can type a column as "integer primary key" to make it an alias for the row ID, or use ROWID directly (not recommended for compatibility).

I think the intriguing part was purposefully using the same sequence value for rows in multiple tables.

I've worked with globally unique (to our application) integer keys, and per table integer sequences (which obviously aren't globally unique), but I don't recall seeing anyone use a global sequence but purposefully reuse elements of the sequence before.

I've seen this fairly recently and I was surprised to also see it in this article because I think that makes it twice that I've seen it in ~25 years.

They may even be the same codebase!

It’s kind of like an idempotency key assigned either by the request or generated at the beginning of processing that request

If the caller/consumer isn't choosing the idempotence key then I don't think it would act as a particularly good one.

In this scenario the database is choosing the id during a transaction and so you'll only find out the value if the transaction successfully commits and the response makes it back to you.

Generally you'd want the client to provide the idempotency key such that whatever failure occurs you know the value and can provide it on a retry such that the server can use it to prevent double entry / etc

Yes, but both these have very different properties. He said (I don't know if its the case) that the db didnt have an autoincremental type. Postgres uses these sequence objects to implement autoincremental ids as he was referring to, they are implemented in-engine and are very fast and have already solved data races.

In the article, what he complains about is not about what a sequence is, but about implementing it manually with a table that is read, incremented and then saved. This us more expensive, and depending on how it was implemented you need to take care of the whole data flow so you are unable to allocate the same id twice. That's what he considers odd

The scary thing to me about that setup is how the global value is updated. Every individual script must successfully increment the value to avoid duplicate keys.

Really hope they had a “get key” stored procedure to handle that.

It's possible they had some requirement for gapless ID numbers. You can't do that with a sequence.

I think you missed the crucial part: every related record across all tables would have the same sequence item (the same id). That's really not normal in the database world. It sounds a lot like ECS though.

yeah, I've seen tables with a single row and single columns a few times, for alright reasons. Sometimes you do just want to store a single global value!