Relational DBs - ID Field

Hello,

I am using a MySQL database for my apps. I’ve done some reading and am confused about the issues with the auto incremented PK field.

From what I have read so far (older messages and articles), AppSheets is unable to insert a record without itself generating the new key. I find it quite boggling that a platform designed to work with data cannot actually work with the standard method of inserting records to a database. Is this still the case? Please tell me this has been addressed.

I saw a forum post from Oct 2018:

I suppose I can adapt to this method, but I am confused on what the implications are.

  • Will the new UNIQUEID key referenced above ever be used for anything other than allowing AppSheets to be “happy” with a new record?

  • Would I still be able to reference all of my data using the actual PKs?

  • Is there another solution that doesn’t use the random number generator? (Only other solution I found used it)

  • Is there any downside to AppSheets not understanding that the REAL PK is one marked as not a key and not required?

In responding to your questions, I thought it worthwhile to point out up front that AppSheet does not and cannot interact with your database in real-time.

Correct. The key must exist before the row is added to the data store. The newly-created row may even be used within the app before it ever arrives at the data store.

AppSheet was designed to work with spreadsheets initially.

It will be the key AppSheet uses to uniquely identify a row within a table. From AppSheet’s perspective, it is the PK.

Not using AppSheet’s inherent “dereference” syntax. To use an alternate key, you’d have to use explicit lookups in expressions. Alternate keys are not supported by any of AppSheet’s inherent features, so there will be many places your alternate keys will be unusable or prohibitively expensive to use.

You can use any method you want to generate keys, so long as it can be expressed with an AppSheet expression and doesn’t require any data not already on the device at the time of generation.


Probably, but tough to say exactly how without knowing what you intend to do.

Thanks, as always, Steve for responding so quickly!

Disturbing. I want my users to always be using up to the minute data. This does explain all the darn syncing every time I update a record.

But it has grown up now and can use real data too. I hope that they decide to grow the way the platform interacts with data also.

Can you please provide some alternate methods of generating unique keys?

What happens if a record is inserted by another avenue (not AppSheets)? If the DB auto increments the REAL PK, when AppSheets syncs the record, will it generate a new UNIQUEID so the record can be utilized by AppSheets?

@Erik_Meyer Hi Erik, one of the main reasons why AppSheet does not support auto-incremented keys in databases is because AppSheet apps support delayed sync and offline mode. To better understand this, consider this scenario:

There are 2 users A and B using the same app. Due to the demands of their jobs, both of these users need to add large numbers of rows to the app without syncing for long periods of time (either by delaying sync, or in offline mode). During the time period before the rows can be written back to the database, the app still needs to be able to uniquely identify the newly added rows so that the users can use features such as filtering, row updates, row deletes, reference expressions, etc. If the only way for the app to assign new keys to the newly added rows is to sync so that the keys can be auto-incremented by the database, these features won’t work. If on the other hand, the app attempts to auto-increment new keys while offline, then very soon the copy of the app used by user A and the copy of the app used by user B will generate the same keys, which will result in duplicate keys in the database when the 2 users finally sync.

Apart from the reason that I explain above, even if you don’t use AppSheet with your database, using auto-incremented keys as your primary keys is actually not a good practice. There are a number of reasons, but below are 2 big ones:

1/ Scaling: using auto-incremented keys prevents you from sharding/partitioning your database. The moment you divide your database into multiple shards/partitions in order to accommodate larger amounts of data or to service larger numbers of requests, your shards will sooner or later end up with duplicate keys. This is actually very similar to the situation between users A and B that I described above. If you need to merge data across the shards for analytics or aggregation, you’ll run into troubles.

2/ Security: auto-incremented keys reveal information about your data. In the past, when databases were not online, this was not a big issue. However, databases are now frequently used by online applications. What’s worse, the rise of APIs means that the serial auto-incremented keys that your database generated might end up in an API URL somewhere, exposed and visible to the public. Just by looking at these keys in the URLs, an attacker can guess the size of your database and more. And since the keys are probably serial, the attacker can easily use a script to send repeated requests to serially generated API URLs to enumerate and scrape the entries in your database.

I’m not a historian, but my bet is that auto-increment keys were invented before the Internet. With the Internet comes scale, complexity (offline mode, concurrent access, etc.), and more threats, so auto-increment keys are fast becoming obsolete. As such, I suggest using auto-incremented keys only for prototyping or experimentation. For production databases, unique primary keys, which can be generated using the built-in UniqueID() expression in AppSheet, are the better options.

4 Likes

A timestamp is common. A person or company’s name. The What is a key? document I linked above has more.

A row in the data source without an AppSheet key will be entirely ignored by AppSheet (at least, that’s what happens with spreadsheets). It will (likely) not see it as an un-keyed row and will not assign it a key. But, the non-AppSheet process that adds rows to the database can itself generate/assign a key for AppSheet’s use. Just ensure the new key for AppSheet is unique within the column.

Is this the UNIQUEID expression you are referring to? I ask because I have seen documentation telling me to create a PK as a varchar(8) and that this format is what UNIQUEID uses. However, a quick trip to the internet tells me that most UUID generators are coming up with 36 character UUIDs. Is there a different method that generates a full sized UUID? Am I misunderstanding something?
.
Wikipedia - A universally unique identifier ( UUID ) is a 128-bit number used to identify information in computer systems…the same 8-4-4-4-12 representation is used for all UUIDs

AI numeric IDs is what I have always used, but then I haven’t worked with online DBs much before, so wasn’t aware of the security concerns. Thanks for pointing that out.

What if I want my users to only use an app while online? Then this could theoretically be supported, right?

.
.
Anyways, since the DBs I am using are new and have little to no data in them, it is not difficult for me to move to using a key that is generated in AppSheets.

@Erik_Meyer Hi Eric, my apologies, I incorrectly remembered that we had a built-in UUID() expression, while in fact we don’t. This means that right now the best option is to use our UniqueID() expression, which generates a sequence of 8 random alphanumeric characters. I will also correct this in my previous response.

Regarding your question about the app’s being always online, in theory, if every single change is immediately synced after it is made by the app user, it is possible to accommodate keys that are generated by the data source instead of the app (such keys include but are not limited to auto-incremented keys in databases). However, this capability would require us to make significant changes to our existing system, so unless more users ask for it, I’m afraid we won’t implement it.

1 Like

@Erik_Meyer
you can readily create a longer UUID with the following type of “initial value” calc:
concatenate(UNIQUEID(),"-",UNIQUEID(),"-",UNIQUEID(),"-",UNIQUEID(),"-",UNIQUEID())

Which is close enough to the standard for practical purposes.
app example here: https://www.appsheet.com/samples/Sample-App?appGuidString=734dbfbb-8289-47bf-9ee1-06ff9714ef2a

Another pattern I like is:

concatenate(UNIQUEID(),"-",Number(Now()))

This one has the following nice results:
** because it leads with 8 digit alpa numeric, it means this data will be compute-dispersed during a subsequent deep analytics or deep machine learning situation (e.g. spark, tensor flow or similar) If this is not relevant to you, then you can ignore this point.
** because it trails with a unix timestamp, you could in theory perform longitudinal analytics using your primary key later on, simply by stripping out this portion of the ID into its own column.
** In other words, you have efficiently encapsulated/stored potential information for future use, while providing a solid primary key for the “now”.

2 Likes

Thanks @Steve, @TyAlevizos and @Harry for your thoughts. I appreciate the clarity you have provided.

I’ve decided to make my PKs varchar(36) to conform with qualified UUIDs that might be generated from another app accessing the data.

1 Like

Hi all,

I have a cloud sql database and it has already thousands of records of data by using my app maker app,

now, I need to try out appsheet to transfer my app maker app to appsheet app, however i need to know firstly if appsheet can be used to generate a primary keys (in my tables for new records) based on the last primary key in that table then add one to it to be the key for this new record.

also I might need to mention that my users use the app only online.

I just need to explain more, that only one particular user who is allowed (in my app) to create a new record that uses sequential primary keys, so there is no possibility that any kind of concurrent users can create new records in this table.

If you can guarantee only one user will be adding new rows, then yes, it’s possible.

Hi Steve, thanks for the reply, and of course, a good news to me,
if it is possible, can you, please, tell me the details of how to perform that?

Please see this post:

It works very well using Initial value expression of (MAX(MyTable[ColumnName]) + 1),
thanks so much, Steve

Also I would also need to know if there is any way in appsheet to create a bulk of records at once (some times more that 100 to 500 records)?

1 Like

Not easily, and adding so many rows in rapid succession might run afoul of resource quotas. What is it you’re trying to accomplish by adding so many at once?

1 Like

my system running now as app maker app is for generating tracking barcode numbers of units of products, so i need to generate a record representing each unit of a product, so as to be able to track its status during a workflow - by using barcode scanner - til it is stored then sold and also if it is returned from the customer.

this scenario works well now using app maker and sql cloud as backend.

So, I need to be sure the main features of my app can be achieved by using an appsheet before I can decide to start the transfar.

So you want pre-generated tracking numbers? Or would you just be recording the tracking numbers from the units quickly?

1 Like

Hi Steve,
yes, I need to each day to generate new records representing each produced unit of a product, this generated record contains some fields, such as (barcodeNumber, currentStatus, dateofStatus, productSpecs, dateofProduction, etc) ,
so then i will need to scan this barcode resided on the unit of product to get it in warehouse which in turns changes its state to be in this particular warehouse name and date , or scan it to sale department or elsewhere,
Also daily the system has to generate up to 2000 new records - as explained above - but each time the system has to generate between 100 - 300 new records.
I hope I succeeded to clarify this feature of the system.
So , can you please, how can I accomplish this using appsheet?