Skip to content

Unable to connect to SQLite database #921

New issue
New issue
Closed
Closed
Unable to connect to SQLite database#921
Labels
macOSsqliteSQLite driver
@danielvy

Description

@danielvy
danielvy
opened on Jul 28, 2022

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
image
image

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

Activity

danielvy
added
triage
on Jul 28, 2022
gjsjohnmurray
added
sqliteSQLite driver
on Jul 28, 2022
gjsjohnmurray

gjsjohnmurray commented on Jul 28, 2022

@gjsjohnmurray
gjsjohnmurray
on Jul 28, 2022
Collaborator

See also #858

stuartstein777

stuartstein777 commented on Aug 1, 2022

@stuartstein777
stuartstein777
on Aug 1, 2022 · edited by stuartstein777

I have the same problem on Ubuntu.

mtxr

mtxr commented on Sep 5, 2022

@mtxr
mtxr
on Sep 5, 2022
Owner

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

gjsjohnmurray
mentioned this on Sep 5, 2022
danielvy

danielvy commented on Sep 5, 2022

@danielvy
danielvy
on Sep 5, 2022 · edited by danielvy
Author

@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

gjsjohnmurray commented on Sep 5, 2022

@gjsjohnmurray
gjsjohnmurray
on Sep 5, 2022
Collaborator

@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.

gjsjohnmurray
removed
triage
on Sep 6, 2022
danielvy

danielvy commented on Sep 6, 2022

@danielvy
danielvy
on Sep 6, 2022
Author

@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

gjsjohnmurray commented on Sep 7, 2022

@gjsjohnmurray
gjsjohnmurray
on Sep 7, 2022
Collaborator

@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?

mtxr
mentioned this on Sep 7, 2022
danielvy

danielvy commented on Sep 7, 2022

@danielvy
danielvy
on Sep 7, 2022
Author

@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

gjsjohnmurray commented on Sep 7, 2022

@gjsjohnmurray
gjsjohnmurray
on Sep 7, 2022
Collaborator

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

gjsjohnmurray
mentioned this on Sep 7, 2022
cclauss

cclauss commented on Sep 8, 2022

@cclauss
cclauss
on Sep 8, 2022

nodejs/node-gyp#2556 (comment)

danielvy

danielvy commented on Sep 8, 2022

@danielvy
danielvy
on Sep 8, 2022
Author

@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

danielvy commented on Sep 8, 2022

@danielvy
danielvy
on Sep 8, 2022
Author

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

cclauss commented on Sep 8, 2022

@cclauss
cclauss
on Sep 8, 2022 · edited by cclauss

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

gjsjohnmurray

gjsjohnmurray commented on Sep 9, 2022

@gjsjohnmurray
gjsjohnmurray
on Sep 9, 2022
Collaborator

@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

mtxr commented on Sep 9, 2022

@mtxr
mtxr
on Sep 9, 2022
Owner

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

gjsjohnmurray commented on Sep 9, 2022

@gjsjohnmurray
gjsjohnmurray
on Sep 9, 2022
Collaborator

@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

danielvy commented on Sep 9, 2022

@danielvy
danielvy
on Sep 9, 2022 · edited by danielvy
Author

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.

gjsjohnmurray
added
macOS
on Sep 9, 2022
daniellockyer

daniellockyer commented on Sep 14, 2022

@daniellockyer
daniellockyer
on Sep 14, 2022

@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

gjsjohnmurray commented on Sep 15, 2022

@gjsjohnmurray
gjsjohnmurray
on Sep 15, 2022
Collaborator

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.

gjsjohnmurray
closed this as completedon Sep 15, 2022
zrong

zrong commented on Oct 2, 2022

@zrong
zrong
on Oct 2, 2022
  • 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
gjsjohnmurray
mentioned this on Oct 2, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    macOSsqliteSQLite driver

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @zrong@danielvy@mtxr@daniellockyer@cclauss

        Issue actions
          Unable to connect to SQLite database · Issue #921 · mtxr/vscode-sqltools