From mboxrd@z Thu Jan 1 00:00:00 1970 Path: news.gmane.io!.POSTED.blaine.gmane.org!not-for-mail From: Marcin Borkowski Newsgroups: gmane.emacs.help Subject: Re: Interacting with PostgreSQL Date: Sun, 22 Nov 2020 22:33:43 +0100 Message-ID: <874klhgjpk.fsf@mbork.pl> References: <87r1oms1z3.fsf@passepartout.tim-landscheidt.de> Mime-Version: 1.0 Content-Type: text/plain Injection-Info: ciao.gmane.io; posting-host="blaine.gmane.org:116.202.254.214"; logging-data="36409"; mail-complaints-to="usenet@ciao.gmane.io" User-Agent: mu4e 1.1.0; emacs 27.0.50 Cc: help-gnu-emacs@gnu.org To: Tim Landscheidt Original-X-From: help-gnu-emacs-bounces+geh-help-gnu-emacs=m.gmane-mx.org@gnu.org Sun Nov 22 22:34:32 2020 Return-path: Envelope-to: geh-help-gnu-emacs@m.gmane-mx.org Original-Received: from lists.gnu.org ([209.51.188.17]) by ciao.gmane.io with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kgx0C-0009M9-Ps for geh-help-gnu-emacs@m.gmane-mx.org; Sun, 22 Nov 2020 22:34:32 +0100 Original-Received: from localhost ([::1]:57560 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kgx0B-0001xy-Rw for geh-help-gnu-emacs@m.gmane-mx.org; Sun, 22 Nov 2020 16:34:31 -0500 Original-Received: from eggs.gnu.org ([2001:470:142:3::10]:55654) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kgwze-0001xj-H8 for help-gnu-emacs@gnu.org; Sun, 22 Nov 2020 16:33:58 -0500 Original-Received: from mail.mojserwer.eu ([195.110.48.8]:39164) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kgwzb-0003Ld-EW for help-gnu-emacs@gnu.org; Sun, 22 Nov 2020 16:33:58 -0500 Original-Received: from localhost (localhost [127.0.0.1]) by mail.mojserwer.eu (Postfix) with ESMTP id D09FCE6A52; Sun, 22 Nov 2020 22:33:51 +0100 (CET) X-Virus-Scanned: Debian amavisd-new at mail.mojserwer.eu Original-Received: from mail.mojserwer.eu ([127.0.0.1]) by localhost (mail.mojserwer.eu [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id CIPoId8wYSyw; Sun, 22 Nov 2020 22:33:46 +0100 (CET) Original-Received: from localhost (178235147162.dynamic-3-poz-k-0-1-0.vectranet.pl [178.235.147.162]) by mail.mojserwer.eu (Postfix) with ESMTPSA id 583F5E6235; Sun, 22 Nov 2020 22:33:46 +0100 (CET) In-reply-to: <87r1oms1z3.fsf@passepartout.tim-landscheidt.de> Received-SPF: pass client-ip=195.110.48.8; envelope-from=mbork@mbork.pl; helo=mail.mojserwer.eu X-Spam_score_int: -25 X-Spam_score: -2.6 X-Spam_bar: -- X-Spam_report: (-2.6 / 5.0 requ) BAYES_00=-1.9, RCVD_IN_DNSWL_LOW=-0.7, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_HELO_NONE=0.001, SPF_PASS=-0.001 autolearn=ham autolearn_force=no X-Spam_action: no action X-BeenThere: help-gnu-emacs@gnu.org X-Mailman-Version: 2.1.23 Precedence: list List-Id: Users list for the GNU Emacs text editor List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: help-gnu-emacs-bounces+geh-help-gnu-emacs=m.gmane-mx.org@gnu.org Original-Sender: "help-gnu-emacs" Xref: news.gmane.io gmane.emacs.help:125492 Archived-At: 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 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