Misframe

Jan 17, 2021

Storing files in SQLite

If you have lots (think millions) of small files and have issues managing them, SQLite may help. A SQLite database is a great way to store lots of files. Here’s how you can turn tons of files into a single SQLite database table.

You’ll need the sqlite3 CLI. It’s included by default in macOS.

First create a SQLite DB with a files table:

$ sqlite3 posts.db \
  'CREATE TABLE files (filename TEXT PRIMARY KEY, contents TEXT);'

Then use find to look for the files you want, and then insert them into the files table one by one. sqlite3 has a useful readfile function for this:

$ find . -name '*.md' -exec \
  sqlite3 posts.db \
  "INSERT INTO files VALUES ('{}', readfile('{}'));" \;

And that’s it! Now you can read and write to a single SQLite DB file, which is easier to copy, move, and share.

$ sqlite3 posts.db \
  'SELECT filename, LENGTH(contents) FROM files LIMIT 10;'
./content/2013/03/04/writing-a-database.md|4404
./content/2013/04/18/working-and-growing-up.md|3808
./content/2013/04/29/etc-shadow-passwords-with-go.md|542
./content/2013/04/30/mastering-the-art-of-context-switching.md|2001
./content/2013/04/14/why-i-dont-like-to-use-my-macbook-or-windows.md|1162
./content/2013/05/05/majoring-in-math.md|4855
./content/2013/05/12/thought-experiments.md|1864
./content/2013/05/13/potential.md|4228
./content/2013/02/10/opportunities.md|1661
./content/2013/02/12/using-c-libraries-with-go.md|828