sqlite file not updated under apps folder

Questions / Help
1

Hello,

When I ssh into the console, I see all the files that I deployed under app folder. The webapp I designed works well and the user interactions are recorded and saved in the sqlite database, which is also under app folder. The webapp displays the previous interactions suggesting that the data are being saved. However, when I tried to view the contents of the database by fetching it to my local machine, I found it empty. Also, the filesize of the database under apps folder has not changed. Can you please guide me where should I look for the updated database file. Are the codes copied to a different folder when executed?

I just started learning app development and have very limited knowledge. Your help will be highly appreciated

Thanks,
Aakash

2

What commands are you using to pull down the database? Also, is the database file zero bytes or does it just not have any data in it?

If you’re using SQLite’s WAL mode then changes can be stored next to the database in a file suffixed with -wal. If you pulled down just the database file then your changes could be in the separate WAL file.

3

I used the get command under sftp (flyctl ssh sftp · Fly Docs). The database is not zero bytes. It has multiple tables and the table which was supposed to save user’s response is empty. The other table that is used to display content have same values I enter before deployment. In other words, it is a copy of my deployed file and no new content was added to it.

I don’t think I am using the WAL mode. All my files are in the same folder (including database), and I do CRUD operation on database using SQLmodel (https://sqlmodel.tiangolo.com/). For now, I also didn’t create separate volumes for database and just used flyctl deploy to upload all the files.

BTW, while deploying, it created an image which was about 1.2 GB, although my files were < 25 mb. I think the data is being updated in that image but I don’t know how to access it

4

When I use the ls -lh command, the date of the database file is updated. However, the size is not changing. The data should have been saved somewhere, else the user would not have seen the updated content.

5

The image includes all the different Docker layers so it’s not just your individual files. If you’re using an ubuntu base or something like that then it can add quite a bit of size to the image.

The SQLite database has empty space in its pages so it’s only when you fill up pages that the database will grow in size. Pages are 4KB by default.

If you’re seeing data in your application then it must be getting saved somewhere on disk. You can also try using fly ssh console to log into the machine and then use the sqlite3 CLI to query against the database file to see if the data exists on the Fly instance.

6

Thanks a lot for the suggestions. I could only see partial data being stored in the database. Maybe, I will redeploy the app again while saving database on a separate volume.

7

Hey there! Just made an account to bump this thread with a lesson that cost me a few hours of confusion to learn.

The buzz around sqlite and rails in production brought me to litestack and fly recently. This stack has helped me rediscover a joy I last felt with coding when I discovered PICO-8. But point is: I’m a SQLite noob.

Turns out litestack enables WAL mode. So the backup instructions in the excellent Fly & Rails blog post don’t work with litestack.

fly sftp get /data/production.sqlite3

If there’s going to be more folks like me coming in cold via all this hype then it’s possible more of us will be stumbling there, so I think it’s worth considering adding something to the blog post even!

Here’s my rake task for this for reference.

namespace :db do
  desc "Download production database"
  task download: :environment do
    sh "fly sftp get /data/production/data.sqlite3"
    sh "fly sftp get /data/production/data.sqlite3-shm"
    sh "fly sftp get /data/production/data.sqlite3-wal"
    sh "mv data.sqlite3 db/development/data.sqlite3"
    sh "mv data.sqlite3-shm db/development/data.sqlite3-shm"
    sh "mv data.sqlite3-wal db/development/data.sqlite3-wal"
  end
end

Thanks for everything you’re doing in this space at Fly BTW. What a lovely, much needed contribution to the tooling landscape everyone involved in this little production sqlite adventure is making.

2 Likes