NonGNU ELPA - sqlite3

The package is available on Melpa (thanks to @tarsius).

The first time you (require 'sqlite3), you will be asked to confirm the compilation of the dynamic module:

sqlite3-api module must be built.  Do so now?

After the module is successfully compiled, you should (unload-feature 'sqlite3) and then (require sqlite3) to reload properly.

$ git co https://github.com/pekingduck/emacs-sqlite3-api
$ cd emacs-sqlite3-api
$ make module

A tar archive called sqlite3-X.Y.tar will be created. Do a M-x package-install-file in Emacs to install that tar archive and you're all set.

For Mac users:

$ make HOMEBREW=1

to build the module against sqlite3 installed by Homebrew.

If you have sqlite3 installed in a nonstandard location:

$ make INC=/path/to/sqlite3/include LIB="-L/path/to/sqlite3/lib -lsqlite3"

If you installed manually, just remove sqlite3.el and sqlite3-api.so from your load path. Otherwise, do M-x package-delete to remove the sqlite3 package.

Emacs 25 and 26: If you are updating from an older version, you'll need to restart Emacs for the new module to take effect. That's because unload-feature doesn't work for dynamic modules.

Emacs 27.1: I can't find it in etc/NEWS, but it seems Emacs 27.1 does support unloading of dynamic modules. To unload sqlite3 properly:

(unload-feature 'sqlite3)
(unload-feature 'sqlite3-api)

(sqlite3-open "/path/to/data-file" flag1 flag2 ...)

Open the database file and return a database handle.

This function calls sqlite3_open_v2() internally and raises db-error in case of error.

flag1, flag2…. will be ORed together.

(sqlite3-close database-handle)

Close the database file.

(sqlite3-prepare database-handle sql-statement)

Compile the supplied SQL statement and return a statement handle.

This function calls sqlite3_prepare_v2() internally and raises 'sql-error in case of error.

(sqlite3-finalize statement-handle1 statement-handle2 ...)

Destroy prepared statements.

(sqlite3-step statement-handle)

Execute a prepared SQL statement. Some of the return codes are:

sqlite-done - the statement has finished executing successfully.

sqlite-row - if the SQL statement being executed returns any data, then sqlite-row is returned each time a new row of data is ready for processing by the caller.

(sqlite3-changes database-handle)

Return the number of rows modified (for update/delete/insert statements)

(sqlite3-reset statement-handle)

Reset a prepared statement. Call this function if you want to re-bind the statement to new variables, or to re-execute the prepared statement from the start.

(sqlite3-last-insert-rowid database-handle)

Retrieve the last inserted rowid (64 bit).

Notes: Beware that Emacs only supports integers up to 61 bits.

(sqlite3-get-autocommit database-handle)

Return 1 / 0 if auto-commit mode is ON / OFF.

(sqlite3-exec database-handle sql-statements &optional callback)

The Swiss Army Knife of the API, you can execute multiple SQL statements (separated by ";") in a row with just one call.

The callback function, if supplied, is invoked for each row and should accept 3 parameters:

1. the first parameter is the number of columns in the current row;
2. the second parameter is the actual data (as a list strings or nil in case of NULL);
3. the third one is a list of column names.

To signal an error condition inside the callback, return nil. sqlite3_exec() will stop the execution and raise db-error.

Raises db-error in case of error.

An example of a callback:

(defun print-row (ncols data names)
  (cl-loop for i from 0 to (1- ncols) do
	   (message "[Column %d/%d]%s=%s" (1+ i) ncols (elt names i) (elt data i)))
  (message "--------------------")
  t)

(sqlite3-exec dbh "select * from table_a; select * from table b"
	      #'print-row)

More examples:

;; Update/delete/insert
(sqlite3-exec dbh "delete from table") ;; delete returns no rows

;; Retrieve the metadata of columns in a table
(sqlite3-exec dbh "pragma table_info(table)" #'print-row)

(sqlite3-bind-text statement-handle column-no value)
(sqlite3-bind-int64 statement-handle column-no value)
(sqlite3-bind-double statement-handle column-no value)
(sqlite3-bind-null statement-handle column-no)

The above four functions bind values to a compiled SQL statements.

Please note that column number starts from 1, not 0!

(sqlite3-bind-parameter-count statement-handle)

The above functions returns the number of SQL parameters of a prepared statement.

(sqlite3-bind-multi statement-handle &rest params)

sqlite3-bind-multi binds multiple parameters to a prepared SQL statement. It is not part of the official API but is provided for convenience.

Example:

(sqlite3-bind-multi stmt 1234 "a" 1.555 nil) ;; nil for NULL

These column functions are used to retrieve the current row of the result set.

(sqlite3-column-count statement-handle)

Return number of columns in a result set. #+END_SRCe1 (sqlite3-column-type statement-handle column-no) #+END_SRC Return the type (sqlite-integer, sqlite-float, sqlite3-text or sqlite-null) of the specified column.

Note: Column number starts from 0.

(sqlite3-column-text statement-handle column-no)
(sqlite3-column-int64 statement-handle column-no)
(sqlite3-column-double statement-handle column-no)

The above functions retrieve data of the specified column.

(sqlite3-column-name statement-handle column-no)

This function returns the column name of the specified column.

Note: You can call sqlite3-column-xxx on a column even if sqlite3-column-type returns sqlite-yyy: the SQLite3 engine will perform the necessary type conversion.

Example:

(setq stmt (sqlite3-prepare dbh "select * from temp"))
(while (= sqlite-row (sqlite3-step stmt))
	(let ((name (sqlite3-column-text stmt 0))
	      (age (sqlite3-column-int64 stmt 1)))
      (message "name: %s, age: %d" name age)))

(sqlite3-fetch statement-handle) ;; returns a list such as (123 56 "Peter Smith" nil)

sqlite3-fetch is not part of the official API but provided for convenience. It retrieves the current row as a list without having to deal with sqlite3-column-* explicitly.

(sqlite3-fetch-alist statement-handle)

sqlite3-fetch-alist is not part of the official API but provided for convenience. It retrieves the current row as an alist in the form of (("col_name1" . value1) ("col_name2" . value2) ..)