SQL

patrol.SQL writes the batch into a patrol_events table. One transaction per flush, one multi-row INSERT per 1000 events, rolled back whole if any statement fails.

The constructors

patrol.NewSQL(ctx context.Context, opts patrol.SQLOptions) (*patrol.SQL, error)
patrol.NewSQLWithDatabase(ctx context.Context, db patrol.DB, opts patrol.SQLOptions) (*patrol.SQL, error)

NewSQL opens the database with database/sql, pings it, and creates the table when it is missing. Engine is required; DSN is passed through to the driver. NewSQLWithDatabase skips the open and the ping and takes a database you already have, which is the one to use when your service already owns a pool.

patrol.DB is the four methods the flusher needs: PingContext, BeginTx, ExecContext and Close. A *sql.DB satisfies it, and so does a test double.

Options

Field	Type	JSON	YAML and TOML	Required
Engine	string	engine	Engine	yes
DSN	string	dsn	DSN	yes for NewSQL, ignored by NewSQLWithDatabase

Engine is the database/sql driver name, and it decides the placeholder style: postgres gets $1, $2, ... numbered across the whole statement, every other engine gets ?. Register the driver yourself with a blank import, because the library imports none of them:

package main

import (
  "context"
  "log"

  "github.com/kataras/patrol"

  _ "github.com/lib/pq"
)

func main() {
  ctx := context.Background()

  flusher, err := patrol.NewSQL(ctx, patrol.SQLOptions{
    Engine: "postgres",
    DSN:    "postgres://patrol:secret@db.internal:5432/events?sslmode=require",
  })
  if err != nil {
    log.Fatal(err)
  }

  producer := patrol.NewProducer(patrol.ProducerOptions{
    ProjectName:  "checkout-api",
    MaxBatchSize: 500,
  }, flusher)
  defer producer.Close(ctx)
}

The table

Created with CREATE TABLE IF NOT EXISTS on the first construction:

// source: sql.go#L87-L97
  const query = `CREATE TABLE IF NOT EXISTS patrol_events (
        id CHAR(36) PRIMARY KEY,
        type SMALLINT,
        project_name VARCHAR (255) NOT NULL,
        captured_at TIMESTAMP NOT NULL,
        sent_at TIMESTAMP NOT NULL,
        received_at TIMESTAMP NOT NULL,
        message TEXT,
        fields JSON,
        stacktrace JSON
    );`

id is the event’s UUID v7, so the primary key is time-ordered and a re-sent batch collides instead of duplicating. type is the numeric event type: 0 none, 1 info, 2 error, 3 debug. fields and stacktrace are the same JSON that goes over the wire. received_at is the moment the flush began, not a timestamp from the event.

What Test does

producer.Test(ctx) pings the database. NewSQL has already pinged once, so this is about the connection still being there rather than about the configuration being right.

A worked example

From _examples/sql, which pushes 100,000 events through one Postgres flusher:

// source: _examples/sql/main.go#L22-L28
  flusher, err := patrol.NewSQL(ctx, patrol.SQLOptions{
    Engine: "postgres",
    DSN:    connStr,
  })
  if err != nil {
    log.Fatal(err)
  }

Gotchas

IF NOT EXISTS never alters a table that is already there. If a future version adds a column, an existing deployment keeps the old shape and the insert starts failing. The library’s HISTORY.md carries the migration SQL when that happens; the table is yours to migrate.

Close closes the database. patrol.SQL implements Closer, and producer.Close(ctx) calls it, which closes the *sql.DB underneath. That is right when NewSQL opened it. It is a surprise when you handed your own pool to NewSQLWithDatabase and the rest of your service is still using it. Give patrol its own pool, or do not close that producer until the service is done.

Files and mentions are not stored. There is no column for either. WithFile and WithMentions survive on the wire and reach Slack; they do not reach this table.

The batch size matters here more than elsewhere. One flush is one transaction. A MaxBatchSize of 10,000 means a 10,000-row transaction holding locks while it commits; the statement chunking at 1000 rows keeps each statement under the driver’s placeholder limit, but the transaction is still one. Sizes in the hundreds are the comfortable range.