I think it is safe to assume that every developer or system administrator has touched MySQL at some point in time. It was often the
rite of passage for any new developer a few years ago when langauges like PHP were flourishing. Now you might be in a situation where
things have changed and now your database might need a change. More specifically a change to schema-less NoSQL database.

At first glance, if you’re skimming over the differences, it might seem like a scary task. What do I do about all my tables and
constraints? How do I query my data? What development languages am I allowed to use?

We’re going to slow it down and walk through a scenario where you’re using a MySQL RDBMS and would like to transition away to Couchbase.

Key Differences Between MySQL and Couchbase

Coming from MySQL, 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

For simplicity, let’s stick to a small data model. It can always be adjusted to accommodate a more complex scenario. Our data model
will be as follows:

customer

  • id: numeric primary key
  • firstname: varchar
  • lastname: varchar

customer_address

  • id: numeric primary key
  • city: varchar
  • state: varchar
  • zip: varchar
  • customer_id: numeric foreign key

The above two tables and their columns are not the most complex, but they still prove to be relational through the primary and
foreign key relationships.

Options for a NoSQL Data Model

NoSQL documents are a little different because in this case they are schema-less. This means that there are multiple options when it
comes to modeling the data we saw for MySQL.

Referring Documents

Referring documents will probably seem most familiar to you in terms of relational data. In an RDBMS like MySQL, 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

ca::1

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 MySQL table would be one document in
Couchbase.

Very similar, correct?

Embedding Documents

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

In the above document we are storing all addresses for a particular customer in an array within the document. This is instead of
creating a new document for every address and referencing the customer document.

You might be wondering what happens if you have very complex relationships in your MySQL 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 say customer_history together without relationships and refer to
others that might change more frequently.

Query Differences Between MySQL and Couchbase

Couchbase N1QL vs MySQL SQL

MySQL, just like every relational database platform, uses its own flavor of SQL. Assuming we’re using the schema defined earlier
for MySQL, we can query for customers and their addresses like so:

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

Not too different right? You might notice that we’re using bucket-name two 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.

Now let’s say you want to insert new data into the MySQL customer table. In MySQL, you might do something like this:

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

More information on Couchbase N1QL can be seen here.

Development Differences Between MySQL and Couchbase

In my previous article regarding Oracle to Couchbase, I wrote about the development differences in the perspective of Java. For
consistency, I’ll use Java in the following examples. MySQL is certainly not restricted to Java and neither is Couchbase.

The MySQL JDBC Driver

In a Java application, if you wanted to connect to a MySQL 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:

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:

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

Tool Differences

When using MySQL you have many tools that you can use. For example, if you want to execute queries against the database you
could use the MySQL CLI. 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
MySQL Workbench, don’t worry because Couchbase has its Query Workbench as
of Couchbase Server 4.5.

Data Migration

When it comes to your data, you might be wondering how you might get your data out of MySQL and into Couchbase as quickly as possible.
Laurent Doguin wrote an excellent migration tool for moving data from MySQL to Couchbase.

Essentially what this tool does is it will connect to your MySQL database via Java and the JDBC driver. Each row of every table will be converted into a unique JSON document.

Although there will be no database constraints in the new JSON documents, the keys will still exist and be query-able via N1QL.

Conclusion

MySQL and Couchbase are two very different database technologies, but how you use them doesn’t have to be. The relational data model
can still be preserved to an extent in a NoSQL document database. Any data stored can then be queried in Couchbase just as you would
in a MySQL database using SQL queries. The major difference between the two is that you’re not limited to what data you can store
and how you can store it.

If you’re an Oracle database user, this similar post I wrote on going from Oracle to Couchbase.

Author

Posted by Nic Raboy, Developer Advocate, Couchbase

Nic Raboy is an advocate of modern web and mobile development technologies. He has experience in Java, JavaScript, Golang and a variety of frameworks such as Angular, NativeScript, and Apache Cordova. Nic writes about his development experiences related to making web and mobile development easier to understand.

3 Comments

  1. hi there, what index should be created for your join query example above?

    1. OK, got it, PRIMARY INDEX is needed in this case

Leave a reply