<p>CSV is still a popular way to keep data because it's so easy to use Excel to enter data, but from the web application view, we would like the data source to be JSON format or via API. In this article, we will learn how to create a Restful API from a CSV file using FeathersJS and MongoDB.</p><p>We use Swimming World Records CSV data downloaded from <a target="_blank" rel="noopener noreferrer" href="https://www.swimrankings.net/index.php?page=recordDetail&amp;recordListId=50001">SwimRankings.net</a> as an example. &nbsp;And we choose MongoDB for the database because Data in MongoDB has a flexible schema, which gives us a lot of freedom. And we use NoSQLBooster for MongoDB (a Powerful FREE MongoDB GUI) &nbsp;to import CSV. Finally, we use FeathersJS to create a Restful API. BWT, &nbsp;you may check out “<a target="_blank" rel="noopener noreferrer" href="https://deniapps.com/blog/create-restful-api-within-minutes-with-feathersjs">Create a Restful API within 5 minutes</a>”.&nbsp;</p><figure class="image"><img src="https://images.unsplash.com/photo-1560090970-feef7ff6e339?ixlib=rb-1.2.1&amp;q=80&amp;fm=jpg&amp;crop=entropy&amp;cs=tinysrgb&amp;w=1080&amp;fit=max&amp;ixid=eyJhcHBfaWQiOjE1NDAxMH0"><figcaption>Photo by <a target="_blank" rel="noopener noreferrer" href="https://unsplash.com/@serenarepice?utm_source=DNX&amp;utm_medium=referral">Serena Repice Lentini</a> on <a target="_blank" rel="noopener noreferrer" href="https://unsplash.com/?utm_source=DNX&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h3>Import CSV into MongoDB</h3><p>To make a job easier, let's use NoSQLBooster. After connecting to the MongoDB server, you can create a database if you don't have one, in our case, we have a database name ‘deniapps’, and we just need to create a collection named “swimmingworldrecords”, and then import the CSV file into the collection.&nbsp;</p><p><strong>Note</strong>: To follow the naming conventions of Mongoose, we should use the lowercase for collection name. Please ignore the name in the screenshot below. &nbsp;Sorry that I am too lazy to update it.</p><blockquote><p>Mongoose will lowercase and pluralize with an ‘s’ of the Model name when it accesses the collection (‘SwimmingWorldRecord’&nbsp;(Model Name) » ‘swimmingworldrecords’ (Collection Name))</p></blockquote><figure class="image"><img src="https://images.deniapps.com/how-to-import-csv-mongodb.png"><figcaption>Import Tool in NoSQLBooster for MongoDB</figcaption></figure><p>Note that we should keep the first row of CSV file as fields, so then the Mongoimport knows how to set fields automatically for each document, and even better, the Mongoimport can auto-detect and set the data type of each field for us.&nbsp;</p><h3>Generate API with FeathersJS</h3><p>We use <a target="_blank" rel="noopener noreferrer" href="https://github.com/feathersjs/cli">FeathersJS CLI</a>, a command-line interface for Feathers applications to build API. &nbsp;Since we have feathers-CLI installed and the feathers app created already, we only need to generate a service. If this is the first time you are using FeathersJS, then checkout “<a target="_blank" rel="noopener noreferrer" href="https://deniapps.com/blog/create-restful-api-within-minutes-with-feathersjs">Create a Restful API within 5 minutes</a>”.&nbsp;</p><p>To generate a new service, we can use the following commands:</p><pre><code class="language-plaintext" id="9pnwId3No">$ feathers generate service
? What kind of service is it? Mongoose
? What is the name of the service? swimming world records
? Which path should the service be registered on? /swimming-world-records
? Does the service require authentication? No
  create src/services/swimming-world-records/swimming-world-records.service.js
….</code></pre><p>Believe it or not, we just make an API. Run <code>‘npm run dev’</code>, and the new service is ready at http://localhost:3030/swimming-world-records. Or you can test it at https://api.deniapps.com/swimming-world-records</p><h3>Add Hooks</h3><p>As we mentioned above “Mongoimport can auto-detect and set the data type of each field for us.” - Good and bad. It may help when we do sorting by a field, but it may cause issues when we search by a field. &nbsp;Let's see one of the records:</p><pre><code class="language-javascript" id="-oBMbgMZC">{
  "_id": "5f7a7e2f174630d0c079e2e2",
  "COURSE": "LCM",
  "GENDER": "M",
  "DISTANCE": 50,
  "STROKE": "Fr",
  "FULLNAME": "CIELO, Cesar",
  "BIRTHDATE": "10.01.1987",
  "NATION": "BRA",
  "SWIMTIME": 20.91,
  "SWIMTIME_N": 20.91,
  "MEETDATE": "18.12.2009",
  "MEETCITY": "Sao Paulo",
  "MEETNAME": "Brasilian Championships",
  "SPLIT50": 20.91
},</code></pre><p>For example, the “DISTANCE” field is <code>integer</code>, but if we try to find this record by http://localhost:3030/swimming-world-records?DISTANCE=50, we will get no result, that is because the HTTP parameter is <code>String</code>, to fix this, we can write a simple hook to fix this.</p><p>Again, we can use feathers CLI - <code>feathers generate hook</code>, like below:</p><pre><code class="language-plaintext" id="sLG9edT8G">$ feathers generate hook
? What is the name of the hook? type cast
? What kind of hook should it be? before
? What service(s) should this hook be for (select none to add it yourself)?
 swimming-world-records
? What methods should the hook be for (select none to add it yourself)? find
   create src/hooks/type-cast.js
    force src/services/swimming-world-records/swimming-world-records.hooks.js</code></pre><p>And then update swimming-world-records.hooks.js to be:</p><pre><code class="language-javascript" id="IMnfRsDzr">module.exports = (options = {}) =&gt; {
  return async (context) =&gt; {
    if (context.params.query.DISTANCE)
      context.params.query.DISTANCE = parseInt(context.params.query.DISTANCE);
    return context;
  };
};</code></pre><p>Now the DISTANCE parameter will be cast into an integer before being used for record lookup. Try http://localhost:3030/swimming-world-records?DISTANCE=50 again, you will see the records.</p><p>That's it! :-)</p>

In this article, we will learn how to easily convert CSV files into a Restful API using FeathersJS Command Line and MongoDB. We will also learn how to cast data type using FeatherJS hook.

Convert CSV File to Restful API Using FeathersJS

<p>Different from MySQL, it's pretty hard to find a FREE and full function GUI tool for MongoDB. We have tried three FREE popular MongoDB GUI.&nbsp;</p><ol><li><a target="_blank" rel="noopener noreferrer" href="https://robomongo.org/ ">Robo 3T</a> - very limited feature, and cannot even import</li><li><a target="_blank" rel="noopener noreferrer" href="https://www.mongodb.com/products/compass">MongoDB Compass</a> &nbsp;- &nbsp;modern feel UI, but lack of some important features, for example, renaming a collection</li><li><a target="_blank" rel="noopener noreferrer" href="https://nosqlbooster.com/">NoSQLBooster</a> - classic UI (okay for me), and has most features we need except missing export</li></ol><p>In case you are looking for a FREE MongoDB GUI, here is a comparison of free vs paid of NoSQLBooster: https://nosqlbooster.com/compareEditions</p><figure class="image"><img src="https://images.unsplash.com/photo-1544383835-bda2bc66a55d?ixlib=rb-1.2.1&amp;q=80&amp;fm=jpg&amp;crop=entropy&amp;cs=tinysrgb&amp;w=1080&amp;fit=max&amp;ixid=eyJhcHBfaWQiOjE1NDAxMH0"><figcaption>Photo by <a target="_blank" rel="noopener noreferrer" href="https://unsplash.com/@jankolar?utm_source=DNX&amp;utm_medium=referral">Jan Antonin Kolar</a> on <a target="_blank" rel="noopener noreferrer" href="https://unsplash.com/?utm_source=DNX&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>We have been used to import CSV data into a MongoDB collection for a while, which works very well. But we got the error when importing JSON data. That might sound very weird, doesn't it? It ends up because we did not provide the "correct" JSON format. Let's see what JSON format to make NoSQLBooster happy.</p><p>Let's first look at the JSON data as below:</p><pre><code class="language-javascript" id="LVG0HE1lB">[{
		"done": false,
		"task": "write about nosqlbooster"
	},
	{
		"done": false,
		"task": "look for the best mongodb gui!"
	}
]</code></pre><p>We used https://jsonlint.com/ to make sure it's valid, and then we tried to import it into a collection.</p><figure class="image"><img src="https://images.deniapps.com/importnosqlboostermongodb.png"><figcaption>Import JSON Data into MongoDB</figcaption></figure><p>Right click on a collection → Click on ‘Import Collection’ → Click on 'Mongoimport (JSON and CSV), and the the window open to allow us to choose the JSON file, but after we chose the validated JSON file, the popup window shown with the error message:</p><blockquote><p>The specified file “xxx” is not valid mongoexport JSON file</p></blockquote><figure class="image"><img src="https://images.deniapps.com/cannot-import-json-nosqlbooster-.png"><figcaption>Cannot Import JSON using NoSQLBooster</figcaption></figure><p>Believe or not we could not find the answer by Googling this issue, but we found that we can import the JSON data if it contains only one record like:</p><pre><code class="language-javascript" id="liks5d2dM">{
	"done": false,
	"task": "write about nosqlbooster"
}</code></pre><p>NoSQLBooster does not allow import multiple records? That does not sound right, we can always import CSV file without any issues. Finally we figured out that it does not like valid JSON data. Instead, we have to provide an invalid JSON data, like below:</p><pre><code class="language-javascript" id="sJnumKfZ7">{"done": false,"task": "write about nosqlbooster"}
{"done": false,"task": "look for the best mongodb gui!"}</code></pre><p>You're right, no Array (i.e., []), no commas. Just need to write your records one by one.</p><p>That's it. Hope you find this helps until NoSQLBooster fixes this issue. :-)</p>

NoSQLBooster is probably the best free MongoDB GUI tool. We have been used to import CSV data into a MongoDB collection for a while, which works very well. But we got the error when importing JSON data. That might sound very weird, doesn't it? It ends up because we did not provide the "correct" JSON format. Let's see what JSON format to make NoSQLBooster happy.

How to Import JSON into MongoDB using NoSQLBooster

<p>Recently I migrated MongoDB (over 2GB) from a cheap server hosted on Hudson Valley Host to a better server hosted on Digital Ocean to improve the performance.</p><figure class="image"><img src="https://images.unsplash.com/photo-1491330779004-002d63718532?crop=entropy&amp;cs=tinysrgb&amp;fit=max&amp;fm=jpg&amp;ixid=MXwxNTQwMTB8MHwxfHJhbmRvbXx8fHx8fHx8&amp;ixlib=rb-1.2.1&amp;q=80&amp;w=1080"><figcaption>Photo by <a target="_blank" rel="noopener noreferrer" href="https://unsplash.com/@rayhennessy?utm_source=DNX&amp;utm_medium=referral">Ray Hennessy</a> on <a target="_blank" rel="noopener noreferrer" href="https://unsplash.com/?utm_source=DNX&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h3>Servers Specs</h3><ul><li><strong>Old</strong>: Ubuntu 16.04 / 4G Memory / &nbsp;4vCPUs E3-1240 V2 @ 3.40GHz / MongoDB 3.6.4</li><li><strong>New</strong>: Ubuntu 20.04 / 8G Memory &nbsp;/ 4vCPUs E5-2650 v4 @ 2.20GHz / MongoDB 4.4</li></ul><h3><strong>Install MongoDB</strong></h3><p>It's pretty straightforward by following the docs on MongoDB official website: https://docs.mongodb.com/manual/tutorial/install-mongodb-on-ubuntu/</p><p>Note that: By default, MongoDB has no default user or password, and you can only access them via localhost. But very likely, you would need to enable access control (i.e., auth) and create a user to access the databases.&nbsp;</p><h3>Create DB User</h3><p>Right after you install the MongoDB, you should be able to log in Mongo Shell, by running the command “<code>mongo</code>” via SSH terminal. And then enter the following command:</p><pre><code class="language-plaintext" id="eIJ2lYIMe">use admin
db.createUser(
  {
    user: "dbUser",
    pwd: "password",
    roles: [ { role: "userAdminAnyDatabase", db: "admin" }, "readWriteAnyDatabase" ]
  }
)</code></pre><p>Note that I assign the user <code>userAdminAnyDatabase</code> role, which gives the user the ability to create users and assign arbitrary roles to them. &nbsp;It sounds like a superuser, but it's not. To be a superuser or root user, you have to assign the following roles:</p><ul><li><a target="_blank" rel="noopener noreferrer" href="https://docs.mongodb.com/manual/reference/built-in-roles/#readWriteAnyDatabase"><code>readWriteAnyDatabase</code></a></li><li><a target="_blank" rel="noopener noreferrer" href="https://docs.mongodb.com/manual/reference/built-in-roles/#dbAdminAnyDatabase"><code>dbAdminAnyDatabase</code></a></li><li><a target="_blank" rel="noopener noreferrer" href="https://docs.mongodb.com/manual/reference/built-in-roles/#userAdminAnyDatabase"><code>userAdminAnyDatabase</code></a></li><li><a target="_blank" rel="noopener noreferrer" href="https://docs.mongodb.com/manual/reference/built-in-roles/#clusterAdmin"><code>clusterAdmin</code></a></li><li><a target="_blank" rel="noopener noreferrer" href="https://docs.mongodb.com/manual/reference/built-in-roles/#restore"><code>restore</code></a></li><li><a target="_blank" rel="noopener noreferrer" href="https://docs.mongodb.com/manual/reference/built-in-roles/#backup"><code>backup</code></a></li></ul><h3>Export Remote Database</h3><p>Run the following command to dump the existing remote database:</p><pre><code class="language-plaintext" id="7ZPnajANp">mongodump  --host REMOTE_DB_HOST --username REMOTE_DB_USERNAME --password REMOTE_DB_PASS --authenticationDatabase admin --db DB_NAME --out backup</code></pre><p><strong>Note</strong></p><ol><li>&nbsp;‘admin’ is the default <code>authenticationDatabase</code>, &nbsp;you may use a different one.</li><li>use “--out” instead of “--archive” as some other tutorial mentioned, and “backup” is the folder name where I save the dump files</li></ol><h3>Import dumped MongodDB</h3><p>In the folder having ‘backup’ subfolder, run the following command:</p><pre><code class="language-plaintext" id="BROQ40YEL">mongorestore backup/</code></pre><h3>Enable Auth and Remote Access</h3><p><strong>NOTE: DON'T DO THIS STEP UNTIL ALL STEPS ABOVE ARE COMPLETED！</strong></p><p>Now it's time to enable auth and remote access. It can be configured in the mongod.conf (by default: it's under /etc/mongod.conf.) Run ‘vim /etc/mongod.conf’, and then modify the following lines:</p><pre><code class="language-plaintext" id="mbBjBqaW_"> net:
  port: 27017
# change bindIP to all IPV4 addresses, otherwise mongod only listens to 127.0.0.1
  bindIp: 0.0.0.0
# ...
# uncomment security and enable authorization
security:
  authorization: enabled</code></pre><p>By default, &nbsp;Ubuntu does not enable UFW (Uncomplicated Firewall) If you use it, then you add the remote server IP the whitelist using the following command:</p><pre><code class="language-plaintext" id="y76NISFrS">sudo ufw allow from remote_server_ip to any port 27017</code></pre><h3>Updated</h3><p><i>On: 12/21.2021</i></p><p>Recently I worked on a project involved in a large dataset. I had to process the data on my local before moving them to the remote database. The way I showed above does not work, because I could not connect to my local machine from the remote server. &nbsp;Here is what I figured out:</p><h4>Dump the database on my local machine</h4><pre><code class="language-javascript" id="BjvAqUpHU">mongodump --db=DBNAME --out=backup1221</code></pre><p>Since it's local, I don't have authorization enabled. If it's not your case, you could do this:</p><pre><code class="language-javascript" id="BjvAqUpHU">mongodump --username=REMOTE_DB_USERNAME -p --authenticationDatabase=admin --db=DBNAME --out=backup1221</code></pre><h4>Upload dump folder to the remote server</h4><p>My local server is Windows, so I use WinSCP. It's slow - about 1M/s. I zipped the folder before uploading. Or you may try gizp when doing the mongodump, like</p><pre><code class="language-javascript" id="BjvAqUpHU">mongodump --gzip --db=DBNAME --out=backup1221</code></pre><h4>Restore on the remote server</h4><p>My remote server has authorization enabled, so I use the command below:</p><pre><code class="language-javascript" id="hDg9TL9fX"> mongorestore -u username --authenticationDatabase=admin dump122121</code></pre><p><strong>Note:</strong></p><ol><li>The authenticationDatabase have to match where user was originally created. In the other words, it's not necessary to be “admin”.</li><li>Some parameter keys have two formats, for example: username could be “--username” or “-u”. We could use “=” or “ ” to assign the value, like -u=USERNAME or -u USERNAME. Check the <a target="_blank" rel="noopener noreferrer" href="https://docs.mongodb.com/database-tools/mongoexport/">doc</a> for details.</li><li>I did not include the password from the command line, &nbsp;it will prompt you to enter.</li></ol><p>&nbsp;</p>

Recently I migrated MongoDB (over 2GB) from a cheap server hosted on Hudson Valley Host to a better server hosted on Digital Ocean to improve the performance. In this article, I would like to write down some notes for this process.

All Posts In Mongodb