# Struggling with sqlite3

**URL:** <https://discourse.nodered.org/t/struggling-with-sqlite3/78411>\
**Category:** General\
**Tags:** sqlite\
**Created:** [14 May 2023 11:41 UTC](https://discourse.nodered.org/t/struggling-with-sqlite3/78411 "2023-05-14T11:41:41Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [14 May 2023 11:41 UTC](https://discourse.nodered.org/t/struggling-with-sqlite3/78411/1 "2023-05-14T11:41:41Z")

</div>

I need to drop a table and recreate it, after which I need to insert 30k records.  
Sometimes dropping works - but then does not recreate the table.  
Nothing in database. Database complains - 'database is locked'.  
When I recreate the table directly on cli, it works.

The 30k records should be a begin/commit transaction (?) for speed?

Currently I use a template node and prepare all statements and put that in `msg.topic` and set the sqlite node to batch without response. But whatever step I take separately it does not appear to function properly.

Is there a "best approach" tactic for working with sqlite because I am losing my patience with the inconsistent behaviour because I don't understand what is happening.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [14 May 2023 13:02 UTC](https://discourse.nodered.org/t/struggling-with-sqlite3/78411/2 "2023-05-14T13:02:28Z")

</div>

It would be helpful if you gave some background like

1. device running NR, the OS and amount of memory
2. version of NR and node.js
3. a copy of the flow

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [14 May 2023 13:14 UTC](https://discourse.nodered.org/t/struggling-with-sqlite3/78411/3 "2023-05-14T13:14:01Z")

</div>

Also, how are you trying to drop the table? I would concentrate on that problem intially to try and work out what is going on. Are you sure it has completed any existing activity when you try to drop it?

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [18 May 2023 06:24 UTC](https://discourse.nodered.org/t/struggling-with-sqlite3/78411/4 "2023-05-18T06:24:55Z")

</div>

I gave up and went with mysql instead and then i gave up on that as well and just used arrays with json objects instead 🤪 in the end it performs faster.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [18 May 2023 08:37 UTC](https://discourse.nodered.org/t/struggling-with-sqlite3/78411/5 "2023-05-18T08:37:48Z")

</div>

If you only have a small amount of data then that can be a good solution.

---

<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:** [1 June 2023 08:37 UTC](https://discourse.nodered.org/t/struggling-with-sqlite3/78411/6 "2023-06-01T08:37:51Z")

</div>

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