Search code examples
node.jssqlitecronforevernode-sqlite3

Update SQLite database without restarting application

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 ?

Solution

  • 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.)