Given that most people are probably not dynamically generating SQL queries, but are rather using a small number of queries and then using ? to fill in the arguments, we can probably get away with just keeping a global cache of prepared statements, In principle this should speed it up pretty significantly, at the cost of increased memory usage.

I know you're probably too busy and possibly not interested in this, but I thought I'd at least record a possible wish here for support of the synchronous API (which was spec'd but apparently not implemented, at least in Chrome or Safari apparently).

See indexeddbshim/IndexedDBShim#296 for one possible use case.

The WebSQL transaction steps list:

Open a new SQL transaction to the database, and create a SQLTransaction object that represents that transaction. If the mode is read/write, the transaction must have an exclusive write lock over the entire database. If the mode is read-only, the transaction must have a shared read lock over the entire database. The user agent should wait for an appropriate lock to be available.

Since the read lock is "shared", it appears that read transactions can run in parallel with other read transactions--something which could be helpful to performance. (Note that this is different from your already existing readOnly check which avoids the commit step.)

Can't module resolve 'websql' in typescript

In browsers you can't read for some odd reason from a virtual tables if you do a readTransaction and that is your first transaction. I don't know how I should describe this better but here is an example.

function output() {
  console.log(arguments);
}
function searchVirtualTable() {
  connection.readTransaction(function(tx) {
    tx.executeSql('SELECT * FROM testing WHERE two MATCH ?', ['Man'], output, output);
  });
}

var connection = window.openDatabase('hello', '1.0', 'world', 1024 * 1024 * 5);

if (localStorage.getItem('created') !== 'true') {
  connection.transaction(function(tx) {
    tx.executeSql(
      'CREATE VIRTUAL TABLE IF NOT EXISTS testing USING fts3(one, two, tokenize=porter)',
      [],
      function(tx, result) {
        tx.executeSql('INSERT INTO testing (one, two) VALUES ("Hello", "World"), ("Mega", "Man")');
        localStorage.setItem('created', 'true');
      }
    );
  });
}

// This won't work because this is the first transaction and it's a readTransaction and it tries to read from a virtual table
searchVirtualTable();

// A fix for this that always ensures that all virtual tables gets initialized
connection.transaction(function(tx) {
  tx.executeSql(
    'SELECT name, sql from sqlite_master WHERE type = ?',
    ['table'],
    function(tx, result) {
      var table, i, j;

      for (i = 0, j = result.rows.length; i < j; i++) {
        table = result.rows.item(i);

        if (/^CREATE VIRTUAL TABLE/i.test(table.sql)) {
          tx.executeSql('SELECT * FROM ' + table.name + ' LIMIT 0');
        }
      }
    }
  );
});

// This will now work for some odd reason
searchVirtualTable();

So my thoughts were to put the code inside browser.js.

module.exports = function openDatabase() {
  var connection = window.openDatabase.apply(window, arguments);
  connection.transaction(function(tx) {
    tx.executeSql(
      'SELECT name, sql from sqlite_master WHERE type = ?',
      ['table'],
      function(tx, result) {
        var table, i, j;

        for (i = 0, j = result.rows.length; i < j; i++) {
          table = result.rows.item(i);

          if (/^CREATE VIRTUAL TABLE/i.test(table.sql)) {
            tx.executeSql('SELECT * FROM ' + table.name + ' LIMIT 0');
          }
        }
      }
    );
  });
  return connection;
};

What do you think about that? Websql is pretty hard because there are so badly documented. This bug took me about 3 days to solve 😄

Currently, everything around versioning and migrations is not supported.

The WebSQL spec, indicates that any string, including the empty string, can be used (and ought to be treated case-sensitively):

All strings including the empty string are valid database names. Database names must be compared in a case-sensitive manner.

Implementations can support this even in environments that only support a subset of all strings as database names by mapping database names (e.g. using a hashing algorithm) to the supported set of names.

However, node-sqlite3 databases, according to the API allow the following:

Valid values are filenames, ":memory:" for an anonymous in-memory database and an empty string for an anonymous disk-based database. Anonymous databases are not persisted and when closing the database handle, their contents are lost.

Filenames do not necessary allow for every type of string, though I suppose it will vary by platform. In addition, as per the above, node-sqlite3 interprets an empty string and ":memory:" in a special manner which at the least may be unexpected for users.

I am guessing you may not wish to escape some or all database names in order to accommodate this (since the file names will be less readable), so if that is the case, my suggestion is to at least document what will happen.

A similar warning should perhaps be added about NULL bytes when making exec calls as this restriction is not listed in the WebSQL spec nor SQLite.

While I'm asking questions, I also wonder what you think of using sqlite3.cached.Database (though note it doesn't cache empty string or ":memory:" databases).

Since readTransactions don't need a lock on the database, they can potentially run in parallel. I.e. a readwrite transaction runs solo, but a readTransaction can run at the same time as any number of other readTransactions.

I don't know if this violates the de-facto standard of WebSQL, though, so it would need to be tested in Safari and Chrome before being implemented.

I have a client who is interested in using Web SQL API for testing in a browser, without need for the Cordova framework itself. Built-in Web SQL is not suitable for reasons such as:

• no support for numbered parameters (?1, ?2, ...)
• deprecation and removal is wanted (WICG/interventions#46)
• some other features such as FTS5, R-Tree, and JSON1 are not supported

I think the obvious solution is to use SQL.js.

While the implementation should be straightforward I think it would be nice to avoid duplicated efforts if possible.

Given the following code:

const openDatabase = require("websql");

const db = openDatabase(":memory:", "1.0", "test", 1);

try {
  db.transaction(txn => {
    console.log(1);
    txn.executeSql("SELECT 1", [], (txn, result) => {
      console.log(2);
      throw new Error("boom");
    }, (txn, error) => {
      console.log(3, error);
      return true;
    });
  }, error => {
    console.log(4, error);
  }, () => {
    console.log(5);
  });
} catch (error) {
  console.log(6, error);
}

As per the spec of the processing model for asynchronous transactions, I would expect the following output:

1
2
4 Error: boom

And for the transaction to be rolled back. Specifically, I am referring to item 6.6 here:

If the callback was invoked and raised an exception, jump to the last step in the overall steps.

Instead, I get the following output:

1
2
/tmp/foo/dbtxn.js:11
      throw new Error("boom");
            ^

Error: boom
    at SQLTask.sqlCallback (/tmp/foo/dbtxn.js:11:13)
    at /tmp/foo/node_modules/websql/lib/websql/WebSQLTransaction.js:70:19
    at checkDone (/tmp/foo/node_modules/websql/lib/sqlite/SQLiteDatabase.js:50:7)
    at /tmp/foo/node_modules/websql/lib/sqlite/SQLiteDatabase.js:59:7
    at Statement.<anonymous> (/tmp/foo/node_modules/websql/lib/sqlite/SQLiteDatabase.js:21:5)

And the whole process terminates with an exit code of 1.

I read the README and saw that you didn't want to make this a full polyfill but it would be awesome. I'm not 100% sure about this but I think flash has sqlite built-in. It would be awesome if we could implement an flash adapter. I don't think should be a major task todo...

They claim to be faster and better in all ways - https://www.npmjs.com/package/better-sqlite3 - are they a viable alternative to existing node-sqlite3?

Hi,

I'm finding your project very helpful for IndexedDBShim, thank you.

I think I'm in need of configuring the transaction duration. You use immediate there which is reasonable, but I'm wondering whether you'd be open to adding a means for the user to provide a callback which would allow continuing on only when it invoked a continuation callback supplied to it, e.g., so as to allow an extra-long transaction which could handle a rollback that included a set of SQL statements which were added over a longer period of time.

(And while we're discussing configuring the SQLite behavior, FYI, I made my own light SQLiteDatabase class which just adds the ability to alter SQLite's busyTimeout, trace, and profile config which I mention in case you felt like exposing this as it is not really a new API either per se, but this was easy enough for me to implement without mucking around with your code.)

I'm tracing through the code for a bug I opened in Expo and led me to this repo because they have a revendor of your code.

What I am trying to do is avoid doing chained callbacks and use async/await, for me to do that I wrapped some of the WebSQL code so that it uses the promise API to resolve the callbacks.

Almost everything goes well except when I chain two executesql in a single transaction where I await. I am suspecting it may be immediate triggering the execution of the SQL tasks, but when it does it, it also completes the transaction since the queue is empty already.

I've created a snack that shows this. But I am unsure how to fix it.

placing tried out sample code below

import React, { Component } from 'react';


class App extends Component {
    render() {
        var PouchDB = require('pouchdb');
        PouchDB.plugin(require('pouchdb-adapter-node-websql'));

        **var db = new PouchDB('mydatabase.db', { adapter: 'websql' });**

please give me suggestion to sort out this issue..

Hello, I was using react-native-sqlite-2 and it has a dependency to websql (node-websql). When I tried to build my app on ci/cd deployment, the pipeline failed and shows this warning:

// the following sqlite tarball (which is 4.2.0) is giving 403 status / forbidden
// probably not found on the server or might have been deleted)

node-pre-gyp http 403 https://mapbox-node-binary.s3.amazonaws.com/sqlite3/v4.2.0/node-v83-linux-x64.tar.gz

I believe it happened because of the sqlite3 version used by websql, since I don’t have sqlite3 installed on my project. The temporary solution I go with is modifying the yarn.lock to make websql use the sqlite3@^5.0.2, then I tried to rerun the pipeline and it works well

Would you be able to update the sqlite3 (or review my PR) regarding this issue? Thanks in advance

As you are well aware, Web SQL is on its way out and is being slowly deprecated. Web SQL access in insecure contexts is deprecated as of Chromium 105 at which time a warning message will be shown in the Chrome DevTools Issue panel. It's planned to fully remove it in later versions. You might want to update your README and let people know it can no longer be used in the browser soon.

I have a case where I need to run specific SQLite syntax not available in WebSQL, as well as run it inside a browser. Was thinking if it's possible to run node-websql with sql.js out of the box, instead of using the node-dependent sqlite3 module.

Imagined this would work by just replacing sqlite3 with sql.js.. tried it out but didn't seem to work. No errors thrown and no luck in debugging either; 0cd0b89.

Any ideas on what could be wrong here? Guessing this could be a neat feature to include in main repo as well if it works good. Thanks.

I have a very simple node module, in which i have used websql node package. below is the code:

productwebsql.js:
const openDatabase = require('websql');
const db = openDatabase('mydb.db', '1.0', 'description', 1);

module.exports = {
init: function( callback ){
var sqlQuery = "CREATE TABLE IF NOT EXISTS PRODUCT( CODE TEXT, DESCRIPTION TEXT, PRICE TEXT )";
db.transaction( function( tx ){
tx.executeSql( sqlQuery, [], function( data ){
console.log( 'Query: ' + sqlQuery + ' PRODUCT table created.' );
if( callback != undefined )
{
callback();
}
}, function( error ){
console.error( error, "error while executing " + sqlQuery + " : message : " + error.message );
return true;
} );
});
},
insert: function( productArray, callback ){
if( productArray )
{
var sqlQuery = "INSERT OR REPLACE INTO PRODUCT( CODE, DESCRIPTION, PRICE ) VALUES( ?, ?, ? )";
var args = productArray || [];
for ( var argumentCounter = 0; argumentCounter < args.length; argumentCounter++ )
{
if(args[ argumentCounter ] == undefined ||args[ argumentCounter ] == null ){
args[ argumentCounter ] = "";
}
if(isNaN(args[ argumentCounter ])){
args[ argumentCounter ] = args[ argumentCounter ].toString();
}
}
db.transaction(function(tx){
tx.executeSql( sqlQuery, args, success, error );
});

        function success( tx, result ){
            console.log("Query : " + sqlQuery );
            console.log( "Data inserted successfully" );
            if( callback != undefined )
            {
                callback();
            }
        }
        function error( tx, error ){
            console.error( error, "error while executing " + sqlQuery + " : message : " + error.message );
            return true;
        }
    }
    else
    {
        console.log( 'Please provide the product data' );
        return true;
    }
},
getAllProducts: function( callback ){
    var sqlQuery = "SELECT * FROM PRODUCT";
    db.transaction(function(tx){
        tx.executeSql( sqlQuery, [], success, error );
    });
    
    function success( tx, result ){
        console.log( "Query : " + sqlQuery );
        callback( result.rows );
    }
    
    function error( tx, error ){
        console.error( error, "error while executing " + sqlQuery + " : message : " + error.message );
        return true;
    }
}

}

and using webpack I am trying to bundle it with below webpack.config.js:
const path = require( 'path' );
const webpack = require( 'webpack' );

module.exports = {
target: 'web',
entry: './productwebsql.js',
output:{
path: path.resolve( __dirname, 'dist' ),
filename: 'product-webpack.bundle.js',
library: 'product'
},
optimization: {
minimize: true
}
}
my package.json is as below:
{
"name": "productwebsql",
"version": "1.0.0",
"description": "",
"main": "productwebsql.js",
"scripts": {
"test": "echo "Error: no test specified" && exit 1"
},
"author": "",
"license": "MIT",
"dependencies": {
"websql": "^1.0.0"
},
"devDependencies": {
"webpack": "^4.43.0"
}
}

When I run webpack command, it gives an error "Module not found: Error: Can't resolve 'websql'"
To fix that issue, I modified the webpack.config.js with an entry externals:{ 'websql': 'websql' } and the error disappeared but when I try to include that bundle.js in my hrml it says "Uncaught ReferenceError: websql is not defined".

I am sure, I am missing or following something wrong. Need some help in fixing this issue or a pointer.

thanks in advance.

Browser's WebSQL and SQLite support Common Table Expressions, but node-websql does not return any result.

Reproducible:

import openDatabase from "websql";

const db = openDatabase(':memory:', '1.0', '', 1);

db.transaction((txn) => {
  txn.executeSql(`
      WITH data as (
        SELECT 1 + 1 as a, 2 + 2 as b
      )
      SELECT b FROM data;
  `, [], (tx, result) => {
      console.log(result.rows._array)
    })
  }
);

The reason

According to this regular expression, 'SELECT' is required to begin a query; otherwise, no results are returned

Workaround

db.transaction((txn) => {
  txn._websqlDatabase._db._db.all(`
      WITH data as (
        SELECT 1 + 1 as a, 2 + 2 as b
      )
      SELECT b FROM data;
  `, [], (err, rows) => {
      console.log(rows)
    });
  }
)

It works well in Electron version 15, but in the latest versions, the module does not load. node-sqlite3 compiles correctly.

The new npm feature of npm audit is revealing some security vulnerabilities with the version of node-pre-gyp required by the pre-4.0.0 version of sqlite3.

Would you be able to update (or accept a PR for) an update of node-sqlite3 to 4.0.0?

Thanks...