Connect to SQLite of app through DB Gui (TablePlus)

Questions / Help
1

I have an app running with an SQLite db on a volume. Now I am trying to connect to it over ssh to manage my db with TablePlus.

My idea was to make the app locally accessible through wireguard and then use ssh to connect to it. Connecting via fly ssh console works well, but I can not do that with TablePlus, so I need to be able to access it with ssh directly.

My steps so far have been:

  • Create a wireguard config (fly wireguard create)
  • Load the generated config with wireguard
  • Check if I can ping it with ping6 <app-name>.internal
  • Create ssh keys with fly ssh issue --agent

And now I am stuck. TablePlus wants the private key, but I have no idea where to get that. It has not been exposed when issuing ssh credentials.

I wanted to see if I can connect via ssh without using TablePlus with ssh root@<app-name>.internal, which errors with

exec: "\nexport": executable file not found in $PATH
                                                    Connection to <app-name>.internal closed by remote host.
Connection to <app-name>.internal closed.

My knowledge on ssh keys and server stuff is pretty limited, so I might have misunderstood how all that works.

2

You can log in with a username and password if you can ping <app-name-db>.internal.

Go to your application console:

fly ssh console -a <app-name>

Get the database name, login, and password:

echo $DATABASE_URL

It will show the string in the following format:

postgres://some_login:some_password@some-address.internal:5432/some_db_name?sslmode=disable

Use the taken parameters to create the connection.

3

Unfortunately I am not using a postgres db, but SQLite here.

Is there any way to do ssh <app-name>.internal instead of fly ssh console -a <app-name>?
I think that would be the key to access it in TablePlus.

I was trying something like this:

image
image1000×900 78.4 KB

4

fly ssh issue creates two files with private and public keys accordingly:

  • some_name.key
  • some_name.key-cert.pub

Have you tried to import the file in the dialog?

I have been able to do ssh <app-name>.internal after connecting to VPN and running

fly ssh issue --agent

but I got `Permission denied (publickey) for some reason now.

5

Yes, I have generated the keys the same way and tried to import the private key. Since that did not work, i have tried to take the same route, that you took (ssh <app-name>.internal), if I do that, I also get Permission denied (publickey), because it uses my local user name as the default one.

in the docs if found:

Unix usernames the SSH cert can authenticate as (default [root,fly])

So I have tried ssh root@<app-name>.internal, which seems to pass the authentication but fails with:

exec: "\nexport": executable file not found in $PATH
                                                    Connection to <app-name>.internal closed by remote host.
Connection to <app-name>.internal closed.

Do you get the same error, or does that work for you?

6

@Hnnng

Yes, I forgot the user name. ssh root@<app-name>.internal works for me: the SSH connection is established and shell commands run.

1 Like
7

Thank you so much! When you said it was working, I was thinking it might be a problem with my terminal, so I tried a different terminal and it worked just fine.

The other thing that I did is not importing the private key in TablePlus. By checking the “Use ssh” checkbox but leaving the field empty it uses the ssh config and seems to read from the ssh-agent. So as long as you have valid ssh keys in your agent you seem to be able to connect.

For some who tries to do the same, here is my config:

image
image1000×900 69.8 KB

3 Likes
8

As far as I know, with TablePlus, you cannot update SQLite via SSH. Did you find a way to do it?

Also, when you connect to the database, do you see the latest data? I need to restart the app with the attached volume to view the latest data.