certmagicsqlite

certmagic-sqlite

A SQLite storage backend for CertMagic. Pure Go, no CGO required.

Features

• Implements certmagic.Storage and certmagic.Locker interfaces
• Pure Go SQLite driver (modernc.org/sqlite) — no CGO
• Supports shared database connections for applications already using SQLite
• Automatic schema migrations
• Distributed locking with expiration-based stale lock detection

Installation

go get github.com/rsclarke/certmagic-sqlite

Usage

Standalone Database

Use New() when CertMagic should manage its own database file:

package main

import (
    "github.com/caddyserver/certmagic"
    "github.com/rsclarke/certmagic-sqlite"
)

func main() {
    storage, err := certmagicsqlite.New("/path/to/certs.db")
    if err != nil {
        log.Fatal(err)
    }
    defer storage.Close()

    certmagic.Default.Storage = storage

    // Use certmagic...
}

New() automatically configures:

• WAL journal mode (for better concurrency)
• synchronous=NORMAL (safe balance of durability and performance)
• busy_timeout=5000 (5 second wait on lock contention)

Shared Database

Use NewWithDB() when you want to store certificates in an existing application database:

package main

import (
    "database/sql"

    "github.com/caddyserver/certmagic"
    "github.com/rsclarke/certmagic-sqlite"
    _ "modernc.org/sqlite"
)

func main() {
    // Open your application database
    db, err := sql.Open("sqlite", "/path/to/app.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Configure recommended PRAGMAs (see below)
    db.Exec("PRAGMA journal_mode=WAL")
    db.Exec("PRAGMA busy_timeout=5000")

    // Create your application tables
    db.Exec("CREATE TABLE IF NOT EXISTS users (...)")

    // Create CertMagic storage (runs schema migrations automatically)
    storage, err := certmagicsqlite.NewWithDB(db)
    if err != nil {
        log.Fatal(err)
    }
    defer storage.Close() // No-op; doesn't close the shared db

    certmagic.Default.Storage = storage
}

Configuration Options

Both constructors accept functional options:

storage, err := certmagicsqlite.New("/path/to/certs.db",
    certmagicsqlite.WithLockTTL(5*time.Minute),      // Lock expiration (default: 2 minutes)
    certmagicsqlite.WithQueryTimeout(10*time.Second), // Query timeout (default: 3 seconds)
)

Lock Owner ID

By default, a random UUID is generated each time the storage is created. This means after a restart, the application cannot clean up its own stale locks — it must wait for them to expire.

For faster recovery after restarts, provide a stable owner ID:

import "os"

hostname, _ := os.Hostname()
storage, err := certmagicsqlite.New("/path/to/certs.db",
    certmagicsqlite.WithOwnerID(hostname),
)

Recommended SQLite Settings for NewWithDB

When using NewWithDB(), you are responsible for configuring the database connection. Here are the recommended settings:

Journal Mode: WAL

db.Exec("PRAGMA journal_mode=WAL")

WAL (Write-Ahead Logging) provides:

• Better concurrency (readers don't block writers)
• Improved performance for mixed read/write workloads
• Crash resilience

Note: WAL is not supported for :memory: databases.

Busy Timeout

db.Exec("PRAGMA busy_timeout=5000")  // 5000ms = 5 seconds

Configures how long SQLite waits when the database is locked by another connection. Without this, you may see "database is locked" errors under contention.

Synchronous Mode

db.Exec("PRAGMA synchronous=NORMAL")

Options:

• FULL (default): Safest, slowest — syncs after every transaction
• NORMAL: Safe with WAL — syncs less frequently, good performance
• OFF: Fastest, but risks corruption on power loss

NORMAL is recommended when using WAL mode.

Connection Pool Settings

db.SetMaxOpenConns(1)  // Serialize all writes
db.SetMaxIdleConns(1)  // Keep connection warm

SQLite only supports one writer at a time. Setting MaxOpenConns(1) prevents "database is locked" errors by serializing access at the Go level.

Complete Example

db, _ := sql.Open("sqlite", "/path/to/app.db")

// Recommended settings
db.SetMaxOpenConns(1)
db.SetMaxIdleConns(1)
db.Exec("PRAGMA journal_mode=WAL")
db.Exec("PRAGMA synchronous=NORMAL")
db.Exec("PRAGMA busy_timeout=5000")

storage, _ := certmagicsqlite.NewWithDB(db)

Schema

The following tables are created automatically:

CREATE TABLE IF NOT EXISTS certmagic_data (
    key TEXT PRIMARY KEY,
    value BLOB NOT NULL,
    modified INTEGER NOT NULL  -- Unix timestamp (milliseconds)
);

CREATE TABLE IF NOT EXISTS certmagic_locks (
    name TEXT PRIMARY KEY,
    expires_at INTEGER NOT NULL,  -- Unix timestamp (milliseconds)
    owner_id TEXT NOT NULL
);

These tables coexist safely with your application tables.

When to Use SQLite vs Other Backends

SQLite is ideal for:

• Single-node deployments
• Embedded applications or appliances
• Simplicity (no external database server)
• Applications already using SQLite

Consider PostgreSQL/Redis/Consul for:

• Multi-node clusters sharing certificates
• High-availability deployments
• Distributed locking across multiple servers

SQLite storage provides the same single-node guarantees as the default filesystem storage, with added benefits of atomic transactions and single-file backup.