sqlite file not updated under apps folder

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

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.

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

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.

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.

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.