Categories
Databases MySQL Productivity sqlite

Ill-Fated Attempt at Importing Quick Notes Data to Joplin

As you may have learned from the title, my attempt at importing data from Quick Notes to Joplin failed. I leave my notes here in case they save trouble for the next person.

Background

I was a Quick Notes user (which, to be clear, is mighty fine software, just not quite what I was looking for) for like two days. During that time I entered as many as ten notes. Obviously I’m not going to manually move such an enormous quantity of data, so I started for looking for ways to automate it.

“Not a problem!” I said to myself, “I’ll just dump the contents of one table into the other.” Well. Quick Notes uses MySQL and Joplin uses sqlite. That complicated things a bit.

“But,” you may be saying, “Wouldn’t you spend less time manually copying the notes over?” I refer you to xkcd.

Step 1 – Data from Quick Notes/MySQL to sqlite

Quick Notes uses MySQL whereas Joplin uses sqlite. The first order of business was to take the data dump from Quick Notes/MySQL and convert it into a sqlite file.

I tried a few things here. I ultimately wound up on an existing script to do the job for me, aptly named mysql2sqlite.

I saw instructions on RebaseData to make the change with curl. I found this resulted in a 4.6 MB file, but didn’t seem to have any tables. I leave the command here in case you want to pursue completing the conversion without downloading another script.

curl -F files[]=@file.sql 'file://[path to your mysql dump]?outputFormat=sqlite&errorResponse=zip' -o output.sqlite

Step 2 – the Joplin Database

Next you need a copy of the Joplin database. I found it in ~/.config/joplin-desktop/database.sqlite. If you go into the Joplin desktop app -> Options -> General, the location of the database is about the first line there. It’s easy to miss. I was not the only person who tried to locate it the hard way.

If you poke around in the databases you’ll find Joplin has a table called notes, which is fairly straightforward. Quick Notes has one called oc_quicknotes_notes.

Step 3 – Realize this Is More Complicated Than I Thought and Throw My Hands in the Air But Don’t Wave Them Like I Just Don’t Care

It was easy enough to insert from oc_quicknotes_notes into notes (attach then insert sub-select). Joplin has a table where it keeps track of what it’s synced and that’s what did in my efforts. I wasn’t able to cram the table full of guesswork well enough for Joplin not to choke on it.

Some other notes

  • The tags tables on both systems really only need an ID and a name. That in itself looks like an easy port. It looks like Joplin uses some kind of hash for the IDs, so the odds of an ID collision seem slim. That simplifies populating the pivot table.
  • Joplin is in Markdown but Quick Notes is HTML.
  • Attachments are probably a big job. In Quick Notes requires you to have uploaded a file to Next Cloud first, there’s no file upload dialog. Joplin has no such restriction.

Happy hacking!