Spacemacs as database client

One of the first thing I did in my journey of trying to learn some Spacemacs was to make it a database client. After some work with the author of ejc-sql we got it to work with SQL Server and instances by using full connection URI. Should work great with any db, but as we all know SQL Server is a bit special. So I wrote this post to make it easier for others to find a way to work with SQL Server.

Thanks to kostafey and his work on ejc-sql and for helping me out to get it to work with SQL Server and instances. https://github.com/kostafey/ejc-sql

I’m in no meaning any expert in Spacemacs/Emacs and it’s configuration, so there might be way better options for this.

You should probably install Leiningen as described at ejc-sql first. The documentation is good, all of what I write is explained there.

So first of we need to add the package:

lisp
dotspacemacs-additional-packages '(ejc-sql)

Then it’s time to add a connection for your database under dotspacemacs/user-config, that is important. And don’t forget the require, mistake I made since I’m not very familiar with the syntax.

lisp
(require 'ejc-sql)
(ejc-create-connection
"SQL Server instance 01"
:classpath (concat
"/home/mynick/.m2/repository/com/microsoft"
"/sqlserver/sqljdbc4/4.2/sqljdbc4-4.2.jar")
:classname "com.microsoft.sqlserver.jdbc.SQLServerDriver"
:connection-uri (concat
"jdbc:sqlserver://mydomain.se\\instance01:4001;"
"databaseName=db_name;"
"user=sa;"
"password=S3cr3t"))

Don’t forget to double check your TCP port, in my case it’s 4001. You can see how to find it out here: https://msdn.microsoft.com/en-us/library/ms177440.aspx.

If your more familiar with JTDS, it’s fine to use that as well:

lisp
(require 'ejc-sql)
(ejc-create-connection
"SQL Server instance 01"
:classpath (concat
"/home/mynick/.m2/repository/"
"net/sourceforge/jtds/jtds/1.3.1/jtds-1.3.1.jar")
:classname "net.sourceforge.jtds.jdbc.Driver"
:connection-uri (concat
"jdbc:jtds:sqlserver://mydomain.se:1433/db_name;"
"instance=instance01;"
"user=sa;"
"password=S3cr3t"))

The configuration isn’t harder than that.

Here’s some commands, running Evil mode, hence the capital V.

First you need to connect to a configured database:
SPC-SPC ejc-sql-mode
SPC-SPC ejc-connect

I often has multiple SQL queries in the same buffer, so when I run a sql-statement I do:
V C-x S

That’s it.

1 thought on “Spacemacs as database client”

Comments are closed.