SQLite database not updating properly.

I’ve been working on a personal project for nearly 10 months. It’s a Node.js application with an SQLite database. Recently, I managed to deploy the application to Fly.io, and it’s been running fine. However, I’m facing an issue with my SQLite database.

The problem is that when I update my database with “X” data, it seems to work initially, but after a few minutes, the changes are lost, and it reverts to the state before I deployed the application. I’ve noticed this especially when the application crashes and restarts.

this is my fly.toml file .

 DATABASE_URL = "sqlite3:///sqllite/volumename/mazoetbe.db"

  source = "mazoetbe"
  destination = "sqllite/volumename"

fly volumes list

ID                      STATE   NAME            SIZE    REGION  ZONE    ENCRYPTED       ATTACHED VM     CREATED AT 
vol_24yeo0ezydpwlzjr    created mazoetbe        1GB     ams     6d3a    true            6e82933c0293d8  1 week ago 

fly machine list

ID              NAME            STATE   REGION  IMAGE                                           IP ADDRESS                      VOLUME                  CREATED                  LAST UPDATED             APP PLATFORM    PROCESS GROUP   SIZE
6e82933c0293d8  damp-fire-7976  started ams     mazoethub:deployment-01HD476DV8S7Q1BW6XZCTVBW7Y fdaa:3:5ee:a7b:c6ef:d85d:40cb:2 vol_24yeo0ezydpwlzjr    2023-10-06T10:07:43Z     2023-10-19T20:29:39Z     v2              app             shared-cpu-1x:256MB

I have already tried a lot of options mentioned, including Mount and Prep for Deployment. In here I didn’t get, where can i locate lib/tasks/fly.rake file. So I add that path to my app code base. But even that didn’t make any changes.

Has anyone encountered a similar issue when using SQLite with Fly.io? I’d appreciate any insights or suggestions to ensure the data remains persistent in my database after updates and application restarts.

Destination should start with a slash.

Is the open call for your database using DATABASE_URL? See Databases · Fly Docs for some examples.

If all else fails, temporarily modify your fly.toml to read:

  auto_stop_machines = false 

Then run fly ssh console. From there, verify that the sqlite3 database is actually being placed on the volume:

ls -l /sqllite/volumename/mazoetbe.db

when i run

ls -l /sqllite/volumename/mazoetbe.dd

there is no such a file exsist there. but when i run

ls -l sqllite/volumename/mazoetbe.db

(without the slash) that return this output.

-rwxr-xr-x 1 chrome chrome 24576 Oct 20 10:05 sqllite/volumename/mazoetbe.db

Is the open call for your database using DATABASE_URL?

No. This is my DB.js file.

const sqlite3 = require("sqlite3").verbose();
const path = require("path");
const dbPath = path.resolve(__dirname, "./volumename/mazoetbe.db");
//? connected to the databse
const DB = new sqlite3.Database(dbPath, sqlite3.OPEN_READWRITE, (error) => {
  if (error) {
    console.error(`Error msg - ${error}`);
  } else {
    console.log(`connected to the database`);

however, i have changed the fly.toml file to this.

  source = "mazoetbe"
  destination = "/sqllite/volumename"

And deployed.
Something I noticed after adding destination. In my fly dashboard UI, in volumes section, block usage area populated.

But when i add some data, as usual, stays few minutes and disappear.

Try this instead:

const dbPath = new URL(process.env.DATABASE_URL).pathname

i have to change my DATABASE_URL to ‘‘sqlite:///MazoetHub_backend/sqllite/volumename/mazoetbe.db’’ to work in local environment after changes.


after that i changed fly.toml file twice. 1st time

 DATABASE_URL = "sqlite3:///sqllite/volumename/mazoetbe.db"

then chnaged to

 DATABASE_URL = "sqlite3:///MazoetHub_backend/sqllite/volumename/mazoetbe.db"

According to my env file in my local environment.
this is my logs in fly.io.

[info] INFO Umounting /dev/vdb from /sqllite/volumename

2023-10-20T17:51:31.154 app[6e82933c0293d8] ams [info] WARN hallpass exited, pid: 314, status: signal: 15 (SIGTERM)

2023-10-20T17:51:31.176 app[6e82933c0293d8] ams [info] 2023/10/20 17:51:31 listening on [fdaa:3:5ee:a7b:c6ef:d85d:40cb:2]:22 (DNS: [fdaa::3]:53)

2023-10-20T17:51:32.153 app[6e82933c0293d8] ams [info] [ 731.228210] reboot: Restarting system

2023-10-20T17:51:32.370 runner[6e82933c0293d8] ams [info] machine did not have a restart policy, defaulting to restart

2023-10-20T17:51:33.326 app[6e82933c0293d8] ams [info] [ 0.038365] PCI: Fatal: No config space access function found

2023-10-20T17:51:33.549 app[6e82933c0293d8] ams [info] INFO Starting init (commit: 15238e9)…

2023-10-20T17:51:33.573 app[6e82933c0293d8] ams [info] INFO Mounting /dev/vdb at /sqllite/volumename w/ uid: 1000, gid: 1000 and chmod 0755

2023-10-20T17:51:33.577 app[6e82933c0293d8] ams [info] INFO Resized /sqllite/volumename to 1069547520 bytes
Error msg - Error: SQLITE_CANTOPEN: unable to open database file

didn’t connect to databse.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.