ejc-sql

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

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

• Installation
• Configuration
  • Set httpd port
  • Autocomplete
  • Fuzzy matching
  • Company mode
  • Minibuffer completion
  • ElDoc
  • Performance & output customization
  • Create connections interactively
  • Create connections manualy
    • Install JDBC drivers
    • MySQL connection
    • MariaDB connection
    • MS SQL Server connection
    • Oracle connection
    • H2 connection
    • SQLite connection
    • PostgreSQL connection
    • Informix connection
    • Presto connection
    • ClickHouse connection
    • ElasticSearch connection
• Usage
  • Basic use case
  • Separators & delimiters
  • Use with org-mode
  • Use existing nREPL
    • Dedicated ejc-sql nREPL
    • Different project nREPL
  • Goto definition & results ring
• List of keybindings & functions
• Yasnippet
• Troubleshooting
• Requirements
• License

Installation

1. To run Clojure, install Leiningen (assuming you havealready installed Java 7+).

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

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

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

Configuration

Here is an full-fledged real-world ejc-sql configuration example:ejc-sql-conf.

First, load ejc-sql package:

(require 'ejc-sql)

Set httpd port

To achieve async SQL queries evaluation, both Emacs and JVM side is an HTTPclient and HTTP server. Emacs as HTTP client via CIDER pass a SQL query to JVMand don't expect any data response from the database. The JVM part prints theresult dataset to the file (in pain text table format). Then JVM as HTTP clientnotifies Emacs: "data printed into filepath, please refresh the outputbuffer". The JVM side port can be configured by related CIDER customizations,whereas the default Emacs side HTTP server port can be customized byclomacs-httpd-default-port variable (8080 by default):

(setq clomacs-httpd-default-port 8090) ; Use a port other than 8080.

Autocomplete

Install auto-complete e.g. by the following command:M-x package-install [RET] auto-complete [RET]Enable autocomplete for ejc-sql minor mode:

(require 'ejc-autocomplete)
(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

Autocompletion data is stored in the database structure cache. This cache islocated on Clojure side, so it's global: the same database structure informationis shared between different buffers connected to the same database. An attemptto autocomplete requires data from cache or lanches a thread aimed to createit. If Clojure side has the database structure cache, autocompletion variantsare returned immediately. If not, the database structure cache creation processstarts. It's async, so the process of Emacs is not blocked, and the user canmove the point (cursor), edit SQL, and so on. If the user waits forautocompletion and doesn't move point (cursor) during this process, he will getautocompletion variants. In order to checkout the current database connectioncache run ejc-print-cache.

Any successfully executed DDL query (CREATE, ALTER, DROP, RENAME) clearscurrent connection cache, so next autocompletion attempt will recreate it.To clean the current connection cache manually, you can runejc-invalidate-cache.

Fuzzy matching

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

(setq ejc-use-flx t)

To customize the minimum number of typed chars use flx for autocompletion,2 by default:

(setq ejc-flx-threshold 2)

Company mode

Install company-mode e.g. by the following command:M-x package-install [RET] company [RET]Enable company-mode completion frontend for ejc-sql minor mode:

(require 'ejc-company)
(push 'ejc-company-backend company-backends)
(add-hook 'ejc-sql-minor-mode-hook
          (lambda ()
            (company-mode t)))

If you want to automatically start completion after inserting a dot forcompany-mode despite company-minimum-prefix-length is bigger than 0,set ejc-complete-on-dot to t:

(setq ejc-complete-on-dot t)

To show documentation quickhelp install company-quickhelp by:M-x package-install [RET] company-quickhelp [RET]

To activate company-quickhelp add the following to your .emacs:

(company-quickhelp-mode)

Minibuffer completion

By default ido is used as minibuffer the completion system. You can changethis to leverage another option by editing ejc-completion-system andselecting standard. This will allow you to use it with any configuredcompletion mechanism for example, ivy:

(setq ejc-completion-system 'standard)

ElDoc

Enable ElDoc for ejc-sql minor mode:

(add-hook 'ejc-sql-minor-mode-hook
          (lambda ()
            (ejc-eldoc-setup)))

ElDoc for functions and procedures is available for the following databases:

• Oracle
• PostgreSQL
• MySQL

Performance & output customization

ejc-set-fetch-size sets limit for the number of records to output (50 bydefault). Set to nil if you want to disable this limit.

ejc-set-max-rows sets the limit for the number of records to contain inResultSet (99 by default). Set to nil if you want to disable this limit, oryou can set it the same value as ejc-set-fetch-size to increase select queryexecution performance.

Any time your ResultSet is bigger than ejc-set-fetch-size you will receivemessages like "Too many rows. Only 50 from 99+ are shown.". To inhibit thismessages you can set ejc-set-show-too-many-rows-message to nil (t bydefault).

ejc-set-column-width-limit sets limit for outputing the number of chars percolumn (30 by default). The rest will be replaced by .... Set tonil if you want to disable this limit. This setting is applied to the textrepresentation of any field type, but it is especially useful for varchar andCLOB fields.

ejc-set-use-unicode sets using unicode for grid borders, e.g. use ─┼─instead of -+- (nil by default).

All these functions change Clojure variables, so if you want to changedefaults, to avoid Clojure nREPL autolaunch on Emacs start, you should addthem to the ejc-sql-connected-hook in your .emacs, e.g.:

(add-hook 'ejc-sql-connected-hook
          (lambda ()
            (ejc-set-fetch-size 50)
            (ejc-set-max-rows 50)
            (ejc-set-show-too-many-rows-message t)
            (ejc-set-column-width-limit 25)
            (ejc-set-use-unicode t)))

Current result set table minor-mode is orgtbl-mode. This mode provides somefunctionality for post-processing and browsing the query results.

(setq ejc-result-table-impl 'orgtbl-mode)

Alternatively, you can use a simple and bare result set mode to maximize thebuffer performance by setting ejc-result-table-impl to 'ejc-result-mode.

If you want to see the full text of some field (e.g. the full text of CLOBfield) despite ejc-set-column-width-limit, and your ejc-result-table-implis 'ejc-result-mode you can select a single-record result set(e.g. SELECT * FROM table WHERE id = 1).

If you want to see the full text of some field with newlines in case ofmultiline fields, you should select single-record and single-column result set(e.g. SELECT field FROM table WHERE id = 1). So, you will get a field valueas-is despite ejc-set-column-width-limit and ejc-result-table-impl.

To illustrate the description above here are some output examples of queryresults that depend on configuration.

Assume you have the following database (this example uses MySQL):

CREATE TABLE product (
  id    INT UNSIGNED  NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(30)   NOT NULL,
  quantity INT,
  price DECIMAL(7,2),
  description VARCHAR(255)
);
INSERT INTO product (name, price, quantity, description)
VALUES ('socks', 1.25, 10, CONCAT('A sock is an item of clothing worn\n',
                                  'on the feet and often covering the\n',
                                  'ankle or some part of the calf.\n',
                                  'Some type of shoe or boot is\n',
                                  'typically worn over socks.'));
INSERT INTO product (name, price, quantity, description)
VALUES ('sweater', 14.56, 5, CONCAT('A sweater, also called a jumper\n'
                                    'in British English, is a piece\n'
                                    'of clothing, typically with long\n'
                                    'sleeves, made of knitted or\n'
                                    'crocheted material that covers\n'
                                    'the upper part of the body.'));

output examples for orgtbl-mode (by default):

SELECT * FROM product

| id | name    | quantity | price | description                    |
|----+---------+----------+-------+--------------------------------|
|  1 | socks   |       10 |  1.25 | A sock is an item of clothi... |
|  2 | sweater |        5 | 14.56 | A sweater, also called a ju... |

SELECT * FROM product WHERE id = 1

| id | name  | quantity | price | description                    |
|----+-------+----------+-------+--------------------------------|
|  1 | socks |       10 |  1.25 | A sock is an item of clothi... |

SELECT description FROM product WHERE id = 1

| description                        |
|------------------------------------|
| A sock is an item of clothing worn |
| on the feet and often covering the |
| ankle or some part of the calf.    |
| Some type of shoe or boot is       |
| typically worn over socks.         |

output examples for ejc-result-mode:

SELECT * FROM product

id | name    | quantity | price | description
---+---------+----------+-------+-------------------------------
1  | socks   | 10       | 1.25  | A sock is an item of clothi...
2  | sweater | 5        | 14.56 | A sweater, also called a ju...

SELECT * FROM product WHERE id = 1

id          | 1
name        | socks
quantity    | 10
price       | 1.25
description | A sock is an item of clothing worn on the feet and often covering the ankle or some part of the calf. Some type of shoe or boot is typically worn over socks.

SELECT description FROM product WHERE id = 1

description
----------------------------------
A sock is an item of clothing worn
on the feet and often covering the
ankle or some part of the calf.
Some type of shoe or boot is
typically worn over socks.

Create connections interactively

The easiest way to create connections configuration is to use interactiveconnections creation.

In any sql-mode buffer run (C-c ei):

M-x ejc-connect-interactive <RET>

Then follow the creation steps: type your connection name, choosedatabase type, host (or file path depends on selected database type), port,user name and password.

ejc-sql uses Aether API ofMaven-resolver to automatically resolve and download the required JDBCdriver (if not yet) for selected database type.

You can customize artifacts and their versions used as JDBC drivers for eachdatabase type in Leiningen format in ejc-jdbc-drivers custom variable.

After you type all required data a and new connection will be created, itwill attempt to immediately connect current-buffer to this connection.Then you can use this connection name to connect from different buffers.Type (C-c ec):

M-x ejc-connect <RET> your-connection-name <RET>

This connection will be available during the current Emacs session. To keepit between Emacs restarts, you can open your .emacs file or any file,loaded as Emacs configuration, locate point (cursor) somewhere after(require 'ejc-sql) expression and run:

M-x ejc-insert-connection-data <RET> your-connection-name <RET>

This function inserts ejc-create-connection expression the same as you canaccomplish via manual connection creation.

Create connections manualy

Install JDBC drivers

In most cases, you don't need to install JDBC drivers manually.Simply put, you can set a parameter :dependencies in ejc-create-connectionfunction as a vector of the required artifacts in Leiningen format.In this case, ejc-sql will resolve, download (if not yet) allrequired jar dependencies via Aetherand load them to CLASSPATH during the ejc-connect function run. E.g.:

(ejc-create-connection
 "Informix-db-connection"
 :dependencies [[com.ibm.informix/jdbc "4.50.3"]]
 ...
 )

Alternatively, you can pass the exact JDBC driver jar file in the:classpath parameter of ejc-create-connection function. E.g.:

(ejc-create-connection
 "Informix-db-connection"
 :classpath (concat "~/.m2/repository/com/ibm/informix/jdbc/4.50.3/"
                    "jdbc-4.50.3.jar")
 ...
 )

ejc-sql will try to resolve all required dependencies if this JBDC driverrequires some dependencies itself anyway. But you can pass all requreddependencies manually as a vector of jar files paths. E.g.:

(ejc-create-connection
 "Informix-db-connection"
 :classpath (vector
              (concat "~/.m2/repository/org/mongodb/bson/3.8.0/"
                      "bson-3.8.0.jar")
              (concat "~/.m2/repository/com/ibm/informix/jdbc/4.50.3/"
                      "jdbc-4.50.3.jar"))
 ...
 )

The rest of this section describes how to download and install JDBC driversmanually. If you are familiar with JDBC, please omit it.

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 outdatedsoon, so please consult Google to install your database JDBC driver.

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

Oracle

Download JDBC driver manually fromoracle.com

Fix your actual JDBC version number -Dversion, filepath -Dfile and runcommand 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 frommicrosoft.com

Fix your actual JDBC version number -Dversion, filepath -Dfile and runcommand 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:get -Dartifact=com.microsoft.sqlserver:mssql-jdbc:6.2.2.jre8

JTDS

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

PostgreSQL

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

MySQL

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

MariaDB

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

H2

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

SQLite

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

Informix

mvn org.apache.maven.plugins:maven-dependency-plugin:get -Dartifact=com.ibm.informix:jdbc:4.50.3

Setup connections with ejc-create-connection function in your .emacs.It's first arg is your custom database connection name, the remaining argsare the same as database connection structure ofclojure/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 autocompletionlist, please provide access to mysql.help_keyword tablefor your user, e.g.:

GRANT SELECT ON mysql.help_keyword TO a_user;

MariaDB connection

;; MariaDB example
(ejc-create-connection
 "MariaDB-db-connection"
 :dependencies [[org.mariadb.jdbc/mariadb-java-client "2.6.0"]]
 :classname "org.mariadb.jdbc.Driver"
 :connection-uri "jdbc:mariadb://localhost:3306/db_name"
 :user "a_user"
 :password "secret")

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")

Informix connection

;; Informix example
(ejc-create-connection
 "Informix-db-connection"
 :dependencies [[com.ibm.informix/jdbc "4.50.3"]]
 :classname "com.informix.jdbc.IfxDriver"
 :connection-uri (concat
                  ;; In the case of IPv6, ::1 should be used
                  ;; as the host instead of localhost.
                  "jdbc:informix-sqli://localhost:8201/test:"
                  "INFORMIXSERVER=myserver;"
                  "user=a_user;"
                  "password=secret;"))

Presto connection

;; Presto example
(ejc-create-connection
 "Presto-db-connection"
 :subprotocol "presto"
 :dependencies [[com.facebook.presto/presto-jdbc "0.232"]]
 :connection-uri (concat
                  "jdbc:presto://localhost:1234/dbName/schemaName?"
                  "user=a_user"))

ClickHouse connection

;; ClickHouse example
(ejc-create-connection
  "ch@180"
  :dependencies [[ru.yandex.clickhouse/clickhouse-jdbc "0.2.6"]]
  :dbtype "clickhouse"
  :classname "ru.yandex.clickhouse.ClickHouseDriver"
  :connection-uri (concat "jdbc:clickhouse://10.1.4.180:8123/" "testdb"))

ElasticSearch connection

;; ElasticSearch example
(ejc-create-connection
  "es@177"
  :dependencies [[org.elasticsearch.plugin/x-pack-sql-jdbc "7.9.1"]]
  :dbtype "elasticsearch"
  :classname "org.elasticsearch.xpack.sql.jdbc.EsDriver"
  :connection-uri (concat "jdbc����//172.16.13.177:9200/"))

Usage

Basic use case

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

On the other hand, there is a handy function to create temporary sql-modebuffers 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 becreated; when you press M-1 C-c eb, *ejc-sql-editor-1* buffer willcreated and so on. This buffers can be saved as ordinary file buffers bysave-buffer command to the appropriate files, located inejc-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 connectioninformation to buffer local variables. Furthermore, if there is no ejc-sqldedicated Clojure REPL running, it will start it.

Since connection information is buffer-local, you should run ejc-connectfor any new buffer. Any of ejc-sql-mode buffers can keep connectioninformation to different databases and database types. But they use the sameejc-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.

Have much fun!

Separators & delimiters

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

select something from my_table
/
select other from other_table

So, you don't need to select SQL snippet, simply put point (cursor) into codesnippet and press C-c C-c (or desired keybinding). Borders of SQLwill be found by Emacs buffer begin/end or this / separator.

It's possible to pass multiple statements, you can use ; delimiter to separatethem:

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

Here, the first part is a single SQL snippet passed to ejc-sql backend butevaluated by 3 independent SQL statements (transactions). The output will lookslike this:

Records affected: 1
Records affected: 1
Records affected: 1

Furthermore, you can change the delimiter inside SQL snippet. E.g. in thisMySQL snippet ; replaced by $$ as transaction delimiter:

DELIMITER $$
CREATE PROCEDURE GetAllProducts()
BEGIN
  SELECT * FROM products;
END $$
/
CALL GetAllProducts();

Since ; symbols can be used very often as part of procedure syntax(e.g. in Oracle), you can disable splitting SQL code snippet to the sequence ofseparate transactions by setting :separator in DB connection configuration(see https://github.com/kostafey/ejc-sql#oracle-connection).

Use with org-mode

You can run M-x ejc-connect <RET> my-db-connection <RET> in org-modebuffers. In this case, major-mode will persists as org-mode, but allconnection-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 viaC-c C-c as always. For org-mode buffers code snippets bordersconsidered as batch of SQL statement(s) boundaries.Furthermore, you can use ejc-sql-separator (/ by default) to dividebatch 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 usersexpected, add to your .emacs:

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

If your org-mode buffer connected via ejc-connect, any time you runC-c ' (org-edit-special) for code snippets, you will get newbuffer 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 isalready connected to the database.

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

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

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

Reference this discussion.

Use existing nREPL

Dedicated ejc-sql nREPL

If you have to restart Emacs multiple times, you can keep the ejc-sql Clojurebackend alive between Emacs restarts by running this backend out of Emacs, andconnect 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 Portfrom your lein repl console output.

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

Different project nREPL

You can use different nREPL for ejc-sql, e.g. if you develop a Clojure projectvia CIDER, you can use your project nREPL to interact with the database by JDBCand ejc-sql. To achieve this, enable using any CIDER nREPL for clomacsprojects in your .emacs:

(setq clomacs-allow-other-repl t)

Then add ejc-sql to your project as a dependency in project.clj:

(defproject some-project "0.1.0-SNAPSHOT"
  ...
  :dependencies [[org.clojure/clojure "1.10.0"]
                 ...
                 [ejc-sql "0.4.1-SNAPSHOT"]]
  ...
  )

or if you don't want to change your project.clj file, you can add it globallyin ~/.lein/profiles.clj, e.g.:

{:user {:plugins [[cider/cider-nrepl "0.25.0-alpha1"]]
        :dependencies [[ejc-sql "0.4.1-SNAPSHOT"]]}}

The actual version of ejc-sql backend in Clojars: 

So, when you start your project nREPL via cider-jack-in, you can open any SQLfile (sql-mode or org-mode buffer) and connect to the database byejc-connect as usual and it will reuse the existing nREPL.

Goto definition & results ring

In terms of ejc-sql, SQL evaluation results can be result sets, recordaffected messages, SQL definition of entities or error messages.

Any SQL evaluation result saved to results ring - list of filesejc-sql-result-0.txt, ejc-sql-result-1.txt, and so on.They located in the TEMP folder, it can be customized by ejc-results-path.The number of files (number of previous results) can be customized by settingejc-ring-length (10 by default).

You can see previous SQL evaluation result by C-M-b(ejc-show-prev-result) in *ejc-sql-output* buffer.To return back use C-M-f (ejc-show-next-result). This way you cannavigate through the results ring.

Since *ejc-sql-output* buffer contains ejc-sql connection information, itmakes possible to navigate through views & stored procedures code definitions.E.g. you can require ejc-describe-entity for some stored procedure, thenrequire ejc-describe-entity inside *ejc-sql-output* for some storedprocedure, used in this (just described) procedure definition, and so on. Thenyou can return to previous procedure definition by ejc-show-prev-result. So,it looks like goto definition, then return back. For the purpose of convenience,the following keybindings are provided:

• M-. ejc-describe-entity
• M-, ejc-show-prev-result

List of keybindings & functions

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

List of other interactive functions

Yasnippet

List of snippets:

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:

• GNU Emacs 26.
• Leiningen 2.x
• clomacs
• clojure/java.jdbc 0.5.8
• dash
• spinner.el
• direx.el
• auto-complete (optional)
• company-mode (optional)
• company-quickhelp (optional)
• flx-ido (optional)
• yasnippet (optional).

License

Copyright © 2012-2020 Kostafey kostafey@gmail.com andcontributors

Distributed under the General Public License 2.0+