# Problem retrieving annotations from sqlite3

**URL:** <https://pdfjs.community/t/problem-retrieving-annotations-from-sqlite3/1617>\
**Category:** Technical Support\
**Created:** [April 21, 2022, 7:38am UTC](https://pdfjs.community/t/problem-retrieving-annotations-from-sqlite3/1617 "2022-04-21T07:38:42Z")\
**Posts on this page:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![abel.manzanera](https://avatars.discourse-cdn.com/v4/letter/a/ea5d25/32.png) [@abel.manzanera](https://pdfjs.community/u/abel.manzanera)\
**Post date:** [April 21, 2022, 7:38am UTC](https://pdfjs.community/t/problem-retrieving-annotations-from-sqlite3/1617/1 "2022-04-21T07:38:42Z")

</div>

**Which product are you using?**  
PDF.js Express Plus

**PDF.js Express Version**  
Latest from npm

**Detailed description of issue**  
i’m use this scripts:

> **[GitHub - PDFTron/webviewer-annotations-sqlite3-sample: WebViewer sample that...](https://github.com/PDFTron/webviewer-annotations-sqlite3-sample)**
>
> WebViewer sample that shows saving annotations to an SQLite3 database in Node.js - GitHub - PDFTron/webviewer-annotations-sqlite3-sample: WebViewer sample that shows saving annotations to an SQLite...

with pdftron with no problems. I have modified the original code so that it creates a database per user and a table per pdf.  
When i try to use with pdf.jsExpress i can save the annotations, create the table, create the database per user but i can’t restore the annotations when refresh or view with other web navigator.

there’re a annotationsHandler.js:

* * *

const fs = require(‘fs’);  
const SQLite3 = require(‘sqlite3’).verbose();  
const TABLE = ‘annotations’;

module.exports = (app) =\> {

// Handle POST request sent to ‘/server/annotationHandler.js’  
app.post(’/server/annotationHandler.js’, (req, res) =\> {  
const documentId = req.query.documentId;  
const nif = req.query.nif;  
var nifDB = ‘/var/www/html/WebViewer/annotations/server/db/’+nif+’.db’;

```
 // Create a database if it doesn't exist
if (!fs.existsSync(nifDB)) {
  console.log('creem la bbdd '+nifDB);
  fs.writeFileSync(nifDB, '');
} 

const annotationId = JSON.parse(req.body).annotationId;
const xfdfString = JSON.parse(req.body).xfdfString.replace(/\'/g, `''`); // To escape single quote character in SQLite

const db = new SQLite3.Database(nifDB);
db.run(`CREATE TABLE IF NOT EXISTS ${TABLE} (documentId TEXT, annotationId TEXT PRIMARY KEY, xfdfString TEXT)`);
db.serialize(() => {
  const isDeleteCommand = /<delete>(.*)<\/delete>/s.test(xfdfString);

  let query;
  if (isDeleteCommand) {
    // Instead of saving the delete command, we can remove the row from the database
    query = `DELETE FROM ${TABLE} WHERE annotationId = '${annotationId}'`;
  } else {
    // Save document ID, annotation ID and XFDF string to database
    query = `INSERT OR REPLACE INTO ${TABLE} VALUES ('${documentId}', '${annotationId}', '${xfdfString}')`;
  }

  db.run(query, err => {
    if (err) {
      res.status(500);
    } else {
      res.status(200);
    }
    res.end();
  });
});
db.close();

```

});

// Handle GET request sent to ‘/server/annotationHandler.js’  
app.get(’/server/annotationHandler.js’, (req, res) =\> {  
const documentId = req.query.documentId;  
const nif = req.query.nif;  
var nifDB = ‘/var/www/html/WebViewer/annotations/server/db/’+nif+’.db’;  
const db = new SQLite3.Database(nifDB);  
// Read from the database and send the rows as a response  
db.all(`SELECT annotationId, xfdfString FROM ${TABLE} WHERE documentId = '${documentId}'`, (err, rows) =\> {  
if (err) {  
res.status(204);  
} else {  
res.header(‘Content-Type’, ‘application/json’);  
res.status(200).send(rows);  
}  
res.end();  
});  
db.close();  
});  
}

* * *

The annotationsHandler is called by the index.js that i paste here:

* * *

var viewerElement = document.getElementById(‘viewer’);  
const queryString = window.location.search;  
const urlParams = new URLSearchParams(queryString);  
var pdf = urlParams.get(‘pdf’);  
var nif = urlParams.get(‘nif’);  
var DOCUMENT\_ID = urlParams.get(‘pdfId’);  
WebViewer({  
path: ‘lib’,  
initialDoc: '[http://estapl02.uab.cat/client/llibres/’+pdf](http://estapl02.uab.cat/client/llibres/'+pdf),  
documentXFDFRetriever: async () =\> {  
const rows = await loadxfdfStrings(DOCUMENT\_ID);  
return JSON.parse(rows).map(row =\> row.xfdfString);  
}  
}, viewerElement).then(instance =\> {  
instance.UI.setLanguage(‘es’);  
instance.UI.setTheme(‘dark’);  
instance.UI.disableElements([‘downloadButton’]);  
instance.UI.disableElements([‘printButton’]);  
instance.UI.disableElements([‘toolbarGroup-Shapes’]);  
instance.UI.disableElements([‘toolbarGroup-Edit’]);  
instance.UI.disableElements([‘toolbarGroup-Insert’]);  
instance.UI.disableElements([‘toolbarGroup-Forms’]);  
instance.UI.disableElements([‘toolbarGroup-FillAndSign’]);

var docViewer = instance.docViewer;  
var annotManager = docViewer.getAnnotationManager();

// Save when annotation change event is triggered (adding, modifying or deleting of annotations)  
annotManager.addEventListener(‘annotationChanged’, function(annots, action, options) {  
// If the event is triggered by importing then it can be ignored  
// This will happen when importing the initial annotations from the server or individual changes from other users  
if (options.imported) return;

```
annotManager.exportAnnotCommand().then(function (xfdfStrings) {
  annots.forEach(function(annot) {
    savexfdfString(DOCUMENT_ID, annot.Id, xfdfStrings);
  });
});

```

});  
});

// Make a POST request with document ID, annotation ID and XFDF string  
var savexfdfString = function(documentId, annotationId, xfdfString) {  
return new Promise(function(resolve) {  
fetch(`/server/annotationHandler.js?documentId=${documentId}&nif=${nif}`, {  
method: ‘POST’,  
body: JSON.stringify({  
annotationId,  
xfdfString  
})  
}).then(function(res) {  
if (res.status === 200) {  
resolve();  
}  
});  
});  
};

// Make a GET request to get XFDF string  
var loadxfdfStrings = function(documentId) {  
return new Promise(function(resolve) {  
fetch(`/server/annotationHandler.js?documentId=${documentId}&nif=${nif}`, {  
method: ‘GET’  
}).then(function(res) {  
if (res.status === 200) {  
res.text().then(function(xfdfStrings) {  
resolve(xfdfStrings);  
});  
}  
});  
});  
};

* * *

---

_[View the full topic](https://pdfjs.community/t/problem-retrieving-annotations-from-sqlite3/1617)._