AUTO_INCREMENT column

Feature Requests
1

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

1 Like
2

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.

3 Likes
3

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

4

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.

1 Like
5

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.

6

Yes, exactly!

2 Likes
7

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

1 Like