Preventing Duplicate Records in Xano (@nocodebackend)
In this tutorial Ray Deck walks through two primary strategies for preventing duplicate records in a Xano table that must be unique across a combination of fields (e.g., user ID + tag). He first shows how to create a multi‑field unique index directly in the Xano database, which automatically blocks inserts that would violate the uniqueness constraint. He then demonstrates a programmatic approach for cases where a database‑level constraint is undesirable or insufficient: building a POST API endpoint that either (a) wraps an add‑record call in a try‑catch to intercept the database error, or (b) explicitly queries the table with a "query all" operation (return type = exists) to test for an existing user‑tag pair before inserting. The demo covers endpoint setup, authentication, input handling, utility functions for error handling, and how to return meaningful responses. Ray explains the trade‑offs between DB‑level enforcement and business‑logic checks, and provides practical tips such as using WebSockets for live refresh, opening debug tabs, and renaming variables for clarity. The video concludes with a recap of the two techniques and advice on choosing the appropriate method based on data cleanliness and control.
Chapters
Quick one today, but one that often gets people tripped up, and that's going to be how to prevent duplicates in a table. Let's say we have a table in our Xano database. We're going to click on here and just going to do, you can call it dupe check. And I'm going to, the starter template is actually not a really great idea. I recommend always telling it to never create tables or never create the endpoints for you. But we'll leave that be for the moment. So let's just say that I want to have something that's going to be, I don't know, connect my users and allow them to pick, say, tags, right? And so I'm going to have a tag. And for each user is allowed to have one and only one tag. So if I were to pick a user, you know, I can say, I'm going to say, Rob me, okay, and I'll say the tag of alpha,, right? And I'm allowed to have someone else, say James, have a tag of alpha, right? And we want Ray to be able to also have a tag of beta, right? But we would like to prevent Ray from also having a tag of alpha, right? We want to say this is basically the situation we want to avoid. So we're looking to prevent duplicates based on the intersect of multiple fields. Now there are a couple of different ways you can go about this problem. Um, the first and probably the easiest way to do this, if this is something that's true in terms of constraining your table, it's to create what's called a unique index. So over here you can see there's this little— I'm not sure why they use like a gauge for indexes, but they do. And you can click where it says indexes and we can create a new index and we can say the type is going to be unique. And this is important. Unique index on user ID, and we click on Add Field, and we also say on tag. So when we do that, that will make it so that this table will only be able to have one record per. So if I were to add a new record, and I were to say, Ray, and I were to say, alpha, it'll say no duplicate record detected, please check your input and try again. And that's because it's just not allowed. So I'm gonna go kill this guy. Confirm. Let's just for the sake of argument say there's something called tag 2, which does not have a unique index on it. And sometimes you have to refresh in order to get to show the right stuff. Xano uses WebSockets under the covers. That can be a little bit finicky. So I'm gonna say text and tag 2. Save. Great. And that makes sure I've got the new column. Perfect. And now we're going to say this is equal to beta, or you know what, I'll just say this is equal to gamma. And this is going to be equal to gamma. And this is going to be equal to eta, right? And we're going to just say that we don't want to allow Ray to have a second gamma. Now, keep in mind that in the case of tag, it would have allowed this. But if I were to say this right now in the table, we're not enforcing it at the table level. Maybe there's a reason you're not enforcing it. Maybe it's because your data might be a little bit dirty, you want to keep it from getting any dirtier or something. But you know, that would create a situation where I can say gamma, right? And Ray gamma and Ray gamma are allowed at the table level, but we would like to stop it at like the business logic level, because we don't have to control it at the table level. Okay, so having done solution number 1, which is unique index, let's do solution number 2, which is we're going to have an endpoint that's going to add a new tag for a given user ID. So I'm going to go to my API, I'm going to create a little silly one here, create an API endpoint. And we're just gonna do a start from scratch. And we're going to call it, you know, add tag of type POST, because we're setting things. So semantically, that makes a little more sense. Not that it matters, we're only gonna use this in the run debug context. We will make this just for fun here, we're going to make this an authenticated endpoint. That way, it'll go with the user table. And we will say by way of inputs, that we will say, okay, there's a tag, right? And you're only going to work if you have added the tag. And if you're a user, you don't already have this tag, right? So the first way we can do this is we can just do a database request, we're going to, you know, add record, add record to the dupe check table. Right. And we're going to say that my user ID is equal to my auth ID. Yep. Good. And that my tag is going to be equal to whatever the tag I put in. We're going to ignore tag 2 for a minute. Now what this will do is if I were to do a run and debug here and the auth token were for Ray and I were to say, I want to do this for Delta. It will run just fine. And in fact, if I were to say, let's just open link in new tab, which is actually a good technique when you're working with databases or have database in one tab, have your function stack in another, we go over to dupe check and we can see delta now exists for raw, right? But if I were to then say, well, let's now do alpha, right? We know that alpha would be a duplicate. We rerun. Blerp, right? Doesn't like it, it will 500 error out on account of the duplicate record being detected. This is a pretty gross way of doing it, but it gets the job done. And if what I wanted was to, you know, have a little bit more of a nuance around how this will represent its error, then what I can do is wrap it in an if condition. Actually, no, sorry. Let's get rid of that. Yeah. I wrapped this in a try-catch, which under utility functions, we go to try-catch. Uh-huh. And we'll say, say fine. And we'll put the add record into here. So that way, when errors out, it can just have something that's supposed to do. And the catch can be that it will, you know, return a very specific value, right? And we'll just say utility functions. And we'll say return, which skips the response at the bottom, and we'll just say have it return, we'll have it return nothing, you know, in that case. So now what I can do is if that happens and I rerun it, see, it just returns absolutely nothing at all, returns a null, whereas if I were to say add in Etta, you know, it will add Etta for me. Okay, so now we have a little technique where it doesn't just bomb out, right? We can combine it, we can combine a try-catch with an add record. To be able to just get control over how to use it within the unique index like we did at the beginning. But let's just say for the sake of argument, we didn't want to have unique index. Maybe there's just some dirt in the data or, you know, other reasons why we don't want to enforce that at the database level, because that's just too restricted. But we do want to make sure we don't make more of a mess going forward. Let's, let's close out that try-catch and let's make something new. And this new thing is going to be, we're going to first just ask, does it already exist? Right, so we're going to do a, a database request and we're going to go look for, ordinarily we'd look for has record, but we actually want to look on, but you'll see that using that test, if I were to go to dupe check, it'll only look for a thing where it matches that one field value, but that's not what we're up to here, right? We're up to looking for where it matches two field values, intersect user ID and the tag too. So the has record is not going to do the job for us, although it's very convenient for simple cases. But for interesting Cases like this, we go to the power tool in Xano, which is going to be query all records. We go over to dupe check and we're just going to say give me everything where the user ID, right, equals my auth ID and, and the, the tag 2 is equal to the tag that I'm putting in. Now this will give me a list of records that will match all that. And that, that might be more information we're looking to pull over the transom. So I can actually make this a little bit simpler. I can go over to where it says output and I can click the pencil here and change this from return type list to return type exists. And that will make it just a Boolean. It's like, it's like the complicated version of has record. It will return a true if it found something and a false if it didn't. We'll say save here. And then I'll say, um, so I query all workers and do check. And if I rerun this, it will say that there is nothing in there, right? Nothing in tag2 for Ray that goes with a tag of Etta. So let's see. Oh, equals db.check_tag. That was a mistake on my part. So what you want to do is check for the input of tag. Great. So the dupe, so we're going to check the table tag 2 equals the input tag. And if that were true, yeah, this should be true, right? Because I know that for Ray, I added gamma and for Ray, I added eta, right? But whereas James has gamma, but James doesn't have eta. So if I were to go and say, well, instead of looking at this guy, I want to take a look at James, right? James, that same eta would be false. So now I've got this true/false thing going here, I can test it and do data manipulation and do an if-then statement and say in the event that the dupe_check_1, which I know by the way that's going to be true or false, is equal to true, right, boolean true, if dupe_check_1 is true, then we're just going to skip, right, just like we talked about before, we do a utility function return, and we'll just say return null, right, in that case. Otherwise, we're going to say, um, database requests, we're going to add a record, uh, for to dupe check, uh, with all of this good stuff, not with tag, we're not gonna add to tag, please. We're gonna add to tag 2, the tag input, and the user ID here is going to be from auth, the ID of the user. Cool. So now we have the idea that this is going to be returning dupe check 2. And because, because I know at the bottom, once returned dupe check 1, I'm just going to rename this variable to dupe check 1 output and call that dupe check 1. Right. So that way, it will add the record and then it'll give me back the answer of what I just returned. Right, so if I'm James and I do a rerun, it'll tell me I can add Etta. And if I do another rerun, it'll tell me no because it's already added Etta, right? So I can just fire off as James as many Ettas as I want. And you'll find over here that, you know, James has his Etta but only gets one Etta. Okay, these become the two major techniques you can use to prevent duplicates. The first is going to be when you use the index, use the unique index technique and do it across multiple fields. Which is the simplest technique when you have high control over the data. When you have less control over the data, and let's face it, that happens more than we might like to admit, we can just compose either this try-catch approach, we can just compose together a query all records plus an add record in order to solve that problem. Even if you do use unique, you don't just want things to error out, and that's why you compose things with a try-catch like you see up here. All right, so this was a very small one, but it is a tricky thing when it comes to dealing with data that is going to be multi-dimensional and making sure that you just have that one pivot point, uh, per pair or per set of data points. And whenever you're thinking, hey, I've got— I'm looking at the intersect of multiples of these fields, you can either just apply a unique index across multiple fields, as many as you want, or you can take the approach of doing the check using querying all before you actually proceed with what you're going to do next. All right, hope you found that helpful. If you have questions about this, you know, drop them into the comments below, and I'll look forward to seeing you next time.
