After experimenting in Chrome, I found success using the html5sql library. Additionally, I created a codepen that showcases pure-HTML5 integration with a clever Promise-based query function, which you can check out here.
function retrieveDatabaseInfo(callback){
html5sql.process("SELECT * FROM sqlite_master WHERE name NOT LIKE 'sqlite\\_%' escape '\\' AND name NOT LIKE '\\_%' escape '\\'", function(txTables, rsTables, tables){
if (!tables.length) return callback(null, []);
tables.forEach(function(table){
var tableQuery = table.sql.split(',');
tableQuery[0] = tableQuery[0].replace(new RegExp('create\\s+table\\s+' + table.name + '\\s*\\(', 'i'),'');
table.fields = tableQuery.map(function(item){
return item.trim().split(/\s/).shift();
})
.filter(function(item){
return (item.indexOf(')') === -1)
});
});
callback(null, tables)
}, callback);
}
Your (error, tables) callback will receive data structured like this:
[{
"type": "table",
"name": "Users",
"tbl_name": "Users",
"rootpage": 6,
"sql": "CREATE TABLE Users(\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n firstName VARCHAR(255),\n lastName VARCHAR(255),\n email VARCHAR(255),\n created TIMESTAMP DEFAULT (DATETIME('now','localtime'))\n)",
"fields": [
"id",
"firstName",
"lastName",
"email",
"created"
]
}]
Take note of the fields section. This method functions even when there are no records. While the regex/string parsing could be enhanced and type information could potentially be extracted as well, this approach worked effectively for my requirements. An alternative SQL method once you have the field names is as follows:
SELECT TYPEOF(id) as id, TYPEOF(firstName) AS firstName , TYPEOF(lastName) AS lastName, TYPEOF(email) AS email, TYPEOF(created) AS created FROM Users;