Editing Complex Fields in @nocodebackend

So a question that's come up a lot in communities as well as in StateChange is the idea of editing complex fields in Xano. And this, by the way, applies to things that are beyond Xano, but we're going to show how to do it within Xano today. And the crux of the problem here is that if you have a table, right, and we're going to take a look at a table I've set up here for illustrating this kind of purpose called JSON test. There are different kinds of fields, right? Some of them are very, very simple, like a text field. And you can update your text field or a number field and you can update the number. And it's relatively straightforward how that works, right? You want to update to a certain number? Great. You can set to that number. You want to update it to a certain text? You can update it to that text. But the truth is there are more complicated things that we use that you want to modify in a more complex way, right? Like, let's say I've got this tags test list of strings here. And I want to have facility where I'm going to add something to my list or remove something from my list. Now that can feel very intuitive to us, right? We think, hey, it shouldn't be that big a deal for us to add or remove or whatever we want to do. But the truth is the way the database thinks about it is actually more atomic, right? It thinks about it the same way it thinks about changing that number or that string. It's going to replace the list with a new version of that list. And that's the reason why when you are, you know, writing to a given table, like we might do in this edit record here, we gotta do something like first having gotten the record, the old view of it, and then we need to push on the new item that we're going to add. Because if what we do is we just update it to the actual direct item to add, it won't give us a correct answer, right? Well, let's just try that right now. If we just set this to item to add, I'm just gonna confirm that. And obviously don't even worry about the GET, right? So if we just update the item specified in this endpoint and we just set it to the item to add and we say we're going to run debug this, we're going to add the letter A to the record at record number 1. And just to illustrate what that looks like, we're gonna say here item number 1 for JSON test right now contains an A. And we're going to go over to here and we're going to say we want to add a, add a B to it, right? Intuitively that should be like we're gonna wind up with an AB at the end. And I click on run and it doesn't do that. It actually just replaces it with the B. And that's the reason why we need to take the technique of when we go into edit record, we go in, we don't set it to item to add, we set it to its old value. And we do that by running a, let me just clarify this first, clear this up a little bit. We have to run a get record first, right? So we're gonna do get record from json-test and we're going to, you know, ID, ID, we'll talk about the lock thing in just a second. And by default it just sort of gets one item from the record and we get it and then we can update based on that. So instead of setting it to that, which would just add a B to it, we're going to say we want to set it to what it was previously. .tags_text, right? That's the field in JSON1 called tags_text for record number 1. And we're going to push the new value I'm to add onto it. So let's remember that right now it just has a, well, let's refresh this for a second. I think as of last time, yeah, it has a B in it, right? So what we're going to do is we're going to run a new one And we want to say we want to add a C to it, right? So if we've done this correctly, it should create a B, C at the end. We run. Now we have a B, C. Go check the table. Great. Now we have a B and a C, right? So that's step 1 of how we deal with complex objects. We need to first pull it from the database, make the modification we're going to make, in this case, pushing something on. And then we save it back into the database. Pull, change, save. Now, this is all relatively intuitive as far as it goes. And, you know, we can just, you know, run this a couple more times, just keep adding more Cs to our table and sort of demonstrate how this works. But, but, but this actually raises another problem, which is the problem with the race condition. And in, in a race condition, you can have two directives that are both firing. And you want to make sure you're getting the best value from each of them. Now let's make a race condition that says we're going to get a data, get a record, and then there's going to be some time that passes and then we're going to update the record. Why would there be time that passes? Maybe what we're going to do is actually getting data from OpenAI, right? And we're going to get like some generated text or structured data or whatever, and then we're going to put that into update a JSON field or update, you know, this list like we're doing or whatever it turns out to be. So to simulate going to OpenAI, which usually just takes time, I'm just gonna have this thing sleep for 5 seconds. And now what I can do is rerun it. And you can see it waits for 5 seconds and then gives me another guy. Now, here's the thing. What if I launch it and then I launch it again? Am I going to get the best of both worlds? Now, to illustrate this, it's actually easiest to do it from the browser as opposed to doing it inside Run Debug, because Run Debug really lets you do one thing at a time well, and Xano doesn't like it when you are editing the same endpoint from two different tabs. So to test this, we're just going to do a little publish here. Publish. And we're going to go over to my browser and In my browser, I have got a URL going over to my little test race condition thingy where, which is giving arguments, the ID equals 1, just like we had before, and the item to add of B. And this time I'm gonna sell it to add D, right? We haven't done D so far. Right now in our table, if I were to refresh this, we have lots of Bs and Cs. In fact, what I'm just gonna do is delete all of these Cs, that way it'll be easier for us to see. Cool. So we're gonna add a D here and then I'm going to have a second tab open where I'm gonna go to the same race condition. And this, in this place I'm gonna add an E. All right? Now intuitively what we want to have happen is we want it to open up, you know, after each one, first add the D and then add the E, right? So let's, let's kick this off. We're going to, did I publish it? Yep, it's all published. Okay, so now we're gonna run the D, chug, chug, chug. And now while that's running, I'm gonna run the E one. Now if this has worked properly, it should give me both the D and the E. Go back over to here, do a little refresh. See, but it didn't, just give me the E. Even though we saw me launch it with a D, and launch it with E. Now if I launch it with a D right now, and I'm just gonna, you know, press enter to, to get it to go and it'll take 5 seconds for it to run, it will give me my D at the end. And if I do this one, run again, 1, 2, 3, 4, 5, and it gives me the E at the end. But in order to do that, I need to tell the set, only launch the second one after the first one was finished. Because if I tell it to launch, let's say launch E, and then I tell it again to launch D, right? Remember what we have so far, 1, 2, 3, 4 of them. It will only create the fifth one because it's only going to be the last past the post. Right? Now they'll predict that was going to be the D. Now, Why does this happen? This happens because first I'm getting the old version of it, right? Which in my case was there's the version that had those first 4 B-E-D-E, right? And when I launched each of the, these 2 versions, it only knew about that B-E-D-E version that I had at the beginning, the B-E version. And the, and then it waited 5 seconds. And then it updated with a new piece of information it wanted to add, either the E or the D. Now, the reason why this matters is that there are gonna be lots of situations where you want things to be updated, complicated data structures to get updated inside your Xano, but you don't necessarily have control over when the user's going to do it. And it's going to be like updating, you know, one list, it's going to be updating complicated JSON or whatever it might be. And you're going to run into this situation where, you know, if two people, or if you have two processes are both trying to modify the same field on the same record, that you wind up with not the best insight from both of those actions, but usually just the last one to have written because, you know, it read before it knew about the write from the other one. So how do we solve that problem? Now, an eagle-eyed person would probably observe that like it would be pretty smart to put these two things right next to each other and not have a sleep statement in the middle of them. And that would, that's true, but not always something we can avoid. Also, when you get to relatively high volumes, it can be pretty hard to control for that because there is time that passes between the get record and the edit record, a small amount of time, but an amount of time, and eventually, especially if you have relatively high volume, you're going to get glitches. So how do we guarantee that the edit record is always going to be based on the latest possible information? We do that through something called a lock. So if we go into get record, we can see, and I've mentioned that we're gonna get to this before, that there is, not only can we select which field we're going to pull, but we can also specify whether we want to lock the record. Now locking the record means nobody else gets to either read or write this record. No other process can, right? So if I have one API endpoint that's going to get and edit the record, a second API endpoint, when it sees that lock, says, oh, I'll hold off, wait for that transaction to finish, and then I will read the record and I will get the record. It basically creates a queue. People stand in line. By having them stand in line, it gets much, much smarter. Now, to do this, you can just change this into a true, and that's pretty good. Now, the other thing I'll note is that this does not work as is. This only works in the context, I used this word before, of a transaction. And so you need to go into database requests and create a database transaction and just put all of your logic that you're gonna relate to reading and writing the data inside that transaction. Here, there, and I'll put that into here. Move myself outta the way. You grab that, put that inside. Okay, so it's important that if you want to use a lock that you put things into a transaction. Transaction, again, doesn't have an effect unless you do a lock. But if you do, and also locks don't have an effect if they're not inside a transaction. So what's going to happen is once I put the lock on here, another process, even running the same endpoint, won't be able to even do the first thing in this database transaction until this transaction has completed., right? That way the second customer can't come into the store until the first customer had checked out. This is pretty cool. So then now what I'm going to do is go publish this. You'll also notice that it's gonna slow things down because since my 5 seconds happens after the get record, the first one's going to come in and say, I'm gonna do get, sleep, edit. And the second one's going to come in and say, and says, well, I want to do a get. And it's gonna say, no, you can't. You can't until the other one has finished this transaction.. So instead of being like the first one taking 5 seconds and the second one taking, you know, 5 seconds, but starting 1 second later. So the net time is 6 seconds. It's just going to go one after another, nose to tail. So it'll take about 10 seconds total and we'll notice that slowdown. We'll notice that slowdown, particularly after we hit publish. Okay. So I now have my JSON test here and I'm going to just get rid of everything except the B, right? And I'm going to add in one that's going to be just for, for just so we can see it work a different way. I'm gonna make one X and one Y. And our previous problem was if I kick off one and then I kick off the other, I only expect the Y to show up. I expect the X to not show up. But if we've done this correctly, then we should get both the X and the Y. Let's see if it works. I kick off the X, kick off the Y. People using timers might notice this actually took the, the Y one took longer than it did before. It took more than 5 seconds. That's because it had to wait not only for its own 5-second timer, but also for the previous one to have finished before it really even got going. See how that got? Refresh. See? I got both the X and the Y. Now I'm using a really simple example here of adding things to an array and doing a push, and this kind of scenario happens all the time, but this applies just as well to any of these other complex data types. Anything you're using as a list, anything you're using as an object, anything you're using in JSON data type, which can be all sorts of, you know, fun ways that you might be storing information. These complex data types really benefit from putting in this kind of lock to be dealing with race conditions. And when you do, you're able to get the best insight from both of your systems. And the, there's some other best practices you can bring to bear, but I think if you just put in this one, using database transaction for solving the pull, modify, push inside the context of a lock, you will first have the easy way to gain the manipulations you want, and second, you'll make sure that you don't deal with that dreaded race condition.