Database Backends

Grove supports multiple database backends for persisting events and projected state. Each backend is accessed through the same Grove code; the backend choice affects only the connection configuration and the generated DDL. Supported backends are SQLite, PostgreSQL, MySQL, and TiDB.

Prerequisites: CLI Reference for database-related commands, Configuration Reference for grove.toml settings. What you'll learn: How to configure each database backend, connection string formats, schema initialization, scoping, and DDL generation.

Supported Backends

SQLite

Connection String

<file-path>       -- file-based database
:memory:          -- in-memory database (lost on process exit)

Examples:

# File-based
manzano run-db ./order \
  --backend sqlite \
  --db "./data/order.db"

# In-memory (for testing)
manzano run-db ./order \
  --backend sqlite \
  --db ":memory:"

Configuration in grove.toml

[database]
backend = "sqlite"
connection = "./data/app.db"

Notes

• SQLite databases are created automatically if they do not exist.
• WAL (Write-Ahead Logging) mode is enabled by default for better concurrent read performance.
• SQLite does not support concurrent writes from multiple processes. Use PostgreSQL or MySQL for multi-process deployments.
• The DECIMAL type is stored as TEXT in SQLite to preserve precision.

Generated DDL

For a module named order with fields customer_id: Id, status: String, and total: Decimal, the per-resource table is:

CREATE TABLE IF NOT EXISTS "grove_order" (
  "pk" INTEGER PRIMARY KEY AUTOINCREMENT,
  "id" TEXT NOT NULL,
  "customer_id" TEXT NOT NULL,
  "status" TEXT NOT NULL,
  "total" TEXT NOT NULL,
  UNIQUE ("id")
);

Events, outbox records, audit logs, and other runtime state live in nine shared system tables prefixed grove_ (for example grove_events, grove_outbox, grove_audit). These are created once per database, not per module.

PostgreSQL

Connection String

postgres://<user>:<password>@<host>:<port>/<database>[?<options>]

Examples:

# Basic connection
manzano run-db ./order \
  --backend postgres \
  --db "postgres://grove:secret@localhost:5432/grovedb"

# With SSL
manzano run-db ./order \
  --backend postgres \
  --db "postgres://grove:secret@db.example.com:5432/grovedb?sslmode=require"

# With connection pool settings
manzano run-db ./order \
  --backend postgres \
  --db "postgres://grove:secret@localhost:5432/grovedb?pool_max=20&pool_min=5"

Configuration in grove.toml

[database]
backend = "postgres"
connection = "postgres://grove:secret@localhost:5432/grovedb"

[database.pool]
max_connections = 20
min_connections = 5
idle_timeout_seconds = 300

Notes

• PostgreSQL 14 or later is recommended.
• The DECIMAL type maps to PostgreSQL NUMERIC.
• Id maps to TEXT (Grove IDs are prefixed identifiers, not UUIDs — see Types).
• DateTime maps to TIMESTAMPTZ.
• Value maps to JSONB.
• Schema creation requires CREATE TABLE privileges.

Generated DDL

CREATE TABLE IF NOT EXISTS "grove_order" (
  "pk" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "id" TEXT NOT NULL,
  "customer_id" TEXT NOT NULL,
  "status" TEXT NOT NULL,
  "total" NUMERIC NOT NULL,
  UNIQUE ("id")
);

CREATE INDEX IF NOT EXISTS "idx_grove_order_customer"
  ON "grove_order" ("customer_id");

Events, outbox records, audit logs, and other runtime state live in nine shared system tables prefixed grove_ (for example grove_events, grove_outbox, grove_audit).

MySQL

Connection String

mysql://<user>:<password>@<host>:<port>/<database>[?<options>]

Examples:

# Basic connection
manzano run-db ./order \
  --backend mysql \
  --db "mysql://grove:secret@localhost:3306/grovedb"

# With charset
manzano run-db ./order \
  --backend mysql \
  --db "mysql://grove:secret@localhost:3306/grovedb?charset=utf8mb4"

Configuration in grove.toml

[database]
backend = "mysql"
connection = "mysql://grove:secret@localhost:3306/grovedb"

Notes

• MySQL 8.0 or later is required.
• The DECIMAL type maps to DECIMAL(65,30).
• Id maps to VARCHAR(96) (96 bytes provides headroom over the 87-byte maximum for cross-project long-form IDs — see Types).
• DateTime maps to BIGINT holding epoch milliseconds. This dodges the MySQL TIMESTAMP 2038 boundary and avoids time-zone ambiguity in DATETIME.
• Value maps to JSON.
• InnoDB engine is required (default in MySQL 8.0+).

Generated DDL

CREATE TABLE IF NOT EXISTS `grove_order` (
  `pk` BIGINT NOT NULL AUTO_INCREMENT,
  `id` VARCHAR(96) NOT NULL,
  `customer_id` VARCHAR(96) NOT NULL,
  `status` VARCHAR(255) NOT NULL,
  `total` DECIMAL(65,30) NOT NULL,
  PRIMARY KEY (`pk`),
  UNIQUE KEY `grove_order_id_uk` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Events, outbox records, audit logs, and other runtime state live in nine shared system tables prefixed grove_. On MySQL the synthetic pk uses plain AUTO_INCREMENT; see the TiDB section below for the distributed-friendly alternative.

TiDB

Connection String

TiDB uses the MySQL protocol, so connection strings follow the same format:

mysql://<user>:<password>@<host>:<port>/<database>[?<options>]

Examples:

manzano run-db ./order \
  --backend tidb \
  --db "mysql://grove:secret@localhost:4000/grovedb"

Configuration in grove.toml

[database]
backend = "tidb"
connection = "mysql://grove:secret@localhost:4000/grovedb"

Notes

• TiDB 6.0 or later is recommended.
• TiDB is MySQL-compatible, so the generated DDL is nearly identical to MySQL, with one important difference: the synthetic pk column uses AUTO_RANDOM(5) instead of AUTO_INCREMENT to spread writes across TiDB regions and avoid hot-spotting.
• All MySQL type mappings apply: Id is VARCHAR(96), DateTime is BIGINT (epoch milliseconds), Value is JSON.
• TiDB provides horizontal scalability for high-throughput workloads.
• Unlike MySQL, TiDB supports distributed transactions natively, making it suitable for multi-region deployments.

Generated DDL

CREATE TABLE IF NOT EXISTS `grove_order` (
  `pk` BIGINT NOT NULL PRIMARY KEY /*T![auto_rand] AUTO_RANDOM(5) */,
  `id` VARCHAR(96) NOT NULL,
  `customer_id` VARCHAR(96) NOT NULL,
  `status` VARCHAR(255) NOT NULL,
  `total` DECIMAL(65,30) NOT NULL,
  UNIQUE KEY `grove_order_id_uk` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Schema Initialization

Automatic Initialization

Use the --init-schema flag to create tables automatically:

manzano run-db ./order \
  --backend postgres \
  --db "postgres://grove:secret@localhost:5432/grovedb" \
  --init-schema

This executes CREATE TABLE IF NOT EXISTS statements before running the action.

Manual Initialization

Generate DDL with the ddl command and apply it manually:

# Generate DDL
manzano ddl ./order --backend postgres > schema.sql

# Apply with psql
psql "postgres://grove:secret@localhost:5432/grovedb" < schema.sql

Schema Migrations

Grove does not have a built-in migration system. When event schemas change:

1. Add new event versions with upcast declarations (handles event-side evolution).
2. For projected state table changes, use ddl --drop to regenerate and re-apply, or use an external migration tool.
3. Projected state can always be rebuilt from the event stream.

Scoping

Grove uses two kinds of tables:

• Per-resource tables. One table per module's root record, named after the resource (e.g. grove_order for a module that declares record { kind "order" ... }). These hold the aggregate state — the projected fields, one row per live record.
• System tables. Nine shared tables prefixed grove_ that hold events, outbox records, audit logs, secrets, webhooks, and agent sessions. These are created once per database and are used by all modules in the project.

grove_order           -- per-resource state for the order module
grove_customer        -- per-resource state for the customer module
grove_events          -- shared event stream for all modules
grove_outbox          -- shared transactional outbox
grove_audit           -- shared audit log
...

Multiple modules can share the same database: each gets its own per-resource table, and they all write to the shared system tables.

Custom Table Prefix

The grove_ prefix is configurable at runtime-store construction time (for example, manzano-console uses mzno_). System tables and per-resource tables share the same prefix.

Type Mapping Summary

Id on MySQL/TiDB. VARCHAR(96) provides headroom over the 87-byte maximum length of a cross-project long-form Grove ID (project:prefix_suffix). See Types — Id.

DateTime on MySQL/TiDB. BIGINT holds epoch milliseconds. This dodges the MySQL TIMESTAMP 2038 cutoff and avoids time-zone ambiguity in DATETIME. The grove runtime converts to and from DateTime automatically; application code continues to use DateTime values normally.

See Also

• CLI Reference -- database-related CLI commands
• Configuration Reference -- database settings in grove.toml
• Server -- database configuration for the server
• Field Classifications -- encrypted column handling