[How-to] Migrate data from MySQL to CouchDB

[Kyominii]

How to migrate to CouchDB from MySQL

As you know, we have now three choice for storing data immediately available now:

• First : use the old mysql library from essentialmode <= 2 (not recommanded because it breaks servers with bugs related to DataReader for example)
• Second : use a custom mysql library released by @Brouznouf : mysql-async ([Release] MySQL Async Library - 3.3.2, there is a guide about this one)
• Third : use CouchDB used by EssentialMode >= 3

If there’s more, sorry, please notice me ^^’

But in this tutorial, we’ll see how to migrate MySQL data to CouchDB for people who wants to test it or develop with it.

Before doing this, I highly recommand you to read this :
https://blog.couchbase.com/how-move-mysql-couchbase-server-20-part-1/
and
https://blog.couchbase.com/how-move-mysql-couchbase-server-20-part-2/

If you are a developper, you can have to think about a new structure about your data

Requirement :

• Having a working CouchDB 2 setup
• Having PhpMyAdmin or knowledge about the tool you are using

1/ Export a table (not all table, just one, example : police), with phpmyadmin or any tool you prefer, in JSON Format

• Select your database
  

  

• Select the table you want to export
  

  

• Click on the Export tab
  

  

• Select the JSON value in the drop-down list then click Go
  

  

• Now you have all table’s data on a single file, please keep it

2/ Create a new database on couchdb related to data you are going to export

• Go to Fauxton on Apache CouchDB : e.g. http://127.0.0.1:5984/_utils/

• Click on Create Database
  

  

• Enter the database name you want (related to data you are migrating) in the field then click Create
  

  

3/ We are going tu use curl so if you you have Windows, I recommand you to install GitBash. On Linux, you can just download packages

• Linux

  • Please install curl package (if you don’t know how, it depends the distribution of your linux, so refer to Google)

• Windows

  • Install GitBash : https://git-for-windows.github.io/
    I recommand to not change default settings during installation if you don’t know what you are doing except about PATH environment (refer to picture)
    
    

4/ Open GitBash/terminal and go to the directory where your exported file is (in the example, my police.json is on the Desktop)

• Linux

  • Use cd to change directory (I think you know how to do )

• Windows

  • Go to the directory you put your file then Left-Click and select “Git Bash here”
    Sorry for French texts by the way ^^
    
    

Now, you should have this kind of window

5/ Now you have to edit your exported file :

• Remove all comments
  

  

• Add ‘{“docs” : ALL_DATA }’
  

6/ Type in GitBash/terminal :

curl -X POST 'http://user:pass@host:port/dbname/_bulk_docs' -d @yourFile.json -H 'Content-Type:application/json'

• In our case : curl -X POST ‘http://root:1202@127.0.0.1:5984/police/_bulk_docs’ -d @police.json -H ‘Content-Type:application/json’
  

  

• If you follow all isntructions, you should have something like that (if you have a lot of data)
  

  

• You can also check on Fauxton, it should have some documents now (1 document per data)
  

  

7/ Finish

Now you can do this tutorial for each table you have in MySQL server
You can also check on Google how to interact with CouchDB from FiveM and check https://wiki.fivem.net/wiki/PerformHttpRequest
You can also use essentialmode >= 3 by @Kanersps and check his Wiki (https://kanersps.pw/wiki/index.php?title=Main_Page)

I hope this tutorial can help some of you.
If there is any mistakes, please notice me and I would correct them

[Guig2z]

Dude …

THATS FREAKING AMAZING !!!

[hoegarden31]

Looks easy with a table where there is only 1 record for each user… Now please make an example where you migrate user_inventory into CouchDB and show us how it works.

[Kyominii]

Humm, same way, it is exporting a complete table, not a single record
(for example, before I made this tutorial, i was testing with users table with 250 players on it)

[hoegarden31]

But how do you deal with the fetching of the data ? You do it one file at the time for that user ? Or is there like a kind of get all documents from that user ?

[Kyominii]

It isn’t about a user, it is about a table
I don’t understand what you mean

[hoegarden31]

@Kyominii

You can also check on Fauxton, it should have some documents now (1 document per data)

You speak of 1 document per data.

Then somewhere in the code, you have a function to get that document :

function db.retrieveUser(identifier, callback)
	local qu = {selector = {["identifier"] = identifier}, fields = {"_rev", "_id", "identifier", "bank", "money", "dirty_money", "group", "permission_level"}}
	PerformHttpRequest("http://127.0.0.1:" .. port .. "/essentialmode/_find", function(err, rText, headers)
		local t = json.decode(rText)
		
		if(t.docs[1])then
			callback(t.docs[1])
		else
			callback(false)
		end
	end, "POST", json.encode(qu), {["Content-Type"] = 'application/json'})		
end

Correct me if I am wrong, but using this will retrieve 1 document. So you have to put this in a loop or something ?
My question is how to you do that kind of stuff in CouchDB ?

[Kyominii]

Ohhh you are talking about code ^^
My tutorial isn’t about that, it is about migrate data from mysql to couchdb, not how to use couchdb on fivem

To answer at you question, there is no need to put it in a loop.
Just see parameters, you provide a identifier and it will return you the document about this user, false else in your callback.

you can also use exposeddb functions provided by essentialmode to create your own db functions easily

[hoegarden31]

Ok never mind then… I have a situation that in one “table” you have 2 or 3 or 100 documents all with the same userID.
But there is no much information on the wiki of couchDB about this kind of stuff. They always talk about 1 document for 1 ID. I have 500 documents for 1 ID… You see the problem ?

[Kyominii]

ID are auto-generated by couchdb, so identifier wouldn’t be a “PRIMARY KEY” and you can retrieve a document with selectors (it like WHERE clause in sql)

[BTCB]

Good job! Do you know if there is a way to recreate player names as is?

[Kyominii]

What do you mean ? If player names already are in your sql table, it should be in COuchDB after migration

[BTCB]

Most of the player names are encrypted, for example if I connect, my name on the DB turns into 25665564576513213579787. This makes it a bit more difficult to find a player name, and update their information. (IE - bank details, last position, etc).

[Kyominii]

Is it a custom script that doing it ?

[BTCB]

Not entirely sure, but I’m guessing CouchDB is changing that information…

[Kyominii]

It’s weird your issue, is it during the migration that informations are “encrypted” ?

[Kanersps]

CouchDB doesn’t encrypt it or whatever you think it is.

You messed up somewhere in your code when trying to save it, I suggest posting an example of what code you use so we can try to resolve it.

[BTCB]

Here is a listing of all players that have connected to my server:

Any way to change this data?

[Kyominii]

it isn’t the player name, it is the data id
click on one data and you’ll see that all data are in there

[BTCB]

I understand that, however, would there be a way to change that information for a more efficient way to handle players?