Xano - Cascading Delete

Cascading delete is a SQL concept that allows you to delete a record from a parent table and automatically remove all associated records from child tables. In Xano, you can implement this functionality using functions, which provide a flexible way to manage multi-level dependencies. In this guide, we'll walk through the process of setting up a cascading delete for authors and their associated books.

Understanding Cascading Delete

Before we dive into the step-by-step process, let's clarify what cascading delete is and when you might want to use it.

Cascading delete is useful when you have a parent-child relationship between tables, and you want to delete a record from the parent table along with all its associated records in the child tables. For example, if you have an "Authors" table and a "Books" table, and you want to delete an author and all their books in a single operation, cascading delete can streamline this process.

However, it's important to note that cascading delete might not be the best choice in certain scenarios:

1. Performance Impact: If you have a large amount of data, cascading delete can be time-consuming and resource-intensive.
2. Lack of Flexibility: Cascading delete follows a predefined set of rules and might not accommodate specific requirements, such as selectively keeping or deleting child records based on certain conditions.

In cases where you need more flexibility or have performance concerns, you might want to consider alternative approaches, like manually deleting child records before or after deleting the parent record.

Step-by-Step Guide

Now, let's dive into the step-by-step process of setting up a cascading delete for authors and their associated books in Xano.

Step 1: Create a Delete Books Function

First, we'll create a function to delete individual book records. This function will be called from within the cascading delete function.

1. Go to the "Functions" tab in the Xano library.
2. Click "Add Function" and name it "Delete Books".
3. Add an input as a table reference for the book ID.
4. In the function stack, add a "Database Request" step and choose "Delete Record" from the books table.
5. Map the input (book ID) to the field value in the delete request.
6. Save the function.

Step 2: Create the Cascading Delete Function

Next, we'll create the main function to perform the cascading delete for authors and their associated books.

1. In the "Functions" tab, click "Add Function" and name it "Delete Authors Cascade".
2. Add an input as a table reference for the author ID.
3. In the function stack, add a "Database Request" step and choose "Query All Records" from the books table.
4. In the filter section, select "Custom Query" and add the condition `WHERE books.authors_id = [Input]`. This will retrieve all books associated with the specified author ID.
5. Add a "Data Manipulation" step and choose "For Loop" to iterate through the queried book records.
6. Inside the for loop, call the "Delete Books" function we created earlier by going to "Custom Functions" and selecting it.
7. Map the input of the "Delete Books" function to the book ID from the current iteration of the for loop by using the variable name and appending `.id` (e.g., `book.id`).
8. After the for loop, add another "Database Request" step and choose "Delete Record" from the authors table.
9. Map the input (author ID) to the field value in the delete request.
10. Save the function.

Step 3: Test the Cascading Delete

Now that we've set up the cascading delete function, let's test it to ensure it works as expected.

1. Go to the "Debug" tab and select the "Delete Authors Cascade" function.
2. Provide an author ID as the input (e.g., the ID of an author you want to delete, along with their associated books).
3. Run the function and observe the results in the database.

You should see that the specified author record has been deleted from the "Authors" table, and all their associated book records have been removed from the "Books" table.

Conclusion

Cascading delete can be a powerful tool for managing related data in Xano, but it's essential to understand its limitations and use cases. By following this step-by-step guide, you've learned how to set up a cascading delete for authors and their associated books using functions in Xano.

Remember, Xano's visual interface and no-code approach make it accessible to both non-technical users and developers, enabling you to build and deploy backend services more efficiently. Whether you're a no-code enthusiast, citizen developer, traditional developer, or part of a startup or small business, Xano can streamline your application development process.