(Image courtesy Sajith T S under Creative Commons license.)

In this post I’ll describe how to hack up the Postgres config for sql.el to work with CockroachDB (aka “CRDB”).

In the future I’d like to submit code to sql.el to make CRDB a fully-supported option, but for now this is what I’ve been using.

Note that these instructions assume you are running a secure local cluster. If you are running insecure, you can avoid all of this and just M-x sql-postgres and use most of the defaults, modulo port numbers and such. It uses psql on the backend which works because CRDB speaks the Postgres wire format.

However, once you get up and running for real and want to use a secure network connection, it’s easier to use the cockroach sql client. That’s what we’ll configure in this post.

(It may be possible to configure psql to use the CRDB certs, but I don’t know since I haven’t looked into it. Also, keep in mind that I have not tested this setup over the network yet – only on my local machine.)

Step 1. Modify basic config

Since the client is invoked by comint as two “words”, cockroach sql , you have to mess with the options a bit.

First set the cockroach binary:

(setq sql-postgres-program "cockroach")

Then invoke the SQL client with the first arg, and pass options in with the rest. The certs directory is where your encryption certificates are stored. Since this is an ephemeral local cluster I’m using the temp directory.

(setq sql-postgres-options '("sql" "--certs-dir=/tmp/certs"))

Finally the login params are pretty standard. My local clusters are not usually long-lived, so I just use the “root” user. This would not be recommended on real systems of course.

(setq sql-postgres-login-params '((user :default "root") (database :default "") (server :default "localhost") (port :default 26500)))

Step 2. Modify the Postgres “product” to work with CRDB

Sql.el calls each of its supported databases “products”. for whatever reason.

In any case, here’s how to modify the Postgres product to work for CRDB.

First we need a new function to talk to comint.el. (See the bottom of this post for the definition since it’s longer and not interesting.)

(sql-set-product-feature 'postgres :sqli-comint-func #'sql-comint-cockroach)

The usual comint prompt regexp things. This one isn’t that well tested but works on my machine ™ … so far.

(sql-set-product-feature 'postgres :prompt-regexp "^[a-z]+\@[a-zA-Z0-9\.-_]+:[0-9]+/\\([a-z]+\\)?> ")

I don’t really know what this does. The CRDB prompt is not necessarily of a fixed length so it doesn’t really apply. It seems to have no effect, I just cargo culted it from some other DBs. Probably not needed.

(sql-set-product-feature 'postgres :prompt-length 0)

Regexp to match CRDB’s little continuation marker thingy:

(sql-set-product-feature 'postgres :prompt-cont-regexp "^ +-> ")

Set the “end of a SQL statement” character to the semicolon. Some of the other DBs have some pretty fancy settings here, but this seems to mostly work.

(sql-set-product-feature 'postgres :terminator '(";" . ";"))

Command to show all the tables in the current database.

(sql-set-product-feature 'postgres :list-all "SHOW TABLES;")

And finally, this is the comint function we need to work with CRDB:

(defun sql-comint-cockroach (product options) "Create comint buffer and connect to CockroachDB." (let ((params (append (if (not (= 0 sql-port)) (list "--port" (number-to-string sql-port))) (if (not (string= "" sql-user)) (list "--user" sql-user)) (if (not (string= "" sql-server)) (list "--host" sql-server)) options (if (not (string= "" sql-database)) (list sql-database))))) (sql-comint product params)))