Update SQLite database without restarting application

Question

I have an NodeJS application that run on a production server with forever.

That application use a third-party SQLite database which is updated every morning with a script triggered by a cron, who download the db from an external FTP to the server.

I spend some time before realising that I need to restart my server every time the file is rapatriated otherwise there is no change in the data used by my application (I guess it's cached in memory at starting).

// sync_db.sh
wget -l 0 ftp://$REMOTE_DB_PATH --ftp-user=$USER --ftp-password=$PASSWORD \
  --directory-prefix=$DIRECTORY -nH
forever restart 0 // <- Here I hope something nicer...

What can I do to refresh the database without restarting the app ?

score 1 · Accepted Answer · answered Jan 27 '18 at 09:13

1

You must not overwrite a database file that might have some open connection to it (see How To Corrupt An SQLite Database File).

The correct way to overwrite a database is to download to a temporary file, and then copy it to the actual database with the backup API, which takes care of proper transactional behaviour. The simplest way to do this is with the sqlite3 command-line shell:

sqlite3 $DIRECTORY/real.db ".restore $DOWNLOADDIRECTORY/temp.db"

(If your application manually caches data, you still have to tell it to reload it.)

answered Jan 27 '18 at 09:13

CL.

173,858
17
217
259

Thanks for your advices and interesting reading. Many way to corrupt a SQLite db... It seems much better to use .restore command indeed. – TGrif Jan 27 '18 at 11:23
Works like a charm. Thanks again. – TGrif Jan 29 '18 at 08:51

Update SQLite database without restarting application

1 Answers1