Graph Façade API over Relational Data

Writing SQL queries can be a lot of work. This work feels especially tedious if you made upfront investment into designing a well normalized database model. With all those relationships already declared, why must we specify all those JOIN statements in queries again?

Consider this database model for an online music store. It has tables for Artists, Albums and Tracks among others. Every Artist can have multiple Albums, and every Album can have multiple Tracks.

Now, suppose we are building a web or mobile application and want to present our users with a rich browsing interface to our music store. For example, we may want a feature, which allows users to browse for music from their favorite Artist. Here’s how you would accomplish this using automatically generated API from SlashDB.

First, GET all Artists (records from table Artist in database Chinook):

http://demo.slashdb.com/db/Chinook/Artist/


curl -H "Accept: application/json" http://demo.slashdb.com/db/Chinook/Artist/

[
{
"Album": {
"__href": "/db/Chinook/Album/ArtistId/1.json"
},
"ArtistId": 1,
"Name": "AC/DC",
"__href": "/db/Chinook/Artist/ArtistId/1.json"
},
{
"Album": {
"__href": "/db/Chinook/Album/ArtistId/2.json"
},
"ArtistId": 2,
"Name": "Accept",
"__href": "/db/Chinook/Artist/ArtistId/2.json"
},

...

,
{
"Album": {
"__href": "/db/Chinook/Album/ArtistId/59.json"
},
"ArtistId": 59,
"Name": "Santana",
"__href": "/db/Chinook/Artist/ArtistId/59.json"
},

...

]

That was easy. The above URL renders HTML by default, so we can look at the data, and visually ascertain if that is what we need. Using curl we additionally send “Accept: application/json” header in order to examine the same resource as JSON objects. Alternatively, we could simply end our URL with “.json” file extension.

[box style=”light-green” ]

Did you know that SlashDB allows you to automatically reflect database table relationships into a hyperlinked REST API? No queries, no tedious object mapping, all automated and ready to use.

[/box]

Suppose a user is interested in Santana’s music, thus we need to GET all Albums from Santana:

http://demo.slashdb.com/db/Chinook/Artist/Name/Santana/Album/

curl -H "Accept: application/json" http://demo.slashdb.com/db/Chinook/Artist/Name/Santana/Album/
[
 {
 "__href": "/db/Chinook/Album/AlbumId/46.json",
 "Title": "Supernatural",
 "Track": {
 "__href": "/db/Chinook/Track/AlbumId/46.json"
 },
 "Artist": {
 "__href": "/db/Chinook/Artist/ArtistId/59.json"
 },
 "AlbumId": 46,
 "ArtistId": 59
 },
 {
 "__href": "/db/Chinook/Album/AlbumId/197.json",
 "Title": "Santana - As Years Go By",
 "Track": {
 "__href": "/db/Chinook/Track/AlbumId/197.json"
 },
 "Artist": {
 "__href": "/db/Chinook/Artist/ArtistId/59.json"
 },
 "AlbumId": 197,
 "ArtistId": 59
 },
 {
 "__href": "/db/Chinook/Album/AlbumId/198.json",
 "Title": "Santana Live",
 "Track": {
 "__href": "/db/Chinook/Track/AlbumId/198.json"
 },
 "Artist": {
 "__href": "/db/Chinook/Artist/ArtistId/59.json"
 },
 "AlbumId": 198,
 "ArtistId": 59
 }
]

We begin from the previous context (the Artist table) and add filtering by Artist’s name (Name/Santana). In SQL terms the equivalent clause would be


WHERE Name = 'Santana'

Since the tables Artist and Album are related, SlashDB automatically lets you traverse that relationship. All we need to do is append /Album to the URL. In SQL we would write it as


JOIN Album ON Artist.ArtistId = Album.ArtistId

Notice how the API also “advertises” possible relationships for each Album record using the __href attribute. The one under the Track field, leads – you guessed it – to the list of Tracks for the given Album.

http://demo.slashdb.com/db/Chinook/Track/AlbumId/46.json


curl -H "Accept: application/json" http://demo.slashdb.com/db/Chinook/Track/AlbumId/46.json

Think of __href attributes as edges in a graph of which nodes are actual data resources. If this seems familiar to crawling the web, that is because it is – a web of data.

[box style=”light-green” ]This is just one of many ways, in which SlashDB makes working with databases fun and easy. Subscribe to this blog to discover them all. Then try SlashDB with your databases on:

[/box]