SQLWriter for Zerolog: save logs to DB

log.go

package logging

import (
	"io"
	"os"

  // import your DB here
	"github.com/mypkg/main/database"
	"github.com/rs/zerolog"
	"github.com/rs/zerolog/log"
	"github.com/rs/zerolog/pkgerrors"
)

func Setup(debug bool) {
	zerolog.TimeFieldFormat = zerolog.TimeFormatUnix
	zerolog.ErrorStackMarshaler = pkgerrors.MarshalStack

	level := zerolog.InfoLevel
	if debug {
		level = zerolog.DebugLevel
	}

	zerolog.SetGlobalLevel(level)

	var writers []io.Writer = []io.Writer{
		NewSqlWriter(SqlWriterOptions{
			DbExec: func(sql string, values ...interface{}) error {
				return database.Db.Exec(sql, values...).Error
			},
		}),
	}

	if debug {
		writers = append(writers, zerolog.ConsoleWriter{Out: os.Stderr})
	}

	log.Logger = log.Output(io.MultiWriter(writers...))
}

sqlwriter.go

package logging

import (
	"bytes"
	"encoding/json"
	"fmt"
	"log"
	"os"
	"path/filepath"

	"github.com/rs/zerolog"
)

// SqlWriter parses the JSON input and writes it to a DB
type SqlWriter struct {
	DbExec DbExecFunction
}

type DbExecFunction func(sql string, values ...interface{}) error

type SqlWriterOptions struct {
	DbExec DbExecFunction
}

func NewSqlWriter(options SqlWriterOptions) SqlWriter {
	w := SqlWriter{
		DbExec: options.DbExec,
	}

	createTable := `CREATE TABLE IF NOT EXISTS logs (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		level INTEGER,
		timestamp INTEGER,
		caller TEXT,
		message TEXT,
		error TEXT,
		stack_trace TEXT,
		fields TEXT
	)`

	err := w.DbExec(createTable)
	if err != nil {
		log.Fatalf("Can't create logs table: %s\n", err)
	}

	return w
}

// Write transforms the JSON input to SQL rows
func (w SqlWriter) Write(p []byte) (n int, err error) {
	var evt map[string]interface{}
	d := json.NewDecoder(bytes.NewReader(p))
	d.UseNumber()
	err = d.Decode(&evt)
	if err != nil {
		return n, fmt.Errorf("cannot decode event: %s", err)
	}

	w.writeFields(evt)

	return len(p), err
}

func (w SqlWriter) writeFields(evt map[string]interface{}) {
	q := `INSERT INTO logs (
			level, timestamp, caller, message, error, stack_trace, fields
		) VALUES (
			?, ?, ?, ?, ?, ?, ?
		)`

	level, _ := zerolog.ParseLevel(evt[zerolog.LevelFieldName].(string))
	delete(evt, zerolog.LevelFieldName)

	timestamp, _ := evt[zerolog.TimestampFieldName].(json.Number)
	delete(evt, zerolog.TimestampFieldName)

	message := evt[zerolog.MessageFieldName]
	delete(evt, zerolog.MessageFieldName)

	errorMsg := evt[zerolog.ErrorFieldName]
	delete(evt, zerolog.ErrorFieldName)

	var stackTrace []byte
	var stackErr error

	stackTrace, stackErr = json.Marshal(evt["stack"])
	if stackErr != nil {
		fmt.Println(stackErr)
	}
	delete(evt, "stack")

	caller := formatCaller(evt[zerolog.CallerFieldName])
	delete(evt, zerolog.CallerFieldName)

	// Convert to JSON only the remaining fields
	var extraFields []byte
	var err error

	if len(evt) > 0 {
		extraFields, err = json.Marshal(evt)
		if err != nil {
			fmt.Println(err)
		}
	}

	err = w.DbExec(q,
		level,
		timestamp,
		caller,
		message,
		errorMsg,
		stackTrace,
		extraFields,
	)

	if err != nil {
		fmt.Println("Can't insert log into DB", err)
	}
}

func formatCaller(i interface{}) *string {
	var c string
	if cc, ok := i.(string); ok {
		c = cc
	}
	if len(c) > 0 {
		if cwd, err := os.Getwd(); err == nil {
			if rel, err := filepath.Rel(cwd, c); err == nil {
				c = rel
			}
		}
		return &c
	} else {
		return nil
	}
}