With the growing popularity of applications like Notion, AFFiNE, AppFlowy, and Anytype, people increasingly want to store and maintain data related to their interests, responsibilities, and lifestyle. And just like me, there are many book lovers who want to track their reading history. Kobo is a popular brand of eReaders. In this article, I will describe how Kobo users can query the underlying SQLite database using the open-source application Beekeeper Studio to extract their book list and reading status and use it as input for their book database.

Content of the article

1. Introduction
2. About SQLite
3. About Beekeeper Studio
4. How to get the database from your Kobo device
5. How to query the KoboReader.sqlite database file
6. How to export the query result and use it in another app
7. Final words

Introduction

Many eReader users probably don’t care much about the underlying technology of their reading hardware, but some of us want something more than just the ability to store and read books. I’ve been a Kobo enthusiast for over 10 years, and my wife and I have since then a Kobo Plus subscription so we can select from an unlimited number of books for a small monthly fee and download them directly to our Kobo device. We do this a lot, as we’re both avid readers of both fiction and non-fiction. We recently had to retire our 10-year-old Kobo devices, and my wife and I both opted for a new Kobo Libra Color.

Next to reading, I also love keeping track of lists, such as lists for the TV series we watch and the movies we see at the cinema. Now, with the purchase of my new Kobo, I also want to keep a list of the books I read in, for example, AppFlowy, Notion, or simply in a regular spreadsheet application. But I don’t feel like manually typing in all of the more than 1500 books (and related data like the author, publisher, and reading status and dates) I’ve read over the past years. So I was looking for a simpler way to extract the necessary book and reading data from my Kobo device’s database. In this article, I will discuss how to do that and how to use this data. To query the data from the SQLite database that the Kobo device is using, I’ll be using the SQL language via the open-source application Beekeeper Studio.

About SQLite

As mentioned above, a database is maintained in the background of every Kobo device. This database contains data about the books available on the device, their download status, reading status, page count, annotations you’ve added, favorite lists, and so on. This database is based on SQLite.

SQLite is a free and open-source relational database engine used worldwide in a vast number of applications requiring a database. It’s used in, for example, software applications, embedded devices, mobile phones, and eReaders. Many other database engines are available, but SQLite is probably the most widely used, partly due to its open-source nature.

SQLite uses the SQL query standard; however, not all SQL features are applicable in SQLite. SQL stands for Structured Query Language and is a relatively simple language for extracting data from databases. With SQL, you can query your database to retrieve specific data, insert records, update and delete data, create databases and tables, etc.

I won’t give a full SQL course here, but I find it helpful to provide a few basic commands to prepare us for how we’ll be reading and querying the Kobo database. In future articles, I want to go deeper into SQLite, the SQL query language, and the different tools available for Linux that we can use to run these SQL statements. For now, let’s keep it simple enough to understand how to query the Kobo database later in this article.

The most important and commonly used statement to be used in SQL is the SELECT statement. Say you have a database with a table named Books, and this table has the attributes Title, SubTitle, Author, NumberOfPages, YearPublished, and DateLastRead. Now we want to see the complete content of the Books database. To do this, we simply use the following query:

SELECT * FROM Books

Now we want to have a list of book titles written by, for example, Ann Cleeves. For this, we use the following query:

SELECT Title FROM Books WHERE Author = ' Ann Cleeves' 

We can extend the list with extra columns:

SELECT Title, SubTitle, Author FROM Books WHERE Author = ' Ann Cleeves' 

Or if you want to see all columns, just use:

SELECT * FROM Books WHERE Author = ' Ann Cleeves' 

This is enough to know for the purpose of this article.

About Beekeeper Studio

To be able to run SQL-based queries like the above, you need some sort of tooling to type the commands, run them, and present the results. There are numerous options available, but for this article, I will use Beekeeper Studio.

Beekeeper Studio is a simple but powerful SQL Client that plays nicely with Linux. If you’re working with databases and want a tool that’s both powerful and pleasant to use, Beekeeper Studio is, in my opinion, a good option. It’s a modern SQL editor and database manager that’s open source, cross-platform, and runs natively on Linux. 

Beekeeper Studio’s mission is to make database access available and easier for everyone through open source software.

In Beekeeper Studio, you can write SQL with ease. The built-in editor offers syntax highlighting and smart auto-complete for your tables, so you can focus on your queries. You can open and use multiple tabs, so it is designed for multitasking. You can view and edit table data. Tables open in a spreadsheet-style interface that makes browsing and editing simple. When you are using complex data, just pop it into a modal or use the JSON sidebar. Do you need to move data around? Just Import from CSV or export to CSV, JSON, JSONL, or SQL. It is also possible to filter your exports to get only what you need.

Although there are different plans for Beekeeper Studio, there is a Community version that is completely free of charge. It is a somewhat limited version of Beekeeper Studio, but perfect for hobbyists, students, or those who want to try the app before buying. For our purpose of reading the SQLite data from our Kobo database, the Community version is perfectly fine.

How to get the database from your Kobo device

To query the data on your Kobo device, we need to access the database. While it’s possible to query the database directly on your device, I recommend making a copy of the database first and using that for our query purposes. This way, you won’t accidentally corrupt the actual database on the device.

1) Connect your Kobo to your computer.

Note: I’ve read a lot about problems connecting a Kobo device to a Linux computer. I’ve tried several cables, but so far, only the USB-C cable that came with the Kobo eReader has worked flawlessly for me.

2) Confirm on your Kobo device that you want to connect.

3) Open your File Manager and select KOBOeReader.

4) Go to your settings in the File Mamnager and make sure your hidden files are visible.

5) Open the .kobo folder and copy the file KoboReader.sqlite, for example, to your desktop.

Now it’s time to open Beekeeper Studio and take a look at the KoboReader.sqlite database.

How to query the KoboReader.sqlite database file

Now that we have the required file, we continue in Beekeeper Studio.

1) To query the data in the KoboReader.sqlite file, we need to start Beekeeper Studio.

Now you see the below:

2) Start a new connection by selecting a connection type. Select SQLite.

3) Now choose the KoboReader.sqlite file that you copied from your device. Also, give the connection a name and click Save.

4) Click Connect.

You will now see the below:

At the left side you see all the available tables in the KoboReader.sqlite database. When a new book is stored into the Kobo database and the book is selected to red by the reader, different data rows are stored and updated into various tables. The “content” table holds a record for each book. Each record describes the book and its status, based on attributes like BookTitle, Description, Attribution, Publisher and DateLastRead.

So, for our purpose, to get data about every book that we read, want to read, or are currently reading, we need to query the “content” table. Below an SQL statement that I use for my personal needs:

SELECT Title, Subtitle, Attribution, Series, Language, ReadStatus, DateLastRead, ISBN, Publisher FROM content WHERE ContentType = 6 order by DateLastRead desc;

We see in the query the following:

SELECT Title, Subtitle, Attribution, Series, Language, ReadStatus, DateLastRead, ISBN, Publisher >> Here we describe the attributes or table columns we are interested in.

FROM content >> Here we describe the table, in this case “content” from which we want to query our data.

WHERE ContentType = 6 >> ContentType indicates the type of content. For most books, the book row has ContentType = 6. But for example chapters have a ContentType = 9. For newspapers, the ContentType = 10. SDo for our purpose we use ContentType = 6.

order by DateLastRead desc >> Here we want to order the result based on the DateLastRead column and we want to show is descending so we see the most recent status at the top of the resulting list.

5) Copy the below query in the Query section of Beekeeper Studio:

SELECT Title, Subtitle, Attribution, Series, Language, ReadStatus, DateLastRead, ISBN, Publisher FROM content WHERE ContentType = 6 order by DateLastRead desc;

6) Click on Run.

Now you will see the result of your query:

The reason why we wanted to query the Kobo database was that we could use the resulting data to store, for example, in Notion or AppFlowy to keep track of our reading statistics. One of the columns in our result is ReadStatus. Here you see values like 0, 1, and 2. It is good to understand the meaning of these values:

• 0: Not read
• 1: Currently reading
• 2: Finished

How to export the query result and use it in another app

Now that we have our result data from the Kobo database, we want to export that data so we can use it, for example, in AppFlowy or Notion.

1) Click on the Download button.

2) Select “Download as CSV”.

Note: A CSV file, which stands for “Comma-Separated Values”, is a simple text format used to store tabular data. Think of it like a stripped-down spreadsheet: rows and columns made up of plain text, with each value separated by a comma. There is no formatting and no formulas, so it is a good format to use to import in applications that can read and process the CSV format.

3) Accept or change the name of the file. Keep the .csv extension. 

Now we can import the Kobo data in our applications.

AppFlowy

Let’s have a look at the CSV importer in AppFlowy.

Note: Have an interest in an open-source Notion alternative? Here are my articles on AppFlowy: 

AppFlowy articles on RealLinuxUser.com

1) Select an existing page or create a new page that you want to use as a placeholder for the (sub)page to be created for your Kobo Book Reading data. In my example, I use the Resources section in my PARA organizational structure in AppFlowy. 

2) Click the ‘+’ icon on the location for your book list that you just created to import your Kobo data into and choose the Import option.

3) Select CSV and select the .CSV file that you created from Beekeeper Studio.

4) Click OK to import your book data into a new AppFlowy book reading page. 

When finished, you can rename the new page, as it now has the same name as your CSV file.

Notion

Let’s have a look at the CSV importer in Notion.

1) Select an existing page or create a new page that you want to use as a placeholder for the (sub)page to be created for your Kobo Book Reading data. In my example, I use the Resources section in my PARA organizational structure in Notion. 

2) On this page, click the ‘…’ icon in the top right corner and choose the Import option.

3) Select the CSV option and in the next screen, click Choose file.

4) Select your CSV file with your book reading data.

5) Click on Map CSV headers.

6) Keep the settings as proposed and click Import CSV to import your book data into a new Notion book reading page. 

When finished, you can rename the new page, as it now has the same name as your CSV file.

Final words

If you’re as avid a reader as I am and also enjoy keeping lists of your book consumption, including book details, status, and related dates, the KoboReader.sqlite database in your Kobo device offers you many possibilities for extracting the data and using it in your favorite second-brain application or spreadsheet. With the relatively simple SQL query language (very complex queries are also possible, but more on that in future articles), it’s easy to extract the book data from the SQLite-based database and use it in other applications. I hope this article has helped you better understand the Kobo database and simplify the process of accessing and querying it. Good luck and have fun with your own Kobo data.

---

---