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;'