How can we highlight SQL code in some log files, with Emacs?

Sample log file:

[DEBUG][2016-05-26 09:04:51,714][FileHelper] - [realContextPath=[/server.xml]]
[DEBUG][2016-05-26 10:24:22,369][DbConnectionImpl] - [DELETE FROM xct_log]
[DEBUG][2016-05-26 11:00:46,925][DataSourceRestrictionConverter] - [parseRestrictions]
[DEBUG][2016-05-26 10:24:22,476][DbConnectionImpl] - [UPDATE hlpdsk_step_log
SET table_name= 'hxct_log' WHERE
 table_name='xct_log' AND pkey_value = 247296]

The goal would be to put a light yellow background behind SQL code, that is:

  • code found between the last [...] (can be on multiple physical lines, as shown in the last line of the log sample), and
  • code found on lines where the "source" (3rd bracket pair) is "DbConnectionImpl".

If possible, the yellow background should only be between those last 2 brackets, on the right lines.

(Originally posted on StackOverflow, but advised to post it here.)

  • Is there always a DbConnectionImpl in the "source" when the text between the last 2 brackets is SQL code? Sep 20, 2016 at 14:37
  • Yes, always -- if you mean "SQL code to highlight". Sep 20, 2016 at 16:30
  • Though, there are other lines with SQL code, but I don't those SQL chunks to be highlighted. Sep 20, 2016 at 16:31
  • ... And those DON'T have DbConnectImpl in the "source". Sep 20, 2016 at 17:36
  • 1
    <s>In (highlight-regexp "\\[.*\\], the greedy .* probably matches the entire line itself. Maybe use "\\[[^\]]*\\]" to match everything other than a ]?</s> Come to think of it, I don't know of a way to make hightlight-regexp only highlight part of a match.
    – omajid
    Sep 21, 2016 at 22:00

2 Answers 2


First, define a mode for this log file. Put the following somewhere in emacs and eval it:

(define-derived-mode sql-log-mode text-mode "sql-log"
  (setq font-lock-multiline t)
   '(("\\[DbConnectionImpl\\] - \\[\\([^\]]*\\)\\]" 1 'font-lock-warning-face))))

This creates a new mode for the sql log file. In this mode, we use a regexp to find the text and highlight it.

Then open the log file and then M-x sql-log-mode. This will activate the mode and turn on syntax highlighting for the sql using 'font-lock-warning-face, which is probably not yellow. 'font-lock-comment-face might be better. Play around with it.

If you don't like any built-in faces, define your own face and use that directly:

(defface sql-log-mode-sql-face '((t :inherit 'font-lock-string-face
                                    :foreground "yellow"))
  "font face for sql")

(define-derived-mode sql-log-mode text-mode "sql-log"
  (setq font-lock-multiline t)
   '(("\\[DbConnectionImpl\\] - \\[\\([^\]]*\\)\\]" 1 'sql-log-mode-sql-face))))

Edit: To add highlighting for other parts, simply extend the regexp and add more face names:

(define-derived-mode sql-log-mode text-mode "sql-log"
  (setq font-lock-multiline t)
   '(("\\[\\([^\]]*\\)\\]\\[\\([^\]]*\\)\\]\\[\\(DbConnectionImpl\\)\\] - \\[\\([^\]]*\\)\\]"
      (1 'font-lock-warning-face)
      (2 'font-lock-doc-face)
      (3 'font-lock-function-name-face)
      (4 'font-lock-string-face)))))
  • Hi omajid, this smells very good. It does already work for SQL code on one line... But it does not work yet for the 4th line of the sample. Can you try and fix it? Thanks. Sep 22, 2016 at 6:09
  • It worked for me :( I will test again later.
    – omajid
    Sep 22, 2016 at 16:51
  • It does work, sorry! How would you colorize the different pair brackets (with severity, time, and "source") -- to compare with Lindydancer's solution? Sep 22, 2016 at 20:38
  • You've responded the first to my question; hence, you win. Thanks a lot. Sep 23, 2016 at 17:42

The following will highlight the fields of the log file. The first three fields are highlighted using plain highlight rules, the fourth use an anchored rule to highlight multiple lines.

To use this, add fl-sql-activate to a suitable major mode hook.

See comments in the code below for implementation details.

(defface fl-sql-yellow-background '((t :background "Yellow"))
  "Face for highlighting SQL statements in log file.")

(defvar fl-sql--inside-dbconnectionimpl nil)

(defun fl-sql-activate ()
  (setq font-lock-multiline t)
   '(("^\\[\\([^]]*\\)\\]\\[\\([^]]*\\)\\]\\[\\([^]]*\\)\\] - \\["
      ;; Plain font-lock rules. Highlight the three initial fields.
      (1 'font-lock-keyword-face)
      (2 'font-lock-constant-face)
      (3 'font-lock-function-name-face)
      ;; Anchored font-lock rule. Highlight the SQL lines.
      ;; An "anchored" rule is effectively a search within a
      ;; search. The search is performed from where the pre-match
      ;; form place the point. The end of the inner search is
      ;; determined by the value the pre-match form is evaluated
      ;; to. In this case, the inner search is performed between
      ;; the "[" and the "]".
      ;; The inner search use the trivial regexp ".+", causing
      ;; each line to be highlighted.
       ;; Pre-match form. Place point where inner search should
       ;; start and the return point where it should end.
           ;; Remember if this is a DbConnectionImpl line.
           (setq fl-sql--inside-dbconnectionimpl
                 (equal (match-string-no-properties 3) "DbConnectionImpl"))
           ;; Place point after the "[" (start of inner search).
           (goto-char (match-end 0))
           ;; Return location before the "]" (end of inner search).
             (- (point) 1))))
       ;; Post-match form
       ;; Highlights of the anchored rule. Highlight SQL
       ;; statement, but only for DbConnectionImpl lines.
       (0 (if fl-sql--inside-dbconnectionimpl

(add-hook 'SUITABLE-MAJOR-MODE-hook #'fl-sql-activate)
  • Thanks a lot, Lindydancer! For my info, if easy to explain, how would you add a yellow background behind HH:MM:SS of the same DbConnectionImpl lines? Sep 22, 2016 at 20:40
  • Imagining that my next wish would be to have yellow background for SELECT, red for DELETE, green for INSERT and blue for UPDATE, would that complexify a lot your code; or would it be, on the contrary, quite straightforward? Sep 22, 2016 at 20:42
  • Both things should be relatively straight forward to match. One way is to have a rule that explicitly match lines containing DbConnectionImpl, that way you could drop the fl-sql--inside-dbconnectionimpl variable from code above. Highlighting SELECT etc. can be done using separate rules that simply color them using a face you pick -- you might need to set the OVERRIDES flag in the rule to prepend, to make it highlight the words on top of the yellow background. Sep 22, 2016 at 21:11
  • While you've done more (and better) than what I asked, I must give the points to omajid who was the first to answer correclty to the question asked. However, I respectfully thank you for your correct answer, improvements to my request, and for your comments for how to extend this in the future. Thanks a lot. (I'll give you "likes"). Sep 23, 2016 at 17:45
  • @user3341592, fair enough. Good luck with your package! Sep 23, 2016 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.