Creating an Array in SQL Database
While working on an app to connect customers and pet groomers, I decided to make a feature that allowed customers to save their favorite groomers to their account. The purpose of this post is to explain the process of creating an array storage instead of creating a new table. This task was challenging at first because I had never created an array in a backend (I wasn’t sure if this would even be possible), and it was also hard to find help and examples through Google.
I needed to find a way to write the code in the backend that stored a list of groomers that each customer liked. The first step that came to mind was to create a new table, titled Favorite Groomers. This table would connect a Customer ID to a Groomer ID, resulting in a one to many relationship; which is multiple Groomer ID’s referring to one Customer ID.
Lately, I have mainly been using Python for data manipulation, which sparked my next idea: Using an Array. I wondered if it would be possible to create an array within the Customer table that would store all the ID’s of the customer’s favorite groomers. This was a little difficult for me to figure out, but it turned out to only be a couple lines of code.
For this code, I used knex. Knex is a SQL query builder that is flexible and really fun to use. Broken down, the code is pretty straight forward. The exports.up function is where I was adding to the existing customer table. I added a new column calling it “favorite_groomers”. Every column needs to have a name and a data type. For mine, I am using the key word “specificType” which takes two properties; the first is the name of the column and the second is the data type. In this example, that was an array of integers. The code below that is a function to deconstruct what I built for clean up purposes. In order to run my migrations and create a table, I needed to use the “up” function, and in order to clear my tables, I needed to run the “down” function. To do this, I could choose between either run knex migrate:latest (up function) or knex migrate:rollback (down function).
The next step in the process was adding the capability of adding a favorite groomer to your customer. The image above is the code I used, I’ll break down what this code does. First, when a customer is built they don’t have any favorite groomers saved, which means that the field is set to null. This scenario is what that first “if” statement is checking for. If the favorite groomers is empty (null), I then need to create an array before passing the incoming groomer ID into the customer database. To do that, I need to first copy over the body of the request (req.body) then set the favorite_groomer to equal the id being passed but in an array.
The “else” statement then addresses the customers that already have an array of favorite groomers. In this circumstance, I want to add on to an existing list, which is done by to using the spread operator ( […oldFav] ) and then adding the new favorite to the array.
Next, I created a function to remove an ID from our list of favorites. I first searched for the customer using the customer ID found in the URL. Then, I checked to make sure that the customer has an array of favorite groomers and use the array filter function to remove the groomer ID passed through the request body.
Once I had an add and remove function, I was finished with the backend! The 3 images posted above are all the code I needed to use an array as opposed to creating a table for Favorite Groomers. The pro of using this method was how quick and easy it was; however, the downside was that I was not able to use the cascade function. One benefit of cascading is that if a Groomer is deleted, that ID would then be removed from all tables that are connected to the groomer’s table. Since the array is only used within the customer table, unnecessary space is used, resulting in a groomer ID that no longer exists. In this example, there were few groomers, so having groomers in the database that no longer existed was not an issue. In the front end, only the existing groomers were displayed, so if a groomer was deleted, his ID remained in the customer’s database but it was never displayed.
If you would like to see how the app turned out please follow the link below.
