[GreyZephyr]

Do you have any resources for your sql workflow in emacs? I use eMacs for everything else, but keep going back to dbeaver whenever I write SQL or interact with PostgreSQL. All the tutorials I’ve turned up seem to be doing simple things with small tables, rather than a more complex workflow. I’m sure there are ways to use emacs effectively for SQL but I just can’t find out how.

[SoftTalker]

Not really. I write SQL in text files, manage them in version control (git these days, but in the past it has been mercurial or subversion, doesn't really matter) and execute them in psql via a SQLi postgres buffer (emacs comint mode that wraps a psql command shell in a buffer).

I haven't used (or heard of) dbeaver but took a quick look at it and I have tried other SQL tools that seem similar. Never really felt they gave me much.

I like keeping my tools very simple.

[GreyZephyr]

The main thing that I like is that it gives you nice table autocomplete as well as some nice metadata. As well as some connection management stuff. And it drives me mad because I am sure there must be a way to get the psql autocomplete to work in a buffer, but I have never managed it.

[iLemming]

Are you writing all that sql simply to examine the existing data, or you're actively writing migratory expressions and such?

For the former - if you're willing to completely alter your perspective here, you may discover something unusually good. To understand the data, I find myself more often reaching for a Clojure REPL with basic odbc lib dependency. Clojure, at times may feel like hands-down the best tool for dealing with certain types of data. It really is great for quickly retrieving/grouping/sorting/slicing/dicing - these days I use it for exploring JSON results from APIs (that I automatically convert to EDN), or for dealing with DB data.

And you can perfectly manage that in org-source blocks too - I typically keep at least one Clojure REPL instance running in Emacs - I'd connect to it and go like:

      (-> (select :*)
          (from :users)
          (where [:= :status "active"])
          (limit 5)
          (group-by :user_id) ...)

For someone with no clue, all these parens may feel like annoyance, for me, they are the best way to deal with structured expressions - composing and writing them is far more satisfactory than even writing plain sql. Although, sure - sql can be a mind-blowing, great tool on itself.

[GreyZephyr]

For the exploratory stuff I tend to use R in code blocks as I’m familiar with it. Sadly I’ve never really done much with Clojure, though its data capabilities always look very cool, especially datatomic. It’s just always been a bit of a poor fit for what I’ve been doing so I’ve never quite got round to it. Maybe this will give me the push I need!

Sadly the things where the extra tooling is missed is more in larger databases where I’m either rebuilding or restructuring an existing database or writing more complex queries to help with this and I want to do it all in the database. Hence autocomplete and general knowledge of the schema being built in is nice.

[SoftTalker]

I didn't even realize psql had autocomplete. It's nice, but I pretty quickly learn the table and column names I'm working with, or I just run \dt and copy/paste from the output. I guess I understand if someone would find that tedious; I learned to program before autocomplete was a thing in any editor, and I'm just used to it.

All the autocomplete and other smart editor features quickly outrun the speed at which I can think about what I'm doing, so I find them of limited use.