NGINX SQLite Logging: Queryable Access Logs

Traditional NGINX access logs are flat text files. Analyzing them requires awkward awk, grep, and sed pipelines. What if your access logs lived in a proper database instead? The NGINX SQLite logging module (ngx_http_sqlitelog_module) writes access logs directly into SQLite databases. This makes every request instantly queryable with SQL.

Why Use NGINX SQLite Logging?

NGINX’s built-in access_log directive writes to plain text files. Answering questions like “which URLs returned the most 404 errors?” requires parsing text with regular expressions. Correlating data across fields demands complex pipelines that are hard to maintain.

NGINX SQLite logging changes this fundamentally. With structured logging, you can:

• Query logs with SQL — filter, aggregate, and join data using a purpose-built language
• Get instant answers — no more piping through awk, sort, and uniq -c
• Detect bots — SELECT * FROM accesslog WHERE http_user_agent LIKE '%bot%'
• Track errors — SELECT status, count(*) FROM accesslog GROUP BY status
• Analyze performance — find slow endpoints by average request time

Moreover, SQLite databases are self-contained files. No external database server is needed. You can copy them to your laptop and run ad-hoc queries immediately.

When to Use NGINX SQLite Logging vs. Native Access Logs

NGINX’s native access_log is excellent for simple scenarios. However, the SQLite logging module shines when you need:

• Ad-hoc analysis without external tools (ELK, Loki, etc.)
• Structured data with proper types (integers, reals, text)
• Local logging on edge servers or embedded systems
• Quick debugging using familiar SQL queries

If you already run a centralized logging stack, native access_log with syslog forwarding may fit better. For standalone servers and debugging, NGINX SQLite logging is remarkably powerful.

How NGINX SQLite Logging Works

The module intercepts NGINX’s log phase. It writes each request’s variables into a SQLite table. Each log format you define becomes a table. Each NGINX variable becomes a column.

The module assigns appropriate SQLite column types automatically. For instance, $status becomes INTEGER and $request_time becomes REAL. All other variables default to TEXT. You can override any type explicitly.

For high-traffic scenarios, the module supports memory buffering. It batches multiple log entries into a single transaction. This matters because grouped inserts are dramatically faster than individual ones.

Installation

RHEL, CentOS, AlmaLinux, Rocky Linux, Fedora

sudo dnf install https://extras.getpagespeed.com/release-latest.rpm
sudo dnf install nginx-module-log-sqlite

Then enable the module at the top of /etc/nginx/nginx.conf:

load_module modules/ngx_http_sqlitelog_module.so;

For details, see the nginx-module-log-sqlite package page.

Debian and Ubuntu

First, set up the GetPageSpeed APT repository, then install:

sudo apt-get update
sudo apt-get install nginx-module-log-sqlite

On Debian/Ubuntu, the package handles module loading automatically. No load_module directive is needed.

For details, see the APT package page.

Configuration

The module provides three directives: sqlitelog, sqlitelog_format, and sqlitelog_async.

The sqlitelog Directive

Syntax: sqlitelog path [format] [buffer=size [max=n] [flush=time]] [init=script] [if=condition] | off

Default: sqlitelog off

Context: http, server

This directive defines where to write the database. Here is a minimal example:

http {
    sqlitelog /var/lib/nginx/logs/access.db;
}

This uses the built-in combined format. It mirrors the standard NGINX combined log with columns for remote_addr, remote_user, time_local, request, status, body_bytes_sent, http_referer, and http_user_agent.

Important: The database path must point to a directory where NGINX workers have write permission. SQLite creates temporary files alongside the database. A dedicated directory owned by the nginx user works best:

mkdir -p /var/lib/nginx/logs
chown nginx:nginx /var/lib/nginx/logs

The sqlitelog_format Directive

Syntax: sqlitelog_format table var1 [type1] var2 [type2] ... varN [typeN]

Default: sqlitelog_format combined $remote_addr $remote_user $time_local $request $status $body_bytes_sent $http_referer $http_user_agent

Context: http

This directive defines a log format. The first argument becomes the table name. The remaining arguments are NGINX variables with optional types:

sqlitelog_format accesslog
    $remote_addr
    $time_local
    $host
    $request_uri
    $request_time
    $status
    $body_bytes_sent
    $http_user_agent
    $http_referer;

You can override column types explicitly:

sqlitelog_format custom
    $remote_addr
    $request_uri
    $upstream_response_time REAL
    $status
    $body_bytes_sent;

Preset Column Types

The module assigns optimal SQLite types to common variables:

Variables without a preset default to TEXT.

The sqlitelog_async Directive

Syntax: sqlitelog_async pool | on | off

Default: sqlitelog_async off

Context: http

This enables asynchronous writes using NGINX thread pools. SQLite writes happen in a background thread, preventing I/O blocking:

sqlitelog_async on;

You can also specify a named thread pool:

thread_pool sqlitelog_pool threads=4;
sqlitelog_async sqlitelog_pool;

This requires NGINX compiled with --with-threads. GetPageSpeed packages include this by default.

Complete Configuration Example

Here is a production-ready NGINX SQLite logging configuration with buffering, WAL mode, and async writes:

load_module modules/ngx_http_sqlitelog_module.so;

events {
    worker_connections 1024;
}

http {
    sqlitelog_format accesslog
        $remote_addr
        $time_local
        $host
        $request_uri
        $request_time
        $status
        $body_bytes_sent
        $http_user_agent
        $http_referer;

    sqlitelog /var/lib/nginx/logs/access.db accesslog
        buffer=64K max=100 flush=1s
        init=/var/lib/nginx/logs/init.sql;

    sqlitelog_async on;

    server {
        listen 80;
        server_name example.com;
        root /var/www/html;
    }
}

Create the init.sql file to enable WAL mode:

PRAGMA journal_mode=wal;
PRAGMA busy_timeout=5000;

Understanding the Buffer Parameters

Buffering is essential for production. Without it, each request triggers a separate transaction. With buffering, inserts are batched into a single BEGIN ... COMMIT.

The buffer also flushes when NGINX reloads or exits. No data is lost during graceful restarts.

Conditional Logging

Like NGINX’s native access_log, the sqlitelog directive supports the if parameter. When the condition is 0 or empty, the request is skipped.

Log Only Errors

map $status $is_error {
    default 0;
    ~^[45]  1;
}

sqlitelog /var/lib/nginx/logs/errors.db accesslog
    buffer=64K max=100 flush=1s
    if=$is_error;

This logs only 4xx and 5xx responses. It creates an error-only database for quick troubleshooting.

Log Only Specific Paths

Since sqlitelog does not support location context, use a map:

map $request_uri $log_api {
    default          0;
    ~^/api/          1;
}

sqlitelog /var/lib/nginx/logs/api.db accesslog
    buffer=64K max=100 flush=1s
    if=$log_api;

Per-Server Logging

The directive works in http and server contexts. Lower contexts take priority:

http {
    sqlitelog_format accesslog $remote_addr $time_local $request_uri $status;

    # Default: all servers log here
    sqlitelog /var/lib/nginx/logs/global.db accesslog buffer=64K flush=1s;

    server {
        server_name app.example.com;
        # Override: own database
        sqlitelog /var/lib/nginx/logs/app.db accesslog buffer=64K flush=1s;
    }

    server {
        server_name static.example.com;
        # Disable logging entirely
        sqlitelog off;
    }
}

WAL Mode for Better Concurrency

By default, SQLite uses rollback journal mode. This can cause SQLITE_BUSY errors with multiple workers. WAL mode allows concurrent reads and writes.

Enable it through an init script:

PRAGMA journal_mode=wal;
PRAGMA busy_timeout=5000;

The busy_timeout tells SQLite to wait up to 5 seconds before returning busy. This gives concurrent workers time to finish. WAL checkpointing happens automatically on NGINX reload or exit.

Querying Your NGINX SQLite Logging Database

Once requests are logged, analyze them with standard SQL. Here are the most useful queries for system administrators.

Traffic Overview

sqlite3 /var/lib/nginx/logs/access.db \
  ".mode columns" ".headers on" \
  "SELECT status, count(*) AS hits FROM accesslog GROUP BY status ORDER BY hits DESC"

status  hits
------  ----
200     5
404     5

Top Requested URLs

sqlite3 /var/lib/nginx/logs/access.db \
  "SELECT request_uri, count(*) AS hits FROM accesslog GROUP BY request_uri ORDER BY hits DESC LIMIT 10"

Detect Bots

sqlite3 /var/lib/nginx/logs/access.db \
  ".mode columns" ".headers on" \
  "SELECT request_uri, http_user_agent FROM accesslog WHERE http_user_agent LIKE '%bot%'"

request_uri  http_user_agent
-----------  ---------------
/            Googlebot/2.1

Slowest Requests

sqlite3 /var/lib/nginx/logs/access.db \
  "SELECT request_uri, request_time, status FROM accesslog ORDER BY request_time DESC LIMIT 10"

Referrer Analysis

sqlite3 /var/lib/nginx/logs/access.db \
  ".mode columns" ".headers on" \
  "SELECT request_uri, http_referer FROM accesslog WHERE http_referer != '' AND http_referer IS NOT NULL"

Traffic by Hour

sqlite3 /var/lib/nginx/logs/access.db \
  "SELECT substr(time_local, 13, 2) AS hour, count(*) AS requests FROM accesslog GROUP BY hour ORDER BY hour"

Bandwidth by Status Code

sqlite3 /var/lib/nginx/logs/access.db \
  "SELECT status, sum(body_bytes_sent) AS total_bytes, count(*) AS requests FROM accesslog GROUP BY status"

You can also use NGINX SQLite logging databases with tools like Grafana’s SQLite datasource for visual dashboards. This pairs well with the NGINX VTS monitoring module for real-time traffic insights.

Log Rotation

SQLite databases need rotation like regular logs. Configure logrotate to stop NGINX, rotate the files, and restart:

/var/lib/nginx/logs/*.db
{
    daily
    missingok
    rotate 52
    compress
    delaycompress
    notifempty
    create 640 nginx nginx

    firstaction
        systemctl stop nginx.service
        while [ -f /var/run/nginx.pid ]; do
            sleep 0.1s
        done
    endscript

    lastaction
        systemctl restart nginx.service
    endscript
}

Save this as /etc/logrotate.d/nginx-sqlite. The stop-rotate-start approach ensures clean database connections.

Performance Considerations

NGINX SQLite logging adds minimal overhead when configured correctly:

1. Always use buffering — Without buffer, each request is a separate transaction. With buffer=64K max=100, inserts are batched for much better throughput
2. Enable WAL mode — Reduces contention between workers
3. Use async writes — sqlitelog_async on keeps the event loop responsive
4. Set a busy timeout — PRAGMA busy_timeout=5000 prevents premature errors
5. Rotate regularly — SQLite databases include metadata overhead. Keep files manageable

Combining buffering, WAL, and async handles thousands of requests per second without bottlenecks.

Troubleshooting

SQLITE_CANTOPEN (14)

sqlitelog: sqlite3 failed to open database ... due to SQLITE_CANTOPEN (14)

Cause: Workers cannot create the database. The directory must be writable.

Fix:

chown nginx:nginx /var/lib/nginx/logs

SQLITE_READONLY (8)

sqlitelog: sqlite3 ... due to SQLITE_READONLY (8)

Cause: The file exists but is not writable by NGINX. Often caused by root creating the file first.

Fix:

rm -f /var/lib/nginx/logs/access.db
systemctl restart nginx

SQLITE_BUSY (5)

sqlitelog: sqlite3 ... due to SQLITE_BUSY (5)

Cause: Workers exceed the busy timeout during concurrent writes.

Fix: Enable buffering, WAL mode, and increase the timeout:

PRAGMA journal_mode=wal;
PRAGMA busy_timeout=5000;

Module Disables Itself

On SQLite errors, the module disables logging for the affected worker. This prevents error log flooding. Check /var/log/nginx/error.log for the error code, fix it, and reload NGINX.

SQLITE_READONLY_DBMOVED (1032)

If the database is moved or deleted at runtime, the module tries to recreate it. If recreation succeeds, logging continues. This error is typically harmless.

Security Best Practices

Consider these measures when using NGINX SQLite logging:

1. Restrict permissions — Set the log directory to 700:

   chmod 700 /var/lib/nginx/logs
   

2. Keep databases outside the web root — Never place them where they could be downloaded. See our guide on NGINX security headers for related hardening tips.

3. Anonymize before sharing — Access logs contain IPs and user agents

4. Rotate frequently — Limit exposure if a file is compromised

Conclusion

The NGINX SQLite logging module transforms access logs from opaque text into structured, queryable databases. For system administrators who need quick answers about traffic, errors, and bots, SQL is far more productive than shell pipelines. Combined with buffering, WAL mode, and async writes, NGINX SQLite logging handles production workloads with minimal overhead.

The module is available from the GetPageSpeed repository for RHEL-based distributions and from the APT repository for Debian/Ubuntu.

Source code: GitHub.