access Sqlite3 database over SSH

Not planned

Hello,

I want to access may sqlite3 DB over SSH. I have no idea what I'm doing wrong.

host: example.com (direct connection)

port: 33322

Auth type: passwordless key pair



 

5
9 comments

You can't access to remote SQLite db, since DataGrip works through jdbc.

-6
Avatar
Permanently deleted user

Follow up on this?  Why can't Datagrip ssh tunnel to an SQLite db?  You can SSH to a postgres db and that also runs through jdbc?

5

I’m also interested in this topic, since my SQLite db is 11GB I would really like to edit the SQL locally in CLion and execute it remotely instead of downloading a local copy.

1

Try sshfs. It will not work directly in PyCharm.

-1

Nice idea ;-) Already tried this before, but this isn’t a real solution, because if you select a relevant amount of data from the 11GB database. SSHFS of course also needs to download all this data in the background. This is only helpful if you only run queries with access to tables with very little data.

0

You're right. I know this not an answer for your question, but did you try this:

https://stackoverflow.com/questions/7682503/how-to-shrink-sqlite-database

And if yes and it didn't help, you probably should think about migrating to something stronger (mySql, Postgress, etc...)

0

Thanks. No, it’s part of the design of the application that it’s that big. It’s part of some research project running on cluster hardware. It is just collecting that much data. We won’t change it, because it is a key feature to use a server less DB. We do not intend to access it remotely in normal operation, it would be just nice for the developer for inspection purpose.

0

I just uninstalled this crap. I have a sqlite db on SSH and I cannot access it. Why paying for it when SQLite DB browser is doing the same thing and for free?

2

The issue is that the SQLite library must have direct access to the file as though that file existed on a local path to your machine. This simply is not doable over an SSH link … However, accessing TCP services such as a MySQL server is certainly possible over just an SSH tunnel alone … but there are no SQLite “servers” as such because the nature of SQLite excludes that as an option for accessing the data because doing it that way defeats the entire reason why SQLite exists in the first place.

There are tools like SSHFS that could do it where it creates a local mount point over an SSH tunnel … but I think there is an even better way …

Clone your project folder to a folder on the remote server that you need to SSH into (your home folder for example under something like JavaProjects would make sense.

Install JetBrains Gateway program on your LOCAL machine.

Open it up and make an SSH connection to that server. You will need to create a new server connection and supply username/password or key file or however, you authenticate.

Connect to the server and click next and then for the project folder, just select click the ellipses and browse to the folder you cloned to the server all the way to and including the root folder of the project.

Click next … Gateway will then make sure that the server has a headless installation of IntelliJ, downloading and installing it if it does not exist and it does this flawlessly.

Next it will start a thin client version of IntelliJ on your local machine.

Open the Database connection tool window and create a new connection via SQLite.

When you click to browse for the SQLite database you will be browsing the folders on the remote server. Pick the file and finish the connection (you'll have to click the link to install the SQLite library of course).

You are now using the SQLite file within IntelliJ via thin client mode to the server where you can develop, compile, run etc. just as though that SQLite file were local to you … the behavior will be identical and it will give you that flexibility that you need.

 

You're welcome…

0

Please sign in to leave a comment.