Normally, you’d associate file path extensions with major modes in Emacs via auto-mode-alist. The associative list contains entries like ("\\.html" . web-mode) so that when you open (aka “visit”) an HTML file, Emacs automatically switches to web-mode, which in turns supplies shortcuts and syntax highlighting and so on.

That doesn’t work with .sqlite files and sqlite-mode, though. The built-in sqlite-mode (since Emacs 29) is peculiar: it expects you to manually run sqlite-mode-open-file and pick a file to open. There are probably very good technical reasons (like not having SQLite support bundled into your Emacs binary), but that’s so weird. Especially since you can open PDFs, images, and SVGs in a graphical preview just like that without special incantations.

On Mastodon, @zardoz.el@mastodon.online suggested magic-mode-alist and using a lambda or function. Never heard of that list, but it turns out that this is checked before auto-mode-alist, and instead of operating on the file path, it operates on the file contents (or rather: buffer contents of the file that is just being opened). That means I can copy and paste whatever Emacs displays as text in its buffer for the binary file format .sqlite and go from there. The start of the file is:

SQLite format 3

Oh, no, that doesn’t render properly as plain text. Of course. Let me try again with a picture:

And then there’s many more of these character sequences, but that should do to disambiguate a binary .sqlite file from a text file that starts with the text “SQLite format 3”.

Now the function to auto-magic-ally use sqlite-mode-open-file (which creates a new buffer!) and close the binary file buffer can work with the buffer-local variable buffer-file-name. The procedure is

1. save buffer-file-name,
2. kill the current binary file buffer,
3. invoke sqlite-mode-open-file with the file name from step (1).

The piece form my init.el thus is:

(use-package sqlite-mode 
  :config
  (defun ct/sqlite-view-file-magically ()
    "Runs `sqlite-mode-open-file' on the file name visited by the
current buffer, killing it."
    (require 'sqlite-mode)
    (let ((file-name buffer-file-name))
      (kill-current-buffer)
      (sqlite-mode-open-file file-name)))

  (add-to-list 'magic-mode-alist '("SQLite format 3\x00" . ct/sqlite-view-file-magically)))

Update 2024-01-09: In the comments, matteo suggested the string "SQLite format 3\x00" instead of my hastily pasted one. I adapted the code accordingly.