# SQLite data into PDF report
**URL:** https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782
**Category:** General
**Created:** [29 July 2022 12:59 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782 "2022-07-29T12:59:14Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [29 July 2022 12:59 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/1 "2022-07-29T12:59:14Z")
Hi.
I want to display SQLite data in a PDF report in a table format. I can get the PDF file to work as well as the table in in the PDF file with PDFMake. I can also get the SQL data out of the data basis with queries.
How can i get the SQLite query data into the PDF Table?
Or alternatively PDFMake uses JSON how do i get the sql query data into JSON?
Kindly assist.
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [29 July 2022 13:15 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/2 "2022-07-29T13:15:58Z")
The sql query should return a JSON object, to make that a JSON string pass it through a Json node.
---
### Author:  [@UnborN](https://discourse.nodered.org/u/UnborN)
#### Post date: [29 July 2022 13:47 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/3 "2022-07-29T13:47:27Z")
Just to calrify ... are you using the PDFMake node [node-red-contrib-pdfmake](https://flows.nodered.org/node/node-red-contrib-pdfmake) ?
if yes .. i dont think there is a need to make the `msg.payload` into a JSON string
but you have to structure your sqlite db's output msg to be in the table structure the pdfmake node expects.
Here is a demo flow of how to make a table :
```auto
[{"id":"f8a7f83d80d07ff5","type":"inject","z":"54efb553244c241f","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":300,"y":3960,"wires":[["924e9713d93837d6"]]},{"id":"924e9713d93837d6","type":"function","z":"54efb553244c241f","name":"fake data","func":"\nmsg.payload = {\n content: [\n\n { text: 'This is my Sqlite Table:', fontSize: 14, bold: true, margin: [0, 20, 0, 8] },\n {\n style: 'tableExample',\n table: {\n headerRows: 1,\n body: [\n [{ text: 'Header 1', style: 'tableHeader' }, { text: 'Header 2', style: 'tableHeader' }, { text: 'Header 3', style: 'tableHeader' }],\n ['Sample value 1', 'Sample value 2', 'Sample value 3'],\n ['Sample value 1', 'Sample value 2', 'Sample value 3'],\n ['Sample value 1', 'Sample value 2', 'Sample value 3'],\n ['Sample value 1', 'Sample value 2', 'Sample value 3'],\n ['Sample value 1', 'Sample value 2', 'Sample value 3'],\n ]\n },\n layout: 'lightHorizontalLines'\n },\n\n ],\n styles: {\n header: {\n fontSize: 18,\n bold: true,\n margin: [0, 0, 0, 10]\n },\n subheader: {\n fontSize: 16,\n bold: true,\n margin: [0, 10, 0, 5]\n },\n tableExample: {\n margin: [0, 5, 0, 15]\n },\n tableHeader: {\n bold: true,\n fontSize: 13,\n color: 'black'\n }\n },\n defaultStyle: {\n // alignment: 'justify'\n }\n\n};\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":480,"y":3960,"wires":[["2c4abf85948ac92c"]]},{"id":"2c4abf85948ac92c","type":"pdfmake","z":"54efb553244c241f","name":"","outputType":"Buffer","inputProperty":"payload","options":"{}","outputProperty":"payload","x":640,"y":3960,"wires":[["3f2acb163d057cda"]]},{"id":"3f2acb163d057cda","type":"file","z":"54efb553244c241f","name":"","filename":"c:/share/pdfTest.pdf","filenameType":"str","appendNewline":false,"createDir":false,"overwriteFile":"true","encoding":"none","x":840,"y":3960,"wires":[[]]}]
```
_if you need help restructuring the db data, then send us a sample msg of a few rows to play with_
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [29 July 2022 14:05 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/4 "2022-07-29T14:05:29Z")
Many thanks.
This part i got working. Your example also works.
My problem is how to i get the SQL query data instead of your fake data. I get the data from the query but how do i get it into json table code
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [29 July 2022 14:10 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/5 "2022-07-29T14:10:22Z")
This is the data that i get from the sql query. How do i get that data into the json code/node and into the pdf table?
---
### Author:  [@UnborN](https://discourse.nodered.org/u/UnborN)
#### Post date: [29 July 2022 14:17 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/6 "2022-07-29T14:17:42Z")
If you hover over the `msg.payload` array (in the Debug window) and use the button **Copy value**
and share a sample of 10 rows (SQL : LIMIT 10) to see ways it can be re-structured.
maybe with Jsonata or with Javascript
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [29 July 2022 14:17 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/7 "2022-07-29T14:17:56Z")
How do i replace the sample values in your example with the real values from the SQL query?
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [29 July 2022 14:21 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/8 "2022-07-29T14:21:46Z")
[{"Probe\_no":"Probe0010","Time":"7/12/2022, 12:08:10 AM","Site":"Mo-Chicks\_1","Area":"Delmas","Shift\_no":"Night","Bat\_percentage":"86","latitude":-26.06963,"longitude":28.607964999999997,"accuracy":11.699999809265137},{"Probe\_no":"Probe0010","Time":"7/12/2022, 12:12:25 AM","Site":"Mo-Chicks\_1","Area":"Delmas","Shift\_no":"Night","Bat\_percentage":"85","latitude":-26.069961666666668,"longitude":28.608556666666665,"accuracy":6.5},{"Probe\_no":"Probe0010","Time":"7/12/2022, 12:14:51 AM","Site":"Mo-Chicks\_1","Area":"Delmas","Shift\_no":"Night","Bat\_percentage":"84","latitude":-26.06991833333333,"longitude":28.60752166666667,"accuracy":6.300000190734863},{"Probe\_no":"Probe0010","Time":"7/12/2022, 12:15:32 AM","Site":"Mo-Chicks\_1","Area":"Delmas","Shift\_no":"Night","Bat\_percentage":"84","latitude":-26.069635,"longitude":28.60752,"accuracy":2.799999952316284},{"Probe\_no":"Probe0010","Time":"7/12/2022, 12:17:39 AM","Site":"Mo-Chicks\_1","Area":"Delmas","Shift\_no":"Night","Bat\_percentage":"84","latitude":-26.069729999999996,"longitude":28.608585,"accuracy":4.800000190734863},{"Probe\_no":"Probe0010","Time":"7/12/2022, 1:05:48 AM","Site":"Mo-Chicks\_1","Area":"Delmas","Shift\_no":"Night","Bat\_percentage":"84","latitude":-26.069713333333333,"longitude":28.608646666666665,"accuracy":6.900000095367432},{"Probe\_no":"Probe0010","Time":"7/12/2022, 1:09:03 AM","Site":"Mo-
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [29 July 2022 14:27 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/9 "2022-07-29T14:27:10Z")
Create the table body array with the header row, then push the sql rows to the body array.
e.g.
```auto
[{"id":"f8a7f83d80d07ff5","type":"inject","z":"30af2d3e.d94ea2","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"one\":\"Sample value 1\",\"two\":\" Sample value 2\",\"three\":\"Sample value 3\"},{\"one\":\"Sample value 1\",\"two\":\" Sample value 2\",\"three\":\"Sample value 3\"},{\"one\":\"Sample value 1\",\"two\":\" Sample value 2\",\"three\":\"Sample value 3\"}]","payloadType":"json","x":110,"y":1700,"wires":[["924e9713d93837d6"]]},{"id":"924e9713d93837d6","type":"function","z":"30af2d3e.d94ea2","name":"fake data","func":"let payload = {\n content: [\n\n { text: 'This is my Sqlite Table:', fontSize: 14, bold: true, margin: [0, 20, 0, 8] },\n {\n style: 'tableExample',\n table: {\n headerRows: 1,\n body: [\n [{ text: 'Header 1', style: 'tableHeader' }, { text: 'Header 2', style: 'tableHeader' }, { text: 'Header 3', style: 'tableHeader' }],\n ]\n },\n layout: 'lightHorizontalLines'\n },\n\n ],\n styles: {\n header: {\n fontSize: 18,\n bold: true,\n margin: [0, 0, 0, 10]\n },\n subheader: {\n fontSize: 16,\n bold: true,\n margin: [0, 10, 0, 5]\n },\n tableExample: {\n margin: [0, 5, 0, 15]\n },\n tableHeader: {\n bold: true,\n fontSize: 13,\n color: 'black'\n }\n },\n defaultStyle: {\n // alignment: 'justify'\n }\n\n};\nObject.values(msg.payload).forEach(obj =>{\n payload.content[1].table.body.push(Object.values(obj));\n})\n\nmsg.payload = payload;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":390,"y":1680,"wires":[["729b9bbd.0f0b44"]]},{"id":"729b9bbd.0f0b44","type":"debug","z":"30af2d3e.d94ea2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":690,"y":1640,"wires":[]}]
```
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [29 July 2022 14:51 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/10 "2022-07-29T14:51:28Z")
Not sure how to do it but i will give it a try 🙂
---
### Author:  [@UnborN](https://discourse.nodered.org/u/UnborN)
#### Post date: [29 July 2022 16:04 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/13 "2022-07-29T16:04:54Z")
Nice solution .. maybe you can populate the table headers also dynamically with
```javascript
...
table: {
headerRows: 1,
body: [
Object.keys(msg.payload[0]).map( key => { return { text: key, style: 'tableHeader' }})
]
},
...
```
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [29 July 2022 16:38 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/14 "2022-07-29T16:38:22Z")
You could then also concat the rows at same time.
```auto
...
table: {
headerRows: 1,
body: [
Object.keys(msg.payload[0]).map( key => { return { text: key, style: 'tableHeader' }})
].concat(
Object.values(msg.payload).map(obj => Object.values(obj))
)
},
...
```
Then no need to push the rows.
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [30 July 2022 06:50 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/15 "2022-07-30T06:50:13Z")
Many thanks.
I really appreciate the help. It works perfectly 🙂
I must now just get the page to landscape and figure out how to get some of the data on a graph.
Again many thanks.
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [30 July 2022 08:46 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/17 "2022-07-30T08:46:49Z")
where would i put the following 2 lines in your example code?
pageSize: 'A4',
pageOrientation: 'landscape'
i tried everything cant get it to work 🙂
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [30 July 2022 08:51 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/18 "2022-07-30T08:51:21Z")
[https://pdfmake.github.io/docs/0.1/document-definition-object/page/](https://pdfmake.github.io/docs/0.1/document-definition-object/page/)
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [30 July 2022 08:55 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/19 "2022-07-30T08:55:12Z")
Thanks i have that page. But where in the code do i put it?
No matter where i put it it does not work.
This is how i have it but for some reason its not working?
let payload = {
```
content: [
{
pageSize: 'A4',
pageOrientation: 'landscape',
text: 'Probe Data:', fontSize: 14, bold: true, margin: [0, 20, 0, 8] },
{
style: 'tableExample',
table: {
headerRows: 1,
body: [
Object.keys(msg.payload[0]).map( key => { return { text: key, style: 'tableHeader' }})
]
},
layout: 'lightHorizontalLines'
},
],
styles: {
header: {
fontSize: 18,
bold: true,
margin: [0, 0, 0, 10]
},
subheader: {
fontSize: 16,
bold: true,
margin: [0, 10, 0, 5]
},
tableExample: {
margin: [0, 5, 0, 15]
},
tableHeader: {
bold: true,
fontSize: 13,
color: 'black'
}
},
defaultStyle: {
// alignment: 'justify'
}
```
};
Object.values(msg.payload).forEach(obj =\>{
payload.content[1].table.body.push(Object.values(obj));
})
msg.payload = payload;
return msg;
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [30 July 2022 08:59 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/20 "2022-07-30T08:59:49Z")
Above content.
Ps, on every post you have made, the code is all messed up because you don't use the code fence...
In order to make code readable and usable it is necessary to surround your code with three backticks (also known as a left quote or backquote `````)
````auto
```
code goes here
```
````
You can edit and correct your post by clicking the pencil ✏ icon.
See this post for more details - [How to share code or flow json](https://discourse.nodered.org/t/how-to-share-code-or-flow-json/506)
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [30 July 2022 09:02 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/21 "2022-07-30T09:02:56Z")
Try
```auto
let payload = {
pageSize: 'A4',
pageOrientation: 'landscape',
content: [
{ text: 'This is my Sqlite Table:', fontSize: 14, bold: true, margin: [0, 20, 0, 8] },
{
style: 'tableExample',
table: {
headerRows: 1,
body: [
Object.keys(msg.payload[0]).map( key => { return { text: key, style: 'tableHeader' }})
].concat(
Object.values(msg.payload).map(obj => Object.values(obj))
)
},
layout: 'lightHorizontalLines'
},
],
styles: {
...
```
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [30 July 2022 09:14 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/22 "2022-07-30T09:14:29Z")
Apologies.....i restarted node-red now its working.
Again many thanks.
---
### Author:  [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)
#### Post date: [30 July 2022 10:03 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/23 "2022-07-30T10:03:29Z")
Thanks for starting this thread , i had same issue and was stuck.
Now i have a perfect pdf report (in landscape!) created and sent by email automatically by getting data from MySQL.
[Next page](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782.md?page=2)