go / sqlite
I use SQLite in Go web servers when I have modest needs and want operational simplicity.
Setup
I use modernc.org/sqlite as a database driver, which does not require Cgo.
go mod init server
go get modernc.org/sqlite
To avoid
database is locked (5) (SQLITE_BUSY)
errors,
I configure SQLite following David Crawshaw's
one process programming notes.
import (
"database/sql"
"fmt"
"log"
"net/http"
_ "modernc.org/sqlite"
)
func initDB(db *sql.DB) error {
pragmas := []string{
"PRAGMA temp_store = memory", // Store temp tables in memory
"PRAGMA mmap_size = 268435456", // 256MB memory-mapped I/O
"PRAGMA cache_size = 10000", // Cache size in pages
}
for _, pragma := range pragmas {
if _, err := db.Exec(pragma); err != nil {
return fmt.Errorf("failed to set pragma %s: %w", pragma, err)
}
}
return nil
}
type Server struct {
db *sql.DB
}
func (s *Server) health(w http.ResponseWriter, r *http.Request) {
var got int
if err := s.db.QueryRow("SELECT 1").Scan(&got); err != nil {
http.Error(w, "Database error", 500)
return
}
w.Write([]byte("OK"))
}
func main() {
conn := "app.db?" +
"_busy_timeout=5000&" + // Avoid immediate lock failures in concurrent access
"_journal_mode=WAL&" + // Better concurrency than default rollback journal
"_synchronous=NORMAL&" + // Faster writes while maintaining crash safety
"_foreign_keys=ON" // Enforce referential integrity
db, err := sql.Open("sqlite", conn)
if err != nil {
log.Fatal(err)
}
defer db.Close()
// For a single-process web server,
// limit connections to prevent lock contention
db.SetMaxOpenConns(1)
db.SetMaxIdleConns(1)
db.SetConnMaxLifetime(0) // Keep connections alive
if err := initDB(db); err != nil {
log.Fatal("Failed to initialize database:", err)
}
server := &Server{db: db}
http.HandleFunc("/health", server.health)
log.Fatal(http.ListenAndServe(":8080", nil))
}
Testing
I use in-memory databases to isolate each test case.
import (
"database/sql"
"net/http"
"net/http/httptest"
"testing"
_ "modernc.org/sqlite"
)
func initTestDB(t *testing.T) (*sql.DB, *Server) {
t.Helper()
db, err := sql.Open("sqlite", ":memory:?_foreign_keys=ON")
if err != nil {
t.Fatalf("Failed to create test database: %v", err)
}
if err := initDB(db); err != nil {
db.Close()
t.Fatalf("Failed to initialize test database: %v", err)
}
server := &Server{db: db}
return db, server
}
func TestHealthCheck(t *testing.T) {
db, server := initTestDB(t)
defer db.Close()
req, err := http.NewRequest("GET", "/health", nil)
if err != nil {
t.Fatalf("Failed to create request: %v", err)
}
rr := httptest.NewRecorder()
http.HandlerFunc(server.health).ServeHTTP(rr, req)
if rr.Code != 200 {
t.Errorf("Expected status 200, got %d", rr.Code)
}
if rr.Body.String() != "OK" {
t.Errorf("Expected body 'OK', got '%s'", rr.Body.String())
}
}