[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: Interacting with PostgreSQL
From: |
Marcin Borkowski |
Subject: |
Re: Interacting with PostgreSQL |
Date: |
Sun, 22 Nov 2020 22:33:43 +0100 |
User-agent: |
mu4e 1.1.0; emacs 27.0.50 |
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
- Interacting with PostgreSQL, Tim Landscheidt, 2020/11/21
- Re: Interacting with PostgreSQL, Jean Louis, 2020/11/21
- Re: Interacting with PostgreSQL, Tim Landscheidt, 2020/11/24
- Re: Interacting with PostgreSQL, Jean Louis, 2020/11/24
- Re: Interacting with PostgreSQL, Tim Landscheidt, 2020/11/25
- Re: Interacting with PostgreSQL, Jean Louis, 2020/11/25
- Re: Interacting with PostgreSQL, Tim Landscheidt, 2020/11/25
- Re: Interacting with PostgreSQL, Jean Louis, 2020/11/26
- Re: Interacting with PostgreSQL, Tim Landscheidt, 2020/11/29
- Re: Interacting with PostgreSQL, Jean Louis, 2020/11/30
Re: Interacting with PostgreSQL,
Marcin Borkowski <=