loading sqlite database in javascript with sql.js

Question

Can someone provide a simple complete example of loading an existing sqlite database not using node.js.

Assume that the sql db is sitting in same location as index.html

example:

I tried this example but "contents" is undefined. Also, I would not know how to access the data in "contents"? I could really use a complete example.

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();

Answer 1

I know this is old, but here you go my friend. You were right there, just a bit of tweaking. I am not sure if you are using the SQL.js library from GitHub but please do as it solves a lot of the browser security issues and makes the SQL side of things much easier on the brain.

If you didn't create the source or have some UTF issues the exceptions will be thrown. I wrote this in a night so I haven't run more than a few functions but I am assuming callbacks will be required to prevent SQLite issues during async processes. This is my first time using SQLite or the SQL.js library so I just don't know yet.

IMPORTANT! This is a LOCAL solution only, it has more blatant vulnerabilities than a high school locker room. In no way should this ever be used on anything that is exposed to the internet.

This is all declared at the top of my class, not within a function. This is purposeful as I run multiple queries and didn't want the overhead of loading/unloading the object if it got too large.

Notice the fully qualified path on the source...relative paths didn't work for me.

var xhrLocal = new XMLHttpRequest();
xhrLocal.open('GET', 'http://localhost/mp3/data/media.sqlite', true);
xhrLocal.responseType = 'arraybuffer';

var localData;

xhrLocal.onload = function(e) {
  var uInt8Array = new Uint8Array(this.response);
  localData = new SQL.Database(uInt8Array);
};

xhrLocal.send();

At this point you have the database loaded into an object called localData and you can query it from anywhere. Here is a query I wrote to get Genre info.

function FillGenreLists() {

    var sqlSel = 'SELECT * FROM GenreData';
    var data = localData.exec(sqlSel);

    var output = [];
    $.each(data[0].values, function(key, value)
    {
        output.push('<option value="'+ value[0] +'">'+ value[1] +'</option>');
    });

    $('#selGenres').html(output.join(''));
}

The output from the SQL call is generally an array of arrays, don't worry about changing that, just output the result of your SQL call to the console and note the return fields and values, from there just use $.each to your hearts content.

Here is another query, same premise but with the goal of creating a SQL statement to push into MS SQL server and get FreeDB data about artists that are in my local collection.

Note: This could all be done in a single call by querying my local sqlite table, generating the sql and pushing it to the MS SQL using a different conn or even better by utilizing a generic proc but let's keep it simple for now.

function PrepareMSSQLFilteredFreeDBTables(StartLetter, EndLetter, TempTableName) {

    var sqlSel = "SELECT * FROM ArtistData WHERE ArtistText BETWEEN '" + StartLetter + "' AND '" + EndLetter + "' ORDER BY ArtistText";
    var data = localData.exec(sqlSel);

    $('.array-cols').append('SELECT * INTO ' + TempTableName + ' FROM FreeDB WHERE DARTIST IN (');

    var iLen = (data[0].values.length - 1);

    $.each(data[0].values, function(a, b) {
        var sRes;
        if (a === iLen) { sRes = "'" + b[1].replace("'", "''") + "')"; }
        else { sRes = "'" + b[1].replace("'", "''") + "', "; }
        $('.array-cols').append(sRes);
    });
}