ejc-sql

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
• Install JDBC Drivers
• Configuration
  • Fuzzy matching
  • MySQL connection
  • MS SQL Server connection
  • Oracle connection
  • H2 connection
  • PostgreSQL connection
• Usage
  • Basic use case
  • Use with org-mode
  • Use existing nREPL
• Autocomplete
• Yasnippet
• Troubleshooting
• Requirements
• License

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]

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

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)

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

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

;; PostgreSQL example
(ejc-create-connetion
 "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")

First of all, open your SQL buffer file (or any temporary buffer) and connect to your database

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

and wait until "Connected." message appears. Since connection information is buffer-local, you should use ejc-connect for any new buffer. There is a handy function to create temporary buffer for playing with SQL: ejc-switch-to-sql-editor-buffer.

Then type you 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!

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-relatead 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, witch is already connected to database.

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.

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

List of other interactive functions

Autocompletion is available for the following databases:

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

List of snippets:

• select
• insert
• update
• begin

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)

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

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

Distributed under the General Public License 2.0+