Skip to content
Emacs SQL client uses Clojure JDBC.
Branch: master
Clone or download
kostafey Merge pull request #90 from alf-mindshift/master
Display the result of ejc-eval-user-sql-region
Latest commit 406beb3 Mar 22, 2019
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
img
snippets/sql-mode [#70] Add keywords autocompletion for MySQL databases. Jan 26, 2019
src/ejc_sql
test
.gitignore
.projectile Add projectile config. Oct 10, 2014
.travis.yml Use EVM for Travis tests run. Oct 4, 2018
Cask
Makefile Use EVM for Travis tests run. Oct 4, 2018
README.md
ejc-autocomplete.el
ejc-direx.el
ejc-doc.el [#70] Add ejc-keywords.el Jan 17, 2019
ejc-flx.el [#53] Fix fuzzy matching for autocompletion. Nov 25, 2018
ejc-format.el
ejc-interaction.el Update obtaining view definition for Oracle databases. Feb 17, 2019
ejc-lib.el [#70] Add ejc-keywords.el Jan 17, 2019
ejc-result-mode.el
ejc-sql.el
project.clj

README.md

Emacs License GPL 2 MELPA Melpa Stable Build Status Coverage Status

ejc-sql

ejc-sql logo

ejc-sql turns Emacs into a simple SQL client; it uses a JDBC connection to databases via clojure/java.jdbc lib.

You can use multiple connections at the same time. Autocompletion and basic formatting of SQL scripts are also available.

Installation

  1. To run Clojure, install Leiningen (assumes you have already installed Java 7+).

  2. Add MELPA (if not yet present) to your package-archives list.

    Then you can install ejc-sql with the following command:

    M-x package-install [RET] ejc-sql [RET]

Install JDBC drivers

If you are familiar with JDBC, please omit this section.

The most common way is to install JDBC drivers to your ~/.m2 directory. Here is a list of such installation examples. Anyway, it will become outdated soon, so please consult Google to install your database JDBC driver.

First of all, install Maven, then you can install your JDBC driver with one of the following commands.

Oracle

Download JDBC driver manually from oracle.com

Fix your actual JDBC version number -Dversion, filepath -Dfile and run command like this:

mvn install:install-file -Dfile="~/downloads/ojdbc7.jar" -DgroupId=com.oracle.jdbc -DartifactId=ojdbc7 -Dversion=12.1.0.2 -Dpackaging=jar -DgeneratePom=true

MS SQL Server

Download JDBC driver manually from microsoft.com

Fix your actual JDBC version number -Dversion, filepath -Dfile and run command like this:

mvn install:install-file -Dfile="~/downloads/sqljdbc.jar" -DgroupId=com.microsoft.sqlserver  -DartifactId=sqljdbc -Dversion=6.0 -Dpackaging=jar -DgeneratePom=true

or from Maven Central:

mvn org.apache.maven.plugins:maven-dependency-plugin:2.10:get -Dartifact=com.microsoft.sqlserver:mssql-jdbc:6.2.2.jre8

JTDS

mvn org.apache.maven.plugins:maven-dependency-plugin:2.10:get -Dartifact=net.sourceforge.jtds:jtds:1.3.1

PostgreSQL

mvn org.apache.maven.plugins:maven-dependency-plugin:2.10:get -Dartifact=postgresql:postgresql:9.3-1102.jdbc41 -DrepoUrl=http://clojars.org/repo/

MySQL

mvn org.apache.maven.plugins:maven-dependency-plugin:2.10:get -Dartifact=mysql:mysql-connector-java:5.1.6

MariaDB

mvn org.apache.maven.plugins:maven-dependency-plugin:2.10:get -Dartifact=org.mariadb.jdbc:mariadb-java-client:1.1.7

H2

mvn org.apache.maven.plugins:maven-dependency-plugin:2.10:get -Dartifact=com.h2database:h2:1.4.192

SQLite

mvn org.apache.maven.plugins:maven-dependency-plugin:2.10:get -Dartifact=org.xerial:sqlite-jdbc:3.8.11.2

Configuration

First, load ejc-sql package:

(require 'ejc-sql)

ejc-set-rows-limit set limit for the number of records to output (1000 by default). Set to nil if you want to disable this limit.

(ejc-set-rows-limit 1000)

Autocomplete

Enable autocomplete for ejc-sql minor mode:

(add-hook 'ejc-sql-minor-mode-hook
          (lambda ()
            (auto-complete-mode t)
            (ejc-ac-setup)))

Autocompletion is available for the following databases:

  • Oracle
  • MS SQL Server
  • PostgreSQL
  • MySQL
  • Informix
  • H2
  • SQLite

Fuzzy matching

Non-nil ejc-use-flx enables flx fuzzy matching engine for autocompletion. flx-ido is required in this case, it can be installed by your favorite approach. E.g. by MEPLA: M-x package-install [RET] flx-ido [RET]

(setq ejc-use-flx t)

Customize the minimum number of typed chars required to use flx for autocompletion, 2 by default:

(setq ejc-flx-threshold 2)

Setup connections with ejc-create-connection function in your .emacs. It's first arg is your custom database connection name, the remaining args are the same as database connection structure of clojure/java.jdbc lib.

The configuration of ejs-sql might looks like this:

;; Create your JDBC database connections configuration:

MySQL connection

;; MySQL example
(ejc-create-connection
 "MySQL-db-connection"
 :classpath (concat "~/.m2/repository/mysql/mysql-connector-java/5.1.6/"
                     "mysql-connector-java-5.1.6.jar")
 :subprotocol "mysql"
 :subname "//localhost:3306/my_db_name"
 :user "a_user"
 :password "secret")

If you want to see MySQL-specific keywords in autocompletion list, please provide access to mysql.help_keyword table for your user, e.g.:

GRANT SELECT ON mysql.help_keyword TO a_user;

MS SQL Server connection

;; MS SQL Server example
(ejc-create-connection
 "MS-SQL-db-connection"
 :classpath (concat "~/.m2/repository/com/microsoft"
                     "/sqlserver/sqljdbc/4.2/sqljdbc-4.2.jar")
 :subprotocol "sqlserver"
 :subname "//localhost:1433"
 :user "a_user"
 :password "secret"
 :database "my_db_name")

;; MS SQL Server example (via URI)
(ejc-create-connection
 "MS-SQL-db-connection-uri"
 :classpath (concat "~/.m2/repository/com/microsoft"
                     "/sqlserver/sqljdbc/4.2/sqljdbc-4.2.jar")
 :connection-uri (concat "jdbc:sqlserver://localhost\\\\instance:1433;"
                         "databaseName=my_db_name;"
                         "user=a_user;"
                         "password=secret;"))

;; MS SQL Server example (via JTDS)
(ejc-create-connection
 "MS-SQL-db-connection-JTDS"
 :classpath (concat "~/.m2/repository/net/sourceforge/jtds"
                     "/jtds/1.3.1/jtds-1.3.1.jar")
 :connection-uri (concat "jdbc:jtds:sqlserver://localhost:1433/dbname;"
                         "instance=instance;"
                         "user=a_user;"
                         "password=secret;"))

Oracle connection

;; Oracle example (via Service Name)
(ejc-create-connection
 "Oracle-db-connection-sname"
 :classpath (concat "~/.m2/repository/com/oracle/jdbc"
                    "/ojdbc8/12.2.0.1/ojdbc8-12.2.0.1.jar")
 :dbtype "oracle"
 :dbname "my_service_name"
 :host "localhost"
 :port "1521"
 :user "a_user"
 :password "secret"
 :separator "/")

;; Oracle example (via SID)
(ejc-create-connection
 "Oracle-db-connection-sid"
 :classpath (concat "~/.m2/repository/com/oracle/jdbc"
                     "/ojdbc7/12.1.0.2/ojdbc7-12.1.0.2.jar")
 :dbtype "oracle:sid"
 :dbname "my_sid_name"
 :host "localhost"
 :port "1521"
 :user "a_user"
 :password "secret"
 :separator "/")

;; Oracle example (via URI)
(ejc-create-connection
 "Oracle-db-connection-uri"
 :classpath (concat "~/.m2/repository/com/oracle/jdbc"
                     "/ojdbc7/12.1.0.2/ojdbc7-12.1.0.2.jar")
 :connection-uri "jdbc:oracle:thin:@localhist:1521:dbname"
 :user "a_user"
 :password "secret"
 :separator "/")

H2 connection

;; H2 example
(ejc-create-connection
 "H2-db-connection"
 :classpath (file-truename
             "~/.m2/repository/com/h2database/h2/1.4.191/h2-1.4.191.jar")
 :subprotocol "h2"
 :subname "file://~/projects/my_proj/db/database;AUTO_SERVER=TRUE"
 :user "a_user"
 :password "secret")

;; H2 remote example
;; run on remote server first:
;; java -jar ~/.m2/repository/com/h2database/h2/1.4.192/h2-1.4.192.jar -tcpAllowOthers
(ejc-create-connection
 "H2-remote-db-connection"
 :classpath "~/.m2/repository/com/h2database/h2/1.4.192/h2-1.4.192.jar"
 :connection-uri (concat "jdbc:h2:tcp://192.168.0.1:9092/~/db/database;ifexists=true;"
                         "user=a_user;"
                         "password=secret;"))

SQLite connection

;; SQLite example
(ejc-create-connection
 "SQLite-conn"
 :classpath (concat "~/.m2/repository/org/xerial/sqlite-jdbc/"
                    "3.23.1/sqlite-jdbc-3.23.1.jar")
 :subprotocol "sqlite"
 ;; Use absolute path, e.g.:
 ;;   "file:///home/user/projects/my_proj/db/sqdb.db"
 ;;   "/home/user/projects/my_proj/db/sqdb.db"
 ;;   "file:///C:/Projects/my_proj/db/sqdb.db"
 ;;   "C:/Projects/my_proj/db/sqdb.db"
 ;; or expand it by file-truename (not applicable for Windows paths):
 :subname (concat "file://"
                  (file-truename "~/projects/my_proj/db/sqdb.db")))

PostgreSQL connection

;; PostgreSQL example
(ejc-create-connection
 "PostgreSQL-db-connection"
 :classpath (concat "~/.m2/repository/postgresql/postgresql/9.3.1102.jdbc41/"
                     "postgresql-9.3-1102.jdbc41.jar")
 :subprotocol "postgresql"
 :subname "//localhost:5432/my_db_name"
 :user "a_user"
 :password "secret")

Usage

Basic use case

First of all, open your SQL buffer file (or any sql-mode buffer).

On the other hand, there is a handy function to create temporary sql-mode buffers for playing with SQL: ejc-get-temp-editor-buffer. If you bind it, e.g. to:

(global-set-key (kbd "C-c eb") 'ejc-get-temp-editor-buffer)

then, when you press C-c eb, *ejc-sql-editor* buffer will be created; when you press M-1 C-c eb, *ejc-sql-editor-1* buffer will created and so on. This buffers can be saved as ordinary file buffers by save-buffer command to the appropriate files, located in ejc-temp-editor-file-path directory ("~/tmp/ejc-sql/" by default).

In any selected SQL buffer connect to your database:

M-x ejc-connect <RET> MySQL-db-connection <RET>.

and wait until "Connected." message appears. This will add connection information to buffer local variables. Furthermore, if there is no ejc-sql dedicated Clojure REPL running, it will start it.

Since connection information is buffer-local, you should run ejc-connect for any new buffer. Any of ejc-sql-mode buffers can keep connection information to different databases and database types. But they use the same ejc-sql dedicated Clojure REPL to interact with databases via JDBC.

Then type your queries like this:

select something from my_table

and press C-c C-c to run it.

Use / char to separate expressions to evaluate (actually \n/), e.g.:

select something from my_table
/
select other from other_table

It's possible to run multiple statements, you can use ; to separate them:

insert into my_table (product, price) values ('socks', 1.25);
insert into my_table (product, price) values ('sweater', 14.56);
insert into my_table (product, price) values ('jeans', 25.30);
/
select * from my_table

Have much fun!

Use with org-mode

You can run M-x ejc-connect <RET> my-db-connection <RET> in org-mode buffers. In this case, major-mode will persists as org-mode, but all connection-related data will be added to the buffer.

* Create DB
** Product table
*** Create
#+begin_src sql
CREATE TABLE product (
  id    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  name  VARCHAR(30)   NOT NULL,
  price DECIMAL(7,2)
);
#+end_src

*** Fill
#+begin_src sql
INSERT INTO product (name, price) VALUES ('socks', 1.25);
INSERT INTO product (name, price) VALUES ('sweater', 14.56);
INSERT INTO product (name, price) VALUES ('jeans', 25.30);
#+end_src

*** Select
#+begin_src sql
SELECT * FROM product;
/
SELECT * FROM product WHERE name = 'jeans';
#+end_src

#+RESULTS:
: id | name    | price
: ---+---------+------
: 1  | socks   | 1.25
: 2  | sweater | 14.56
: 3  | jeans   | 25.30

Place point (cursor) into code snippet and run SQL statements via C-c C-c as always. For org-mode buffers code snippets borders considered as batch of SQL statement(s) boundaries. Furthermore, you can use ejc-sql-separator (/ by default) to divide batch of SQL statement(s) inside code block as in sql-mode buffers.

The SQL query evaluation result will be added to this org-mode buffer in #+RESULTS: section - an expected behaviour for org-mode users by default (see example above).

To avoid this behaviour and get results in popup window - as ejc-sql users expected, add to your .emacs:

(setq ejc-org-mode-show-results nil)

If your org-mode buffer connected via ejc-connect, any time you run C-c ' (org-edit-special) for code snippets, you will get new buffer with this minor-mode (ejc-sql-mode) and all connection-related data. So, you can operate inside it like in ordinary sql-mode buffer, which is already connected to the database.

You can use both ejc-sql and org-mode original org-babel execution engine simultaneously in one buffer.

To disable ejc-sql wrapper around org-mode SQL source code blocks, set ejc-org-mode-babel-wrapper to nil (enabled by default).

If ejc-org-mode-babel-wrapper is enabled and the current SQL source code block has a connection header arguments, you will be asked for confirmation.

Reference this discussion.

Use existing nREPL

If you have to restart Emacs multiple times, you can keep the ejc-sql Clojure backend alive between Emacs restarts by running this backend out of Emacs, and connect to it from Emacs.

To accomplish that, you should cd to your ejc-sql project folder (typically ~/.emacs.d/elpa/ejc-sql-<version>) and launch the nREPL via lein repl.

Then run in Emacs M-x ejc-connect-existing-repl, type Host and Port from your lein repl console output.

Finally, use M-x ejc-connect from any SQL buffer to connect to the exact database, as always.

List of keybindings & functions

New keybindings defined in ejc-sql-mode minor mode:

Keyboard shortcut Command Description
C-c C-c ejc-eval-user-sql-at-point Evaluate SQL/JPQL script bounded by the ejc-sql-separator or/and buffer boundaries.
C-g ejc-cancel-query Terminate current running query or run keyboard-quit if there is no running queries.
C-h t ejc-describe-table Describe SQL table.
C-h d ejc-describe-entity Get entity definition: show creation SQL of view, package, function, procedure or type.
C-c e up ejc-show-last-result Show last result.
C-c e t ejc-show-tables-list Show tables list.
C-c e v ejc-show-views-list Show views list.
C-c e p ejc-show-procedures-list Show stored procedures list.
C-c e T ejc-show-user-types-list Show user types list.
C-c e s ejc-strinp-sql-at-point Strip SQL (trim java string tokens).
C-c e S ejc-dress-sql-at-point Dress SQL (to copy-paste it to java code).
C-c e f ejc-format-sql-at-point Format (pretty-print) this SQL statement.
C-M-b ejc-previous-sql Goto previous SQL statement.
C-M-f ejc-next-sql Goto next SQL statement.
C-M-S-b ejc-previous-sql Select from point to previous SQL statement.
C-M-S-f ejc-next-sql Select from point to next SQL statement.

List of other interactive functions

Command Description
ejc-connect Connect to database for current buffer
ejc-quit-connection Close all database connections, quit Clojure REPL.
ejc-format-sql-region Format (pretty-print) selected SQL snippet
ejc-mark-this-sql Mark SQL script bounded by the ejc-sql-separator or/and buffer boundaries
ejc-show-tables-list Show tables list
ejc-show-constraints-list Show constraints list
ejc-open-log Open log
ejc-get-temp-editor-buffer Create ad-hoc SQL editor buffer, use prefix arg number to get many buffers
ejc-invalidate-cache Clean your current connection cache (database owners and tables list)
ejc-direx:pop-to-buffer Create buffer with database structure tree

Yasnippet

List of snippets:

select where
insert begin
update grant
delete revoke

Troubleshooting

Error running timer ‘ac-update-greedy’: (error "Sync nREPL request timed out (op eval session...

Increase nrepl-sync-request-timeout, e.g.:

(setq nrepl-sync-request-timeout 60)

Requirements:

License

Copyright © 2012-2019 Kostafey kostafey@gmail.com and contributors

Distributed under the General Public License 2.0+

You can’t perform that action at this time.