# Create daily sqlite3 databases

**URL:** <https://discourse.nodered.org/t/create-daily-sqlite3-databases/85088>\
**Category:** General\
**Created:** [31 January 2024 18:24 UTC](https://discourse.nodered.org/t/create-daily-sqlite3-databases/85088 "2024-01-31T18:24:07Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ela](https://avatars.discourse-cdn.com/v4/letter/e/a9adbd/32.png) [@Ela](https://discourse.nodered.org/u/Ela)\
**Post date:** [31 January 2024 18:24 UTC](https://discourse.nodered.org/t/create-daily-sqlite3-databases/85088/1 "2024-01-31T18:24:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [31 January 2024 18:25 UTC](https://discourse.nodered.org/t/create-daily-sqlite3-databases/85088/2 "2024-01-31T18:25:34Z")

</div>

> [@Ela](#):
>
> I am trying to create daily sqlite3 databases.

Welcome to the forum.

Why do you want to create daily databases?

---

<div class="post-metadata">

**Author:** ![Ela](https://avatars.discourse-cdn.com/v4/letter/e/a9adbd/32.png) [@Ela](https://discourse.nodered.org/u/Ela)\
**Post date:** [31 January 2024 18:37 UTC](https://discourse.nodered.org/t/create-daily-sqlite3-databases/85088/3 "2024-01-31T18:37:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ghayne](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ghayne/32/39_2.png) [@ghayne](https://discourse.nodered.org/u/ghayne)\
**Post date:** [31 January 2024 18:39 UTC](https://discourse.nodered.org/t/create-daily-sqlite3-databases/85088/4 "2024-01-31T18:39:31Z")

</div>

Have you considered InfluxDB?

---

<div class="post-metadata">

**Author:** ![Ela](https://avatars.discourse-cdn.com/v4/letter/e/a9adbd/32.png) [@Ela](https://discourse.nodered.org/u/Ela)\
**Post date:** [31 January 2024 19:21 UTC](https://discourse.nodered.org/t/create-daily-sqlite3-databases/85088/5 "2024-01-31T19:21:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![edje11](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/edje11/32/572_2.png) [@edje11](https://discourse.nodered.org/u/edje11)\
**Post date:** [31 January 2024 20:00 UTC](https://discourse.nodered.org/t/create-daily-sqlite3-databases/85088/6 "2024-01-31T20:00:28Z")

</div>

> [@Ela](#):
>
> 1. copy the existing db file to another location and

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

---

<div class="post-metadata">

**Author:** ![Ela](https://avatars.discourse-cdn.com/v4/letter/e/a9adbd/32.png) [@Ela](https://discourse.nodered.org/u/Ela)\
**Post date:** [2 February 2024 19:09 UTC](https://discourse.nodered.org/t/create-daily-sqlite3-databases/85088/7 "2024-02-02T19:09:09Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [2 May 2024 19:09 UTC](https://discourse.nodered.org/t/create-daily-sqlite3-databases/85088/8 "2024-05-02T19:09:18Z")

</div>

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