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
\nrite of passage for any new developer a few years ago when langauges like PHP were flourishing. Now you might be in a situation where
\nthings have changed and now your database might need a change. More specifically a change to schema-less NoSQL database.<\/p>\n
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
\nconstraints? How do I query my data? What development languages am I allowed to use?<\/p>\n
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.<\/p>\n
Coming from MySQL, I take it you already know that it is a relational database that consists of tables, rows, and columns.
\nPretty standard when it comes to any relational database. This is not the case with a NoSQL document database like Couchbase.
\nInstead you’re working with JSON objects and arrays that have no structure and pretty much no limitations.<\/p>\n
Although data modeling is, in my opinion, the largest difference, it isn’t the only one. However, let’s start there.<\/p>\n
For simplicity, let’s stick to a small data model. It can always be adjusted to accommodate a more complex scenario. Our data model
\nwill be as follows:<\/p>\n
customer<\/strong><\/p>\n customer_address<\/strong><\/p>\n The above two tables and their columns are not the most complex, but they still prove to be relational through the primary and NoSQL documents are a little different because in this case they are schema-less. This means that there are multiple options when it Referring documents will probably seem most familiar to you in terms of relational data. In an RDBMS like MySQL, you are referring For example, take the following NoSQL documents:<\/p>\n c::1<\/strong><\/p>\n ca::1<\/strong><\/p>\n Assume the above documents are modeled similarly to their RDBMS equivilent. c::1<\/strong> is just some id value I made up for the 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 Very similar, correct?<\/p>\n This is where things can become very different coming from MySQL. Being that JSON is complex data, we can have arrays within our In the above document we are storing all addresses for a particular customer in an array within the document. This is instead of You might be wondering what happens if you have very complex relationships in your MySQL data that, when transposed to MySQL, just like every relational database platform, uses its own flavor of SQL. Assuming we’re using the schema defined earlier Now what if I told you that you could do almost the same thing with Couchbase NoSQL data? Take the following Couchbase N1QL query:<\/p>\n Not too different right? You might notice that we’re using `bucket-name`<\/strong> two times. This is because there are no Now let’s say you want to insert new data into the MySQL customer<\/strong> table. In MySQL, you might do something like this:<\/p>\n If you wanted to insert data in Couchbase you can do the following:<\/p>\n More information on Couchbase N1QL can be seen here<\/a>.<\/p>\n In my previous article regarding Oracle to Couchbase<\/a>, I wrote about the development differences in the perspective of Java. For In a Java application, if you wanted to connect to a MySQL database you’d use the Java Database Connector (JDBC) driver. An example of this might look like the following:<\/p>\n To connect to Couchbase via a Java application you would use the Couchbase Java SDK rather than a JDBC driver, even though For example, with the Couchbase SDK, the same kind of operation as Oracle might look like the following:<\/p>\n The above assumes, of course, that you downloaded the Couchbase Java SDK or used Maven to obtain it.<\/p>\n When using MySQL you have many tools that you can use. For example, if you want to execute queries against the database you 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. 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.<\/p>\n Although there will be no database constraints in the new JSON documents, the keys will still exist and be query-able via N1QL.<\/p>\n MySQL and Couchbase are two very different database technologies, but how you use them doesn’t have to be. The relational data model\n
\n
\nforeign key relationships.<\/p>\nOptions for a NoSQL Data Model<\/h3>\n
\ncomes to modeling the data we saw for MySQL.<\/p>\nReferring Documents<\/h4>\n
\nto 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
\nmean you can’t rig together the same kind of relationship.<\/p>\n\r\n{\r\n \"type\": \"customer\",\r\n \"first_name\": \"Nic\",\r\n \"last_name\": \"Raboy\"\r\n}\r\n<\/code><\/pre>\n\r\n{\r\n \"type\": \"customer_address\",\r\n \"city\": \"San Francisco\",\r\n \"state\": \"CA\",\r\n \"zip\": \"94101\",\r\n \"customer_id\": \"c::1\"\r\n}\r\n<\/code><\/pre>\n
\ncustomer document and ca:1<\/strong> is an id I made up for the customer_address<\/strong> document.<\/p>\n
\nrelational database. For example one row of the customer<\/strong> MySQL table would be one document in
\nCouchbase<\/a>.<\/p>\nEmbedding Documents<\/h4>\n
\ndocuments. So what if we wanted to keep all like data together?<\/p>\n\r\n{\r\n \"type\": \"customer\",\r\n \"first_name\": \"Nic\",\r\n \"last_name\": \"Raboy\",\r\n \"addresses\": [\r\n {\r\n \"city\": \"San Francisco\",\r\n \"state\": \"California\"\r\n },\r\n {\r\n \"city\": \"Mountain View\",\r\n \"state\": \"California\"\r\n }\r\n ]\r\n}\r\n<\/code><\/pre>\n
\ncreating a new document for every address and referencing the customer document.<\/p>\n
\nCouchbase, would result in the same data being embedded in more than one Couchbase document. This could happen, but it isn’t
\na bad thing. You don’t need normalized data in a NoSQL database such as Couchbase. However, if you’re really concerned, why not
\nmix both approaches? Keep data such as say customer_history<\/strong> together without relationships and refer to
\nothers that might change more frequently.<\/p>\nQuery Differences Between MySQL and Couchbase<\/h3>\n
Couchbase N1QL vs MySQL SQL<\/h4>\n
\nfor MySQL, we can query for customers and their addresses like so:<\/p>\n\r\nSELECT\r\n c.firstname, c.lastname, ca.city, ca.state\r\nFROM customer_address ca\r\nLEFT JOIN customer c ON ca.customer_id = c.id\r\n<\/code><\/pre>\n\r\nSELECT\r\n c.firstname, c.lastname, ca.city, ca.state\r\nFROM `bucket-name` ca\r\nLEFT JOIN `bucket-name` c ON KEYS ca.customer_id\r\n<\/code><\/pre>\n
\ntables in NoSQL and all the different documents and document types will exist in the same bucket. The document key is the
\nvalue that we join on.<\/p>\n\r\nINSERT INTO customer (id, first_name, last_name)\r\nVALUES (1, 'Arun', 'Gupta');\r\n<\/code><\/pre>\n\r\nINSERT INTO `bucket-name` (KEY, VALUE)\r\nVALUES (1, {\"first_name\": \"Arun\", \"last_name\": \"Gupta\"});\r\n<\/code><\/pre>\nDevelopment Differences Between MySQL and Couchbase<\/h2>\n
\nconsistency, I’ll use Java in the following examples. MySQL is certainly not restricted to Java and neither is Couchbase.<\/p>\nThe MySQL JDBC Driver<\/h3>\n
\nWith the driver included in your project, either through tools like Maven or manually, you can load it and start querying for data.<\/p>\n\r\nClass.forName(\"com.mysql.jdbc.Driver\");\r\nProperties info = new Properties();\r\ninfo.put(\"user\", \"nraboy\");\r\ninfo.put(\"password\", \"password\");\r\nConnection connection = DriverManager.getConnection(\"jdbc:mysql:\/\/HOST:PORT\/DATABASE\", info);\r\nStatement stmt = connection.getConnection().createStatement();\r\nResultSet rs = stmt.executeQuery(\"SELECT * FROM customer\");\r\nwhile(rs.next()) {\r\n \/\/ rs.getString(\"first_name\");\r\n}\r\nstmt.close();\r\n<\/code><\/pre>\nThe Couchbase Java SDK<\/h3>\n
\none does exist. The reason we would use the SDK is because things become incredibly easy with it.<\/p>\n\r\nCouchbaseCluster.create(HOST).openBucket(BUCKET, PASSWORD);\r\nString query = \"SELECT * FROM `bucket-name-here`\";\r\nN1qlQueryResult queryResult = bucket.query(query);\r\nfor (N1qlQueryRow row : queryResult) {\r\n \/\/ row.value().toMap();\r\n}\r\n<\/code><\/pre>\nTool Differences<\/h2>\n
\ncould use the MySQL CLI<\/strong>. You still have the ability to use comparable tools when making the switch to Couchbase.
\nIf you’re looking for a command line tool, you can use CBQ<\/strong> to query your data. If you’re a power user of
\nMySQL Workbench, don’t worry because Couchbase has its Query Workbench<\/strong> as
\nof Couchbase Server 4.5<\/a>.<\/p>\nData Migration<\/h2>\n
\nLaurent Doguin wrote an excellent migration tool for moving data from MySQL to Couchbase<\/a>.<\/p>\nConclusion<\/h2>\n
\ncan still be preserved to an extent in a NoSQL document database. Any data stored can then be queried in Couchbase just as you would
\nin a MySQL database using SQL queries. The major difference between the two is that you’re not limited to what data you can store
\nand how you can store it.<\/p>\n