EmacSQL: storing Lisp objects in SQLite, and why the values are all strings

Everything in the database is a printed Lisp value

EmacSQL is described as a high-level Emacs Lisp front-end for SQLite, and the value model is the whole design. Any readable Lisp value can be stored, including numbers, strings, symbols, lists, vectors and closures. There is no concept of TEXT values, it is all just Lisp objects, and the Lisp object nil corresponds one to one with NULL in the database. PostgreSQL and MySQL are also supported, but the README says use of these connectors is not recommended. The reason appears in the FAQ, and it is the single most important thing to know before you use the library. EmacSQL is not intended to interact with arbitrary databases, but to be an ACID-compliant database for Emacs extensions, which means it cannot be used with a regular SQL database used by other non-Emacs clients. All database values must be s-expressions, and a value is printed and written to the database in its printed form, with strings wrapped in quotes and escaped as necessary, so bare symbols in the database generally look like strings. The only exception is nil, stored as NULL.

The example query loop, with prepared statement templates

The usage example is a compact tour of the API. You open a database, create a table with symbol identifiers for table and column names, insert rows, and query.

  (defvar db (emacsql-sqlite-open "~/company.db"))

  ;; Create a table. Table and column identifiers are symbols.
  (emacsql db [:create-table people ([name id salary])])

  ;; Or optionally provide column constraints.
  (emacsql db [:create-table people
               ([name (id integer :primary-key) (salary float)])])

  ;; Insert some data:
  (emacsql db [:insert :into people
               :values (["Jeff" 1000 60000.0] ["Susan" 1001 64000.0])])

  ;; Query the database for results:
  (emacsql db [:select [name id]
               :from people
               :where (> salary 62000)])
  ;; => (("Susan" 1001))

Statements can also be templates, with parameters written as dollar-prefixed names, and the README notes the M-x emacsql-show-last-sql command, which it compares to eval-last-sexp, for seeing what the actual SQL will become when the statement is compiled. That is a genuinely useful debugging tool for a DSL, and the fact that it exists at all tells you the compiler is doing more than a simple substitution.

Schemas, constraints and the fact that only three types exist

A table schema is a list whose first element is a vector of column specifications, and the rest of the list specifies table constraints. A column identifier is a symbol, and a column's specification can be just that symbol or it can include constraints as a list. Because EmacSQL stores entire Lisp objects as values, the only relevant and allowed types are integer, float and object, with object as the default. Constraints follow simple rules.

  ;; No constraints schema with four columns:
  ([name id building room])

  ;; Add some column constraints:
  ([(name :unique) (id integer :primary-key) building room])

  ;; Add some table constraints:
  ([(name :unique) (id integer :primary-key) building room]
   (:unique [building room])
   (:check (> id 0)))

Foreign keys are supported and are enabled by default, and the example shows the reference syntax with a cascade on delete. One convention worth knowing is that dashes in identifiers are turned into low lines when compiled into SQL, which is what allows Lisp-style identifiers to be used in SQL without quoting them.

Operators, the missing concatenation operator, and quoting

Expressions are written Lisp style with the operator first, and if something looks like an operator EmacSQL treats it as one, though several are special. The less-than-or-equal and greater-than-or-equal operators accept two or three operands and transform into a SQL BETWEEN AND operator as appropriate. For function-like operators such as count and max there is a funcall operator, so a max over a column is written as a funcall form rather than as a bare function call. The glob and like operators match the printed representations of values, which matters because even a string is matched in its printed form. The concatenation operator is unsupported, and the reason given is precise: concatenating printed representations breaks an important constraint, since all values must remain readable within SQLite. Quoting has a similar subtlety, because inside an expression EmacSQL cannot tell a symbol literal from a column reference, so you quote it as you would in normal Elisp, and a quoted string becomes a raw string that is not printed when assembled into a query. Raw strings are intended for special cases like filenames in an ATTACH or pattern matching, and the README is emphatic that they must not be returned as results.

Prepared statements, and the compiler that memoises them

The database is interacted with via prepared SQL s-expression statements, and the README says you should not normally be concatenating strings on your own, partly because it leaves out any possibility of a SQL injection. A statement is a vector of keywords and other Lisp objects. Prepared statements are compiled into SQL, and the statement compiler is memoised so that using the same statement multiple times is fast. A statement can also act as a template, using parameters written in the style of the Elisp format function. The compilation rules are described in the README, and the first point is that rather than the typical uppercase SQL keywords, the keywords in a prepared statement are literally just that, Lisp keywords. EmacSQL only understands a very small amount of SQL's syntax, and the compiler follows some simple rules to convert an s-expression into SQL. This is a DSL with a narrow surface, and the small syntax is what makes the memoisation and the readback of the compiled SQL practical.

Maintenance mode, the Windows gap, and a plain Elisp build

Two constraints are worth knowing up front. The project is in maintenance mode, and the README says there are no plans to make any fundamental changes, not least because they would break all existing packages and databases that rely on the current behaviour. The author also admits in the FAQ that restricting the library to Emacs by storing only printed values was probably a design mistake, and that it would be a lot more useful if it just handled primitive database types, which is a candid statement of the trade you are accepting. On Windows, the Emacs start-process-shell-command function is not supported, and since both the MySQL and the PostgreSQL connectors rely on it, neither connection type works there. The SQLite path is unaffected. The project itself is built with a Makefile that includes a config.mk and a default.mk, with targets to generate byte-code and autoloads, run the tests interactively or not, and produce statistics for the documentation. The licence is the Unlicense, and the source is a set of Emacs Lisp files, one per backend, plus a compiler file and a test directory.

Editorial conclusion

EmacSQL fits an Emacs package author who needs ACID-compliant local storage for a cache, a bookmark list or a set of notes, and who is happy for the data to be readable rather than portable. It does not fit anything that needs to share a database with a non-Emacs client, since the FAQ is explicit that the printed-value design rules that out, and it does not fit a Windows user who needs the PostgreSQL or MySQL connectors. Verify first that Emacs 28 or later is what you have, and note that the project is in maintenance mode with no plans for fundamental changes, so anything you build on it will be built on a stable but frozen base.

Frequently asked questions

Official sources

1. Issues
2. License: Unlicense
3. magit/emacsql on GitHub
4. README