elizaos--eliza/plugins/plugin-sql/AGENTS.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.

Purpose / role

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. It is the default database plugin; elizaOS agents load it automatically if no other adapter is already registered. 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).

Plugin surface

The exported plugin object (src/index.ts / src/index.node.ts / src/index.browser.ts) registers:

Kind	Name	Description
Service	AdvancedMemoryStorageService (serviceType = "memoryStorage")	Implements MemoryStorageProvider; persists long-term memories and session summaries to dedicated SQL tables via the runtime memory API
Schema	schema (all tables)	Passed as plugin.schema so DatabaseMigrationService can auto-migrate at startup

No actions, providers, evaluators, routes, or event handlers are registered by this plugin.

Layout

plugins/plugin-sql/
  package.json                  npm manifest; scripts, deps
  README.md                     human-facing docs
  src/
    index.ts                    Default entry (same implementation as index.node.ts; uses ./utils)
    index.node.ts               Node/Bun entry: PostgreSQL + PGlite; createDatabaseAdapter()
    index.browser.ts            Browser entry: PGlite-only plugin
    base.ts                     BaseDrizzleAdapter — shared IDatabaseAdapter implementation
    types.ts                    DrizzleDatabase union type; getDb() helper
    agent-mapping.ts            Utilities for normalizing agent message examples from DB rows
    utils.ts / utils.node.ts / utils.browser.ts  Platform-specific helpers (resolvePgliteDir)
    utils/
      string-to-uuid.ts         String-to-UUID conversion utility
    connector-credential-store.ts  ConnectorCredentialStore/Vault interfaces + factory
    migration-service.ts        DatabaseMigrationService — discovers plugin schemas, runs migrations, re-applies RLS
    migrations.ts               One-off migrations (e.g., entity RLS backfill)
    rls.ts                      Row Level Security helpers (install/apply/uninstall)
    pg/
      adapter.ts                PgDatabaseAdapter (wraps BaseDrizzleAdapter for Postgres)
      manager.ts                PostgresConnectionManager — pg Pool singleton, withEntityContext
      sslmode.ts                SSL mode resolver
    pglite/
      adapter.ts                PgliteDatabaseAdapter (wraps BaseDrizzleAdapter for PGlite)
      manager.ts                PGliteClientManager — PGlite singleton, lifecycle
      errors.ts                 PGlite-specific error types
    neon/
      adapter.ts                NeonDatabaseAdapter — serverless adapter using @neondatabase/serverless
      manager.ts                NeonConnectionManager — WebSocket-based connection for Neon/Vercel/Cloudflare
    schema/
      index.ts                  Re-exports all table definitions
      agent.ts / room.ts / memory.ts / entity.ts / ...  One file per table
    services/
      advanced-memory-storage.ts  AdvancedMemoryStorageService implementation
    stores/
      agent.store.ts / memory.store.ts / room.store.ts / ...  Query logic split by domain
    runtime-migrator/
      index.ts                  RuntimeMigrator entry
      runtime-migrator.ts       Diff-based migration engine
      schema-transformer.ts     Drizzle schema → SQL diff
      extension-manager.ts      PGlite extension loading
    write-back/
      index.ts                  WriteBackService — forwards local PGlite writes to cloud API (Electric Pattern 1)
    drizzle/                    Drizzle ORM re-exports

Commands

All scripts run from the plugin root via bun run --cwd plugins/plugin-sql <script>.

bun run --cwd plugins/plugin-sql build          # Build (cd src && bun run build.ts)
bun run --cwd plugins/plugin-sql dev            # Watch build (bun --hot build.ts)
bun run --cwd plugins/plugin-sql test           # vitest run
bun run --cwd plugins/plugin-sql typecheck      # tsc --noEmit
bun run --cwd plugins/plugin-sql lint           # biome lint
bun run --cwd plugins/plugin-sql lint:check     # biome lint (no write)
bun run --cwd plugins/plugin-sql format         # biome format (write)
bun run --cwd plugins/plugin-sql format:check   # biome format (check only)
bun run --cwd plugins/plugin-sql clean          # rm -rf src/dist .turbo
bun run --cwd plugins/plugin-sql test:e2e       # live smoke test (needs running stack)

Config / env vars

Variable	Required	Default	Effect
POSTGRES_URL	No	—	PostgreSQL connection string. When absent, PGlite is used.
DATABASE_URL	No	—	Alternative connection string used by the Neon serverless adapter.
PGLITE_DATA_DIR	No	.eliza/.elizadb	Directory (or idb:// URL) for PGlite data storage.
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.
ELIZA_ELECTRIC_SYNC_URL	No	—	URL for the Electric sync service; enables PGlite cloud sync read path.
ELIZA_CLOUD_WRITE_BASE_URL	No	—	Base URL of the cloud API for WriteBackService (e.g. https://api.elizacloud.ai). If unset, write-back is a no-op.
ELIZA_CLOUD_SERVICE_KEY	No	—	X-Service-Key header value sent by WriteBackService to the cloud API.
ELIZA_PGLITE_DISABLE_EXTENSIONS	No	false	Disables PGlite extension loading when set.
ELIZA_IOS_LOCAL_BACKEND	No	—	Overrides the local backend URL for iOS platform targets.
ELIZA_ANDROID_LOCAL_BACKEND	No	—	Overrides the local backend URL for Android platform targets.
ELIZA_BENCH_DISABLE_DOTENV	No	false	Prevents benchmark processes from loading ancestor .env files while resolving PGlite storage. Subscription chat-only benchmark mode enforces the same behavior.
NODE_ENV	No	development	production disables verbose migration logging and tightens safety checks.

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

How to extend

Add a new schema table

1. Create src/schema/<tableName>.ts exporting a Drizzle pgTable(...).
2. Add the export to src/schema/index.ts.
3. The plugin's schema export is picked up by DatabaseMigrationService at startup — no manual drizzle-kit generate step needed in normal development.

Add a new store (domain queries)

1. Create src/stores/<domain>.store.ts implementing your query functions against DrizzleDatabase.
2. Export from src/stores/index.ts.
3. Call from BaseDrizzleAdapter in src/base.ts or from the relevant PgDatabaseAdapter / PgliteDatabaseAdapter.

Add a new service

1. Implement Service from @elizaos/core in src/services/<name>.ts.
2. Add it to the services array in the plugin object in src/index.ts (and mirror in src/index.node.ts / src/index.browser.ts as appropriate).

Conventions / gotchas

• Global singleton managers. 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 create manager instances directly — always go through createDatabaseAdapter().
• Skips init if adapter already registered. If another plugin already called registerDatabaseAdapter before this plugin's init runs, the plugin does nothing. This is intentional; use it to swap in a custom adapter by loading it first.
• Dual-runtime exports. The exports field in package.json conditionally resolves index.node.js (Bun/Node) vs index.browser.js (browser). The node entry has PostgreSQL support; the browser entry is PGlite-only. Do not import the node adapter directly in browser-targeted code.
• Schema subpath export. Consumers that only need schema types (e.g., for drizzle queries outside the plugin) can import from @elizaos/plugin-sql/schema without pulling in adapters.
• Drizzle subpath export. Common Drizzle query helpers (eq, sql, and, etc.) are re-exported from @elizaos/plugin-sql and @elizaos/plugin-sql/drizzle to avoid direct drizzle-orm version coupling in consumer code.
• Vector dimensions are active-width scoped. ensureEmbeddingDimension(n) selects the current vector column, and runtime boot calls clearEmbeddingsOutsideActiveDimension() to delete vectors in other dimension columns and queue those memories for re-embedding at the active width. Memory rows survive; stale vectors do not.
• RLS is PostgreSQL-only. PGlite does not support Row Level Security. The ENABLE_DATA_ISOLATION path is silently skipped on PGlite.
• Tests live under src/__tests__/ and run via vitest configured in src/vitest.config.ts.

Verification

Follow the repository-wide verification and evidence standard in the root CLAUDE.md. Run the package's relevant build, typecheck, lint, and test commands, then exercise the real integration boundary changed by the work. Inspect the produced domain artifacts and failure behavior; do not substitute mocked success for the system under test.