elizaos--eliza/plugins/plugin-sql/README.md

@elizaos/plugin-sql

SQL database adapter plugin for elizaOS — provides persistent storage via PostgreSQL or embedded PGlite (WASM), with Drizzle ORM, automatic schema migrations, and optional Row Level Security.

Installation

bun add @elizaos/plugin-sql

Overview

This plugin registers a DatabaseAdapter with the elizaOS agent runtime so that all core runtime persistence (memories, entities, rooms, tasks, cache, logs, relationships, etc.) works against a real SQL backend. On Node/Bun it selects PostgreSQL when POSTGRES_URL is set, otherwise falls back to embedded PGlite. In the browser build it always uses PGlite (WASM).

Database Schema

The plugin uses the following main tables:

• Agent: Agent information and configurations
• Room / Channel: Conversation rooms and messaging channels
• Participant / ChannelParticipant: Participants in rooms and channels
• Memory: Agent memories with vector embeddings for semantic search
• Embedding: Vector embeddings for entities
• Entity: Entities agents interact with
• Relationship: Relationships between entities
• Component: Agent components and configurations
• Tasks: Tasks and goals
• Log: System logs
• Cache: Frequently accessed data cache
• World: World settings and configurations

Table definitions live in src/schema/.

Electric Sync (PGlite ↔ Electric Cloud)

When ELIZA_ELECTRIC_SYNC_URL and AGENT_ID are set, PGlite connects to an Electric sync service and streams real-time updates for all core tables. Each agent syncs only its own rows (filtered by agent_id / id), preserving per-agent isolation in shared-Neon deployments.

Local dev with Electric Cloud

The e2e write-back test (__tests__/integration/electric-write-back.test.ts) uses a Caddy reverse proxy to forward shape requests to Electric Cloud with auth. Caddy is the proxy recommended by Electric.

# 1. Set your Electric Cloud credentials (get these from dashboard.electric-sql.cloud)
export ELECTRIC_CLOUD_SOURCE_ID=svc-xxxxxxxxxxxx
export ELECTRIC_CLOUD_SECRET=eyJ...

# 2. Start the Caddy proxy
caddy run --config plugins/plugin-sql/caddy/electric-proxy.Caddyfile

# 3. Run the e2e test
bun run --cwd plugins/plugin-sql test -- \
  __tests__/integration/electric-write-back.test.ts

The Caddyfile at plugins/plugin-sql/caddy/electric-proxy.Caddyfile forwards every incoming request to api.electric-sql.cloud with auth query params appended.

Environment Variables

Variable	Required	Default	Effect
POSTGRES_URL	No	—	PostgreSQL connection string. When absent, PGlite is used.
PGLITE_DATA_DIR	No	.eliza/.elizadb	Directory (or idb:// URL) for PGlite data storage.
ELIZA_ELECTRIC_SYNC_URL	No	—	Base URL of Electric sync service (e.g. http://localhost:3001 via Caddy).
AGENT_ID	Conditional	—	UUID of the agent. Required when ELIZA_ELECTRIC_SYNC_URL is set (per-agent WHERE filter).
ELIZA_CLOUD_WRITE_BASE_URL	No	—	Write-back cloud endpoint for forwarding local PGlite writes to Postgres.
ELIZA_CLOUD_SERVICE_KEY	No	—	Service key for authenticating write-back requests.
ELECTRIC_CLOUD_SOURCE_ID	Test-only	—	Electric Cloud source ID (consumed by Caddy, not the runtime).
ELECTRIC_CLOUD_SECRET	Test-only	—	Electric Cloud JWT secret (consumed by Caddy, not the runtime).
ELIZA_PGLITE_DISABLE_EXTENSIONS	No	false	Set to 1 to disable PGlite extensions (vector, live, fuzzystrmatch, Electric sync).
ENABLE_DATA_ISOLATION	No	false	When true, enables PostgreSQL Row Level Security per-server isolation.
ELIZA_SERVER_ID	Conditional	—	Required when ENABLE_DATA_ISOLATION=true; becomes the RLS server UUID.
ELIZA_ALLOW_DESTRUCTIVE_MIGRATIONS	No	false	Allow column drops and other destructive schema changes at startup.
ELIZA_APPLY_MESSAGE_SEARCH_OBJECTS	No	auto	Controls automatic install of the message_search_document generated column and message-search GIN indexes. Production Postgres adapters skip this DDL by default; set true after scheduling the generated-column/index migration.
NODE_ENV	No	development	production disables verbose migration logging and tightens safety checks.

Settings are read via runtime.getSetting(key) inside plugin.init.

Vector Dimensions

VECTOR_DIMS = {
  SMALL: 384,
  MEDIUM: 512,
  LARGE: 768,
  XL: 1024,
  XXL: 1536,
  XXXL: 3072,
};

When an agent switches embedding dimensions, runtime boot deletes vectors stored in other dimension columns and queues those memories for re-embedding at the active width. Memory rows survive; stale vectors do not.

Runtime Migrations

Plugins export a schema object; DatabaseMigrationService diffs the schema against the live DB at startup and runs migrations automatically. No manual drizzle-kit generate / drizzle-kit push step is needed in normal development.

// In your plugin
export const plugin = {
  name: "@your-org/plugin-name",
  schema: schema, // Drizzle schema object
  // ...
};

Destructive changes (column drops, type changes) are blocked by default. Set ELIZA_ALLOW_DESTRUCTIVE_MIGRATIONS=true to allow them.

Message-search DDL is also guarded on production Postgres. The generated column and GIN indexes are still installed automatically for development/test and embedded PGlite. For production Postgres, schedule the table rewrite/index creation and run with ELIZA_APPLY_MESSAGE_SEARCH_OBJECTS=true once the deployment window is approved.

Connection Management

Both PostgresConnectionManager and PGliteClientManager are stored under Symbol.for("elizaos.plugin-sql.global-singletons") on globalThis. This prevents multiple pools when the module is imported from multiple paths in the same process. Do not construct manager instances directly — always go through createDatabaseAdapter().

Database Pool Configuration

Default Postgres pool configuration (src/pg/manager.ts):

{
    max: 20,
    min: 2,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 5000,
    keepAlive: true,
    keepAliveInitialDelayMillis: 10000
}

Retry Configuration

BaseDrizzleAdapter retries failed operations with exponential backoff and jitter (src/base.ts):

{
    maxRetries: 3,
    baseDelay: 1000,
    maxDelay: 10000,
    jitterMax: 1000
}

Requirements

• Node.js or Bun
• PostgreSQL with vector extension (for Postgres mode)