# SQLite - Add support for multiple queries/statements via msg.topic
**URL:** https://discourse.nodered.org/t/sqlite-add-support-for-multiple-queries-statements-via-msg-topic/1637
**Category:** Feature Requests
**Tags:** database
**Created:** [17 July 2018 11:43 UTC](https://discourse.nodered.org/t/sqlite-add-support-for-multiple-queries-statements-via-msg-topic/1637 "2018-07-17T11:43:35Z")
**Posts on this page:** 12
**Page:** 1
### Author:  [@tmdoit](https://discourse.nodered.org/u/tmdoit)
#### Post date: [17 July 2018 11:43 UTC](https://discourse.nodered.org/t/sqlite-add-support-for-multiple-queries-statements-via-msg-topic/1637/1 "2018-07-17T11:43:35Z")
SQLite is great for local storage, jugglery with every single node with db operation creates mess in tab space.
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [17 July 2018 15:03 UTC](https://discourse.nodered.org/t/sqlite-add-support-for-multiple-queries-statements-via-msg-topic/1637/2 "2018-07-17T15:03:53Z")
> [@tmdoit](#):
>
> jugglery with every single node with db operation creates mess in tab space.
Would you care to expand on this since I have no idea what you mean.
---
### Author:  [@tmdoit](https://discourse.nodered.org/u/tmdoit)
#### Post date: [18 July 2018 14:19 UTC](https://discourse.nodered.org/t/sqlite-add-support-for-multiple-queries-statements-via-msg-topic/1637/3 "2018-07-18T14:19:20Z")
I want to pass to msg.topic multiple SQL statements and execute at once:
`CREATE TABLE City ( Id INTEGER PRIMARY KEY, Name STRING ); INSERT INTO City VALUES(null, 'Warsaw'); INSERT INTO City VALUES(null, 'Poznan');`
Instead execute them separately.
Sample below:
[{"id":"b21d1f1d.62a0e","type":"inject","z":"18570be0.597ed4","name":"","topic":"CREATE TABLE IF NOT EXISTS City ( Id INTEGER PRIMARY KEY, Name STRING ); INSERT INTO City VALUES(null, Warsaw); INSERT INTO City VALUES(null, Poznan);","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":460,"wires":[["98dcf78e.647bf8"]]},{"id":"98dcf78e.647bf8","type":"sqlite","z":"18570be0.597ed4","mydb":"92ac279b.86a7e8","sqlquery":"msg.topic","sql":"","name":"","x":250,"y":460,"wires":[["b60c54de.ba4118"]]},{"id":"7a8f4239.008f7c","type":"inject","z":"18570be0.597ed4","name":"","topic":"SELECT \* FROM City","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":500,"wires":[["43d78354.b535ec"]]},{"id":"43d78354.b535ec","type":"sqlite","z":"18570be0.597ed4","mydb":"92ac279b.86a7e8","sqlquery":"msg.topic","sql":"","name":"","x":250,"y":500,"wires":[["13d9bc86.e19903"]]},{"id":"b60c54de.ba4118","type":"debug","z":"18570be0.597ed4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":390,"y":460,"wires":[]},{"id":"13d9bc86.e19903","type":"debug","z":"18570be0.597ed4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":390,"y":500,"wires":[]},{"id":"92ac279b.86a7e8","type":"sqlitedb","z":"","db":"test"}]
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [18 July 2018 17:57 UTC](https://discourse.nodered.org/t/sqlite-add-support-for-multiple-queries-statements-via-msg-topic/1637/4 "2018-07-18T17:57:49Z")
How would you expect the output to look like? Would the output from all the statements be bunched together in an array? What if the middle statement caused an error, how would you expect it to be handled? I would never code a program that fed one statement after another, I would always do an error check.
And what is wrong with using multiple nodes?
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [18 July 2018 18:10 UTC](https://discourse.nodered.org/t/sqlite-add-support-for-multiple-queries-statements-via-msg-topic/1637/5 "2018-07-18T18:10:28Z")
> [@tmdoit](#):
>
> I want to pass to msg.topic multiple SQL statements and execute at once:
> `CREATE TABLE City ( Id INTEGER PRIMARY KEY, Name STRING ); INSERT INTO City VALUES(null, 'Warsaw'); INSERT INTO City VALUES(null, 'Poznan');`
> Instead execute them separately.
Well, you're CREATE TABLE would only normally happen once wouldn't it so that shouldn't be much of an issue.
In regard to inserts. SQLite supports batch inserts using begin and end transaction functions. [Squeezing Performance from SQLite: Insertions | by Jason Feinstein | Medium](https://medium.com/@JasonWyatt/squeezing-performance-from-sqlite-insertions-971aff98eef2)
When you don't explicitly start a transaction, SQLlite adds one for every statement because it is on the end transaction that it actually writes to disk rather than just to memory. So wrapping your inserts in a transaction will give you some benefit. However, if you use a prepared statement to do your inserts, you should get nearly the same performance anyway.
---
### Author:  [@tmdoit](https://discourse.nodered.org/u/tmdoit)
#### Post date: [18 July 2018 20:11 UTC](https://discourse.nodered.org/t/sqlite-add-support-for-multiple-queries-statements-via-msg-topic/1637/6 "2018-07-18T20:11:50Z")
This is not real world example, it's just to point the problem, there are use cases to work that way.
@TotallyInformation could you show me how to do a transaction with above example?
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [18 July 2018 20:25 UTC](https://discourse.nodered.org/t/sqlite-add-support-for-multiple-queries-statements-via-msg-topic/1637/7 "2018-07-18T20:25:38Z")
> [@tmdoit](#):
>
> @TotallyInformation could you show me how to do a transaction with above example?
OK, so here is a raw SQL example taken from [SQLite Transaction Explained By Practical Examples](http://www.sqlitetutorial.net/sqlite-transaction/)
```sql
BEGIN TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE account_no = 100;
UPDATE accounts
SET balance = balance + 1000
WHERE account_no = 200;
INSERT INTO account_changes(account_no,flag,amount,changed_at)
values(100,'-',1000,datetime('now'));
INSERT INTO account_changes(account_no,flag,amount,changed_at)
values(200,'+',1000,datetime('now'));
COMMIT;
```
It's been a while since I did any SQL but I think that if you separate out your create, then form a string such as:
```sql
BEGIN TRANSACTION; INSERT INTO City VALUES(null, 'Warsaw'); INSERT INTO City VALUES(null, 'Poznan'); COMMIT;
```
Then the data will only be committed to disk on the COMMIT statement. I think that you can probably do 999/# fields INSERTs in a single transaction.
The SQL is just a string so you can use JavaScript to form it quite easily.
See also [Transaction](https://www.sqlite.org/lang_transaction.html)
---
### Author:  [@dceejay](https://discourse.nodered.org/u/dceejay)
#### Post date: [18 July 2018 21:11 UTC](https://discourse.nodered.org/t/sqlite-add-support-for-multiple-queries-statements-via-msg-topic/1637/8 "2018-07-18T21:11:26Z")
you could also form up several queries into an array of strings in msg.payload - feed that through a split node to create one message per item and then through a change node to move the payload to topic before feeding to the sqlite node.
---
### Author:  [@tmdoit](https://discourse.nodered.org/u/tmdoit)
#### Post date: [19 July 2018 05:46 UTC](https://discourse.nodered.org/t/sqlite-add-support-for-multiple-queries-statements-via-msg-topic/1637/9 "2018-07-19T05:46:58Z")
@TotallyInformation I've already tried that, It doesn't work It process only first statement - when I run batch for the second time I get an error "Error: SQLITE\_ERROR: cannot start a transaction within a transaction".
[{"id":"b105cd3e.5f79e","type":"inject","z":"8dfa453c.668ce8","name":"","topic":"SELECT \* FROM City","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":200,"wires":[["55b00e57.50a49"]]},{"id":"55b00e57.50a49","type":"sqlite","z":"8dfa453c.668ce8","mydb":"92ac279b.86a7e8","sqlquery":"msg.topic","sql":"","name":"","x":330,"y":200,"wires":[["bafb47cc.da6e68"]]},{"id":"bafb47cc.da6e68","type":"debug","z":"8dfa453c.668ce8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":470,"y":200,"wires":[]},{"id":"8f217c0d.40fe9","type":"sqlite","z":"8dfa453c.668ce8","mydb":"92ac279b.86a7e8","sqlquery":"msg.topic","sql":"","name":"","x":330,"y":160,"wires":[["98534300.962ea"]]},{"id":"a42881d5.6351f","type":"inject","z":"8dfa453c.668ce8","name":"","topic":"BEGIN TRANSACTION; CREATE TABLE IF NOT EXISTS City ( Id INTEGER PRIMARY KEY, Name STRING ); INSERT INTO City VALUES(null, 'Warsaw'); INSERT INTO City VALUES(null, 'Poznan'); COMMIT;","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":160,"wires":[["8f217c0d.40fe9"]]},{"id":"98534300.962ea","type":"debug","z":"8dfa453c.668ce8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":470,"y":160,"wires":[]},{"id":"92ac279b.86a7e8","type":"sqlitedb","z":"","db":"test"}]
@dceejay ... and after - join node, this is my current solution but instead one node (sqlite) there are four (split, change, sqlite, join).
Repeating this multiple times in flow creates unnecessary mess.
---
### Author:  [@tmdoit](https://discourse.nodered.org/u/tmdoit)
#### Post date: [19 July 2018 06:34 UTC](https://discourse.nodered.org/t/sqlite-add-support-for-multiple-queries-statements-via-msg-topic/1637/10 "2018-07-19T06:34:55Z")
OK, I've did some research and resolved the issue, I'll send PR.
Here it is: [https://github.com/node-red/node-red-nodes/pull/466](https://github.com/node-red/node-red-nodes/pull/466)
---
### Author:  [@tmdoit](https://discourse.nodered.org/u/tmdoit)
#### Post date: [19 July 2018 08:58 UTC](https://discourse.nodered.org/t/sqlite-add-support-for-multiple-queries-statements-via-msg-topic/1637/11 "2018-07-19T08:58:56Z")
Don't know if you get notification on post edit so pinging back, above is a link to PR.
---
### Author:  [@dceejay](https://discourse.nodered.org/u/dceejay)
#### Post date: [19 July 2018 09:27 UTC](https://discourse.nodered.org/t/sqlite-add-support-for-multiple-queries-statements-via-msg-topic/1637/12 "2018-07-19T09:27:56Z")
Yes - I see all the channels :-)... comments incoming - on github