EmacSQL Documentation

Queries are written in a Lisp-style prefix notation. If a symbol looks like an operator, EmacSQL treats it as one.

Special Operators:

• Range: <= and >= accept 2 or 3 operands and transform into SQL BETWEEN logic.
• Function calls: Use the funcall operator for SQL functions like count or max.
• Concatenation: The || operator is unsupported to ensure all values remain readable within SQLite.

Important Note on Quoting:

• Identifiers vs Values: EmacSQL cannot distinguish between a symbol used as a column name and a symbol used as a value. To use a symbol as a value, you must quote it (e.g., 'hiking).
• Raw Strings: Quoting a string (e.g., "%foo%") makes it a "raw string" that is not printed/escaped. Use this for LIKE patterns or ATTACH commands.

;; Using funcall for aggregate functions
[:select (funcall max age) :from people]

;; Quoting a symbol to treat it as a value
(emacsql db [... :where (= category 'hiking)])

;; Using raw strings for pattern matching
(emacsql db [... :where (like name '"%foo%")])

EmacSQL uses EIEIO to provide a generic interface for database connections. To implement a new back-end, you must define a new class that inherits from emacsql-connection.

To ensure a consistent experience for users, your implementation should aim to normalize the interface by following these requirements:

• Core Functions: Implement emacsql-send-message, emacsql-waiting-p, emacsql-parse, and emacsql-close.
• Lifecycle: Provide a constructor that initializes the connection and calls emacsql-register for automatic cleanup. It is also recommended to provide emacsql-reconnect.
• Data Handling: Ensure NULL is read as nil. You can achieve this by having your back-end print NULL in a way that maps to nil.
• Types: Provide emacsql-types if necessary (using a class-allocated slot is recommended).
• Configuration:
  • Set the default isolation level to *serializable*.
  • Enable autocommit mode by default.
  • Enable foreign key constraints by default.
  • Prefer ANSI syntax (for value escapes, identifier escapes, etc.).
• Reserved Words: Register all reserved words using emacsql-register-reserved.

Tip: If your back-end outputs data in a clean, standard way, you can use the emacsql-protocol-mixin class to simplify much of the implementation.

To prevent SQL injection and improve performance, always use prepared statements (vectors of keywords and Lisp objects) instead of manual string concatenation. The statement compiler is memorized for speed.

Template Parameters: Use $tn placeholders to insert arguments. The letter indicates the type, and the number is the 1-indexed position of the argument following the emacsql call.

Type Prefixes:

• $i: Identifier
• $s: Scalar
• $v: Vector (or multiple vectors)
• $r: Raw (unprinted strings)
• $S: Schema

Debugging: Use M-x emacsql-show-last-sql to see the actual SQL generated from your s-expression.

;; Using templates for identifiers and scalars
(emacsql db [:select * :from $i1 :where (> salary $s2)] 'employees 50000)

;; Using raw string templates
(emacsql db [:select * :from employees :where (like name $r1)] "%Smith%")

;; Using vector templates for multiple rows in :values
(emacsql db [:insert-into favorite-characters :values $v1]
            '([0 "Calvin"] [1 "Hobbes"] [3 "Susie"]))

EmacSQL is a high-level Emacs Lisp front-end for SQLite designed to act as an ACID-compliant database for Emacs extensions. It allows you to store any readable Lisp value (numbers, strings, symbols, lists, vectors, and closures) directly.

Key characteristics:

• Lisp-centric storage: It does not use standard SQL types like TEXT. Instead, it stores the printed s-expression of the Lisp object. This means it is not intended to be used with non-Emacs clients.
• Nil mapping: The Lisp object nil corresponds 1:1 with NULL in the database.
• Requirements: Requires Emacs 26 or later.
• Windows Support: Only SQLite is supported on Windows. emacsql-mysql and emacsql-psql are not supported because they rely on start-process-shell-command, which is unavailable on Windows.

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

;; Create a table
(emacsql db [:create-table people ([name id salary])])

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

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

A table schema is defined as a list where the first element is a vector of column specifications, followed by optional table constraints.

Column Specifications:

• A column can be a simple symbol (the identifier).
• A column can be a list containing the identifier and constraints like :unique, :primary-key, or a type (integer, float, or object which is the default).

Table Constraints:

• Constraints like :unique or :check follow the column specifications in the main list.
• Foreign keys are supported using the :foreign-key keyword.

Note: Dashes in identifiers are automatically converted to underscores during SQL compilation.

;; Schema with column and table constraints
([(name :unique) (id integer :primary-key) building room]
 (:unique [building room])
 (:check (> id 0)))

;; Schema with foreign keys
([(id integer :primary-key) subject])
([(subject-id integer) tag]
 (:foreign-key [subject-id] :references subjects [id]
               :on-delete :cascade))

When converting Lisp s-expressions to SQL, EmacSQL follows these rules:

1. Statements are Vectors: Every prepared statement must be a vector starting with a keyword.
2. Keyword Transformation: Keywords are split and capitalized. :if-not-exists becomes IF NOT EXISTS. Dashes in keywords become spaces.
3. Symbols as Identifiers: Standalone symbols are treated as identifiers (e.g., people in :insert-into people).
4. Vectors for Rows: Row-oriented data (inserted rows or selected columns) must be represented as vectors.
5. Lists as Expressions: Lists are treated as Lisp-style expressions (e.g., (/ seconds 60)).
6. Special Keywords: :values expects a vector or a list of vectors.
7. Schema Detection: A list whose first element is a vector is recognized as a table schema.

;; Example of keyword splitting and identifier usage
[:insert-into people :values [1 2 3]]

;; Example of row-oriented vectors
[:select [id name] :from people]

;; Example of list as expression
[:select [(/ seconds 60) count] :from people]