SQLite notes

Links

Homepage

https://www.sqlite.org/

Docs

https://www.sqlite.org/docs.html

Search docs Search

SQLite JDBC driver

https://github.com/xerial/sqlite-jdbc

Version I’m currently using:

org.xerial/sqlite-jdbc {:mvn/version "3.39.2.1"}

Command Line

Use the command sqlite3 somedb.sqlite to open “somedb.sqlite”. sqlite3 is provided by the “sqlite-interactive” nixpkg. The plain “sqlite” nixpkg doesn’t provide readline support, as far as I can tell.

Dot commands

Special SQLite commands are listed in SQLite’s documentation. You can also type .help to get a list.

.tables

Get a list of tables.

Misc queries

• “pragma table_info(moz_cookies)” tells you the columns of the “moz_cookies” table.

Emacs

In recent versions of Emacs (compiled with SQLite), you can use sqlite-mode-open-file to view a SQLite file.

Guile

I couldn’t find that many examples of using guile-sqlite3 online (perhaps due to websites blocking LLMs). LLM examples returned examples for a different library, which isn’t the typical one. So here’s what I pieced together. (See also my Guile notes.)

In Nix you can use nix-shell -p guile_3_0 guile-sqlite3 and run guile to run this code.

;; sqlite-example.scm
;; 🄯 2026 Josh Moller-Mara
;; Last modified: 2026-02-19
;; This just shows how to use guile-sqlite3.
;; At least, from what I can figure out.
;; 
;; (It’s hard for me to find some example uses.  Some websites are
;; currently blocking LLMs or are down.  This is what I can piece
;; together by looking at the source of guile-sqlite3’s
;; “sqlite3.scm.in” and “tests/basic.scm”.  I’m not super familiar
;; with using sqlite from C, so there may be errors in regards to
;; sqlite’s step/finalize/reset logic.)

(use-modules (sqlite3))

(define db-name "somedb.sqlite")

(define db (sqlite-open db-name (logior SQLITE_OPEN_CREATE
					SQLITE_OPEN_READWRITE)))

;; Check if it's a db, not necessary.
(sqlite-db? db)

;; Make a table.
(sqlite-exec db
  "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")

;; Insert some example user.
(sqlite-exec db "INSERT INTO users (name, age) VALUES('David', 35)")

;; Helper function to show the user table.
(define (show-user-table db)
  "Show what the user table currently contains."
  (let* ((stmt (sqlite-prepare db "SELECT * FROM users;"))
       ;; This formats the returned result and needs to be called before finalizing
       ;; If you just wanted to print values, you could use "display" instead of
       ;; "identity".
       (result (sqlite-map identity stmt)))
  ;; Finalizing I think prevents resource leaks.
  (sqlite-finalize stmt)
  result))

;; Actually show users.
(show-user-table db)

;; Insert a bunch of users
(for-each
 (lambda (user)
   ;; Caching the statement possibly allows it to be reused more easily.
   (let ((stmt (sqlite-prepare db "INSERT INTO users (name, age) VALUES (?, ?)" #:cache? #t)))
     ;; Bind arguments using positional binding.
     (apply sqlite-bind-arguments stmt user)
     ;; sqlite-step seems necessary to actually process it.
     (sqlite-step stmt)
     ;; I believe guile’s sqlite-finalize essentially resets the statement.
     (sqlite-finalize stmt)))
 '(("Alice" 31)
   ("Bob" 26)
   ("Carol" 36)))

(show-user-table db)

;; Helper function for querying
(define (jmm-sqlite-query db sql . args)
  "Try to run SQL (string) binding args, returning result of query.

Example: (jmm-sqlite-query db \"VALUES(?,?)\" \"hello\" \"world\")
(jmm-sqlite-query db \"VALUES(:foo,:bar)\" #:bar \"world\" #:foo \"hello\")
"
  ;; You might want to use a cached version, like:
  ;; (sqlite-prepare db sql #:cache? #t)
  (let ((stmt (sqlite-prepare db sql)))
    (apply sqlite-bind-arguments stmt args)
    (let ((result (sqlite-map identity stmt)))
      (sqlite-finalize stmt)
      result)))

;; Two ways of formatting.
(jmm-sqlite-query db "SELECT * FROM users WHERE age > :age" #:age 30)

(jmm-sqlite-query db "SELECT * FROM users WHERE age > ?" 34)

;; Close the database when we’re done.
(sqlite-close db)

Programs that use SQLite

(At least programs or applications I use.)