feat(querylog): add SQLite query log target by 0xERR0R · Pull Request #2080 · 0xERR0R/blocky

0xERR0R · 2026-06-03T13:46:03Z

What

Adds a new sqlite query log target, so query logs can be written to a local SQLite database file with no external database required.

New queryLog.type: sqlite with the target being a local file path (e.g. /var/lib/blocky/querylog.db); the file and its parent directory are created automatically.
Switches the SQLite driver from the cgo-based gorm.io/driver/sqlite (mattn) to the pure-Go github.com/glebarez/sqlite (modernc), so it works in CGO_ENABLED=0 release/Docker builds.
Opens the DB in WAL mode automatically (with busy_timeout) so external tools can read it concurrently while blocky writes.
Retention (logRetentionDays) and bulk flushing work the same as the other DB targets.
Docs + e2e test + unit tests included.

Hardening (from review)

DSN path safety — the path is percent-encoded before going into the file: URI, so a target containing ?, # or % is opened verbatim instead of being truncated (which also silently disabled WAL) or mis-decoded.
Single connection — the sqlite pool is pinned to one connection so blocky's own periodic flush and retention cleanup can't collide into SQLITE_BUSY.
Typed QueryLogType — NewDatabaseWriter/databaseMigration take the config.QueryLogType enum instead of magic strings.
Deterministic test ordering and a teardown fix (cancel context before closing the DB).

⚠️ Note: incidental PostgreSQL primary-key fix

While threading the typed enum through the migration code, this PR also restores the id primary key on the PostgreSQL query-log table, which had silently regressed.

The id PK was originally added in Add primary key to the query log table #609 (v0.20, closes [Feature Request] Add primary key by default on DNS query log database table #585) because some managed-DB providers (e.g. DigitalOcean MySQL) require every table to have a primary key. It applied to both mysql and postgres.
The original code switched on db.Config.Name(), which returns "postgres" for the postgres dialector — so case "postgres" matched and the migration ran.
In Add Timescale DB with Hypertable and Retention support #1517 (v0.25) the Timescale work changed the switch to a passed-in dbType string to disambiguate postgresql from timescale (they share the postgres dialector). It passed "postgresql" but left case "postgres", so from v0.25 on the ALTER TABLE ... ADD id silently stopped running for PostgreSQL (mysql/timescale were unaffected).
This PR keys the migration on the typed enum (QueryLogTypePostgresql), so the migration matches in production again — restoring the v0.20 behaviour.

Upgrade impact: existing PostgreSQL deployments will run a one-time, idempotent ALTER TABLE log_entries ADD column if not exists id bigserial primary key on the next startup after upgrading.

Addresses review feedback on the sqlite query log: - buildSQLiteDSN: percent-encode %, ? and # in the target path. The "file:" URI mode runs the path through SQLite's URI parser, so a path containing "?" or "#" was truncated (opening a different file, "?" also silently disabling WAL) and "%xx" was decoded into a different path. - sqlite: pin the connection pool to a single connection so blocky's own periodic flush and retention cleanup can't collide into SQLITE_BUSY. - Pass config.QueryLogType through NewDatabaseWriter/databaseMigration instead of magic strings, removing the dialector type assertion. - Restore the postgres "id" primary key migration. It regressed in #1517 (v0.25): the migration switch moved from db.Config.Name() ("postgres") to the dbType string ("postgresql") without updating the case, so the ALTER silently stopped running for postgres. - Add an explicit sqlite migration case documenting that it relies on SQLite's implicit rowid as the primary key. - tests: deterministic queryEntries ordering; cancel the context before closing the DB in the WAL test so the flush goroutine can't write to a closed connection. - docs: sqlite target is a plain path (not "file:"), WAL needs a local filesystem, and retention deletes rows but VACUUM is needed to reclaim disk space.

codecov · 2026-06-03T13:49:14Z

Codecov Report

❌ Patch coverage is 80.00000% with 5 lines in your changes missing coverage. Please review.
✅ Project coverage is 86.18%. Comparing base (93cc238) to head (455d505).

Files with missing lines	Patch %	Lines
querylog/database_writer.go	78.26%	3 Missing and 2 partials ⚠️

Additional details and impacted files

@@           Coverage Diff           @@
##             main    #2080   +/-   ##
=======================================
  Coverage   86.18%   86.18%           
=======================================
  Files         111      111           
  Lines        8278     8287    +9     
=======================================
+ Hits         7134     7142    +8     
+ Misses        929      928    -1     
- Partials      215      217    +2

☔ View full report in Codecov by Harness.
📢 Have feedback on the report? Share it here.

🚀 New features to boost your workflow:

❄️ Test Analytics: Detect flaky tests, report on failures, and find test suite problems.

Copilot

Pull request overview

This PR adds a new sqlite query-log backend so Blocky can persist query logs to a local SQLite database file (no external DB), using a pure-Go SQLite driver to support CGO_ENABLED=0 builds. It also threads the typed config.QueryLogType through database-writer selection/migrations and restores the PostgreSQL query-log primary-key migration behavior that previously regressed.

Changes:

Add queryLog.type: sqlite support, including WAL + busy_timeout configuration and automatic parent-directory creation.
Refactor database-backed query-log writer/migrations to use the typed config.QueryLogType instead of string db-type identifiers.
Update docs/schema and add tests (unit + e2e) for the SQLite backend; restore PostgreSQL id PK migration matching.

Reviewed changes

Copilot reviewed 12 out of 14 changed files in this pull request and generated 2 comments.

Show a summary per file

File	Description
resolver/query_logging_resolver.go	Routes sqlite/mysql/postgresql/timescale through the shared database writer using typed `QueryLogType`.
resolver/query_logging_resolver_test.go	Adds a resolver-level test that sqlite returns a `DatabaseWriter`.
querylog/database_writer.go	Implements SQLite dialector selection, DSN construction (WAL + busy timeout), directory creation, and typed migrations.
querylog/database_writer_test.go	Updates tests for typed `QueryLogType` and adds sqlite file/WAL behavioral tests.
e2e/querylog_test.go	Adds an end-to-end test validating Blocky writes query logs to a SQLite file and can be read externally.
docs/configuration.md	Documents sqlite query-log usage, WAL behavior, and operational considerations (volumes/backups).
docs/config.yml	Extends example config to include sqlite query-log type/target example.
docs/config.schema.json	Extends schema enum/descriptions to include sqlite, and clarifies `target` semantics.
config/query_log.go	Updates config comments to include sqlite as a supported query-log target and clarifies flush interval semantics.
config/query_log_test.go	Adds parsing coverage for `QueryLogTypeSqlite`.
config/config.go	Extends the `QueryLogType` doc comment list with sqlite.
config/config_enum.go	Extends the generated `QueryLogType` enum/string mappings with sqlite.
go.mod	Switches SQLite driver dependency to `github.com/glebarez/sqlite` and drops `gorm.io/driver/sqlite`.
go.sum	Updates sums to reflect the new SQLite driver and transitive modernc dependencies.

Files not reviewed (1)

config/config_enum.go: Language not supported

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

…rfacing - queryEntries: order e2e results by request_ts (oldest first) rather than relying on the database's implicit row order, which SQL does not guarantee. Matches the positional (entries[0]/entries[1]) assertions; no portable tie-breaker exists across all four backends and the tests use distinct timestamps. - Add a countLogEntries helper that returns the query error and use it in the Eventually count loops, so a failing query surfaces the error instead of silently polling 0 until the timeout.

immanuelfodor · 2026-06-04T08:56:08Z

+
+WAL requires a normal local filesystem. It is **not** supported on network filesystems (NFS, SMB/CIFS); placing the database on a network share can cause lock errors or corruption, so use local/block storage.
+
+Retention (`logRetentionDays`) works the same as for the other database targets: entries older than the limit are deleted. Note that SQLite does not hand freed space back to the operating system on its own, so the `.db` file does not shrink after a deletion — the space is reused for new entries. Run `VACUUM` manually if you need to reclaim disk space.


Is it safe to run VACUUM in a cron job periodically eg. overnight?

It should be safe. Since blocky deletes data periodically (retention), the deleted space will be reused by the sqlite db. So I would assume, if you have roughly the same amount of queries every day, the vacuum is not necessary. The vacuum is only needed if you had a spike of data and you want to reclaim it (basically to shink the data file).

This is very good intel, thank you! 🙏
It might be worth mentioning in the docs, though

This PR contains the following updates: | Package | Update | Change | |---|---|---| | [ghcr.io/0xerr0r/blocky](https://github.com/0xERR0R/blocky) | minor | `v0.30.0` → `v0.31.0` | --- ### Release Notes <details> <summary>0xERR0R/blocky (ghcr.io/0xerr0r/blocky)</summary> ### [`v0.31.0`](https://github.com/0xERR0R/blocky/releases/tag/v0.31.0) [Compare Source](0xERR0R/blocky@v0.30.0...v0.31.0) #### Changelog ##### Features - [`132c42f`](0xERR0R/blocky@132c42f): feat(config): validate and log ports.dohPath ([#2072](0xERR0R/blocky#2072)) ([@0xERR0R](https://github.com/0xERR0R)) - [`e43b5e5`](0xERR0R/blocky@e43b5e5): feat(querylog): add SQLite query log target ([#2080](0xERR0R/blocky#2080)) ([@0xERR0R](https://github.com/0xERR0R)) - [`5d806b6`](0xERR0R/blocky@5d806b6): feat(server): opt-in IP\_FREEBIND for DNS listeners ([#2078](0xERR0R/blocky#2078)) ([@0xERR0R](https://github.com/0xERR0R)) - [`f18e52b`](0xERR0R/blocky@f18e52b): feat: capability-aware image that runs under drop:ALL ([#2071](0xERR0R/blocky#2071)) ([@0xERR0R](https://github.com/0xERR0R)) - [`bf19d6d`](0xERR0R/blocky@bf19d6d): feat: config JSON schema generation and schema-driven validation ([#2066](0xERR0R/blocky#2066)) ([@0xERR0R](https://github.com/0xERR0R)) - [`99ae703`](0xERR0R/blocky@99ae703): feat: filter ipv6hint in HTTPS/SVCB RR when AAAA is filtered ([#2070](0xERR0R/blocky#2070)) ([@0xERR0R](https://github.com/0xERR0R)) - [`c44017a`](0xERR0R/blocky@c44017a): feat: load sensitive config values from files ([#2077](0xERR0R/blocky#2077)) ([@0xERR0R](https://github.com/0xERR0R)) - [`e6b41db`](0xERR0R/blocky@e6b41db): feat: per-client DNS rate limiting ([#2063](0xERR0R/blocky#2063)) ([@0xERR0R](https://github.com/0xERR0R)) - [`f457ec9`](0xERR0R/blocky@f457ec9): feat: read bootstrap nameservers from a resolv.conf file (resolvFile) ([#2068](0xERR0R/blocky#2068)) ([@0xERR0R](https://github.com/0xERR0R)) - [`fdcf351`](0xERR0R/blocky@fdcf351): feat: resolve client names from hosts file and custom DNS ([#1964](0xERR0R/blocky#1964)) ([#2069](0xERR0R/blocky#2069)) ([@0xERR0R](https://github.com/0xERR0R)) ##### Bug fixes - [`93cc238`](0xERR0R/blocky@93cc238): fix(caching): sync prefetched entries to Redis ([#2079](0xERR0R/blocky#2079)) ([@0xERR0R](https://github.com/0xERR0R)) - [`69b0663`](0xERR0R/blocky@69b0663): fix(config): handle DNS stamp port on hostname field and bootstrap IPs ([#2065](0xERR0R/blocky#2065)) ([@0xERR0R](https://github.com/0xERR0R)) - [`31f8df3`](0xERR0R/blocky@31f8df3): fix(querylog): build SQLite target only on modernc-supported platforms ([#2082](0xERR0R/blocky#2082)) ([@0xERR0R](https://github.com/0xERR0R)) ##### Build and dependencies - [`a5cdeb2`](0xERR0R/blocky@a5cdeb2): build(deps): bump github.com/go-chi/chi/v5 from 5.2.5 to 5.3.0 ([#2074](0xERR0R/blocky#2074)) ([@dependabot](https://github.com/dependabot)\[bot]) - [`5a25f67`](0xERR0R/blocky@5a25f67): build(deps): bump github.com/mattn/go-colorable from 0.1.14 to 0.1.15 ([#2075](0xERR0R/blocky#2075)) ([@dependabot](https://github.com/dependabot)\[bot]) - [`1da222a`](0xERR0R/blocky@1da222a): build(deps): bump github.com/oapi-codegen/runtime from 1.4.0 to 1.4.1 ([#2064](0xERR0R/blocky#2064)) ([@dependabot](https://github.com/dependabot)\[bot]) - [`6ff1d13`](0xERR0R/blocky@6ff1d13): build(deps): bump golang.org/x/net from 0.54.0 to 0.55.0 ([#2067](0xERR0R/blocky#2067)) ([@dependabot](https://github.com/dependabot)\[bot]) - [`63f8d9a`](0xERR0R/blocky@63f8d9a): build: upgrade Go to 1.26.2 and golangci-lint to v2.12.2 ([#2062](0xERR0R/blocky#2062)) ([@0xERR0R](https://github.com/0xERR0R)) ##### Misc - [`e2de6ae`](0xERR0R/blocky@e2de6ae): docs(redis): document unix socket support and add e2e test ([#2076](0xERR0R/blocky#2076)) ([@0xERR0R](https://github.com/0xERR0R)) *** #### ❤️ Support Blocky Blocky is free and open source, developed in my spare time — no telemetry, no ads, no hidden filtering. If it's useful to you, please consider supporting its continued development: [GitHub Sponsors](https://github.com/sponsors/0xERR0R) · [thanks.dev](https://thanks.dev/u/gh/0xERR0R) · [Liberapay](https://liberapay.com/spx01) · [Ko-fi](https://ko-fi.com/0xerr0r) · [PayPal](https://paypal.me/spx01) Thank you to everyone supporting Blocky! 🙏 </details> --- ### Configuration 📅 **Schedule**: (UTC) - Branch creation - At any time (no schedule defined) - Automerge - At any time (no schedule defined) 🚦 **Automerge**: Disabled by config. Please merge this manually once you are satisfied. ♻ **Rebasing**: Whenever PR becomes conflicted, or you tick the rebase/retry checkbox. 🔕 **Ignore**: Close this PR and you won't be reminded about this update again. --- - [ ] If you want to rebase/retry this PR, check this box --- This PR has been generated by [Mend Renovate](https://github.com/renovatebot/renovate).

0xERR0R added 10 commits June 3, 2026 14:13

feat(querylog): use pure-Go glebarez/sqlite driver (CGO-free)

3ef609b

feat(querylog): add buildSQLiteDSN helper with auto WAL pragma

d36017c

feat(querylog): support sqlite database writer with auto-created dir

6c76838

feat(config): add sqlite to QueryLogType enum

fe02525

feat(resolver): wire sqlite query log writer

f644bf1

test(e2e): verify sqlite query log via select

5bd2953

docs: document sqlite query log target and WAL handling

1ceffb6

style: satisfy linters for sqlite query log

83a5d12

docs(querylog): clarify file: URI rationale in buildSQLiteDSN comment

0e06c77

Copilot AI review requested due to automatic review settings June 3, 2026 13:46

Copilot started reviewing on behalf of 0xERR0R June 3, 2026 13:46 View session

0xERR0R added this to the v0.31.0 milestone Jun 3, 2026

0xERR0R added the 🔨 enhancement New feature or request label Jun 3, 2026

Copilot AI reviewed Jun 3, 2026

View reviewed changes

Comment thread e2e/querylog_test.go Outdated

Comment thread querylog/database_writer_test.go Outdated

0xERR0R merged commit e43b5e5 into main Jun 4, 2026
21 of 22 checks passed

0xERR0R deleted the feat/sqlite-querylog branch June 4, 2026 05:41

0xERR0R linked an issue Jun 4, 2026 that may be closed by this pull request

Support SQLite as a query log target #1991

Closed

This was referenced Jun 4, 2026

Support SQLite as a query log target #1991

Closed

fix(querylog): build SQLite target only on modernc-supported platforms #2082

Merged

immanuelfodor reviewed Jun 4, 2026

View reviewed changes

Navigation Menu

Repository navigation

feat(querylog): add SQLite query log target - #2080

feat(querylog): add SQLite query log target#2080
0xERR0R merged 11 commits into
mainfrom
feat/sqlite-querylog

0xERR0R commented Jun 3, 2026 •

edited

Loading

Uh oh!

codecov Bot commented Jun 3, 2026 •

edited

Loading

Uh oh!

Copilot AI left a comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

immanuelfodor Jun 4, 2026

Uh oh!

0xERR0R Jun 4, 2026

Uh oh!

immanuelfodor Jun 4, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants


		WAL requires a normal local filesystem. It is not supported on network filesystems (NFS, SMB/CIFS); placing the database on a network share can cause lock errors or corruption, so use local/block storage.

		Retention (`logRetentionDays`) works the same as for the other database targets: entries older than the limit are deleted. Note that SQLite does not hand freed space back to the operating system on its own, so the `.db` file does not shrink after a deletion — the space is reused for new entries. Run `VACUUM` manually if you need to reclaim disk space.

Conversation

0xERR0R commented Jun 3, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

What

Hardening (from review)

⚠️ Note: incidental PostgreSQL primary-key fix

Uh oh!

codecov Bot commented Jun 3, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Codecov Report

Uh oh!

Copilot AI left a comment

Choose a reason for hiding this comment

Pull request overview

Reviewed changes

Uh oh!

Uh oh!

Uh oh!

Uh oh!

immanuelfodor Jun 4, 2026

Choose a reason for hiding this comment

Uh oh!

0xERR0R Jun 4, 2026

Choose a reason for hiding this comment

Uh oh!

immanuelfodor Jun 4, 2026

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

0xERR0R commented Jun 3, 2026 •

edited

Loading

codecov Bot commented Jun 3, 2026 •

edited

Loading