Oracle was the first database I developed with, so I know that thinking about switching to something like NoSQL and leaving behind the relational model can seem like a scary thing. The thing is, it really wasn’t scary when I finally opted to make the switch to the NoSQL document model that Couchbase offers. This is because unlike other NoSQL databases, Couchbase offers a SQL-like language that would seem very familiar to an RDBMS user.

To make the transition easier, we’re going to walk through a scenario where you’re using an Oracle RDBMS and would like to transition away to Couchbase.

Key Differences Between Oracle and Couchbase

Coming from Oracle, I take it you already know that it is a relational database that consists of tables, rows, and columns. Pretty standard when it comes to any relational database. This is not the case with a NoSQL document database like Couchbase. Instead you’re working with JSON objects and arrays that have no structure and pretty much no limitations.

Although data modeling is, in my opinion, the largest difference, it isn’t the only one. However, let’s start there.

The Relational Database Data Model

To keep things simple and easy to follow in this article, let’s assume we have the following tables:

customer id: numeric primary key first_name: varchar last_name: varchar



customer_history id: numeric primary key product_id: numeric foreign key quantity: numeric customer_address id: numeric primary key customer_id: numeric foreign key city: varchar state: varchar



product id: numeric primary key name: varchar description: varchar



product_review id: numeric primary key product_id: numeric foreign key customer_id: numeric foreign key review: varchar



The above tables and columns are not the most complex, but they prove the relational model. They are all connected by the use of primary and foreign key relationships.

Options for a NoSQL Data Model

Because NoSQL is schema-less, there are multiple ways to model the data we just saw in Oracle.

Referring Documents

Referring documents will probably seem most familiar to you in terms of relational data. In an RDBMS like Oracle, you are referring to other rows of data through primary and foreign keys. There is no concept of a primary or foreign key in NoSQL, but that doesn’t mean you can’t rig together the same kind of relationship.

For example, take the following NoSQL documents:

c::1

{ "type": "customer", "first_name": "Nic", "last_name": "Raboy" } 1 2 3 4 5 6 7 { "type" : "customer" , "first_name" : "Nic" , "last_name" : "Raboy" }

ca:1

{ "type": "customer_address", "customer_id": "c::1", "city": "San Francisco", "state": "California" } 1 2 3 4 5 6 7 8 { "type" : "customer_address" , "customer_id" : "c::1" , "city" : "San Francisco" , "state" : "California" }

Assume the above documents are modeled similarly to their RDBMS equivilent. c::1 is just some id value I made up for the customer document and ca:1 is an id I made up for the customer_address document.

Now although we won’t query them yet, we can think of these documents as each being the equivalent of a single row in a relational database. For example one row of the customer Oracle table would be one document in Couchbase.

Very similar, correct?

Embedding Documents

This is where things can become very different coming from Oracle. Being that JSON is complex data, we can have arrays within our documents. So what if we wanted to keep all like data together?

{ "type": "customer", "first_name": "Nic", "last_name": "Raboy", "addresses": [ { "city": "San Francisco", "state": "California" }, { "city": "Mountain View", "state": "California" } ] } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 { "type" : "customer" , "first_name" : "Nic" , "last_name" : "Raboy" , "addresses" : [ { "city" : "San Francisco" , "state" : "California" } , { "city" : "Mountain View" , "state" : "California" } ] }

What do you think of that? With the above, instead of one address per document, we are now storing all addresses for any particular customer, with the customer data.

You might be wondering what happens if you have very complex relationships in your Oracle data that, when transposed to Couchbase, would result in the same data being embedded in more than one Couchbase document. This could happen, but it isn’t a bad thing. You don’t need normalized data in a NoSQL database such as Couchbase. However, if you’re really concerned, why not mix both approaches? Keep data such as customer_history together without relationships and refer to others that might change more frequently.

Query Differences Between Oracle and Couchbase

Oracle SQL vs Couchbase N1QL

It’s no secret that Oracle uses its own flavor of traditional SQL to query data in the database, but it is SQL. For example, if you wanted to return all product reviews and expand the relational tables around them, you would do a query like this:

SELECT c.firstname, c.lastname, p.name, r.review FROM review r JOIN customer c ON r.customer_id = c.id JOIN product p ON r.product_id = p.id 1 2 3 4 5 6 7 SELECT c . firstname , c . lastname , p . name , r . review FROM review r JOIN customer c ON r . customer_id = c . id JOIN product p ON r . product_id = p . id

What if I told you that you could do almost the same thing with Couchbase NoSQL data? Take the following Couchbase N1QL query:

SELECT c.firstname, c.lastname, p.name, r.review FROM `bucket-name` r JOIN `bucket-name` c ON KEYS r.customer_id JOIN `bucket-name` p ON KEYS r.product_id 1 2 3 4 5 6 7 SELECT c . firstname , c . lastname , p . name , r . review FROM ` bucket - name ` r JOIN ` bucket - name ` c ON KEYS r . customer_id JOIN ` bucket - name ` p ON KEYS r . product_id

Not too different right? You might notice that we’re using bucket-name three times. This is because there are no tables in NoSQL and all the different documents and document types will exist in the same bucket. The document key is the value that we join on.

Maybe you want to insert new data into the Oracle customer table. In Oracle, you might do something like this:

INSERT INTO customer (id, first_name, last_name) VALUES (1, 'Arun', 'Gupta'); 1 2 3 4 INSERT INTO customer ( id , first_name , last_name ) VALUES ( 1 , 'Arun' , 'Gupta' ) ;

If you wanted to insert data in Couchbase you can do the following:

INSERT INTO `bucket-name` (KEY, VALUE) VALUES (1, {"first_name": "Arun", "last_name": "Gupta"}); 1 2 3 4 INSERT INTO ` bucket - name ` ( KEY , VALUE ) VALUES ( 1 , { "first_name" : "Arun" , "last_name" : "Gupta" } ) ;

Development Differences Between Oracle and Couchbase

From the developer perspective, many who use Oracle as their database tend to use Java. Oracle isn’t restricted to just Java, but since Java is Oracle, it often makes sense. This is why the next few examples will be based around Java specifically.

The Oracle JDBC Driver

In a Java application, if you wanted to connect to an Oracle database you’d use the Java Database Connector (JDBC) driver. With the driver included in your project, either through tools like Maven or manually, you can load it and start querying for data.

An example of this might look like the following:

Class.forName("oracle.jdbc.driver.OracleDriver"); Properties info = new Properties(); info.put("user", "nraboy"); info.put("password", "password"); Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@//HOST:PORT/DATABASE", info); Statement stmt = connection.getConnection().createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM customer"); while(rs.next()) { // rs.getString("first_name"); } stmt.close(); 1 2 3 4 5 6 7 8 9 10 11 12 13 Class . forName ( "oracle.jdbc.driver.OracleDriver" ) ; Properties info = new Properties ( ) ; info . put ( "user" , "nraboy" ) ; info . put ( "password" , "password" ) ; Connection connection = DriverManager . getConnection ( "jdbc:oracle:thin:@//HOST:PORT/DATABASE" , info ) ; Statement stmt = connection . getConnection ( ) . createStatement ( ) ; ResultSet rs = stmt . executeQuery ( "SELECT * FROM customer" ) ; while ( rs . next ( ) ) { // rs.getString("first_name"); } stmt . close ( ) ;

The Couchbase Java SDK

To connect to Couchbase via a Java application you would use the Couchbase Java SDK rather than a JDBC driver, even though one does exist. The reason we would use the SDK is because things become incredibly easy with it.

For example, with the Couchbase SDK, the same kind of operation as Oracle might look like the following:

CouchbaseCluster.create(HOST).openBucket(BUCKET, PASSWORD); String query = "SELECT * FROM `bucket-name-here`"; N1qlQueryResult queryResult = bucket.query(query); for (N1qlQueryRow row : queryResult) { // row.value().toMap(); } 1 2 3 4 5 6 7 8 CouchbaseCluster . create ( HOST ) . openBucket ( BUCKET , PASSWORD ) ; String query = "SELECT * FROM `bucket-name-here`" ; N1qlQueryResult queryResult = bucket . query ( query ) ; for ( N1qlQueryRow row : queryResult ) { // row.value().toMap(); }

The above assumes, of course, that you downloaded the Couchbase Java SDK or used Maven to obtain it.

Tool Differences

When using Oracle you have many tools that you can use. For example, if you want to execute queries against the database you could use the command line tool SQLPlus. You still have the ability to use comparable tools when making the switch to Couchbase. If you’re looking for a command line tool, you can use CBQ to query your data. If you’re a power user of Oracle’s SQL Developer, don’t worry because Couchbase has its Query Workbench in the works.

Data Migration Tools

When it comes to your data, you might be wondering how you might get your data out of Oracle and into Couchbase as quickly as possible. Manuel Hurtado wrote an excellent blog article for moving data from Oracle to Couchbase.

In short, Manuel’s post walks through using a Java utility called oracle2couchbase. This tool will export rows of a table into JSON documents.

Conclusion

Even though Oracle and Couchbase are two very different database platforms, there are enough similarities to where a switch wouldn’t be so difficult to do. The relational data model can still be preserved to an extent using referred documents and these documents can still be queried using a SQL-like language similar enough to Oracle SQL that would make you feel right at home.

If you’re an Oracle user, Couchbase should not be dismissed. The way data exists now is different than it did twenty years ago. Having the flexibility of NoSQL is great for the future.