CODEWITHSUNDEEP
mysqlnode.jsdatabase-designnosqlcouchdb
cnandreu
cnandreu

Reputation: 5111

Easiest way to export a MySQL database to CouchDB?

I'm just looking to play around with NOSQL and Node.js by re-writing a very simple MySQL and PHP app so students can leave comments to professors: 

Table: Prof
Columns: id, first, last, university, department

Table: Comments
Columns: id, user_id, likes, data

The relationship is basically a Professor has many Comments and a Comment belongs to one Professor.

I think the CouchDB/JSON version would look like this:

{ "Profs" : [{
  "Comments" : [ {"data" : "comment1", "likes" : 42 } , { "data" : "comment2", "likes" : 1} ],
 "first" : "Carlos",
 "last" : "Marx",
 "university" : "some university",
 "department" : "math"
}, {...another prof with comments...}], 
 }

My first question is, is there a tool or script I can run to import all of my data into something CouchDB can work? If not, what would you recommend? I can export it to .sql and .cvs from phpmyadmin.

My second question is did I correctly structure my data in JSON?

Thanks!

Upvotes: 2

Views: 2100

Answers (2)

dch
dch

Reputation: 1542

This is a classic blog post + comments type scenario.

Instead of using a monolithic JSON doc for the whole DB, split comments and profs into separate JSON blobs. I suggest using email addresses for ids as they're unique and pretty much everybody has them. Use CouchDB's include_docs feature to link the prof doc and the comment docs together.

{
  "_id": "[email protected]",
  "university" : "Some Uni",
  "department" : "Math",
  "name" : "Carlos Marx"
}

{
  "_id": "comment1",
  "prof": "[email protected]",
  "comment": "Very smart"
}

Upvotes: 3

Timothy Strimple
Timothy Strimple

Reputation: 23070

You really don't just export a MySQL database to CouchDB or any other NoSQL database. They are different paradigms and require changing the way you think about how your data is stored. There are excellent use cases for both relational and document databases, and while there is a lot of overlap, they both excel at different things.

With NoSQL databases, it is very important to understand how your data is going to be used. How data is written and used by your application and if data is frequently updated all have very important effects on how you should design your documents. In your example for instance. Can you edit comments? How are comments viewed? Do you always show all of them or do you want the results to be paged? How frequently are comments added? Can they be removed? Without knowing the answers to some of these questions, I can't say for certain whether CouchDB is even the right NoSQL solution for the problem!

I'm sorry that this is sort of a non-direct answer to your question, but approaching a migration from a relational database to a NoSQL solution as a simple map from tables to collections is a recipe for failure. Once you have put the time into designing how your documents should look in CouchDB, the data will have to go through a transformation process. That can take many shapes. You could write an application that can read from MySQL and write to CouchDB or you can export to some other format and have a script import that. There is no magic button that will take your MySQL database and turn it into a Couch database, and if there is, it really should not exist due to the issues mentioned above.

Upvotes: 2

Related Questions