Vscode-sqltools: SQLite Support

[mtxr]

Issue Type

• [x] Feature Request
• [ ] Bugfix/Errors
• [ ] Questions
• [ ] Other

Prerequisites (For bugfixes)

• [x] Are you running the latest version?
• [x] Did you check the logs?
• [x] Did you check the Setup?

Description

Add SQLite support

[frankred]

Currently there is no VSC Extension that can do this. This would be a unique selling point.

[mtxr]

Yeah, but it's also a bit difficult to develop that.
The currently sqlite modules for node are compiled for SO version. The extension is packaged with the modules to avoid end-user to install it because it would take a lot of time.

I'm working in a way to do it, It would be awesome to be the first supporting SQLite, but it will take at least more 2 or 3 weeks.

Any tips that I could follow?

[frankred]

I've used this(https://github.com/mapbox/node-sqlite3) client several times and it worked very fine. We did not had any problems with different plattforms. It worked like a charm and npm install sqlite3 was enough to got it run. And then "just" run the following commands:

Show all tables:
SELECT name FROM sqlite_master WHERE type='table';

Show Tables Schemas:
SELECT * FROM sqlite_master

Select data from table
SELECT * FROM tableX

For the rest you could use the same logic like for mysql.

[mtxr]

That's the module I've been trying to use.

The problem is that, when I run npm install sqlite3 from my laptop, it builds sqlite3 from source for my arch.
When I publish the package, it generates a ZIP like file that VSCode installs in any user computers, but the sqlite3 built will be the same.

I have 3 alternatives for now that I'm testing:

• A way to create per-platform packages for complementary usage with sqltools for sqlite support;
• Use a tool like https://github.com/xo/usql that should be download automatically after extension has been installed. That would help to support many other SGDBs too
• Port SQLite3 package for a pure JS version. I found some projects like this but they are not maintained anymore.

[mtxr]

FYI Microsoft/vscode#41444.

Let's see what they will say, maybe they have a workaround for that.

[pakLebah]

Any progress on this? I need sqlite support as well. Thank you.

[PapsOu]

What about this extension ? https://marketplace.visualstudio.com/items?itemName=alexcvzz.vscode-sqlite

You probably do something like this one ?

[BrettMoan]

FYI Microsoft/vscode#41444.

Let's see what they will say, maybe they have a workaround for that.

Just out of curiosity, did you decide to use the activate command to do the node install on the user's side or did that not address the issue?

I'm curious how generic odbc/jdbc support will work once you've finished oracle/sqlite support./

[mtxr]

Yes, there is a branch with a lot of changes feature/oracle that will resolve this issue. I'm not installing on activate, but on first use.

Hard work, but almost done. Needs just some polishing

[BrettMoan]

Awesome! 🥇 Looking forward to see how it turns out.

I don't have experience with the syntax of how vscode and its extensions work otherwise I would offer to help test/review/document.

Best wishes. Looking forward to having an integrated solution as it will make vscode even more functional for my everyday development. 👍

[pakLebah]

SQLite support in this extension requires node.js installed on the system which I refuse to do. Just do it like AlexCovizzi's extension here.

[mtxr]

I saw this extension before, it's an option, but it goes against the architecture used here. The idea to use NPM packages is to avoid parsing results from STDOUT to JSON, that's a lot of processing, it can become slow easily. Packges just give us results as JSON.

I've found the way it handle queries (https://github.com/AlexCovizzi/vscode-sqlite/blob/master/src/sqlite/cliDatabase.ts), I can create a fallback to SQLite binary if it's already installed, users could chose which one to use.

Also, if I'm not wrong, this extension bundle the binaries inside of the extension package, that makes the extension very heavy to install. I've worked I few months ago to bring it from 19MB to ~3MB, so I won't consider bundling it together. Maybe downloading from a URL.

@pakLebah do you have JS skills? If you want, you can open a pull request for this. Take a look at MySQL dialect implementation. It supports 2 protocols, with different packages. You could do the same.

If you want to work on this, I suggest you to create a NPM package that we can just use as a dependency here. This way others can use your package two, like AlexCovizzi.

[pakLebah]

I don't see a few MB requirement would be a hurdle to your users. It's now common to see an application that requires a few hundreds MB or even a few GB. I have been using Alex Covizzi's extension for a few months and it's all fine, I don't see any major problems with it. Well, I only use sqlite for small databases (more or less 1000 records per table). I use other RDBMS for large databases.

I don't have good JS skill. And since Alex's extension already works well for me, I don't see why I need to do it myself with your extension. Sorry. I just thought it'd be nice if your extension could "just works"™.

[mtxr]

@pakLebah don't get me wrong please, I'll give you some background about the project and reasons why I believe we are doing great here.

I can consider this as a fallback as said. Anyway I would implement that differently, and download the binaries on first use.

If you take a look at VSCode issues and their project roadmap, you will see that they are encouraging extension developers to keep extensions small as possible and they are developing a way to allow dependencies installing on extension install, so I won't prioritize this at least for now, I believe the pattern VSCode is following is more maintainable than bundling binaries for all/few systems. SQLite is working beautifully here, I now Alex extensions is working as well.

My intention is not to eliminate all the other RDBMS extensions, but one day to be a base for other developers to add plugins and quick extend SQLTools to other RDBMS dialects. I'm learning with them and I hope they are learning as well.

I'm happy Alex extension fits your usage, sincerely. Your feedback is very important and relevant, but for now I would encourage you to keep with his extension, he did great there, his support to the project is amazing, so I don't see why to change.

If one of our other features (completions, results view, export, copy etc) fits your needs, then you could change.

We have a great discussion here and I like it.

Thanks @pakLebah!

[pakLebah]

Small is relative. Today's storage capacity is now moving to terrabyte. Unless you're still using a computer that only have a few hundred megabytes storage, I think a few megabytes for a useful extension would be fine for common people.

Don't get me wrong, too. I like your extension, but I don't need nodejs. Installing nodejs just for a VS Code extension is too much for me. If I would want to uninstall it, I have to uninstall nodejs as well. It's an extra work that should be unnecessary (and automated). I don't want that extra work. As a long time Mac user, I expect something that just works out of the box. Even XCode installation requires only a few clicks. Except for a very important software, I don't want to bother with manual or complex installation or uninstallation.

Your extension is great and a very useful contribution to the VS Code community. It just doesn't fit my need. That's all.

So, keep up the good works! 👍