Interacting with PostgreSQL

[Tim Landscheidt <tim@tim-landscheidt.de>]

Hi,

I've been using Emacs with PostgreSQL for a bit more than
20 years now, and every now and then I look at Emacs's SQL
"stuff" and think that I'm doing something absurdly wrong,
but can never find a better solution.  So I'd like to pre-
sent you with three scenarios, my tried and tested workflow
for each of them and ask for your input on solving them more
Emacsily.

1. Mass data and structure changes: These are changes where
   I'd like to see the result or test triggers & Co. before
   committing them.  For that, I have a function to create a
   scratch buffer for SQL commands in a transaction:

   | (defun tl-sql-scratch nil
   |   "Create a scratch buffer for PostgreSQL."
   |   (interactive)
   |   (switch-to-buffer (generate-new-buffer "scratch.sql"))
   |   (sql-mode)
   |   (sql-highlight-postgres-keywords)
   |   (insert "BEGIN WORK;\n-- COMMIT WORK;\n")
   |   (forward-line -1))

   and set [f12] to pipe the buffer's contents to psql:

   | (define-key sql-mode-map
   |   [f12]
   |   (lambda nil
   |     (interactive)
   |     (shell-command-on-region
   |      (point-min)
   |      (point-max)
   |      "/usr/bin/psql")))

   I then add UPDATEs, CREATE FUNCTIONs, test cases, etc.,
   press [f12] until I am satisfied with the results, remove
   the test cases, uncomment the COMMIT and then press [f12]
   for the last time before killing the buffer.

   The closest replacement for that that I have come across
   is sql-postgres which essentially opens a glorified psql
   session, but does not even support editing the input in
   any "advanced" or just meaningful way; for example, if I
   yank "SELECT 1 + \n2;\n" into the buffer, the "2;" does
   not get prefixed with the continuation prompt, I then
   have to press RET twice to get the result, and the prompt
   is totally gone.

   But even if that stuff would work, it would still not
   provide the clean slate I get with my approach: These are
   the steps to execute on a known state of the database;
   and if they do not "work", the next iteration will start
   again at the known state, not some in-between where some
   functions may have been created/data has been changed
   while others have not.

2. Reading data from the database: These are queries where,
   for example, I'd like to read data from the database to
   set up mail abbreviations.  For simple data, I execute
   "psql -0Atc" and split-string, for more complex struc-
   tures I build a JSON object.  If the query results are
   dependent on some parameter, as there is no
   sql-postgres-quote function and psql does not allow to
   refer in "-c" statements to parameters set with "-v", I
   add an inline Perl script that uses DBI to connect to and
   pass @ARGV to the server and return the results.

3. Writing data to the database: These are queries where I
   want to store data, either single tuples or complex
   structures.  Here again I use an inline Perl script that
   uses DBI to connect to the database and do all the neces-
   sary transaction handling, escaping and sanity checks.

   For very simple queries it feels possible to create a new
   sql-postgres connection, send the query with
   sql-send-string and then check whether the "last" answer
   was "INSERT 0 1" or something like that, but even so the
   buffer looks like a mess because there is no context for
   the answer as the sent query is not printed (yes, there
   is "\set ECHO queries", but it does not necessarily in-
   spire confidence).

As an alternative to 2. and 3., I'm aware of three approach-
es:

a) Speaking natively with the PostgreSQL server from Emacs
   Lisp (https://github.com/cbbrowne/pg.el): This does not
   work with PostgreSQL versions currently supported.

b) Wrapping libpq in an Emacs module
   (https://github.com/anse1/emacs-libpq): This looks (very)
   promising, but requires to compile and install yet anoth-
   er binary (and keep it working with Emacs and PostgreSQL
   versions increasing).

c) EmacsSQL (https://github.com/skeeto/emacsql): This is
   probably the most interesting approach, however instead
   of using standard SQL which I have mastered reasonably
   well and can also reuse in Perl, Python and whatever, it
   comes up with a similar, but different syntax, and more
   complex queries (checking the number of affected rows,
   etc.) do not seem be supported (CMIIW).

None of these feel superior enough to my inline Perl scripts
to warrant the effort of a rewrite.

What do other users use to work on PostgreSQL databases from
Emacs?

Tim