exporting playlists from NewPipe

This tutorial explains how to export playlists from NewPipe.

NewPipe does not officially support a “export playlist” feature. Instead, we need to retrieve the information on playlists from NewPipe’s internal app data, which is stored in a SQLite database named newpipe.db.

This database contains three tables of interest:

  1. streams: a table with information (such as URL, title, etc.) on the videos in all of the user’s playlists. Each row in this table represents a different video, which might be inside multiple playlists. The rows of this table are not stored in any particular order.
  2. playlists: a table containing metadata about each of the playlists. Each row in this table contains a playlist_id column, used to identify the playlist it represents.
  3. playlist_stream_join: a table containing information about how to reconstruct each playlist. Each row of this table contains three columns: playlist_id, stream_id and join_index. The playlist_id column represents the index in playlists for the playlist this entry represents, while stream_id represents the index in streams for the video this entry represents. The entries in playlist_stream_join are not stored in any particular order. Instead, we should use the join_index column to sort the videos inside a playlist in the correct order.

This tutorial may stop working at any moment! It relies on NewPipe’s implementation details, which are subject to undisclosed changes by the NewPipe devs.

First, follow the official instructions on how to export NewPipe’s data data to obtain a ZIP file containing all of NewPipe’s app data. You will find newpipe.db inside this ZIP file. You can now open this database using the sqlite3 tool.

Next, to figure out your playlist’s playlist_id, you can run the following query inside sqlite3. 

select playlist_id, title from playlists;

Finally, we can re-construct a table with information on each video in our playlist – sorted in the correct order! – by running the following query. 

select   s.url, s.title
from     streams s
join     playlist_stream_join psj
on       psj.stream_id = s.uid
where    psj.playlist_id = PLAYLIST_ID
order by psj.join_index;

You should replace PLAYLIST_ID from the last query with the id obtained by inspecting the entries of the playlists table.

To export this table to a different format, such as JSON or CSV, you can use the .output and .mode directives. The .output OUTPUT_PATH directive tells SQLite to export the result of your queries to the file OUTPUT_PATH, while the .mode MODE directive tells SQLite what is the format you want to export to. Here MODE should be one of the following: ascii, box, c, column, count, csv, html, insert, jatom, jobject, json, line, list, markdown, off, psql, qbox, quote, split, table, tabs, tcl, batch or tty.

For example, to export your table to a JSON file named output.json, you can prepend the last query with the following directives. 

.output output.json
.mode   json