Learning basic MongoDB by installing and using CRUD

Today, we are going to learn MongoDB, including understand what is MongoDB, installation and doing CRUD operation. We start with the basic question.

what is MongoDB?

MongoDB (from "humongous") is a cross-platform document-oriented database. Classified as a NoSQL database, MongoDB eschews the traditional table-based relational database structure in favor of JSON-like documents with dynamic schemas (MongoDB calls the format BSON), making the integration of data in certain types of applications easier and faster.

With that said, let's move on to install MongoDB. There are many ways to install MongoDB but with this article, the one I'm chosen is to install MongoDB using deb package built by MongoDB. Even though MongoDB comes with ubuntu however the version in the repository is just too old. Current in the ubuntu repository, mongodb version is 1:2.4.9-1ubuntu2 and meanwhile official production release version is 2.6.1.

The instructions below are from http://docs.mongodb.org/manual/tutorial/install-mongodb-on-ubuntu/ . But I summarize into one liner. You will add a new MongoDB repository from official database site and install latest version.

sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 7F0CEB10 && echo 'deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen' | sudo tee /etc/apt/sources.list.d/mongodb.list && sudo apt-get update && sudo apt-get install mongodb-org

If everything goes well, you should get a similar output installation MongoDB such as below:

jason@localhost:~$ sudo apt-get install mongodb-org
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  jhead libcec2 libgdata-google1.2-1 libgdata1.2-1 libjdependency-java liblockdev1 libmaven-archiver-java libmaven-clean-plugin-java
  libmaven-compiler-plugin-java libmaven-dependency-tree-java libmaven-filtering-java libmaven-install-plugin-java libmaven-jar-plugin-java
  libmaven-resources-plugin-java libmaven-shade-plugin-java libphp-adodb libpigment0.3-11 libplexus-compiler-java libplexus-digest-java oxideqt-codecs-extra
  php-auth-sasl php-cache php-date php-file php-http-request php-log php-mail php-mail-mime php-mdb2 php-mdb2-driver-mysql php-net-dime php-net-ftp
  php-net-smtp php-net-socket php-net-url php-services-weather php-soap php-xml-parser php-xml-serializer printer-driver-c2esp printer-driver-min12xxw
  printer-driver-pnm2ppa printer-driver-pxljr python-axiom python-coherence python-configobj python-epsilon python-gpod python-louie python-nevow python-pgm
  python-pyasn1 python-storm python-tagpy python-twill python-twisted-conch python-twisted-web2 qtdeclarative5-window-plugin tinymce2 xbmc-pvr-argustv
  xbmc-pvr-dvbviewer xbmc-pvr-mediaportal-tvserver xbmc-pvr-mythtv-cmyth xbmc-pvr-nextpvr xbmc-pvr-njoy xbmc-pvr-tvheadend-hts xbmc-pvr-vdr-vnsi
  xbmc-pvr-vuplus xdg-user-dirs-gtk
Use 'apt-get autoremove' to remove them.
The following extra packages will be installed:
  mongodb-org-mongos mongodb-org-server mongodb-org-shell mongodb-org-tools
The following NEW packages will be installed:
  mongodb-org mongodb-org-mongos mongodb-org-server mongodb-org-shell mongodb-org-tools
0 upgraded, 5 newly installed, 0 to remove and 51 not upgraded.
Need to get 113 MB of archives.
After this operation, 284 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 http://downloads-distro.mongodb.org/repo/ubuntu-upstart/ dist/10gen mongodb-org-shell i386 2.6.1 [4,389 kB]
Get:2 http://downloads-distro.mongodb.org/repo/ubuntu-upstart/ dist/10gen mongodb-org-server i386 2.6.1 [9,308 kB]
Get:3 http://downloads-distro.mongodb.org/repo/ubuntu-upstart/ dist/10gen mongodb-org-mongos i386 2.6.1 [7,045 kB]
Get:4 http://downloads-distro.mongodb.org/repo/ubuntu-upstart/ dist/10gen mongodb-org-tools i386 2.6.1 [92.3 MB]
Get:5 http://downloads-distro.mongodb.org/repo/ubuntu-upstart/ dist/10gen mongodb-org i386 2.6.1 [3,652 B]
Fetched 113 MB in 3min 25s (549 kB/s)
Selecting previously unselected package mongodb-org-shell.
(Reading database ... 564794 files and directories currently installed.)
Preparing to unpack .../mongodb-org-shell_2.6.1_i386.deb ...
Unpacking mongodb-org-shell (2.6.1) ...
Selecting previously unselected package mongodb-org-server.
Preparing to unpack .../mongodb-org-server_2.6.1_i386.deb ...
Unpacking mongodb-org-server (2.6.1) ...
Selecting previously unselected package mongodb-org-mongos.
Preparing to unpack .../mongodb-org-mongos_2.6.1_i386.deb ...
Unpacking mongodb-org-mongos (2.6.1) ...
Selecting previously unselected package mongodb-org-tools.
Preparing to unpack .../mongodb-org-tools_2.6.1_i386.deb ...
Unpacking mongodb-org-tools (2.6.1) ...
Selecting previously unselected package mongodb-org.
Preparing to unpack .../mongodb-org_2.6.1_i386.deb ...
Unpacking mongodb-org (2.6.1) ...
Processing triggers for man-db (2.6.7.1-1) ...
Processing triggers for ureadahead (0.100.0-16) ...
Setting up mongodb-org-shell (2.6.1) ...
Setting up mongodb-org-server (2.6.1) ...
Adding system user `mongodb' (UID 143) ...
Adding new user `mongodb' (UID 143) with group `nogroup' ...
Not creating home directory `/home/mongodb'.
Adding group `mongodb' (GID 155) ...
Done.
Adding user `mongodb' to group `mongodb' ...
Adding user mongodb to group mongodb
Done.
mongod start/running, process 22386
Setting up mongodb-org-mongos (2.6.1) ...
Setting up mongodb-org-tools (2.6.1) ...
Processing triggers for ureadahead (0.100.0-16) ...
Setting up mongodb-org (2.6.1) ...

Looks like installation processed is done and fine. Even it is already started. So now let's play using mongo db command line.

jason@localhost:~$ mongo
MongoDB shell version: 2.6.1
connecting to: test
Welcome to the MongoDB shell.
For interactive help, type "help".
For more comprehensive documentation, see
	http://docs.mongodb.org/
Questions? Try the support group
	http://groups.google.com/group/mongodb-user
Server has startup warnings:
2014-06-02T22:29:43.933+0800 [initandlisten]
2014-06-02T22:29:43.933+0800 [initandlisten] ** NOTE: This is a 32 bit MongoDB binary.
2014-06-02T22:29:43.933+0800 [initandlisten] **       32 bit builds are limited to less than 2GB of data (or less with --journal).
2014-06-02T22:29:43.933+0800 [initandlisten] **       Note that journaling defaults to off for 32 bit and is currently off.
2014-06-02T22:29:43.933+0800 [initandlisten] **       See http://dochub.mongodb.org/core/32bit
2014-06-02T22:29:43.934+0800 [initandlisten]
>

As you can see, I'm running 32bit cpu, but it should work fine for 64bit cpu and the rest of this article. So everything has been smooth sailing so far, we will start to create, read, update and delete operation.

• create

To create or insert a document, it is as easy as

db.inventory.insert( { _id: 10, type: "misc", item: "card", qty: 15 } )

More insert example

db.inventory.update(
                     { type: "book", item : "journal" },
                     { $set : { qty: 10 } },
                     { upsert : true }
                   )

Interesting insert using save

db.inventory.save( { type: "book", item: "notebook", qty: 40 } )

• read

to read or query document, it is as easy as

db.inventory.update(
                     { type: "book", item : "journal" },
                     { $set : { qty: 10 } },
                     { upsert : true }
                   )

read more example here.

• update

see create above for example.

• delete

to remove all documents,

db.inventory.remove({})

That's it for this lengthy introduction.

Introduction to CRUD on cql 3.0 data type

In a previous article, we covered a basic data definition language,  and in this article, we are going to cover data manipulation language. With cql3, composite data type is pretty interesting compare to sql. Official documentation available here.

We covered all data type in cql 3.0 except counter and now we will create all the available data types that can coexists within a table. Let's do it.

CREATE TABLE dataType (
  id uuid,
  name ascii,
  amount bigint,
  binary blob,
  isSingle boolean,
  lamp decimal,
  salary double,
  works float,
  ip inet,
  car int,
  email set<text>,
  kidsAge map<text,int>,
  places list<text>,
  description text,
  lastUpdate timestamp,
  myTimeUUID timeuuid,
  longDescription varchar,
  spending varint,
  PRIMARY KEY (id)
);

cqlsh:jw_schema1> desc table datatype;

CREATE TABLE datatype (
  id uuid,
  amount bigint,
  binary blob,
  car int,
  description text,
  email set<text>,
  ip inet,
  issingle boolean,
  kidsage map<text, int>,
  lamp decimal,
  lastupdate timestamp,
  longdescription text,
  mytimeuuid timeuuid,
  name ascii,
  places list<text>,
  salary double,
  spending varint,
  works float,
  PRIMARY KEY (id)
) WITH
  bloom_filter_fp_chance=0.010000 AND
  caching='KEYS_ONLY' AND
  comment='' AND
  dclocal_read_repair_chance=0.000000 AND
  gc_grace_seconds=864000 AND
  index_interval=128 AND
  read_repair_chance=0.100000 AND
  replicate_on_write='true' AND
  populate_io_cache_on_flush='false' AND
  default_time_to_live=0 AND
  speculative_retry='99.0PERCENTILE' AND
  memtable_flush_period_in_ms=0 AND
  compaction={'class': 'SizeTieredCompactionStrategy'} AND
  compression={'sstable_compression': 'LZ4Compressor'};

looks good, table created and let's continue by insert data into this table!

insert into datatype (id, name, amount, binary, issingle, lamp, salary, works, ip, car, email, kidsage, places, description, lastUpdate, myTimeUUID, longDescription, spending) values (62c36092-82a1-3a00-93d1-46196ee77204, 'jason wee', 123, 0xff, false, 10, 100000, 1, '192.168.0.1', 3, {'a@b.com', 'c@d.com'}, {'juniorA':1, 'juniorB':2}, ['kuala lumpur', 'petaling jaya', 'kepong'], 'hello world', '2014-04-15 00:00:00', maxTimeuuid('2014-04-15 00:05+0000'), 'this is a longer hello world', 123);

cqlsh:jw_schema1> select * from datatype;

 id                                   | amount | binary | car | description | email                  | ip          | issingle | kidsage                      | lamp | lastupdate               | longdescription              | mytimeuuid                           | name      | places                                      | salary | spending | works
--------------------------------------+--------+--------+-----+-------------+------------------------+-------------+----------+------------------------------+------+--------------------------+------------------------------+--------------------------------------+-----------+---------------------------------------------+--------+----------+-------
 62c36092-82a1-3a00-93d1-46196ee77204 |    123 |   0xff |   3 | hello world | {'a@b.com', 'c@d.com'} | 192.168.0.1 |    False | {'juniorA': 1, 'juniorB': 2} |   10 | 2014-04-15 00:00:00+0800 | this is a longer hello world | 95fc050f-c431-11e3-7f7f-7f7f7f7f7f7f | jason wee | ['kuala lumpur', 'petaling jaya', 'kepong'] |  1e+05 |      123 |     1

(1 rows)

Goodies, all data were inserted. Let's try update, we start by single update to one column.

cqlsh:jw_schema1> update datatype set amount = 456 where id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select amount from datatype where id = 62c36092-82a1-3a00-93d1-46196ee77204;

 amount
--------
    456

(1 rows)

Looks good too! Now we will update three fields.

cqlsh:jw_schema1> update datatype set binary = 0x68656c6c6f20776f726c64, car = 6, description = 'changed description' where id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select binary, car, description from datatype where id = 62c36092-82a1-3a00-93d1-46196ee77204;

 binary                   | car | description
--------------------------+-----+---------------------
 0x68656c6c6f20776f726c64 |   6 | changed description

(1 rows)

cqlsh:jw_schema1>

Looks good! The binary data type always prefix with 0x. It is hex representation of hello world. Let's now change the composite data type.

cqlsh:jw_schema1> update datatype set email = {'e@f.com'} where id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select email from datatype;

 email
-------------
 {'e@f.com'}

(1 rows)

Hmm... email field values get overridden. So how do we append it? concat them ! :-)

cqlsh:jw_schema1> update datatype set email = email + {'a@b.com', 'c@d.com'} where id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select email from datatype;

 email
-----------------------------------
 {'a@b.com', 'c@d.com', 'e@f.com'}

(1 rows)

Move on, update data type boolean and IP number.

cqlsh:jw_schema1> update datatype set ip = 'a.b.c.d', issingle = True where id = 62c36092-82a1-3a00-93d1-46196ee77204;
Bad Request: unable to make inetaddress from 'a.b.c.d'
cqlsh:jw_schema1> update datatype set ip = '255.255.255.255', issingle = True where id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> update datatype set ip = '255.255.255.255', issingle = Trued where id = 62c36092-82a1-3a00-93d1-46196ee77204;
Bad Request: line 1:61 no viable alternative at input 'where'
cqlsh:jw_schema1> update datatype set ip = '255.255.255.255', issingle = True where id = 62c36092-82a1-3a00-93d1-46196ee77204;

cqlsh:jw_schema1> select ip,issingle from datatype;

 ip              | issingle
-----------------+----------
 255.255.255.255 |     True

(1 rows)

Simple checking on IP number and boolean data type is enforce. Let's change map now.

cqlsh:jw_schema1> update datatype set kidsage = {'juniorC':3} where  id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select kidsage from datatype;

 kidsage
----------------
 {'juniorC': 3}

(1 rows)

cqlsh:jw_schema1> update datatype set kidsage = kidsage + {'juniorA':1, 'juniorB':2} where  id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select kidsage from datatype;

 kidsage
--------------------------------------------
 {'juniorA': 1, 'juniorB': 2, 'juniorC': 3}

(1 rows)

Exactly like set behavior, if you need to append the data, you need to concat them using plus sign.

cqlsh:jw_schema1> update datatype set lamp = 12.34, lastupdate = '2014-04-16 20:00', longdescription = 'this is a long long long hello world', mytimeuuid = maxTimeuuid('2014-04-16'), name = 'john smith' where  id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select lamp, lastupdate, longdescription, mytimeuuid, name from datatype;

 lamp  | lastupdate               | longdescription                      | mytimeuuid                           | name
-------+--------------------------+--------------------------------------+--------------------------------------+------------
 12.34 | 2014-04-16 20:00:00+0800 | this is a long long long hello world | ff72270f-c4b6-11e3-7f7f-7f7f7f7f7f7f | john smith

(1 rows)

cqlsh:jw_schema1>

Everything looks good, timestamp provided by hour and longer text and time uuid can accept only date, everything seem cool.

cqlsh:jw_schema1> update datatype set places = places + ['cheras'], salary = 985621.35, spending = 12355, works = 89.36 where  id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select places, salary, spending, works from datatype;

 places                                                | salary     | spending | works
-------------------------------------------------------+------------+----------+-------
 ['kuala lumpur', 'petaling jaya', 'kepong', 'cheras'] | 9.8562e+05 |    12355 | 89.36

(1 rows)

cqlsh:jw_schema1>

Okay, we pretty all cover update all the data type. Let's remove this one row.

cqlsh:jw_schema1> delete amount,binary,car,description,email,ip,issingle,kidsage,lamp,lastupdate,longdescription,mytimeuuid from datatype where id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select * from datatype;

 id                                   | amount | binary | car  | description | email | ip   | issingle | kidsage | lamp | lastupdate | longdescription | mytimeuuid | name       | places                                                | salary     | spending | works
--------------------------------------+--------+--------+------+-------------+-------+------+----------+---------+------+------------+-----------------+------------+------------+-------------------------------------------------------+------------+----------+-------
 62c36092-82a1-3a00-93d1-46196ee77204 |   null |   null | null |        null |  null | null |     null |    null | null |       null |            null |       null | john smith | ['kuala lumpur', 'petaling jaya', 'kepong', 'cheras'] | 9.8562e+05 |    12355 | 89.36

(1 rows)

Pretty interesting, we can delete columns within a row. The data is set to null.

cqlsh:jw_schema1> delete from datatype where id = 62c36092-82a1-3a00-93d1-46196ee77204;
cqlsh:jw_schema1> select * from datatype;

(0 rows)

Now we have deleted everything.

• looks like it is case insensitive, that, is when we created the table name, dataType it is stored as datatype.


• the composite datatype is definitely nice to have as we don't have to link a few tables.


• we can also delete a few columns or we can delete the entire row.