11436 SSO

NoSQL - NoProblem! Mapping your SQL thinking to NoSQL

pdardeau
May 22, 2013

I've been hearing about the virtues of NoSQL for a number of years, but only lately did I really have a good opportunity to dig in and see what all the fuss is about.  As with many folks, my background with storage technologies has been 99% standard SQL in common relational databases. The big hurdle for me is how do I go about 'thinking' properly in this brave new world of NoSQL.

To dig in - and for this article - I used a simple music database as my example. I like this example because it's simple enough that everyone understands the concepts, plus I also like music.

The structure I'd use in SQL is this.

CREATE TABLE artist
(
        artist_id INTEGER PRIMARY KEY NOT NULL,
        artist_name VARCHAR(50) UNIQUE NOT NULL,
        country_of_origin VARCHAR(50) NULL
);

CREATE TABLE album
(
        album_id INTEGER PRIMARY KEY NOT NULL,
        artist_id INTEGER NOT NULL REFERENCES artist(artist_id),
        album_name VARCHAR(50) NOT NULL,
        year_released INTEGER NULL
);

CREATE TABLE song
(
        song_id INTEGER PRIMARY KEY NOT NULL,
        album_id INTEGER NOT NULL REFERENCES album(album_id),
        song_name VARCHAR(50) NOT NULL,
        track_number INTEGER NULL
);

Now, we'll add some data:

INSERT INTO artist (artist_id,artist_name,

country_of_origin) VALUES (1,'The Who','England');
INSERT INTO artist (artist_id,artist_name,country_of_origin) VALUES (2,'ZZ Top','United States');
INSERT INTO artist (artist_id,artist_name,country_of_origin) VALUES (3,'Van Morrison','Ireland');

INSERT INTO album (album_id,artist_id,album_name,year_released) VALUES (1,1,'Who''s Next',1971);
INSERT INTO album (album_id,artist_id,album_name,year_released) VALUES (2,2,'Tres Hombres',1973);
INSERT INTO album (album_id,artist_id,album_name,year_released) VALUES (3,3,'Moondance',1970);

INSERT INTO song (song_id,album_id,song_name,track_number) VALUES (1,1,'Baba O''Riley',1);
INSERT INTO song (song_id,album_id,song_name,track_number) VALUES (2,1,'Bargain',2);
...
INSERT INTO song (song_id,album_id,song_name,track_number) VALUES (17,2,'La Grange',8);
...
INSERT INTO song (song_id,album_id,song_name,track_number) VALUES (21,3,'Moondance',2);
INSERT INTO song (song_id,album_id,song_name,track_number) VALUES (24,3,'Into the Mystic',5);

Who sang that song and when?

If we remember that a song's name is 'La Grange' but forgot who made it and the year, we can query it.

SELECT a.artist_name,b.year_released
FROM artist a, album b, song c
WHERE a.artist_id = b.artist_id
AND b.album_id = c.album_id
AND c.song_name = 'La Grange';  

Or, if we prefer using inner joins:

SELECT artist.artist_name,album.year_released
FROM artist
INNER JOIN album
ON artist.artist_id = album.artist_id
INNER JOIN song
ON album.album_id = song.album_id
WHERE song.song_name = 'La Grange';

This SQL stuff is old hat and most of us could probably do this in our sleep!

How do we go about doing the same thing in this strange new territory of NoSQL?

One bit of advice that was given was "just think of it as a database without the structure". Well, that's exactly the problem that I have -- I don't think of databases without having structure.

So here's how we would think of the problem in a NoSQL situation, like with Apigee's App Services.

We will have a collection for artists, one for albums, and another for songs. 

We can roughly think of a collection as being synonymous with a 'table' in relational databases. What's the difference?

NoSQL systems don't have any strict rules on what data you can or can't put somewhere. Huh? Think of it as being similar to SQL, but without the constraints on table structure or referential integrity.

Well, that's kind of the way that SQLite works, isn't it?

Somewhat, but this new territory is even more flexible than SQLite. You see, SQLite doesn't care much about referential integrity and data types, but it still cares a great deal about column names -- meaning you can't decide to add a new piece of data to a table without first issuing the correct ALTER TABLE statement to add the new column.

NoSQL Music Database

Let's take a look at how we go about setting up our music database in App Services.

Here's pseudo-code for how we might set things up.

artistData = {'type'='artist', 'name'='The Who', 'country_of_origin'='England'}
client.create_entity(artistData)

artistData = {'type'='artist', 'name'='ZZ Top', 'country_of_origin'='United States', 'notes'='long beards and dark sunglasses'}
client.create_entity(artistData)

artistData = {'type'='artist', 'name'='Van Morrison', 'country_of_origin'='Ireland'}
client.create_entity(artistData)

Wait, I see you did something weird there. You added a 'notes' value for one of the artists, but not for the others. Yep! But notice that we never declared a structure for the artist collection at all. This means that we're free to put whatever we want in it at any time.

albumData = {'type'='album', 'name'='Who\'s Next', 'artist'='The Who', 'year_made'=1971}
client.create_entity(albumData)

albumData = {'type'='album', 'name'='Tres Hombres', 'artist'='ZZ Top', 'year_made'=1973}
client.create_entity(albumData)

albumData = {'type'='album', 'name'='Moondance', 'artist'='Van Morrison', 'year_made'=1970}
client.create_entity(albumData)

songData = {'type'='song', 'name'='Baba O\'Riley', 'artist'='The Who', 'album'='Who\'s Next', 'track_index'=1}
client.create_entity(songData)

songData = {'type'='song', 'name'='Bargain', 'artist'='The Who', 'album'='Who\'s Next', 'track_index'=2}
client.create_entity(songData)

songData = {'type'='song', 'name'='La Grange', 'artist'='ZZ Top', 'album'='Tres Hombres', 'track_index'=8}
client.create_entity(songData)

songData = {'type'='song', 'name'='Moondance', 'artist'='Van Morrison', 'album'='Moondance', 'track_index'=2}
client.create_entity(songData)

songData = {'type'='song', 'name'='Into the Mystic', 'artist'='Van Morrison', 'album'='Moondance', 'track_index'=5}
client.create_entity(songData)

Query

If we remember that a song's name is 'La Grange' but forgot who made it and the year, we can query it.

songQueryData = {'type'='song' 'name'='La Grange'}
songData = client.get_entity(songQueryData)

if( songData )
{
        albumQueryData = {'type'='album' 'name'=songData.album 'artist'=songData.artist}
        albumData = client.get_entity(albumQueryData)
        if( albumData )
        {
                print albumData.artist, albumData.year_made
        }
}

Hopefully, this gives a good first taste of NO-SQL goodness in App Services. In our next part, we'll have a look at more sophisticated queries, update, and delete operations.

API Management Decision-Making Kit

Next Steps

 
 

Resources Gallery

News