When will sqlite3 support the new SQLITE V3.25.0

[austinmeiron]

This update is pretty huge, introducing windows functions and enhanced ALTER_TABLE. With the original sqlite 3.25.0 release on 09/15/2018 followed by two minor revisions on 9/18 and 9/25, we have yet to see support for ^3.25.0 almost a month later. 😢

I personally would really like to use some windows functions such as ROW_NUMBER() in node.js.

[aguynamedben]

I'm also curious about this if you could provide any feedback.

The feature I want from 3.25.x is a critical bug fix. UPSERT was broken in 3.24.0 if you're using a partial index as your conflict target.
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg111554.html
https://www.sqlite.org/src/tktview?name=908f001483

[aguynamedben]

As a workaround to SQlite 3.25.2 not being supported yet, I'm trying to build node-sqlite3 from source (SQLite 3.25.2 installed via Homebrew), using the Source install instructions.

Is this even a possible workaround? I'm having difficulties getting 3.25.2 features working using this method.

SQLite 3.25.2 is installed via Homebrew

Sequence!ben:/Users/ben/code/tesla$ brew info sqlite
sqlite: stable 3.25.2 (bottled) [keg-only]

package.json has sqlite3 v4.02

{
  // ...
  "devDependencies": {
    "sqlite3": "^4.0.2",
    // ...
  }

As a postinstall or manual step I'm running:

npm rebuild sqlite3 --build-from-source --sqlite=`brew --prefix`

The rebuild seems to succeed, but when I run my app the SQLite upsert syntax fails to work, indicating that somehow a SQLite version before 3.24.0 is being used.

I'm not even sure this workaround is possible. It appears that the package downloads a node_sqlite binary.

[aguynamedben]

The upgrade looks pretty simple: https://github.com/mapbox/node-sqlite3/pull/998/files

Does the CI system automatically deploys the new binaries if the build passes? I can submit a similar PR for 3.25.2 if that would be helpful.

[aguynamedben]

FWIW, I got my temporary workaround working by setting the compilation flags documented for building from source with SQLCipher (in the README). This seems to build node-sqlite3 using SQLite 3.25.2, enabling upsert support.

yarn installs sqlite3 with a pre-built binary, then after that is installed I rebuild the native module compiling from source.

package.json (partial)

{
  "scripts": {
    "postinstall": "npm run rebuild-sqlite",
    "rebuild-sqlite-dev": "./internals/scripts/RebuildSQLiteDev.sh",
  },
  "devDependencies": {
    "sqlite3": "^4.0.2",
  },
}

RebuildSQLiteDev.sh

brew install sqlite
cd ../..
export LDFLAGS="-L`brew --prefix`/opt/sqlite/lib"
export CPPFLAGS="-I`brew --prefix`/opt/sqlite/include"
npm rebuild sqlite3 --build-from-source --sqlite=`brew --prefix`

[austinmeiron]

@aguynamedben it appears sqlite3 was versioned to 4.0, but the sqlite dependency from deps/common-sqlite.gypy has not been updated to support current 3.25.3 found here

Were you able to submit a PR for that?

@bruce-one could you please update to 3.25.3 as you did in ticket #998 ?

[aguynamedben]

@austinmeiron I haven't made a PR because I don't know how to properly upgrade it. There is a file called sqlite-autoconf-3240000.tar.gz and I think that includes the pre-packaged binary (i.e. if you don't specify a custom version of SQLite to use during rebuild).

I'm also curious if there is an unpublished matrix of support for various versions of SQLite vs. node-sqlite3. I.e. are all versions of node-sqlite3 compatible with all versions of SQLite? We're seeing some intermittent crashes in our Electron app after implementing SQLCipher, and I'm curious if the node-sqlite3 is making bad calls into SQLCipher because features/versions don't line up.

[austinmeiron]

@aguynamedben the latest source zip can be located here

I would do it myself but honestly my git skills are pretty sub par.

[aguynamedben]

@springmeyer Anything we should know before taking amateur hacks at upgrading the SQLite version in node-sqlite3? I'll copy the previous PR as close as I can. I need to first learn what a SQLite "amalgamation build" is.

Happy to learn how to do it, I've been using node-sqlite3 a lot recently and have a vested interest in learning more about it and supporting it.

[bruce-one]

3.26.0 was just merged (#1088) :-)

The single commit: 924cf56 has everything that's required for the update, in case anyone wants to do it in the future (it's nice and trivial :-) )