Increasingly, databases which do not use SQL are becoming popular. These are collectively known as "NoSQL" databases (see here for an explanation and discussion of the advantages). NoSQL databases typically store structured data (using JSON for instance) or whole objects. One of the commonly-used NoSQL databases is MongoDB.
MongoDB stores data in a JSON-based format which makes it very easy to use with JavaScript - and it works well with node.js. Furthermore, a JavaScript-like syntax is used to perform queries. Like MySQL, MongoDB operates as a database server running continuously in the background, waiting for requests to come in from clients. It operates on port 27017 by default. Also like MySQL, you need to use a client to send queries to the MongoDB server. A NodeJS script would be one example of such a client; another is the default client called mongo, which is used from the command-line.
Logon to Neptune with PuTTY and enter the following at the command-line to run the database client:
mongo
db.getCollectionNames()You should see the following appear:
[ "hits", "system.indexes" ]A collection is equivalent to a table in a relational database. It is a collection of individual documents (records); you can see that in the MongoDB on Neptune, there is a collection called "hits". Now type in:
db.hits.find()You should now see all the individual documents in the "hits" collection, in JavaScript object syntax. Note how each object has a series of fields (properties), "title", "artist" and so on, similar to the column names in a relational database.
Hopefully you can see how MongoDB syntax is very closely related to that of JavaScript. db can be thought of as similar to a JavaScript object representing the database as a whole, and getCollectionNames() is a method which returns an array of all the collections in the database.
Likewise, find() can be considered a method of a collection which returns all the documents in that collection. So db.hits.find() will return all the documents in the hits collection.
We probably want to retrieve only those documents which match particular criteria. For example, we might want to search for all songs by a particular artist. Here is how to do this. Enter the following at the mongo prompt:
db.hits.find({artist : "Oasis"})
This will return all songs by Oasis. Note how we use syntax similar to a
JavaScript object as a parameter to find() to describe the search.
Next enter the following:
db.hits.find({artist:"Oasis", title:"Some Might Say"})
This will return Some Might Say by Oasis. Note how we search on artist AND
song by combining the two criteria in a single JavaScript-like "object".
You can also do "less than" and "greater than" queries. Here is an example:
db.hits.find({year: { $lt:1962}});
This will find all songs released before 1962. Note how the
query is now a further object ({$lt:1962}) rather than a
single value such as "Oasis". "$lt" is an operator which means "less than".
Note how MongoDB makes use of the syntax of JavaScript objects to perform more
complex queries. Another example:
db.hits.find({year: { $lt: 1990, $gt: 1985}})
will find songs released from 1986-1989 inclusive. The similar
db.hits.find({year: { $lte: 1990, $gte: 1985}})
will find songs released from 1985-1990 inclusive ($lte meaning "less than
or equal to" and $gte meaning "greater than or equal to").
Again, note the similar approach to the simple searches above: we combine
conditions by putting them in a single JavaScript-like "object".
"or" queries are a bit more complex than "and". The simplest way to do them, when you are searching for one of a range of discrete values for a particular field, is to use the $in operator, e.g:
db.hits.find({artist: {$in: ["Oasis", "Madonna"]}});
will find all hits by either Oasis or Madonna. For more complex queries
you have to use the "$or" operator, e.g:
db.hits.find({ $or: [ {artist: "Madonna"}, {title:"Some Might Say"} ] } )
This will find documents where EITHER the artist is Madonna OR the title is Some Might Say.
Note how both $in and $or take an array: for $in this is an array of
possible values whereas for $or it is an array of conditions, at least one of which
must be matched.
Pattern matching can also be done. This also follows JavaScript syntax: in JavaScript pattern matching (using regular expressions) can be done using the slash /. So for example:
db.hits.find({title: /The/})
will find all songs with "The" in the name, while
db.hits.find({title: /^The/})
will find all songs beginning with "The" (remember from the REST topic that ^ indicates the start
of a string in regular expressions).
We will now look at how to update a MongoDB database, for example add new documents or modify existing ones. To do this, you should first set up your own database, so that any changes made by you don't interfere with other students. Quit mongo by entering
exitto return you to the Linux shell prompt. You now need to import the database. Enter:
mongoimport --db yourusername --collection hits --drop --file ~vmadmin/mongosongsreplacing "yourusername" by your actual username. This imports the hits collection into your own database.
Now re-enter the mongo client:
mongoand enter:
use yourusername(again replace "yourusername" by your actual username). This will switch to your own database from the default one, "test".
To test it worked, enter:
db.hits.find()and all the hits should appear.
Inserting new documents is quite intuitive in MongoDB. You use the insert() method, which takes a JavaScript-like "object" representing the new song. For example:
db.hits.insert({artist:"Oasis", title: "Wonderwall", year:1995, downloads:100, _id:2001});
Hopefully this should be obvious. The only thing that needs commenting on is the "_id" field. This
is equivalent to the primary key in a relational database and must have a unique value. By
default, it is given an ObjectId (an object containing a
large unique number)
by MongoDB if you do not specify an explicit value for it.
See the documentation.
Also note that unlike a relational database, the fields can be completely freeform. For example, you could do
db.hits.insert({author:"Owen Jones"});
and even though the document has nothing to do with music, it would be accepted.
It is important that any numeric values are inserted WITHOUT quotes. If quotes are used, they will be stored as a string which means that you will be unable to perform arithmetic operations on them, such as increase them by one or use less-than and greater-than operators. So for example it should be:
db.hits.insert({title:"Wonderwall", downloads:100})
and not
db.hits.insert({title:"Wonderwall", downloads:"100"})
Add a song of your own choice to the database, then search for it to prove that it has been added.
You can also update documents with update(). Try this first (the find commands are to illustrate how the update happens):
db.hits.find({_id:1})
db.hits.update({_id: 1}, {downloads: 200})
db.hits.find({_id:1})
What do you see? Is this what you want?
Now try this:
db.hits.find({_id:2})
db.hits.update({_id: 2}, {$set:{downloads: 200}})
db.hits.find({_id:2})
This will update the song with the _id of 2 so that its downloads is now 200.
Note:
db.hits.update({_id: 2}, {$set:{downloads: 200, price:0.79}})
This will reset the downloads and the price of the song with the _id of 2.
db.hits.update({title: "Wonderwall", artist:"Oasis"}, {$set:{downloads: 200}})
This will set the downloads of Wonderwall by Oasis to 200.
A possible mistake, illustrated above, is to leave out the $set instruction when updating documents. For example, this is valid syntax but it will have an unexpected effect:
db.hits.update({_id: 1}, {downloads: 200})
Without the $set instruction, it will reset the details of the song with the _id of 1 so
that it has a downloads of 200 and no other fields! In other words, update() without $set
will clear out all the existing fields of a document!
By default update() will only update the first document. To tell it to match more than one, you have to pass 'true' as the fourth parameter e.g this one updates the downloads of all Beatles songs:
db.hits.update({artist: "The Beatles"}, {$set:{downloads : 1000}}, false, true)
(The third parameter, false here,
is 'upsert', which if true will insert the document if it
does not exist, i.e. an 'upsert' operation)
Another useful instruction in update queries is $inc. This increases numerical fields by the specified amount. For example:
db.hits.update({_id:2}, {$inc:{downloads:1}})
will increase the downloads by 1, while
db.hits.update({_id:2}, {$inc:{"downloads":-1}})
will decrease it by 1. (Note that there is no $dec instruction!)
You can also delete documents. This is done with remove, e.g:
db.hits.remove({_id:2});
will remove the song with the _id of 2, or
db.hits.remove({artist:"Woop", title:"Woop"});
will remove Woop by Woop from the hits collection.
Here is some data about students:
| name | course | mark |
|---|---|---|
| Tom | Software Engineering | 90 |
| James | Medicine | 5 |
| Laura | Computing | 60 |
| Nigel | Computing | 30 |
| Sarah | Web Development | 80 |
Note that you do NOT have to create a collection (using the equivalent of CREATE TABLE): you can just start adding the documents to the collection straight away, and if the collection does not exist, it will be created.