From mboxrd@z Thu Jan 1 00:00:00 1970 Path: news.gmane.io!.POSTED.blaine.gmane.org!not-for-mail From: Tim Landscheidt Newsgroups: gmane.emacs.help Subject: Interacting with PostgreSQL Date: Sat, 21 Nov 2020 23:51:28 +0000 Organization: http://www.tim-landscheidt.de/ Message-ID: <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="35289"; mail-complaints-to="usenet@ciao.gmane.io" User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/26.3 (gnu/linux) To: help-gnu-emacs@gnu.org Cancel-Lock: sha1:S0/qJzsxnq+Vp66tE0nY89vsIpI= Original-X-From: help-gnu-emacs-bounces+geh-help-gnu-emacs=m.gmane-mx.org@gnu.org Sun Nov 22 00:52:00 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 1kgcff-00090Q-0O for geh-help-gnu-emacs@m.gmane-mx.org; Sun, 22 Nov 2020 00:51:59 +0100 Original-Received: from localhost ([::1]:53470 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kgcfe-0005sn-1q for geh-help-gnu-emacs@m.gmane-mx.org; Sat, 21 Nov 2020 18:51:58 -0500 Original-Received: from eggs.gnu.org ([2001:470:142:3::10]:48108) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kgcfL-0005sb-2B for help-gnu-emacs@gnu.org; Sat, 21 Nov 2020 18:51:39 -0500 Original-Received: from static.214.254.202.116.clients.your-server.de ([116.202.254.214]:43050 helo=ciao.gmane.io) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kgcfJ-0000Js-3P for help-gnu-emacs@gnu.org; Sat, 21 Nov 2020 18:51:38 -0500 Original-Received: from list by ciao.gmane.io with local (Exim 4.92) (envelope-from ) id 1kgcfG-0008Wj-2t for help-gnu-emacs@gnu.org; Sun, 22 Nov 2020 00:51:34 +0100 X-Injected-Via-Gmane: http://gmane.org/ Received-SPF: pass client-ip=116.202.254.214; envelope-from=geh-help-gnu-emacs@m.gmane-mx.org; helo=ciao.gmane.io X-Spam_score_int: -16 X-Spam_score: -1.7 X-Spam_bar: - X-Spam_report: (-1.7 / 5.0 requ) BAYES_00=-1.9, HEADER_FROM_DIFFERENT_DOMAINS=0.249, SPF_HELO_NONE=0.001, SPF_PASS=-0.001 autolearn=no 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:125460 Archived-At: 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