What is the best practice in getting each item from a specific category with MySQL and Node.js?

Question

For example, I have a table called albums

album_id	name	description	price	release_date
1	album1	description1	10.00	current date
2	album2	description2	10.00	current date
...	...	...	...	...

and now I have a table called songs

song_id	name	description	duration	album_id
1	song1	song description 1	180	1
1	song2	song description 2	180	2
1	song3	song description 3	180	2
1	song4	song description 4	180	2

Now, I'm trying to build an API endpoint where I return a JSON of all albums which includes a list of the songs that are included in each album.

Currently, I can do it by first getting all the albums through this:

let albums;
db.query('SELECT * FROM albums', (error, results) => {
    albums = results;
}

This returns a JSON with the details of all albums. Now, I want each entry in the JSON to have a songs property which contains an array of songs that belong to that album.

To do that, I thought about doing it this way

albums = albums.forEach(album => {
    let songs;
    db.query('SELECT * from songs WHERE album_id = ?', [album.album_id], (error, results) => {
        album.songs = results;
    }
}

My expected output is a JSON of containing all the properties from the first query with an additional "songs" property that contains an array of the details of each song that is in that album.

I think this would accomplish what I am trying to do but is there a better way of doing it? I have a feeling this is not the most efficient way of doing this. I am new to relational databases so I am not sure how to do this purely off of SQL.

Guide us through the flow here. This will end up on a web app, correct? So assuming that I'm a user and when I open the page, I'll see all albums listed, according to you first query. Then, how about the song list? Should it be shown together on the same page or would it require me to select an album first then only the song list will load? What about the load type? Will it load on a different page (showing a single album details) or when I click the album, it will just expand to show the song list? — FanoFN, Jun 19 '21 at 02:17
This won't be in a web app. I am just trying to learn how to best do an api endpoint like this. Basically, for example, a user sends a GET request to the /albums/ endpoint. This will give them the details of all the albums that exists in the database, including songs that are in each album. Sorry if the question was badly worded. — Enn Vee, Jun 19 '21 at 06:10
I see, so basically you want information from these two table in a single query, right? If that so, you can just do a [JOIN](https://dev.mysql.com/doc/refman/8.0/en/join.html) — FanoFN, Jun 21 '21 at 00:35

score 2 · Accepted Answer · answered Jun 21 '21 at 14:13

Based on your stated goal:

I'm trying to build an API endpoint where I return a JSON of all albums which includes a list of the songs that are included in each album.

The best way to do this probably with a JOIN query, a VIEW created on the DB side, or a STORED PROCEDURE created on the DB side. You can read more about the when/why to use each here.

All 3 of these options can potentially return 100% of the albums and their associated song data based on how you structure the queries. Depending on the size of your datasets it might be prudent to stream the results.

Beyond your stated goal:

It is not generally best practice to simply pull all the data (especially when adding nested data) and return it to the front-end or consumer of an API endpoint. Large datasets may cause undesirable performance on both sides of the process.

The best approach is to model your API endpoints based on how they will be used and consumed. In the generic example you provide we might expect the following endpoints:

GET /api/list_albums
GET /api/get_album_by_id/1001
GET /api/list_songs_by_album_id/1001
GET /api/get_song_by_id/2001

As a user of your API works their way through these endpoints they get increasingly more specific results. The results of each query contain data that can be used to inform followup queries.

If I have a DB with 10,000 albums and I only need to see the song list for one of them I can query for the list, locate the album I care about and use its ID to query for its songs.

Two queries are required but an enormous amount of data aggregation, transmission, and parsing is saved.

You can read more about the best practices of API design at the links below. These can help you think about how to best structure data for any type of solution.

https://swagger.io/resources/articles/best-practices-in-api-design/

https://jsonapi.org/recommendations/

What is the best practice in getting each item from a specific category with MySQL and Node.js?

1 Answers1