AUTO_INCREMENT column

[Tony.Nguyen]

There is row_id, but it is unreliable, can be changed unexpectedly

While it can done by code, it would be much much much convenience if the system makes it automatically with AUTO_INCREMENT column. So that this kind of issue 1 and issue 2 can be solved without headache and confusion

Finally, I can rely to my boss who just has basic IT knowledge when he says “mysql has it”

Thanks so much for your consideration

[p.colbert]

If used as the designers intended, Anvil row ids are, in fact, very reliable.

1. A row id can’t change while a program has it in memory. This allows row ids to be passed back and forth between server and client, and still refer to the same record every time they are used.
2. Columns of type Link do, in fact, store Row ids. If Anvil must change a row id – which, as far as I can tell, has never happened – Anvil can therefore track down any matching Link-column values, and change them to match the new id. If the format of Anvil’s row ids must change, then Anvil takes responsibility for updating all the valid Links.

That said, other column types can certainly be handy for “ids”, if they are used in other ways. If an id is generated externally, for example, and used to keep sync with external databases, then you need whatever column type can hold that value.

If you need an id that is

1. generated by Anvil
2. guaranteed never to change in value or type

then auto-increment can make a lot of sense. (So can an automatic timestamp, for that matter. Or a GUID. Or a very large random number. It really depends on what you’re doing.)

Note that maintaining a counter, and preventing conflicts from multiple users (each trying to create “the next” record at effectively the same time), has some overhead. On the other hand, doing it ourselves requires us to write code, and there’s a really good chance of our getting it incomplete, or outright wrong. This is probably why every major database provides a built-in auto-increment option.

[Tony.Nguyen]

@p.colbert

If used as the designers intended, Anvil row ids are, in fact, very reliable.

Thanks @p.colbert, as at Dec 19, it isn’t recommended to use row_id

What if I am relying on the row ID?

I was reading this post and… Oops! What if I have already done it? I use it in SQL JOIN queries building the string out of the table and row ids. I knew I was playing with the fire, that’s undocumented stuff, but it works well and I couldn’t find a better alternative. I can’t ask you not to change it, but please let us know before you change the row ID management and how to fix the things that rely on it.

[stucork]

there’s a difference between using it (as intended) and relying on it.

i.e.

table, my_id = row.get_id()[1:-1].split(',')
# relying on the id being a string of the form [12321, 429048101]

would be bad

but…

linked_table_row_id = row['linked_row'].get_id()
linked_row = app_tables.linked_table.get_by_id(linked_table_row_id)

would be fine
since in this case I’m not relying on the format of the id, and
I’m not hard coding it into my project
but I’m instead relying on the promise that anvil will give me an id if I ask for one
and will get me a row if I give it a valid id.

[Tony.Nguyen]

Thanks for your explanation, so as long as row_id is NOT saved or manipulated, loading and leaving it as is from database is safe.

[campopianoa]

Yes, exactly!

[p.colbert]

Well… saving it in a Link column is perfectly fine. These columns are designed for doing exactly that.