SQLite database not updating properly.

[manojfer]

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 .

[env]
 DATABASE_URL = "sqlite3:///sqllite/volumename/mazoetbe.db"

[[mounts]]
  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.

[rubys]

@manojfer

destination = "sqllite/volumename"

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

[manojfer]

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.

[[mounts]]
  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.

[rubys]

@manojfer

const dbPath = path.resolve(__dirname, "./volumename/mazoetbe.db");

Try this instead:

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

[manojfer]

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

DATABASE_URL=‘sqlite:///MazoetHub_backend/sqllite/volumename/mazoetbe.db’

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

[env]
 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.