The right - and wrong - ways to do versioning in Xano

Don't make the mistake I did.

One of the great things about Xano is its simplicity. It’s easy to get up and running in no time with a database and APIs. What is a little trickier, however, is doing more advanced things. For example, applying versions to records.

To explain what I mean, consider what happens whenever you edit a record in Xano. By default, when this happens, Xano either partially or entirely modifies the old record (“version 1”) instead of creating a new record and calling the new one “version 2” or something similar.

This behavior is helpful in that it allows you to maintain existing references to (and from) other tables. But it is unhelpful if you want to maintain a historical record of what certain entries were at a given point in time. For example, if you wanted to offer any kind of undo or audit trail capability in your product, you would need to maintain at least some historical record of what records were at some point in the past.

How to version records in Xano

The best solution to this problem I have found is to create a copy of your original table with the suffix _archive or something similar. I’ll give a hat tip to this Reddit thread for giving me a general outline of how to proceed.

As part of your function or API call that modifies the original record, you should use conditional logic to create a copy of the original record in the _archive table, as in:

Additionally, you should use all of the original record’s attributes, including its created_at timestamp when adding the new record in the _archive table.

Finally, you should add a table reference column in the _archive table that refers back to the id (index) of the original record. This way you can quickly lookup all of the historical records for a given entry.

This method allows you to query the _archive table using the original records index id, so you can sort by time of modification, person editing the record, or any other characteristic.

What you should NOT do

The first time I approached this problem, I though I would keep all of the data in just one table but create a new column called version. Every time I created a new record, the new record’s version would increment by +1. This necessitated creating the following:

• A record_id, separate from the auto-generated index id, to uniquely identify each record referring to the same thing.

• A record_version to identify which version of the record_id the query was referring to.

• A function that would look up the highest record_version for a record_id record. This required a for loop through all of the existing records.

If you are computer science expert, you are probably laughing already, but suffice to say, this did not go well. As soon as I had a few hundred records in the database, the for loop took an extremely long time to execute and basically prevented my application from running.

Additionally, this approach made it impossible to use table references within Xano, greatly complicating my application development efforts.

Conclusion

In summary, unless you are aware of a better approach (please comment with it), then you should version records in Xano in this manner:

• Create a base and base_archive table.

• Add in function/API logic that copies records from the base table into the base_archive table whenever a record in the former is modified.

• Create a table reference from the base_archive table back to the base table to allow for lookups of all historical records for a given id in the base table.

If this post has been helpful, then give it a like and let me know! Until next time, keep plugging away on your app.