Web SQL get column list from table

To get the columns of a table, execute PRAGMA table_info(table_name):

PRAGMA table_info()

Return a single row for each column of the named table. The columns of the returned data set are:

• cid: Column id (numbered from left to right, starting at 0)
• name: Column name
• type: Column declaration type.
• notnull: True if 'NOT NULL' is part of column declaration
• dflt_value: The default value for the column, if any.

Unfortunately, Chrome blocks all PRAGMAs, so this doesn't work in WebSQL.

In WebSQL, you can access only tables that were created by your app, so you should just remember which columns your tables have.

Alternatively, you can just try to read from the table:

SELECT * FROM table_name LIMIT 1

With the LIMIT clause, this will be very efficient because you read only some random record. (Except if you have some very big blob in that record.)

In chrome, this worked for me with html5sql. I also made a codepen that uses pure-HTML5 with a cool Promise-based query function, here.

function getDB(cb){    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 cb(null, []);        tables.forEach(function(table){            var s = table.sql.split(',');            s[0] = s[0].replace(new RegExp('create\\s+table\\s+' + table.name + '\\s*\\(', 'i'),'');            table.fields = s.map(function(i){                return i.trim().split(/\s/).shift();            })            .filter(function(i){                return (i.indexOf(')') === -1)            })        });        cb(null, tables)    }, cb);}

This will hit your (error, tables) callback 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"    ]}]

Note the fields section. This works, even if there are not records. The regex/string parsing could probably use some improvement, and you could probably grab type-info with it too, but this seemed to work with all my usecases. An alternate method once you know the fieldnames, in SQL:

SELECT TYPEOF(id) as id, TYPEOF(firstName) AS firstName , TYPEOF(lastName) AS lastName, TYPEOF(email) AS email, TYPEOF(created) AS created FROM Users;