I'm trying to create a web application that can be used via a file:// URI. This means that I can't use AJAX to load binary files (without turning off security features in the browser, which I don't want to do as a matter of principle).

The application uses a SQLite database. I want to provide the database to a sql.js constructor, which requires it in Uint8Array format.

Since I can't use AJAX to load the database file, I could instead load it with <input type="file"> and FileReader.prototype.readAsArrayBuffer and convert the ArrayBuffer to a Uint8Array. And that's working with the following code:

input.addEventListener('change', function (changeEvent) {
  var file = changeEvent.currentTarget.files[0];
  var reader = new FileReader();
  reader.addEventListener('load', function (loadEvent) {
    var buffer = loadEvent.target.result;
    var uint8Array = new Uint8Array(buffer);
    var db = new sql.Database(uint8Array);
  });
  reader.readAsArrayBuffer(file);
});

However, <input type="file"> requires user interaction, which is tedious.

I thought I might be able to work around the no-AJAX limitation by using a build tool to convert my database file to a JavaScript object / string and generate a ".js" file providing the file contents, and then convert the file contents to a Uint8Array, somehow.

Psuedocode:

// In Node.js:
var fs = require('fs');
var sqliteDb = fs.readFileSync('path/to/sqlite.db');
var string = convertBufferToJsStringSomehow(sqliteDb);
fs.writeFileSync('build/db.js', 'var dbString = "' + string + '";');

// In the browser (assume "build/db.js" has been loaded via a <script> tag):
var uint8Array = convertStringToUint8ArraySomehow(dbString);
var db = new sql.Database(uint8Array);

In Node.js, I've tried the following:

var TextEncoder = require('text-encoding').TextEncoder;
var TextDecoder = require('text-encoding').TextEncoder;
var sql = require('sql.js');

var string = new TextDecoder('utf-8').decode(fs.readFileSync('path/to/sqlite.db'));
// At this point, I would write `string` to a ".js" file, but for
// the sake of determining if converting back to a Uint8Array
// would work, I'll continue in Node.js...
var uint8array = new TextEncoder().encode(string);
var db = new sql.Database(uint8array);
db.exec('SELECT * FROM tablename');

But when I do that, I get the error "Error: database disk image is malformed".

What am I doing wrong? Is this even possible? The database disk image isn't "malformed" when I load the same file via FileReader.

I'm trying to create a web application that can be used via a file:// URI. This means that I can't use AJAX to load binary files (without turning off security features in the browser, which I don't want to do as a matter of principle).

The application uses a SQLite database. I want to provide the database to a sql.js constructor, which requires it in Uint8Array format.

Since I can't use AJAX to load the database file, I could instead load it with <input type="file"> and FileReader.prototype.readAsArrayBuffer and convert the ArrayBuffer to a Uint8Array. And that's working with the following code:

input.addEventListener('change', function (changeEvent) {
  var file = changeEvent.currentTarget.files[0];
  var reader = new FileReader();
  reader.addEventListener('load', function (loadEvent) {
    var buffer = loadEvent.target.result;
    var uint8Array = new Uint8Array(buffer);
    var db = new sql.Database(uint8Array);
  });
  reader.readAsArrayBuffer(file);
});

However, <input type="file"> requires user interaction, which is tedious.

I thought I might be able to work around the no-AJAX limitation by using a build tool to convert my database file to a JavaScript object / string and generate a ".js" file providing the file contents, and then convert the file contents to a Uint8Array, somehow.

Psuedocode:

// In Node.js:
var fs = require('fs');
var sqliteDb = fs.readFileSync('path/to/sqlite.db');
var string = convertBufferToJsStringSomehow(sqliteDb);
fs.writeFileSync('build/db.js', 'var dbString = "' + string + '";');

// In the browser (assume "build/db.js" has been loaded via a <script> tag):
var uint8Array = convertStringToUint8ArraySomehow(dbString);
var db = new sql.Database(uint8Array);

In Node.js, I've tried the following:

var TextEncoder = require('text-encoding').TextEncoder;
var TextDecoder = require('text-encoding').TextEncoder;
var sql = require('sql.js');

var string = new TextDecoder('utf-8').decode(fs.readFileSync('path/to/sqlite.db'));
// At this point, I would write `string` to a ".js" file, but for
// the sake of determining if converting back to a Uint8Array
// would work, I'll continue in Node.js...
var uint8array = new TextEncoder().encode(string);
var db = new sql.Database(uint8array);
db.exec('SELECT * FROM tablename');

But when I do that, I get the error "Error: database disk image is malformed".

What am I doing wrong? Is this even possible? The database disk image isn't "malformed" when I load the same file via FileReader.

• javascript
• sqlite
• character-encoding
• arraybuffer
• uint8array

• What is result of fs.readFileSync('path/to/sqlite.db')? – guest271314 Commented Sep 26, 2016 at 20:34
• @guest271314 The result is <Buffer 53 51 4c 69 ... >. – Jackson Ray Hamilton Commented Sep 26, 2016 at 20:35
• What is the result of string? – guest271314 Commented Sep 26, 2016 at 20:37
• A gigantic string (string.length is 3202320). – Jackson Ray Hamilton Commented Sep 26, 2016 at 20:39
• Though var uint8array = new TextEncoder().encode(string); is not encoding string back to Uint8Array correctly? Note, you can create a .js file using Blob or File object by setting type to "application/javascript" see stackoverflow./questions/39315017/… – guest271314 Commented Sep 26, 2016 at 20:40

2 Answers 2

Using the following code, I was able to transfer the database file's contents to the browser:

// In Node.js:
var fs = require('fs');
var base64 = fs.readFileSync('path/to/sqlite.db', 'base64');
fs.writeFileSync('build/db.js', 'var dbString = "' + base64 + '";');

// In the browser (assume "build/db.js" has been loaded via a <script> tag):
function base64ToUint8Array (string) {
  var raw = atob(string);
  var rawLength = raw.length;
  var array = new Uint8Array(new ArrayBuffer(rawLength));
  for (var i = 0; i < rawLength; i += 1) {
    array[i] = raw.charCodeAt(i);
  }
  return array;
}
var db = new sql.Database(base64ToUint8Array(dbString));
console.log(db.exec('SELECT * FROM tablename'));

And that's working with the following code:

input.addEventListener('change', function (changeEvent) {
  var file = changeEvent.currentTarget.files[0];
  var reader = new FileReader();
  reader.addEventListener('load', function (loadEvent) {
    var buffer = loadEvent.target.result;
    var uint8Array = new Uint8Array(buffer);
    var db = new sql.Database(uint8Array);
  });
  reader.readAsArrayBuffer(file);
});

However, <input type="file"> requires user interaction, which is tedious.

Using current working approach would be less tedious than attempting to create workarounds. If user intends to use application, user can select file from their filesystem to run application.