Create daily sqlite3 databases

General
1

I am trying to create daily sqlite3 databases.
I have a flow that

  1. copy the existing db file to another location and
  2. deletes the copied file
  3. creates a new sqlite3 db file.
  4. Create tables
    Step 1,2 and 3 no issues. but when trying to create tables, it say table already exists. But the db file is empty with no tables. Any suggestions help!

For Sqlite Node I am using node-red-node-sqlite

2

Welcome to the forum.

Why do you want to create daily databases?

3

our sqlite database file grows very big since its a timeseries db and its tough to query them.

4

Have you considered InfluxDB?

5

not really. thanks for the recommendations. But any insight on the problem i'm facing?

6

How do you copy the file? You can't simple copy a sqlite file, most of the time it becomes unusable after copying due the fact that is already opened by the NR sqlitenode and the file can't be opened multiply times.
You can make a backup of the sql file with the exec node and the .backup option, for example /home/nodered/dbs/yourdata.sqlite ".backup /home/nodered/dbs/yourdata.sqlite.backup"

For the same reason I think you can't delete the file because it's in use by the the node-red-node-sqlite node and those strange thinks happen.

Best is to choose for a db that can handle large data sets, sqlite is not suitable for that.
Have look at Influxdb or mysql.

If you want still want to use sqlite, I think this will work, not tested.

  1. Make a backup of the database file.
  2. Empty the existing dbase file. (with the DELETE query)
  3. Ready to use again
7

Thank you for your thoughts. I will try the backup option