Using Node.js to generate dynamic word document using Database value

Question

I am trying to dynamically populate the WORD Document using npm docx. I am trying to read the data from the SQLite database but due to async node js property the values are not getting into the variable and it shows undefined. If I make the function synchronous the npm docx throws error and doesn't populate the document.

package.json

{
  "name": "demoName",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.19.0",
    "docx": "^5.1.1",
    "express": "^4.17.1",
    "md5": "^2.2.1",
    "sqlite3": "^4.2.0"
  }
}

index.js

const docx = require('docx');
var express = require('express');
var app = express();
var db = require("./database.js")

var bodyParser = require("body-parser");
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());

const { AlignmentType, Document, Footer, Header, HeadingLevel, Packer, Paragraph, TextRun, UnderlineType, Table, TableCell, TableRow } = docx;

app.get("/doc", async(req, res) => {
    var sql = "select * from DocDetails"
    var params = []
    //let DocDetailsData;
    //let DocDetailsData = [{docId: "Some Doc Id"}];
    const DocDetailsData = db.all(sql, params, (err, rows) => {

        if (err) {
          res.status(400).json({"error":err.message});
          return;
        }
        console.log(rows[0]);
        return rows[0];

    });
    console.log(DocDetailsData.docId);

    const doc = new Document();
    doc.addSection({
        children: [
            new Paragraph({
                children: [
                    new TextRun({
                        text: "DEMO TEST DOCUMENT"
                    }),
                    new TextRun({
                        text: DocDetailsData.docId,
                    }),
                ]
            }),
        ],
    });

    const b64string = await Packer.toBase64String(doc);
    res.setHeader('Content-Disposition', 'attachment; filename=My Document.docx');
    res.send(Buffer.from(b64string, 'base64'));
});

madeDoc = function(){


}

app.use(function(req, res){
    res.status(404);
});

var server = app.listen(4041, function () {
   var host = 'localhost'
   var port = server.address().port

   console.log("Example app listening at http://%s:%s", host, port)
})

database.js

var sqlite3 = require('sqlite3').verbose()
var md5 = require('md5')

const DBSOURCE = "db.sqlite"

let db = new sqlite3.Database(DBSOURCE, (err) => {
    if (err) {
      // Cannot open database
      console.error(err.message)
      throw err
    }else{
        console.log('Connected to the SQLite database.')
        db.run(`CREATE TABLE DocDetails (
            id INTEGER PRIMARY KEY,
            docId text NOT NULL,
            version float NULL,
            editedBy text NULL,
            editedDate text NULL,
            effectiveDate text NULL)`,
          (err) => {
              if (err) {
                  // Table already created
                  console.log('Table not created');
              }else{
                  console.log('Table created');
                  var insert = 'INSERT INTO DocDetails (docId, version, editedBy, editedDate, effectiveDate) VALUES (?,?,?,?,?)'
                  db.run(insert, ["NESS-RD-TEMP-EDCHB",2.1, "manab", "18-Jul-2017", "18-Jul-2020"])
              }
          })
    }
});


module.exports = db

If you go to localhost:4041/doc, a word document should get downloaded but it shows only one row and not the data from database. I need the database value to be populated in the doc. Thanks.

score 8 · Accepted Answer · answered Jun 14 '20 at 08:47

In order for this example to work, you need to understand how to work with asynchronous execution and callbacks. You cannot return anything from the callback and get it in the DocDetailsData variable as it would in synchronous code, because when you call the db.all method, the code continues to execute further, without waiting for the callback that you passed to it to work. Instead, you need to put the code for generating the doc file in the callback and do it in it. I hope that at least I could explain to you how it works. This is how your code will work correctly:

index.js

const docx = require('docx');
var express = require('express');
var app = express();
var db = require("./database.js")

var bodyParser = require("body-parser");
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());

const { AlignmentType, Document, Footer, Header, HeadingLevel, Packer, Paragraph, TextRun, UnderlineType, Table, TableCell, TableRow } = docx;

app.get("/doc", async (req, res) => {
    var sql = "select * from DocDetails"
    var params = []

    db.all(sql, params, async (err, rows) => {
        if (err) {
          res.status(400).json({"error":err.message});
          return;
        }

        const DocDetailsData = rows[0];

        const doc = new Document();
        doc.addSection({
            children: [
                new Paragraph({
                    children: [
                        new TextRun({
                            text: "DEMO TEST DOCUMENT"
                        }),
                        new TextRun({
                            text: DocDetailsData.docId,
                        }),
                    ]
                }),
            ],
        });

        const b64string = await Packer.toBase64String(doc);
        res.setHeader('Content-Disposition', 'attachment; filename=My Document.docx');
        res.send(Buffer.from(b64string, 'base64'));
    });
});


app.use(function(req, res){
    res.status(404);
});

var server = app.listen(4041, function () {
   var host = 'localhost'
   var port = server.address().port

   console.log("Example app listening at http://%s:%s", host, port)
});

database.js does not require changes

Using Node.js to generate dynamic word document using Database value

1 Answers1

Linked