Import Existing Data into LiteFS
If you're moving an existing application over to LiteFS, you probably have some existing data in your SQLite database you don't want to lose.
The Fly.io Docs has information about the LiteFS Import Command:
Instructor: 0:00 If you're moving an existing application over to LiteFS, you probably have some existing data in your SQLite database that you don't want to lose. In our case, this is just a simple counter app, but I thought it'd be useful for me to show you how you can accomplish an import once you've made this migration.
0:17 This is the app before I deployed LiteFS. You'll see that the count is four. I hit refresh, and now the count is set to zero. That's because we lost some data, which is something no business person ever wants to hear.
0:30 What we're going to do is use the LiteFS Import command so that we can import the old data, because we do still have that old data. Let's SSH into our box so that we can look at the persistent volumes. Here we have our fly.toml, we have the app, and it's specified here.
0:47 I can simply say fly ssh console. I'm going to execute the command bash so that we run into bash and not just a simple shell. This will give us a couple more commands that are useful. I just make a habit out of doing this. Once it creates a secure tunnel between me and our virtual machine that's currently running, we can take a look at the data directory.
1:10 This, we see our litefs directory. That's where our new database is found, and this is where our old database is. The old database is still around. We haven't deleted it. We just made a new one that doesn't have all the data that's in here.
1:23 We need to import the data that's in here into the database that's under this litefs directory. Another interesting thing to look at while we're here is the litefs directory, which is where we have that virtual file system. Here is where the virtual database lives. This is what our application is accessing, but this file doesn't actually exist.
1:43 The real file is under this LiteFS directory, which we configured right here. To do this import, we're going to do litefs import -name, and the name is going to be the name of the database that we have configured that we're accessing.
2:00 If we look at our dockerfile, we have our database URL. This piece right here is what determines that name. It's the file name of the database that we're creating, so the name is going to be sqlite.db.
2:13 The source is going to be our old database, which is under data, so we'll say data/sqlite.db. This will very quickly import all of the data from the old database into the new one. Now, if I come over here and I hit refresh, we're going to see we have our old data all back and it works wonderfully.
2:37 I personally have had a little experience with this. I lost over half a million rows in a SQLite database. It was the worst day ever. Then I realized, oh, I do still have all of this data. I ran this import. It ran in milliseconds, and I got all my data back. This is how you accomplish importing your old data into your new LiteFS-backed SQLite database.
3:01 In review, to get all of this imported, you simply fly SSH into your virtual machine, and then you run litefs import, specify the name argument is the name of the database that you have running, and then the path to the old database that you had before. In just a couple of milliseconds, you should have all of your data back, and it works great.