Unclear how to use litefs with existing database

I’ve been trying to get Volumes and LiteFS set up using the LiteFS Getting Started.

It’s a bit confusing that the Getting Started doesn’t use Volumes. What’s the point of replicating a database that doesn’t persist? Or am I misunderstanding? Are volumes needed?

Another problem with the guide is that it assumes you are starting from scratch. In my case I have an existing database.

Here’s roughly what i have:

fly.toml

[mounts]
  source = "social_data"
  destination = "/data"

litefs.yml

# The path to where the SQLite database will be accessed.
mount-dir: "/dbdata"

# The path to where the underlying volume mount is.
data-dir: "/data/db"

Dockerfile

RUN mkdir -p /dbdata /data/db
ADD social.db /data/db/social.db

Note that I’m copying social.db to the volume’s subfolder. This will only happen the first time I deploy, then I’ll remove that line.

My app then loads /dbdata/social.db

But before I get to that point it fails with this:

initializing consul: key= url=https://:bd17c53d-d238-7dab-72e9-d1d462378652@consul-fra-3.fly-shared.net/quiet-mountain-5747-4nml165rrngqvp2y/ hostname=e83d90fb advertise-url=http://e83d90fb.vm.quiet-mountain-5747.internal:20202

2022-11-18T18:59:04.478 app[e83d90fb] lhr [info] cannot open store: open databases: open database("social.db"): verify database file: database checksum (82f3bcb26aa2c3e7) does not match latest LTX checksum (0000000000000000)

I think this is litefs not liking that I have a file already. I’m not sure though.

One confusing thing about this setup is that it seems like I need 3 “folders”:

  • /data is created by the volume
  • /data/db is a subfolder where I copy my database
  • /dbdata is the virtual folder that litefs creates to mirror /data/db (I think)

And then my app reads from /dbdata/social.db

I might have something set up wrong here, please let me know.

My guess would be because of the way litefs works, it cannot yet start up on an existing non-litefs sqlite.db.

As a one time migration, you can test if attach + insert works with litefs: SQLite: Easiest way to copy a table from one database to another? - Stack Overflow


If requirement is for more than the existing ephemeral 8GBs that every Fly instance gets, and if all litefs Fly instances may go down at once, then attaching Volumes is a viable strategy.

Generally, I agree that folks would sleep better at night if their litefs instances lived on persistent volumes rather than ephemeral ones.

I’ve moved off of using litefs for now and am just using volumes which works for my use case. Take my comment as feedback for documentation.

Speaking of doing a migration, it’s really unclear what the best way to do that kind of thing is on Fly.io at all. Like, how do you run a one-time script against a Fly.io app? The only way I can think is make it so that on deployment a bash script or something runs, and then delete that the first time you deploy. Would be good to have docs on recommendation on how to do this sort of thing.

That’s a good point. I avoided volumes to focus the instructions on LiteFS but I can see how that’s confusing. I’ll update the guide to include volumes when I update it for the LiteFS v0.3.0 release.

We do have plans to release a managed storage layer so you won’t need volumes for small to midsized databases but as of right now it’s recommended to use volumes.

This is a shortcoming of LiteFS right now and we’ve been wrestling with the best way to solve it. LiteFS calculates a rolling checksum of the database after every transaction, however, basic file copies don’t operate using transactions. I think this could be implemented pretty easily as long as no other applications are using the database at the same time. I added an issue (#192) and I’ll get that into the next release after v0.3.0.

The naming of the folders adds to this confusion. Someone else recently opened an issue (#164) about this. You could make it so you save LiteFS data to the volumes directly (/data) instead of a subfolder. I may change mount-dir to fuse-dir to avoid confusion about FUSE mounts vs volume mounts.

I appreciate the feedback! LiteFS still has rough edges so every bit of feedback helps to get it polished in the long run.

If you’re trying to do a one-time upload of data, you can set the Dockerfile’s ENTRYPOINT to sleep inf and then you can log in and run any setup stuff you need to.

For incremental migrations, I’d suggest using incrementally-numbered SQL migration files and track where which one your database is on by using the built-in PRAGMA user_version.

3 Likes

Another option for initial migrations is to use the sqlite3 CLI’s .dump command to get a SQL file and then run that in a sqlite3 CLI session against LiteFS.

2 Likes

Thanks for the responses @benbjohnson2. Hopefully others who are running into similar issues will find this thread useful.

The closest thing in Fly is, the release_command directive which runs once, in a single VM, before every deploy. In your case, however, I don’t see how even the release_command helps…