Stable user ID =?

[p.colbert]

Context
It is often necessary to translate between an external ID (e.g., Anvil’s) and a company’s equivalent internal ID. For this purpose, the external ID should be at least as stable as the internal ID.

What can’t work
We can’t use the password hash for this purpose. Users may log in with non-password schemes, e.g. Google or Facebook. Even if we do require a password, they can change it (and should be encouraged to do so, from time to time). The same user may have several different hashes in succession, so the hash cannot be considered a stable ID.

Might this work?
But perhaps there is something else that is stable. In each database table, each row has a unique rowid. That includes table Users.

How stable is such an ID? Will it survive a database “vacuum”?

[david.wylie]

Not a direct answer, but assuming I’m understanding you correctly you could generate your own UUID using a standard Python library and store it in your User table.

import uuid
...
myUID = str(uuid.uuid4())

Then it would only change if you change it. Relies on you creating it when a user is created (ie not automatic), but it should survive a vacuum.

[p.colbert]

Agreed. Then it’s as stable as I want to make it.

[meredydd]

Are you looking for the get_id() method on a data table row?

  user_id = anvil.users.get_user().get_id()

and then later you can do:

  user = app_tables.users.get_by_id(user_id)

[p.colbert]

Thanks, Meredydd.

The question was

@p.colbert

How stable is such an ID? Will it survive a database “vacuum”?

But if we make our own user IDs, then their life-cycle is our responsibility. Which is easier on everyone. Kudos to @david.wylie for reminding me of that fact.

[meredydd]

How stable is such an ID?

Ah, I get the question now. The answer is that this ID is stable, and will remain the same until/unless you delete that row from your data table.

[p.colbert]

Sorry to take so long to finish this topic. It’s not a priority item for me, anymore. I’m going with @david.wylie’s suggestion, and will generate my own UUID, where needed. This post is intended simply to complete a developer’s general understanding of how Anvil-generated row ids behave, long-term. May as well close any holes in that understanding.

After all, if someone’s tied their own (external) records to their Anvil records, using Anvil-supplied row ids, these details can matter. Also, I hope to write a data backup/restore routine (e.g., back up from one app, restore to its next-generation version.) In that light, a few questions remain:

1. Under what circumstances would the row ID format (list of exactly two integers) change?
2. Is the row’s ID guaranteed to never be reused after a “delete”?
3. Are there any database circumstances that would change a row ID? Vacuum? Move to/from a different (e.g., dedicated) server? User-written database backup (from one app) and restore (to another)?

Hypothesis: the two integers identify a table, and a row within that table, PostgreSQL-style. In that case, a 2nd app, with its own tables, would probably not have the same table-id number, making every row id be different in the 2nd app. A foreign-key-aware user-written backup/restore program would have to take that into account. Any continuing synchronization (between the two apps’ tables) would probably require a UUID on every row.

[meredydd]

And here come some Official Answers!

1. Under what circumstances would the row ID format (list of exactly two integers) change?

We make no promises about what format the ID is in for new rows. Don’t rely on it.

If you store the ID of a particular row (using get_id()) and store it somewhere, then calling app_tables.my_table.get_row_by_id() with that ID will always get you back the same row (unless you’ve deleted it).

2. Is the row’s ID guaranteed to never be reused after a “delete”?

Yes, that is correct.

3. Are there any database circumstances that would change a row ID?

No. Calling get_row_by_id() with a row ID you’ve saved will always get you that row back (as long as it hasn’t been deleted, obviously). This remains true over migrations, backup/restore, etc. (Although obviously if you copy data around yourself, you’re creating new rows with new IDs.)

[p.colbert]

Thanks, Meredydd! That’s actually more stable than I was expecting! Many databases will reuse row ids, after a delete, unless you take (database-specific) extra steps.

If I may summarize, I think the underlying lesson (for developers) here is simple. A row id identifies one row in one table. Period. Its suitability for identifying anything else will depend on the size, reach, and lifetime of the application. We can define two categories:

1. My app has only one table, in one database, containing the sole copy of the definition of some external entity (e.g., a user, a product order). In this case, the row id can be a good proxy for the entity’s own id. At least, it will have the same stability and the same lifetime.

2. I need to identify such an entity across multiple contexts (databases, tables, or apps, or multiple versions of the above). In that case, to tie the contexts together, I have the choice of a cross-reference table, a cross-context id, or both.

Apps often live far longer, and reach into more contexts, than originally intended. So we should be aware, and watch for, those occasions when a Category 1 table slips into Category 2.

When this happens, a UUID can make a good cross-context id. It may be overkill in many cases, but at least it should work. There are a variety of UUID schemes, suitable for different occasions.