emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
* Combining ob-sql, docker-tramp, and tramp ssh?
@ 2020-10-21 22:11 Jay Zawrotny
  2020-10-21 23:58 ` Tim Cross
  0 siblings, 1 reply; 2+ messages in thread
From: Jay Zawrotny @ 2020-10-21 22:11 UTC (permalink / raw)
  To: emacs-orgmode

[-- 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 --]

^ permalink raw reply	[flat|nested] 2+ messages in thread

* Re: Combining ob-sql, docker-tramp, and tramp ssh?
  2020-10-21 22:11 Combining ob-sql, docker-tramp, and tramp ssh? Jay Zawrotny
@ 2020-10-21 23:58 ` Tim Cross
  0 siblings, 0 replies; 2+ messages in thread
From: Tim Cross @ 2020-10-21 23:58 UTC (permalink / raw)
  To: emacs-orgmode


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


^ permalink raw reply	[flat|nested] 2+ messages in thread

end of thread, other threads:[~2020-10-21 23:59 UTC | newest]

Thread overview: 2+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2020-10-21 22:11 Combining ob-sql, docker-tramp, and tramp ssh? Jay Zawrotny
2020-10-21 23:58 ` Tim Cross

Code repositories for project(s) associated with this inbox:

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).