Read SQLite database from the disk using sql.js

Question

I am able to read the file when it is being uploaded using - input type="file", but I want to read the SQLite file from a hard coded path. The documentation for sql.js does provide a solution as given below, but i'm not able to get it working. Please help.

var fs = require('fs');
var SQL = require('sql.js');
var filebuffer = fs.readFileSync('test.sqlite');
// Load the db
var db = new SQL.Database(filebuffer);

I hope its very simple and any one using sql.js can answer this.

Do you get any errors? I assume you're using nodejs (what with readFileSync and all), so you should tag the question with that — CodingIntrigue
– CodingIntrigue, Commented Jan 21, 2015 at 11:57
Where is your file located? If it is not in the current path, you should give exact path. But I think you already done that, are you? — mgokgoz
– mgokgoz, Commented Jan 21, 2015 at 12:08
Yes the example provide in the sql.js documentation uses Node.js, but is there a simpler solution to this? maybe using just the client-side scripting, because I want to access the file locally. — Div
– Div, Commented Jan 21, 2015 at 12:09

agershun · Accepted Answer · 2015-01-22 08:59:18Z

5

Probably the problem is in the location of sql.js and test.sqlite files. If you keep them in the same directory, write the name as './sql.js' './test.sqlite' with dot and slash:

var fs = require('fs');
var SQL = require('./sql.js');
var data = fs.readFileSync('./Chinook_Sqlite.sqlite');
var sqldb = new SQL.Database(data);

I just run the code above, and it is works fine with Node.js (node test.js). My directory has the following files:

test.js - file from above sample
sql.js - library file
Chinook_Sqlite.sqlite - Chinook test database

UPDATE 2 People from SQL.js recommend to use this code (see SQL.js wiki) for recent versions of SQL.js:

var xhr = new XMLHttpRequest();
xhr.open('GET', '/path/to/database.sqlite', true);
xhr.responseType = 'arraybuffer';

xhr.onload = function(e) {
    var uInt8Array = new Uint8Array(this.response);
    var db = new SQL.Database(uInt8Array);
    var contents = db.exec("SELECT * FROM my_table");
    // contents is now [{columns:['col1','col2',...], values:[[first row], [second row], ...]}]
};
xhr.send();

UPDATE 1 If you need to open SQLite files in the browser, you can use the code below (see the working example here):

<script src="sql.js"></script>
<script>
    function loadBinaryFile(path,success) {
        var xhr = new XMLHttpRequest();
        xhr.open("GET", path, true); 
        xhr.responseType = "arraybuffer";
        xhr.onload = function() {
            var data = new Uint8Array(xhr.response);
            var arr = new Array();
            for(var i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]);
            success(arr.join(""));
        };
        xhr.send();
    };

    loadBinaryFile('./Chinook_Sqlite.sqlite', function(data){
        var sqldb = new SQL.Database(data);
        // Database is ready
        var res = db.exec("SELECT * FROM Genre");
    });
</script>

edited Jan 22, 2015 at 8:59

answered Jan 21, 2015 at 14:23

agershun

4,10742 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Div Over a year ago

The thing is, I will be self hosting this asp.net MVC application in a windows form. Can I still use node.js? or to make things simpler is there any way to read a file from hard coded file path on client-side?

agershun Over a year ago

1) You can use iisnode package (github.com/tjanczuk/iisnode) to host Node.js under ASP.NET. 2) If you want to read Sqlite on the client (in browser), then you need a different code structure, like in the example alasql.org/demo/016sqlite (I posted the new code as an update to this answer):

agershun Over a year ago

3) If you need to specify you server file path to the browser, you need to setup your HTTP server (IIS or Node.js) to map the path from "http:// mysite/mypath/myfile.sqlite" to you server path like "/myserverpath/path/path/myfile.sqlite". This setup depends on your web server

Div Over a year ago

Thank you for this answer but I am getting this error : The page you are requesting cannot be served because of the extension configuration. If the page is a script, add a handler. If the file should be downloaded, add a MIME map. I am able to read a plain text file. How can I fix this?

Div Over a year ago

But when I do something similar to the example, I get this in the div element : {"filename":"dbfile_951670714","db":5287952,"Yb":{}}, but I am not able to read the .sqlite file as it gives the above mentioned error.

|

Collectives™ on Stack Overflow

Read SQLite database from the disk using sql.js

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related