pocketbase

README

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.

Documentation

Index

• Variables
• type Config
• type PocketBase
  • func New() *PocketBase
  • func NewWithConfig(config Config) *PocketBase
  • func (pb *PocketBase) Execute() error
  • func (pb *PocketBase) Start() error

Variables

var Version = "(untracked)"

Version of PocketBase

Types

type Config

type Config struct {
	// hide the default console server info on app startup
	HideStartBanner bool

	// optional default values for the console flags
	DefaultDev           bool
	DefaultDataDir       string // if not set, it will fallback to "./pb_data"
	DefaultEncryptionEnv string
	DefaultQueryTimeout  time.Duration // default to core.DefaultQueryTimeout (in seconds)

	// optional DB configurations
	DataMaxOpenConns int                // default to core.DefaultDataMaxOpenConns
	DataMaxIdleConns int                // default to core.DefaultDataMaxIdleConns
	AuxMaxOpenConns  int                // default to core.DefaultAuxMaxOpenConns
	AuxMaxIdleConns  int                // default to core.DefaultAuxMaxIdleConns
	ConnMaxIdleTime  time.Duration      // default to core.DefaultConnMaxIdleTime
	ConnMaxLifetime  time.Duration      // default to core.DefaultConnMaxLifetime
	DBConnect        core.DBConnectFunc // default to core.dbConnect
}

Config is the PocketBase initialization config struct.

type PocketBase

type PocketBase struct {
	core.App

	// RootCmd is the main console command
	RootCmd *cobra.Command
	// contains filtered or unexported fields
}

PocketBase defines a PocketBase app launcher.

It implements core.App via embedding and all of the app interface methods could be accessed directly through the instance (eg. PocketBase.DataDir()).

func New

func New() *PocketBase

New creates a new PocketBase instance with the default configuration. Use NewWithConfig if you want to provide a custom configuration.

Note that the application will not be initialized/bootstrapped yet, aka. DB connections, migrations, app settings, etc. will not be accessible. Everything will be initialized when PocketBase.Start is executed. If you want to initialize the application before calling PocketBase.Start, then you'll have to manually call [PocketBase.Bootstrap].

func NewWithConfig

func NewWithConfig(config Config) *PocketBase

NewWithConfig creates a new PocketBase instance with the provided config.

Note that the application will not be initialized/bootstrapped yet, aka. DB connections, migrations, app settings, etc. will not be accessible. Everything will be initialized when PocketBase.Start is executed. If you want to initialize the application before calling PocketBase.Start, then you'll have to manually call [PocketBase.Bootstrap].

func (*PocketBase) Execute

func (pb *PocketBase) Execute() error

Execute initializes the application (if not already) and executes the pb.RootCmd with graceful shutdown support.

This method differs from pb.Start() by not registering the default system commands!

func (*PocketBase) Start

func (pb *PocketBase) Start() error

Start starts the application, aka. registers the default system commands (serve, superuser, version) and executes pb.RootCmd.