Skip to main content

Database drivers

Log Lens's storage engine is SQLite by default and requires zero configuration — one file per application, as described in Database schema. Postgres and MySQL are supported as opt-in alternatives for deployments that already run one of those and would rather not add SQLite files to their backup/ops story. Nothing about switching engines is enforced: SQLite remains the default, and every feature works identically on all three.

Choosing an engine​

Set database.driver in config.php (or LOG_LENS_DB_DRIVER in the environment/.env, which takes precedence):

LOG_LENS_DB_DRIVER=pgsql   # or: mysql   (default: sqlite — no variable needed)

Connection settings live under database.pgsql / database.mysql in config.php, each overridable by its own environment variable:

VariableApplies toDefaultPurpose
LOG_LENS_DB_HOSTpgsql, mysql127.0.0.1Server host.
LOG_LENS_DB_PORTpgsql, mysql5432 / 3306Server port.
LOG_LENS_DB_NAMEpgsql onlylog_lensThe one physical database every application's schema lives in (see below). Not used by MySQL — each application gets its own physical database instead.
LOG_LENS_DB_USERpgsql, mysqlpostgres / rootConnection username.
LOG_LENS_DB_PASSWORDpgsql, mysql(empty)Connection password.

Set it everywhere, not just for the web server. The CLI entry points (bin/import.php, bin/sync.php, the background sync worker, the Laravel log-lens:* commands) resolve the engine the same way the dashboard does. If the variables are exported only in the PHP-FPM pool, a cron job or a manual import runs on the default engine instead and silently writes to a SQLite file the dashboard never reads. Put them somewhere both see — the .env/config.php, or the cron entry's own environment.

Per-application isolation​

Log Lens never aggregates data across applications — each one owns a completely isolated store. SQLite gets this for free (a private file per application); Postgres and MySQL each get the closest native equivalent:

EngineIsolation unitHow
SQLite (default)One fileapplications/<id>/log-lens.sqlite, as always.
PostgresOne schemaCREATE SCHEMA "app_<id>" inside the single database named by LOG_LENS_DB_NAME, with search_path pinned to it per connection. Every unqualified table reference already in the codebase (FROM error_groups, …) keeps working unchanged.
MySQLOne databaseCREATE DATABASE log_lens_<id> (created automatically on first connection — unlike Postgres, MySQL has no lightweight schema-within-database concept, so a full database is the nearest equivalent).

Postgres precondition: unlike MySQL, Postgres has no CREATE DATABASE IF NOT EXISTS — the physical database named by LOG_LENS_DB_NAME (default log_lens) must already exist. Create it once:

CREATE DATABASE log_lens;

Everything after that (schemas, tables, indexes) is created and migrated automatically per application, exactly like SQLite's automatic file + schema creation.

Requirements​

  • Postgres: 12+ (generated columns). Verified against Postgres 17.
  • MySQL: 8.0.13+ — expression column defaults (used for every timestamp column's "now" default) only exist from that version on. Verified against MySQL 8.0.

What's engine-specific under the hood​

You do not need to know any of this to use Postgres/MySQL — it is only relevant if you are reading the source. A handful of storage decisions keep the whole codebase's SQL portable across all three engines without a query rewrite:

  • enabled/flag columns stay INTEGER (0/1), not a native boolean type — SQLite has no boolean type either, so the app already treats them as integers everywhere (WHERE enabled=1).
  • Every timestamp column stays TEXT, in SQLite's exact 'YYYY-MM-DD HH:MM:SS' UTC format — not a native temporal type. The app does string operations on them (substr(created_at,1,10), lexicographic ORDER BY) that a temporal type would reject.
  • Case-insensitive uniqueness (modules.name, tags.name, …) comes from SQLite's COLLATE NOCASE on SQLite, a functional LOWER() unique index on Postgres (which has no such column collation), and an explicit utf8mb4_0900_ai_ci column collation on MySQL (already case-insensitive by default).
  • Generated columns (the access-log analytics fields parsed out of context_preview) use SQLite's json_valid()/json_extract(), an IMMUTABLE safe_jsonb() helper function on Postgres (which has no equivalent of json_valid() — invalid JSON simply throws), and MySQL's native JSON_VALID()/->>.
  • MySQL has no partial/filtered index — the three access-log indexes cover the whole table instead of just access rows on that engine; functionally identical, just larger.

All of this is implemented behind a small Dialect (SQL-fragment generation) and Driver (connect/migrate/tenancy) seam — see src/Storage/ if you're curious, or want to add a fourth engine.

The schema is a set of migrations​

Schema changes live as discrete, versioned files under database/migrations/ — a small, dependency-free migration system (Storage\Schema\Blueprint/Grammar/Migrator), the same shape as Laravel's Schema/Blueprint/migration files, scoped to exactly what this schema needs:

  • Each migration file returns a Migration with up() (and usually down()), building an engine-neutral Blueprint — $table->string('name')->caseInsensitiveUnique(), $table->foreign('module_id')->references('id')->on('modules')->onDelete('set null'), and so on.
  • Each engine's Grammar (SqliteGrammar/PostgresGrammar/MysqlGrammar) compiles that same Blueprint to its own correct DDL — the type mapping, case-insensitive uniqueness strategy, and generated-column syntax described above all live here, once, rather than being hand-duplicated per migration.
  • A migrations table (auto-created, just like every other table) tracks which files have run, in batches — Migrator::rollback() reverses the most recent batch (or however many you ask for) by running each migration's down().
  • Existing SQLite databases (built before this system existed, by the old inline runMigration()) are bridged automatically the first time they're opened: every migration file is recorded as already applied, without re-running any of them — the schema they describe is already there. Postgres/MySQL have no such installs (they never shipped before this system did), so the bridge is SQLite-only.

You do not need any of this to run Log Lens — migrations apply automatically on connect, exactly as the schema always has. It matters if you are changing the schema: add a new migration file (never edit an old one), and it runs on every engine identically.

Verifying it's working​

GET /?api=health reports the active engine and whether it's reachable:

{
  "status": "ok",
  "checks": { "database": true, "database_version": true, "pdo_driver": true, "...": "..." },
  "versions": { "database_driver": "pgsql", "database_version": "17.0", "database_minimum": null }
}

database_minimum is only non-null for engines with a documented version floor (SQLite today).