plugin-sql

Imported from gabrielmoreira/agent-skills-mirror (mirrors/repos/elizaOS@eliza/plugins/plugin-sql/AGENTS.md). Install upstream with npx skills add gabrielmoreira/agent-skills-mirror --skill plugin-sql. Copyright stays with the author.

@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. The plugin runs in Node; browser clients use their host transport.

Plugin surface

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

No actions, providers, evaluators, or event handlers are registered by this plugin. Identity mutation is never model-callable.

Layout

plugins/plugin-sql/
  package.json                  Single npm manifest; scripts, deps
  build.ts                      Node ESM and bundled declarations in dist/
  README.md                     human-facing docs
  src/
    index.ts                    Node entry: PostgreSQL + PGlite; createDatabaseAdapter()
    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                    Node storage helpers (resolvePgliteDir)
    utils/
      string-to-uuid.ts         String-to-UUID conversion utility
    connector-credential-store.ts  ConnectorCredentialStore/Vault interfaces + factory
    routes/
      identity-person-link.ts    Authenticated operator attestation + verification routes
    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
    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
      sql-principal.ts  Canonical identity authority implementation
      sql-membership.ts  Canonical connector-room membership authority 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
    drizzle/                    Drizzle ORM re-exports

Identity HTTP ingress belongs to packages/agent/src/api/identity-person-link-routes.ts; this package registers storage and authority services only.

Commands

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

bun run --cwd plugins/plugin-sql build          # Node ESM + bundled declarations
bun run --cwd plugins/plugin-sql dev            # Watch build
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          # Remove generated dist and cache
bun run --cwd plugins/plugin-sql test:e2e       # live smoke test (needs running stack)

Config / env vars

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.

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.
• One Node entry. The root export uses dist/index.js and dist/index.d.ts. Schema and Drizzle have explicit entries; generated code stays in package-root dist/. Shared bundle chunks preserve schema object identity across the root and schema entries.
• 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 identity includes the representation. ensureEmbeddingSpace(spaceId) activates a named encoder/pooling/normalization representation for one agent and returns source memories needing re-embedding. Reads exclude unversioned and other-model vectors even when dimensions match. Memory rows survive. Named-vector writes replace a nonce; legacy writers cannot overwrite named vectors or downgrade them to unversioned rows. updateMemoryEmbedding retains its atomic source comparison. Dimension changes still use ensureEmbeddingDimension and scoped stale-width reconciliation.
• RLS is PostgreSQL-only. PGlite does not support Row Level Security. The ENABLE_DATA_ISOLATION path is silently skipped on PGlite.
• Document entitlements are query-time authority. Document list, lookup, and fragment queries authorize the parent before constructing results. Current room IDs satisfy the parent's single room entitlement; validated directGrantEntityIds provide read-only access outside the room, except for agent-private documents. Never materialize per-member document grants or move these predicates after pagination/ranking.
• 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.

The base plugin has no Electric sync, cloud write forwarding, or Neon serverless driver. Use ordinary PostgreSQL connections for hosted Postgres. PGlite local live queries are available only through explicitly supplied extensions.