Over a million developers have joined DZone.

Moving From Oracle to Couchbase

A from-the-beginning walk through of migrating an example Oracle database over to Couchbase. Read on for more info.

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

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 an 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 modeing 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:


    "type": "customer",
    "first_name": "Nic",
    "last_name": "Raboy"


    "type": "customer_address",
    "customer_id": "c::1",
    "city": "San Francisco",
    "state": "California"

Assume the above documents are modelled similarly to their RDBMS equivalent. 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"

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:

    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:

    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');

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"});

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:

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");

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();

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.


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 an 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.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.


Published at DZone with permission of Nic Raboy, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}