![]() They’re great for visually exploring data by clicking around. There are times when I like to use a dedicated GUI database client (my favourite is Postico). org files inline, so that you don’t even need to export to HTML yourself. I have found this to be a great way of documenting complex queries, as org-mode is readable enough for humans - even if they don’t use Emacs. With some configuration, we can even generate multiple files, annotate files with comments or even pull external changes back into our. sql file named after our org-mode file: select extract ( year from created_at ) as year, extract ( month from created_at ) as month, count ( * ) as nr_of_users from users join signups using ( user_id ) group by date_trunc ( 'month', created_at ) order by 1, 2 But with org-babel-tangle we can extract all the source code blocks into a single. List the results with latest month first, and then earlier months.įrom this contrived example, we might use regular Org-mode exporting functions to produce documentation in HTML (or Markdown, or any other format). We want to group by the year and month when the user signed up, so we truncate the date to the nearest month. Note that we will have to combine the signups with the users table. Marketing needs the total number of users, the year and the month. This report produces a listing of the number of users in our system per month. Here’s what a typical literate programming SQL file written with org-mode might look like: #+title: Users report That means I can include my SQL code and human-language documentation in a single file and use Emacs to generate both human-readable HTML documentation - weaving in literate programming terms - and the actual source code files - called tangling. Sqlformat-args '("-s2" "-g" "-u1"))) Literate programming with org-modeįinally, I have found it to be quite useful to embed my SQL code in org-mode files using org-babel for literate programming. :commands (sqlformat sqlformat-buffer sqlformat-region) I combine that with the sqlformat Emacs package so that my SQL buffers are automatically formatted on save: (use-package sqlformat I use pgformatter, installed via Homebrew, which gives me the pg_format program. There are no great tools to do so out there, but there a few decent ones. One more thing I like to do is to auto-format my SQL code. A paragraph in Emacs is usually delineated by blank lines, which I have found to be a good approximation of “the current query”. My favourite command is sql-send-paragraph, which is bound by default to C-c C-c by default. That way, you can use the full power of Emacs to write your SQL in a dedicated file, and only use the database REPL to evaluate them and print the results. ![]() ![]() That means that you can use commands like sql-send-buffer, sql-send-paragraph and sql-send-region to send queries to the database prompt for evaluation. Running M-x sql-postgres from an SQL buffer will link the SQL buffer to the *SQL: Postgres* buffer with your database REPL. But this being Emacs, of course there’s more. If this is all we could do with sql-mode, it would give us little more than just opening a shell and running psql manually. Once you have set up these defaults, M-x sql-postgres in an SQL file will immediately drop you in your database prompt. You could set these as directory-local variables in your project: /path/to/project/.dir-locals.el To avoid having to type all the connection details out every time you want to connect to the database, you can configure some connection defaults in the sql-postgres-login-params variable. If you are working on a typical web development project, you might have a single development database you will want to keep connecting to. At the prompt, you can enter a query, hit and see the results. You can move around and select and copy text like in any other buffer. If, like me, you use evil-mode for Vim keybindings, those will also apply to this buffer. The new *SQL: Postgres* buffer in this window will work mostly like psql from the regular shell does, but it is still Emacs. It will look like you ran psql manually from a shell. If you enter them correctly, Emacs should then open a new window with a database prompt in it. Emacs will prompt us for a few connection details: the username, database name and host of our database. There are equivalent commands for My SQL and a few others. We can connect to it from Emacs using M-x sql-postgres. Let’s assume we’ve got a Postgre SQL database running locally. But since Emacs also supports embedded prompts, like a shell, it can also work just fine as a database client. It will come as no surprise that Emacs, being the extensible text editor, supports working with SQL files.
0 Comments
Leave a Reply. |