PocketBase on MariaDB

NOTES

• Most of the migration from sqlite to mariadb was done by Opus 4.8. Gemini 3.1 Pro kept failing.
• I primarily orchestrated the direction to go in and verified that the code and tests worked.
• I have tested the application thoroughly for my use case and it works as a library. Didn't test as a standalone binary.
• I am using it in production for one app.
  • Almost all pocketbase functionalities are working including migration, superuser creation, logs, serve works too
• However, you're recommended to test it thoroughly before making it your primary tool.
• I won't be actively maintaining it since I am quite short on time. But feel free to raise issues regarding failures, bugs, etc so I can look at them.
• I cannot promise the addition of feature requests.
• This readme was updated by Opus as well.

A production-oriented fork of PocketBase with its storage engine ported from SQLite to MariaDB (10.6+). It keeps PocketBase's full feature set — REST-ish API, realtime subscriptions, auth, file management, JS/Go extensibility, and the Admin dashboard — but every query, schema operation, backup and migration runs against MariaDB instead of SQLite.

It is designed to run as a single application instance against a single MariaDB server (optionally with your own cache tier — Redis, otter, etc. — in front for read scaling).

Import path: github.com/namankumar80510/pb_mariadb

---

Status & production readiness

~95 / 100 for a single-node deployment (one app, one MariaDB box, cache tier in front, mysqldump-based backups).

• ✅ SQLite → MariaDB port is functionally complete: strict column types, JSON / || / strftime / collation rewrites, partial-unique-index emulation, view canonicalization, non-transactional-DDL compensating cleanup, mysqldump backup/restore.
• ✅ Builds clean (go build ./..., and with -tags no_default_driver).
• ✅ The full test suite passes against a live MariaDB — apis, core, forms, plugins/* (jsvm, migratecmd, ghupdate), migrations, and all tools/* — with per-test schema isolation. (Note: the earlier "fully green" migration claim was inaccurate; the suite had 8 real failures that have since been fixed and independently re-verified.)
• ✅ Connection pool and DSN tuned for high request volume (see Performance).
• ✅ Importable as a Go library under github.com/namankumar80510/pb_mariadb.
• ✅ Load-tested via ./load_testing at 100k records (reads ~2.4k rps, writes ~2.1k rps, zero errors, backup/restore verified) — see load_testing/RESULTS.md. CI, a DB-ping /health readiness probe, and a security pass are in place.
• ▶️ The remaining ~5 points are owner-environment tasks, not code gaps — see the Roadmap to 100/100: re-run the load test on the target dedicated server (the reference run was on a shared-core laptop), an interpolateParams/pool sweep, a multi-GB backup drill, slow-query/metrics hooks, and tagging a release.

This fork intentionally does not target horizontal scaling (multiple app instances, read replicas, cross-node cache coherency). PocketBase's in-memory collection/settings cache and realtime broker assume a single process; that assumption is fine for the single-node model here and is out of scope.

---

Requirements

• MariaDB 10.6+ (not MySQL 8). Driver: github.com/go-sql-driver/mysql.
• Go 1.25+ to build from source.
• mysqldump and mysql CLIs on PATH — used by the backup/restore code.

---

Configuration

All database access is driven by environment variables (a .env file in the working directory is loaded automatically via godotenv):

Variable	Required	Default	Notes
POCKETBASE_MARIADB_DSN	yes	—	Base DSN without a database name, e.g. root:pass@tcp(127.0.0.1:3306)
POCKETBASE_MARIADB_DATA_SCHEMA	no	pb_data	Main data schema (auto-created if missing)
POCKETBASE_MARIADB_AUX_SCHEMA	no	pb_aux	Auxiliary schema for logs (auto-created if missing)

.env.sample:

POCKETBASE_MARIADB_DSN="user:password@tcp(host:port)"
# Optional
POCKETBASE_MARIADB_DATA_SCHEMA="pb_data"
POCKETBASE_MARIADB_AUX_SCHEMA="pb_aux"

Never commit the DSN / password. Set it in the shell, systemd unit, or a git-ignored .env.

Strategy A (locked): dates & JSON stored as strings

Dates and JSON are stored as VARCHAR/LONGTEXT strings — the DSN deliberately omits parseTime=true. This matches PocketBase's original string-based handling and avoids per-row time.Time/[]byte allocation in the driver (lower GC pressure at high RPS). Do not add parseTime=true.

---

Quick start (standalone app)

export POCKETBASE_MARIADB_DSN='root:pass@tcp(127.0.0.1:3306)'

cd examples/base
go build            # produces ./base
./base serve        # http://127.0.0.1:8090  (Admin UI at /_/)

The first boot auto-creates the pb_data / pb_aux schemas and seeds the system tables.

---

Use as a Go library

Like upstream PocketBase, this is a regular Go package you embed in your own binary.

go get github.com/namankumar80510/pb_mariadb

package main

import (
	"log"

	pocketbase "github.com/namankumar80510/pb_mariadb"
	"github.com/namankumar80510/pb_mariadb/core"
)

func main() {
	app := pocketbase.New()

	app.OnServe().BindFunc(func(se *core.ServeEvent) error {
		se.Router.GET("/hello", func(re *core.RequestEvent) error {
			return re.String(200, "Hello from MariaDB-backed PocketBase!")
		})
		return se.Next()
	})

	if err := app.Start(); err != nil {
		log.Fatal(err)
	}
}

Run with POCKETBASE_MARIADB_DSN set, then go run . serve.

Injecting the DSN programmatically (no env var)

For a fully self-contained binary you can supply a custom DBConnect instead of relying on the env var:

app := pocketbase.NewWithConfig(pocketbase.Config{
	DataMaxOpenConns: 200,          // tune for your hardware
	DataMaxIdleConns: 80,
	DBConnect: func(dbPath string) (*dbx.DB, error) {
		// build your own DSN / schema selection here
		return dbx.Open("mysql", myDSN)
	},
})

---

Performance

Tuned defaults and guidance for high request volume on a single node. (Everything below is reasoned from the driver/InnoDB behavior; benchmark on your own hardware before locking in numbers — see the roadmap.)

1. Connection pooling

• DataMaxOpenConns = 120 (read/concurrent pool); DataMaxIdleConns = 40 — a large idle pool avoids paying the TCP+auth handshake on every burst. Idle connections above the working set are still reaped after 3 min, so this does not permanently pin 120 sockets.
• Non-concurrent (write) pool = 10 (InnoDB handles write concurrency with row-level locking; SQLite's single-writer limit is gone).
• Aux pool = 20 open / 5 idle.
• All are overridable via AppConfig (DataMaxOpenConns, DataMaxIdleConns, …).
• Ensure MariaDB max_connections covers 120 + 10 + aux for your instance (e.g. SET GLOBAL max_connections = 500;).

2. Connection lifetime

• SetConnMaxIdleTime(3m) reaps idle connections after a spike subsides.
• SetConnMaxLifetime(1h) retires/reopens every connection within an hour — avoids the server-side wait_timeout racing an in-flight transaction. Keep both under the MariaDB wait_timeout (8 h default — fine).

3. interpolateParams=true (single round trip per query)

The app DSN enables client-side parameter interpolation. Instead of a server-side prepare → execute → close cycle (2–3 round trips) per parametrized query, the driver safely inlines bind values (utf8mb4, injection-safe) and issues one round trip. This is the single biggest throughput lever for a round-trip-bound REST workload, and it pairs naturally with Strategy A (all values already flow as strings/primitives).

4. Pagination COUNT bottleneck

SELECT COUNT(...) over millions of InnoDB rows is expensive. Pass ?skipTotal=1 on any list endpoint you hit directly (not through your cache) to skip the total-count query and make the endpoint effectively O(page size).

5. Multi-relation joins (JSON_TABLE)

"Multiple relation" fields are stored as JSON arrays and joined with JSON_TABLE lateral joins — correct but slower than native link tables at scale. Prefer single relations (VARCHAR(255)) on hot paths; for heavy multi-relation queries, add an indexed generated virtual column in MariaDB.

6. Partial unique indexes

MariaDB has no CREATE UNIQUE INDEX … WHERE cond. It is emulated with hidden generated virtual columns (_pbpu_*) plus a unique index — negligible write-time cost, fully index-optimized reads. No action required.

---

MariaDB adaptation notes (gotchas)

Kept here so the hard-won knowledge survives. If you extend the SQL layer, respect these:

• DDL is non-transactional. CREATE/ALTER/DROP TABLE|VIEW auto-commit; a surrounding RunInTransaction cannot roll them back. The codebase uses compensating cleanup (snapshot state; on failure, drop/recreate to restore) for ImportCollections and view-delete. Any new DDL-in-a-transaction path needs the same treatment.
• || is logical OR, not concat → use CONCAT(...).
• SUBSTR(x, 0, n) returns '' (MariaDB is 1-indexed) → use SUBSTRING(x, 1, n).
• CAST targets: SIGNED / UNSIGNED / DECIMAL / DOUBLE / CHAR — not INT/REAL/NUMERIC/TEXT/BOOL.
• No sqlite_master/PRAGMA → use information_schema.{TABLES,VIEWS,COLUMNS,STATISTICS}.
• json_each → JSON_TABLE(...); json_extract → JSON_UNQUOTE(JSON_EXTRACT(...)); iif(...) → IF(...)/CASE; total() → sum().
• Text columns default to utf8mb4_unicode_ci (case-insensitive) — intended for email/username auth lookups.
• Index names are per-table on MariaDB (were global in SQLite).
• InnoDB returns rows in clustered-PK order without an ORDER BY (not rowid order).
• MariaDB canonicalizes CREATE VIEW; assert view behavior, not byte-identical SQL.
• dbutils/index.go: Index.Build() = DDL form (drops WHERE/COLLATE); Index.BuildCanonical() = stored/display form (keeps them). Use the right one.

Grep sweep for any remaining SQLite-only SQL when editing:

grep -rn --include=*.go -E "sqlite_master|PRAGMA| \|\| |substr\(|iif\(|json_each|randomblob|strftime" \
  core/ apis/ forms/ plugins/ migrations/ tools/

---

Backup & restore

Backups are full mysqldump logical dumps of the data schema (the mysql/mysqldump CLIs must be on PATH). Create/download/restore via the Admin UI, the backups API, or the OnBackup* hooks. Restore streams the dump back through a temporary multiStatements connection. Because MariaDB DDL auto-commits, restore is a replace-in-place operation — take a fresh backup before restoring over a live schema.

Restore runbook (manual):

# 1. Take a fresh safety dump of the current live schema first
mysqldump -h HOST -u USER -p --single-transaction --quick pb_data > safety.sql

# 2. Restore a backup into a NEW schema and verify before cutting over
mysql -h HOST -u USER -p -e "CREATE DATABASE pb_data_restore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -h HOST -u USER -p pb_data_restore < backup.sql
mysql -h HOST -u USER -p -e "SELECT COUNT(*) FROM pb_data_restore._collections;"   # sanity check

# 3. Cut over (stop the app, point POCKETBASE_MARIADB_DATA_SCHEMA at the verified schema, restart)

Measured: a 100k-record schema dumps in ~2s (17MB) and restores in ~3s with --single-transaction (no live-schema lock stalls). See load_testing/RESULTS.md.

---

Operations & security

• Readiness probe — GET /api/health returns 200 only when the app and its MariaDB backend are reachable (a cheap SELECT 1), and 503 if the DB is unreachable. Point your load balancer / orchestrator liveness+readiness checks at it. Superusers additionally get dbOK, canBackup, and proxy-detection fields.
• max_connections — the app can open up to DataMaxOpenConns(120) + write(10) + aux(≈23) ≈ 153 connections, which exceeds MariaDB's default max_connections of 151. Raise the server limit (SET GLOBAL max_connections = 500; + my.cnf) or lower the pool sizes via AppConfig before a high-concurrency deployment.
• Secrets — the DB DSN is read only from POCKETBASE_MARIADB_DSN (never hardcoded). Keep it in the environment / a git-ignored .env / a secrets manager.
• SQL console — POST /api/sql is gated by superuser auth (middleware + explicit check). It executes raw SQL; restrict superuser accounts accordingly.
• Backups contain all data in plaintext — ensure the pb_data/backups directory and any temp dir have restricted filesystem permissions, and treat downloaded dumps as secrets.

---

Testing

Requires a running MariaDB 10.6+ and the DSN env var. Each test gets its own throwaway, fixture-seeded schema (-p 2 limits schema-creation contention).

export POCKETBASE_MARIADB_DSN='root:pass@tcp(127.0.0.1:3306)'

# core integration suite (schema isolation; ~13 min)
go test ./core/ -p 2 -timeout 30m

# a single test
go test ./core/ -run '^TestSomething$' -count=1

# everything
go test ./... -p 2 -timeout 40m

Committed SQL fixtures live in tests/fixtures/mariadb_{data,aux}.sql, loaded by tests/db_mariadb.go.

---

Roadmap to 100/100

The port is feature-complete for single-node use; these close the gap to a benchmarked, hardened production release. Tracked in detail in TASKS.md.

P1 — Validate at scale (85 → 92)

• Load test on the target dedicated server at realistic write + cache-miss RPS; record p50/p95/p99 latency and MariaDB CPU/IO.
• Benchmark interpolateParams=true vs false and sweep pool sizes on real hardware; lock in tuned DataMaxOpenConns/DataMaxIdleConns for the box.
• Backup/restore drill on a large (multi-GB) dataset: time a full mysqldump, verify a clean restore, confirm no lock stalls on the live schema during backup.

P2 — Full green + CI (92 → 96)

• Run and triage the remaining suites (forms/, plugins/…, migrations/, tools/…) against live MariaDB; fix real bugs, adapt only genuinely-correct MariaDB-output tests.
• Wire CI: MariaDB 10.6+ service + POCKETBASE_MARIADB_DSN secret; run go test ./... -p 2.
• Run the SQLite-only-SQL grep sweep (above) and clear every hit.

P3 — Harden operations (96 → 99)

• Add tests for partial-failure DDL paths (collection create/alter/delete) to prove the compensating-cleanup logic under mid-operation errors.
• Add operational observability: slow-query logging threshold, a /health (DB-ping) endpoint, and basic metrics hooks.
• Verify scheduled/cron backups and a documented restore runbook.
• Security pass: SQL console authz, superuser auth, secret handling, dump file perms.

P4 — Polish (99 → 100)

• Expose ConnMaxLifetime/ConnMaxIdleTime as AppConfig fields (currently constants).
• Scrub remaining upstream pocketbase.io/SQLite references in ui/src console hints.
• Publish a tagged release of github.com/namankumar80510/pb_mariadb.

---

License

MIT — see LICENSE.md. Based on PocketBase by Gani Georgiev. See CONTRIBUTING.md for contribution notes.