Unable to connect to SQLite database

[danielvy]

Since updating to Node 18.6 SQLTools extension stopped working with SQLite.

When trying to connect to the sqlite database, I get the error 'You need to install "sqlite3@4.2.0" to connect'. Trying to install the driver by clicking "install" reports a successful installation but the problem persists. There doesn't seem to be any errors in the logs from the installation.

Also tried uninstalling SQLTools and SQLite extensions, clearing the caches and reinstalling.

To Reproduce

1. Create sqlite connection
2. Attempt to connect to database
3. Select "Install" from the error message popup

Expected behavior
SQLTools should connect to sqlite database

Screenshots

Desktop

• SQLTools Version: 0.23.0
• SQLTools SQLite: 0.2.0
• VSCode Version: 1.69.2
• OS: MacOS 12.5
• Database version: sqlite3 3.39.0

[gjsjohnmurray]

See also #858

[stuartstein777]

I have the same problem on Ubuntu.

[mtxr]

@danielvy can you provide more information? have you checked the extension output to see if it looks like #858 ?

[danielvy]

@mtxr

Not sure which additional information to provide.

As I said, there's prompt to install "sqlite3@4.2.0". When proceeding, a terminal opens for a brief moment that attempts to install the driver. The npm command appears to be successful from what I can see (it closes pretty quickly). Then when trying to connect again, it still requires to install "sqlite3@4.2.0".

The only relevant thing I can find in the logs is the SQLTools output:

[1662384400316] INFO  (ext): EXECUTING COMMAND => sqltools.selectConnection
[1662384400317] INFO  (ls): REQUEST RECEIVED => connection/GetConnectionsRequest
[1662384402568] INFO  (ext): EXECUTING COMMAND => sqltools.getConnections
[1662384402571] INFO  (ls): REQUEST RECEIVED => connection/GetConnectionsRequest
[1662384402572] INFO  (ls): REQUEST RECEIVED => connection/ConnectRequest
[1662384402572] INFO  (ls): Connection instance created for dummy.
    ns: "conn-manager"
[1662384402575] ERROR (ls): Connecting error: {"code":1000,"data":{"notification":"Core/missingModule","dontNotify":true,"args":{"conn":{"previewLimit":50,"driver":"SQLite","name":"dummy","database":"/Users/daniel/temp/dummy.db","isActive":false,"isConnected":false,"id":"dummy|SQLite||\\Users\\daniel\\temp\\dummy:db"},"action":"install","deps":[{"type":"package","name":"sqlite3","version":"4.2.0"}]}}}
    ns: "conn-manager"
[1662384402576] INFO  (ext): Connection explorer changed. Will be updated.
    ns: "conn-man:explorer"

If you have any specific information you need, let me know and I'll look it up.

[gjsjohnmurray]

@danielvy please download the ZIP from the link it #953 (comment) then unzip it and install it into VS Code. One way to install is to drag/drop it onto the Extensions view.

Then let us know if you can connect.

[danielvy]

@gjsjohnmurray

Then let us know if you can connect.

Still can't connect.

Now I get a prompt to upgrade to "sqlite3@5.0.10", after selecting "upgrade now" it installs sqlite but next time I try to connect I still get the prompt.

One thing I noticed is that the npm i sqlite3@5.0.10 is being executed in ~/Library/Application Support/<project name>, so the sqlite package in installed under ~/Library/Application Support/<project name>/node_modules. I assume this is not the intended location and the reason the extension can't find it?

[gjsjohnmurray]

@danielvy what exactly is the string that you represented above as <project name>?

Through reading source code and testing on Linux I would expect it to be vscode-sqltools, but maybe on macOS it is something else. And are you seeing it vary depend on what folder you opened in VS Code?

[danielvy]

@danielvy what exactly is the string that you represented above as <project name>?

Through reading source code and testing on Linux I would expect it to be vscode-sqltools, but maybe on macOS it is something else. And are you seeing it vary depend on what folder you opened in VS Code?

My bad, I feel silly. I named my test project "vscode-sqltools" so I just assumed it's where the install path is taken from. The path is indeed ~/Library/Application Support/vscode-sqltools/. Sorry about that.

I deleted all relevant directories and reinstalled the extensions. Now there's an error when sqlite3@5.0.10 is compiled from source, but it doesn't seem to be directly caused by the extension. Looks like there's a known issue with node-gyp that fails when the directory contains spaces ("Application Support").

632 error clang: error: no such file or directory: 'Support/vscode-sqltools/node_modules/node-addon-api'
632 error make: *** [Release/obj.target/node_sqlite3/src/backup.o] Error 1
632 error gyp ERR! build error 
632 error gyp ERR! stack Error: `make` failed with exit code: 2

See nodejs/node-gyp#65, nodejs/node-gyp#2556 and nodejs/node-gyp#1038. It's 10 years old, the bug marked as completed but apparently it's still an issue.

[gjsjohnmurray]

@danielvy thanks for the extra information. Does your workstation contain Apple silicon (e.g. M1, M2) or is it an Intel one?

[cclauss]

nodejs/node-gyp#2556 (comment)

[danielvy]

@danielvy thanks for the extra information. Does your workstation contain Apple silicon (e.g. M1, M2) or is it an Intel one?

Yes, it's a Macbook Pro with M1 chip.

[danielvy]

Even though the installation error is not caused directly by the extension, vscode-sqltools still reports a successful installation of sqlite3. The terminal window sometimes closed quickly and it's hard to understand what's going on unless someone digs in the npm logs. Maybe the extension can detect and report unsuccessful driver install?

Also, the node-gyp bug may or may not be fixed anytime soon. Is there any reasonable workaround? Manually installing sqlite3 someplace else and and copying it to ~/Library/Application Support/vscode-sqltools/ ?

[cclauss]

Try backslash-space or quotes?
~/Library/Application\ Support/vscode-sqltools/
"~/Library/Application Support/vscode-sqltools/"

[gjsjohnmurray]

@danielvy please try the dev VSIX in the attached zip. It won't solve the problem of sqlite3 failing to build an M1 binary on a path containing spaces, but it should keep the build window open so you can see the command that was run. Then I suggest you run that command (omit the && exit(0) bit) in a temp folder whose path doesn't contain spaces. Assuming it succeeds, move the node_modules folder into ~/Library/Application Support/vscode-sqltools/Data/ and see if this gets SQLTools working with SQLite.

sqltools-0.25.0-dev.zip

@cclaus thanks for your suggestion, but it's not clear to me that we're going to be able to fool the sqlite3 package into passing a quoted or escaped path into node-gyp when it needs to build the M1 binary.

Perhaps a better hope is that the https://github.com/TryGhost/node-sqlite3/tree/arm64-test branch might get merged soon and produce a sqlite3 release that contains the M1 binary. @daniellockyer do you have a timescale for this?

[mtxr]

Guys, I’m about to help with the SQLite over the weekend. I was able to install it on my m1 here outside of the extension, I haven’t detected though the difference between the extension and the terminal I was using.

possibly workaround would be install somewhere and move, but I’ll try to get a better steps on how to do this.

[gjsjohnmurray]

@mtxr glad to hear you've got an m1 to work on. When you installed the sqlite3 package from a terminal was there a space in your path?

[danielvy]

I copied the entire folder from ~/Library/Application Support/vscode-sqltools to a temp directory, ran npm i sqlite3@5.0.10 there, sqlite3 was successfully built and installed on my M1, then copied it all back as is.

SQLTools is now able to connect to the database. Will have to do this each time I update node or a new sqlite3 version is required but I can work with this for now.

[daniellockyer]

@gjsjohnmurray Hey! 👋🏻

Perhaps a better hope is that the https://github.com/TryGhost/node-sqlite3/tree/arm64-test branch might get merged soon and produce a sqlite3 release that contains the M1 binary. @daniellockyer do you have a timescale for this?

I'd love to merge and release M1 binaries but I'm hitting a blocker that is preventing that. If anyone here has any ideas, I'd be glad to hear them 🙂

[gjsjohnmurray]

Today's 0.4.0 release of the SQLTools SQLite driver uses a new release of the sqlite3 package (5.1.1) which should be able to download binaries suitable for macOS on ARM64 hardware. I'm closing this but will reopen if told that it doesn't work.

[zrong]

• SQLTools Version: 0.25.1
• SQLTools SQLite: 0.4.0
• VSCode Version: 1.71.2
• OS: macOS 12.6 (Intel)
• Database version: sqlite3 3.37.0
• Node: v16.17.1

~/Library/Application Support/vscode-sqltools$ npm install sqlite3

609 error gyp info using node-gyp@9.0.0
609 error gyp info using node@16.17.1 | darwin | x64
609 error gyp info find Python using Python version 3.10.4 found at "/Users/zrong/.pyenv/versions/3.10.4/bin/python3"
609 error gyp info spawn /Users/zrong/.pyenv/versions/3.10.4/bin/python3
609 error gyp info spawn args [
609 error gyp info spawn args   '/Users/zrong/.nvm/versions/node/v16.17.1/lib/node_modules/npm/node_modules/node-gyp/gyp/gyp_main.py',
609 error gyp info spawn args   'binding.gyp',
609 error gyp info spawn args   '-f',
609 error gyp info spawn args   'make',
609 error gyp info spawn args   '-I',
609 error gyp info spawn args   '/Users/zrong/Library/Application Support/vscode-sqltools/node_modules/sqlite3/build/config.gypi',
609 error gyp info spawn args   '-I',
609 error gyp info spawn args   '/Users/zrong/.nvm/versions/node/v16.17.1/lib/node_modules/npm/node_modules/node-gyp/addon.gypi',
609 error gyp info spawn args   '-I',
609 error gyp info spawn args   '/Users/zrong/Library/Caches/node-gyp/16.17.1/include/node/common.gypi',
609 error gyp info spawn args   '-Dlibrary=shared_library',
609 error gyp info spawn args   '-Dvisibility=default',
609 error gyp info spawn args   '-Dnode_root_dir=/Users/zrong/Library/Caches/node-gyp/16.17.1',
609 error gyp info spawn args   '-Dnode_gyp_dir=/Users/zrong/.nvm/versions/node/v16.17.1/lib/node_modules/npm/node_modules/node-gyp',
609 error gyp info spawn args   '-Dnode_lib_file=/Users/zrong/Library/Caches/node-gyp/16.17.1/<(target_arch)/node.lib',
609 error gyp info spawn args   '-Dmodule_root_dir=/Users/zrong/Library/Application Support/vscode-sqltools/node_modules/sqlite3',
609 error gyp info spawn args   '-Dnode_engine=v8',
609 error gyp info spawn args   '--depth=.',
609 error gyp info spawn args   '--no-parallel',
609 error gyp info spawn args   '--generator-output',
609 error gyp info spawn args   'build',
609 error gyp info spawn args   '-Goutput_dir=.'
609 error gyp info spawn args ]
609 error gyp info ok 
609 error gyp info it worked if it ends with ok
609 error gyp info using node-gyp@9.0.0
609 error gyp info using node@16.17.1 | darwin | x64
609 error gyp info spawn make
609 error gyp info spawn args [ 'BUILDTYPE=Release', '-C', 'build' ]
609 error warning: /Library/Developer/CommandLineTools/usr/bin/libtool: archive library: Release/nothing.a the table of contents is empty (no object file members in the library define global symbols)
609 error /bin/sh: Support/vscode-sqltools/node_modules/sqlite3/build/Release/lib.host:/Users/zrong/Library/Application: No such file or directory
609 error clang: error: no such file or directory: 'Support/vscode-sqltools/node_modules/node-addon-api'
609 error make: *** [Release/obj.target/node_sqlite3/src/backup.o] Error 1
609 error gyp ERR! build error