Re: Interacting with PostgreSQL

[Marcin Borkowski <mbork@mbork.pl>]

Hi there,

not sure if this will help, but I sometimes use Org-mode to interact
with Postgres:
http://mbork.pl/2020-03-09_Using_Org-mode_as_a_PostgreSQL_client

Best,
Marcin

On 2020-11-22, at 00:51, Tim Landscheidt <tim@tim-landscheidt.de> wrote:

> 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


-- 
Marcin Borkowski
http://mbork.pl