Combining ob-sql, docker-tramp, and tramp ssh?

Combining ob-sql, docker-tramp, and tramp ssh?

[Jay Zawrotny]

[-- Attachment #1: Type: text/plain, Size: 868 bytes --]

I've been using ob-sql to document & prune our dev db and it's been
amazing.

https://media.discordapp.net/attachments/428916969861808130/768589313964507166/image0.gif

The only rough edge has been that I have to expose the SQL ports to my
host, forward it over ssh (I'm using a remote machine to offload docker
resources), then install a version of psql required to connect. Lastly I
then need to symlink psql to point to the targeted version of psql I need.

This means I can only match one setup at a time. A preferred route would be
to use the :dir property to eval over tramp like:

#+begin_src sql :dir "/sshx:user@devbox|docker:user@vm:/src"
SELECT count(id) from accounts;
#+end_src

If I use Python it works, but something specific about this combo is not
working. Any suggestions to fix this, personal config, docs, or debugging
steps one could point me to?

[-- Attachment #2: Type: text/html, Size: 1284 bytes --]

Re: Combining ob-sql, docker-tramp, and tramp ssh?

[Tim Cross]

I have done something very similar, but with a few differences which
might help.

In my case, I didn't use tramp, but instead used ssh tunnels that I
defined in my ssh config file e.g.

Host wdb-dev
  HostName dev-example.com
  User fred
  LocalForward localhost:3330 localhost:5432
Host wdb-prod
  HostName prod-example.com
  User fred
  LocalForward localhost:3331 localhost:5432

This allows me to run psql using the -p switch to set the port e.g. 3330
or 3331 with same user name and database name. 

I then used sql's connection alist to define connection names for the
different databases. You could probably use a .dir-locals.el file to do
the same thing on a per directory/project basis.

I would then open a terminal for each connection I needed and do ssh
wdb-dev or ssh wdb-prod to setup the tunneling.

The downside of this setup is that if you lose the ssh connection, you
lose connection to the host and will need to restart it for things to
work (where with the tramp setup it will make the connection when
needed). The advantage is that I could use other tools, like psql or
dbbeaver/pgadmin/whatever at the same time.

If you want things to work in such a way that your org sql blocks all
use the same database name, you can just adjust the ssh config to use
the same port number, but then you can only have one connection active
at a time. However, this means you can setup things to connect to your
dev system, refine and test all your sql blocks and once confident they
are working, just change the ssh connection to point to the prod system
and re-run. 
Jay Zawrotny <jayzawrotny@gmail.com> writes:

> I've been using ob-sql to document & prune our dev db and it's been
> amazing.
>
> https://media.discordapp.net/attachments/428916969861808130/768589313964507166/image0.gif
>
> The only rough edge has been that I have to expose the SQL ports to my
> host, forward it over ssh (I'm using a remote machine to offload docker
> resources), then install a version of psql required to connect. Lastly I
> then need to symlink psql to point to the targeted version of psql I need.
>
> This means I can only match one setup at a time. A preferred route would be
> to use the :dir property to eval over tramp like:
>
> #+begin_src sql :dir "/sshx:user@devbox|docker:user@vm:/src"
> SELECT count(id) from accounts;
> #+end_src
>
> If I use Python it works, but something specific about this combo is not
> working. Any suggestions to fix this, personal config, docs, or debugging
> steps one could point me to?


-- 
Tim Cross