elizaOS
Advanced

Database Adapters

PostgreSQL and PGlite database adapters with vector search, memory management, and advanced features

Overview

The database adapter system provides:

  • PostgreSQL Support - Full-featured PostgreSQL with pgvector extension
  • PGlite Fallback - Embedded database for development and testing
  • Vector Search - Semantic memory search with configurable dimensions
  • Automatic Migrations - Schema management and versioning
  • Connection Management - Efficient resource management with singleton pattern
  • Type Safety - Full TypeScript support with Drizzle ORM

Architecture

┌─────────────────────────────────────────────┐
│            Eliza Runtime                     │
│  ┌─────────────────────────────────────┐    │
│  │      Database Interface              │    │
│  └─────────────────┬───────────────────┘    │
│                    │                         │
└────────────────────┼─────────────────────────┘
                     │
     ┌───────────────┴───────────────┐
     │    Plugin SQL Adapter         │
     │  ┌─────────────────────────┐  │
     │  │   Connection Manager    │  │
     │  │  - Singleton Pattern    │  │
     │  │  - Connection Pooling   │  │
     │  │  - Connection Testing   │  │
     │  └───────────┬─────────────┘  │
     │              │                │
     │    ┌─────────┴─────────┐     │
     │    │                   │     │
     │ ┌──┴──┐           ┌───┴───┐ │
     │ │ PG  │           │PGlite │ │
     │ └─────┘           └───────┘ │
     └───────────────────────────────┘

Installation

bun add @elizaos/plugin-sql
npm install @elizaos/plugin-sql
pnpm add @elizaos/plugin-sql

Configuration

Environment Variables

# PostgreSQL connection (optional)
POSTGRES_URL=postgresql://user:password@localhost:5432/eliza

# PGlite data directory (optional, defaults to ./.eliza/.elizadb)
PGLITE_PATH=./data/pglite
# Alternative environment variables (also supported)
DATABASE_PATH=./data/pglite
PGLITE_DATA_DIR=./data/pglite

Vector Dimensions

The adapter supports multiple embedding dimensions:

// From @elizaos/core
const VECTOR_DIMS = {
  SMALL: 384, // Lightweight models
  MEDIUM: 512, // Balanced performance
  LARGE: 768, // Standard BERT-like models
  XL: 1024, // Large models
  XXL: 1536, // OpenAI ada-002
  XXXL: 3072, // Large context models
};

// Dimension mapping in plugin-sql
const DIMENSION_MAP = {
  [384]: "dim384",
  [512]: "dim512",
  [768]: "dim768",
  [1024]: "dim1024",
  [1536]: "dim1536",
  [3072]: "dim3072",
};

Important: Once an agent is initialized with a specific dimension, it cannot be changed.

Usage

Basic Integration

import { AgentRuntime } from "@elizaos/core";
import { plugin as sqlPlugin } from "@elizaos/plugin-sql";

// Plugin automatically initializes based on environment
const runtime = new AgentRuntime({
  plugins: [sqlPlugin],
});

// The plugin will:
// 1. Check if adapter already exists
// 2. Read POSTGRES_URL from runtime settings
// 3. Read PGLITE_PATH/DATABASE_PATH from runtime settings
// 4. Create adapter using createDatabaseAdapter
// 5. Register adapter with runtime.registerDatabaseAdapter

Direct Adapter Usage

import {
  PgDatabaseAdapter,
  PgliteDatabaseAdapter,
  createDatabaseAdapter,
} from "@elizaos/plugin-sql";
import { v4 as uuidv4 } from "uuid";

// Create adapter using factory function
const adapter = createDatabaseAdapter(
  {
    postgresUrl: "postgresql://user:password@localhost:5432/eliza",
  },
  agentId
);

// Initialize adapter
await adapter.init();

// Use adapter methods
const memories = await adapter.getMemories({
  tableName: "memories",
  agentId,
  roomId,
  count: 10,
});

Database Schema

Core Tables

Agent Table

{
  id: uuid,                    // Unique agent identifier
  enabled: boolean,            // Agent enabled status
  createdAt: timestamp,        // Creation timestamp
  updatedAt: timestamp,        // Last update timestamp
  name: text,                  // Agent name
  username: text,              // Unique username
  system: text,                // System prompt
  bio: jsonb,                  // Agent biography
  messageExamples: jsonb,      // Example messages
  postExamples: jsonb,         // Example posts
  topics: jsonb,               // Agent topics
  adjectives: jsonb,           // Agent adjectives
  knowledge: jsonb,            // Knowledge base
  plugins: jsonb,              // Plugin configuration
  settings: jsonb,             // Agent settings
  style: jsonb                 // Agent style configuration
}

Memory Table

{
  id: uuid,           // Memory identifier
  type: text,         // Memory type
  createdAt: timestamp, // Creation timestamp
  content: jsonb,     // Memory content (JSON)
  entityId: uuid,     // Associated entity
  agentId: uuid,      // Associated agent
  roomId: uuid,       // Conversation room
  worldId: uuid,      // World identifier
  unique: boolean,    // Uniqueness flag
  metadata: jsonb     // Additional metadata
}

Room Table

{
  id: uuid,           // Room identifier
  name: text,         // Room name
  source: text,       // Room source
  type: text,         // Room type (ChannelType)
  createdAt: timestamp, // Creation time
  agentId: uuid,      // Managing agent
  metadata: jsonb     // Room metadata
}

Additional Tables

  • Participant - Room membership tracking
  • Relationship - Entity relationships
  • Entity - Named entities extraction
  • Component - Agent components/plugins
  • Tasks - Goal and task tracking
  • Cache - Performance optimization
  • Embedding - Vector storage with multiple dimensions
  • Log - System event logging

Embedding Table

{
  id: uuid,            // Embedding identifier
  memoryId: uuid,      // Associated memory
  createdAt: timestamp, // Creation time
  dim384: vector,      // 384-dimensional embedding
  dim512: vector,      // 512-dimensional embedding
  dim768: vector,      // 768-dimensional embedding
  dim1024: vector,     // 1024-dimensional embedding
  dim1536: vector,     // 1536-dimensional embedding
  dim3072: vector      // 3072-dimensional embedding
}

Features

Semantic search across memories:

// Search memories by semantic similarity
const relevantMemories = await adapter.searchMemories({
  tableName: "memories",
  agentId: agent.id,
  roomId: currentRoom,
  embedding: queryVector,
  match_threshold: 0.75, // Similarity threshold
  match_count: 20, // Maximum results
  unique: true, // Only unique memories
});

// The search uses pgvector's cosine similarity
// Results are ordered by relevance

Memory Management

import { v4 as uuidv4 } from "uuid";

// Create a memory with embedding
const memory = {
  id: uuidv4(),
  agentId,
  roomId,
  entityId: userId,
  content: { text: "Important information" },
  type: "knowledge",
  unique: true,
  metadata: { type: "knowledge" },
};
const memoryId = await adapter.createMemory(memory, "memories");

// Retrieve memories by room
const roomMemories = await adapter.getMemories({
  tableName: "memories",
  agentId,
  roomId,
  count: 50,
});

// Remove memories
await adapter.deleteMemory(memoryId);

Connection Management

The adapter uses a singleton pattern for connection management:

// PostgreSQL connection manager
class PostgresConnectionManager {
  private pool: Pool;
  private db: NodePgDatabase;

  constructor(connectionString: string) {
    this.pool = new Pool({ connectionString });
    this.db = drizzle(this.pool);
  }

  getDatabase(): NodePgDatabase {
    return this.db;
  }

  async getClient(): Promise<PoolClient> {
    return this.pool.connect();
  }

  async testConnection(): Promise<boolean> {
    // Test database connectivity
  }
}

// PGlite connection manager
class PGliteClientManager {
  private connection: PGlite;
  private shuttingDown = false;

  constructor(config: { dataDir: string }) {
    this.connection = new PGlite(config.dataDir);
  }

  getConnection(): PGlite {
    return this.connection;
  }

  isShuttingDown(): boolean {
    return this.shuttingDown;
  }
}

Migrations

Automatic Migrations

Migrations are handled by the DatabaseMigrationService:

// Import the migration service
import { DatabaseMigrationService } from "@elizaos/plugin-sql";

// Create migration service
const migrationService = new DatabaseMigrationService(adapter);

// Run migrations
await migrationService.migrate();

// Migrations check for:
// 1. Missing tables
// 2. Missing vector extension
// 3. Schema updates

Manual Migrations

For schema updates:

# Generate migration files
npx drizzle-kit generate:pg

# Apply migrations
npx drizzle-kit push:pg

# Or use the migrate command
npx drizzle-kit migrate

Migration Configuration

// drizzle.config.ts
export default defineConfig({
  dialect: "postgresql",
  schema: "./src/schema/index.ts",
  out: "./drizzle/migrations",
  dbCredentials: {
    url: process.env.POSTGRES_URL || "file:../../.eliza/.elizadb",
  },
});

Advanced Usage

Custom Schemas

Extend the database with custom tables:

import { schema } from "@elizaos/plugin-sql";
import { pgTable, text, uuid } from "drizzle-orm/pg-core";

export const customTable = pgTable("custom_data", {
  id: uuid("id").primaryKey().notNull(),
  agentId: uuid("agent_id").notNull(),
  data: text("data"),
  metadata: text("metadata"),
});

// Use with adapter
const results = await adapter.db.select().from(customTable).where(eq(customTable.agentId, agentId));

Schema Factory Pattern

The plugin uses a schema factory pattern for dynamic schema creation:

import { createSchemaFactory } from "@elizaos/plugin-sql";

// Create schema factory with custom tables
const schemaFactory = createSchemaFactory({
  customTables: {
    myTable: pgTable("my_table", {
      id: uuid("id").primaryKey(),
      data: text("data"),
    }),
  },
});

// Use factory to create schemas
const schema = schemaFactory.createSchema();

Batch Operations

Efficient bulk operations:

// Batch insert memories
await adapter.db.transaction(async (tx) => {
  const memories = dataArray.map((data) => ({
    id: uuid(),
    agentId,
    entityId: data.entityId,
    roomId: data.roomId,
    content: data.content,
    type: data.type,
    unique: true,
    metadata: data.metadata || {},
  }));

  await tx.insert(memoryTable).values(memories);
});

Performance Optimization

// Use indexes for frequent queries
await adapter.db.execute(sql`
  CREATE INDEX IF NOT EXISTS idx_memories_agent_room 
  ON memories(agent_id, room_id);
`);

// Partition large tables
await adapter.db.execute(sql`
  CREATE TABLE memories_2024 PARTITION OF memories
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
`);

PGlite Support

When PostgreSQL is not available, the adapter automatically uses PGlite:

// PGlite features:
// - Embedded PostgreSQL
// - No external dependencies
// - File-based storage
// - Vector extension support
// - Same API as PostgreSQL adapter

// Configuration
const pgliteAdapter = createDatabaseAdapter(
  {
    dataDir: "./data/pglite", // Storage directory
  },
  agentId
);

PGlite Limitations

  • Single connection only
  • No network access
  • Limited concurrent operations
  • Best for development/testing

Error Handling

Basic Error Handling

// Basic error handling for database operations
try {
  await adapter.getMemories({
    tableName: "memories",
    agentId,
    roomId,
    count: 50,
  });
} catch (error) {
  logger.error("Database operation failed:", error);
  // Handle specific error types
  if (error.code === "ECONNREFUSED") {
    // Connection refused
  }
}

PGlite Shutdown Handling

// PGlite adapter checks for shutdown state
protected async withDatabase<T>(operation: () => Promise<T>): Promise<T> {
  if (this.manager.isShuttingDown()) {
    logger.warn('Database is shutting down');
    return null as unknown as T;
  }
  return operation();
}

Best Practices

1. Connection Management

// Good: Use the plugin system
const runtime = new AgentRuntime({
  plugins: [sqlPlugin],
});
// Adapter is automatically created and registered

// Good: Manual adapter creation
const adapter = createDatabaseAdapter(
  {
    postgresUrl: process.env.POSTGRES_URL,
    dataDir: "./.eliza/.elizadb",
  },
  agentId
);

// Bad: Creating multiple adapters for same database
const adapter1 = createDatabaseAdapter(config, agentId);
const adapter2 = createDatabaseAdapter(config, agentId); // Duplicate connections

2. Vector Dimensions

// Set consistent dimensions across agents
const STANDARD_DIMENSION = 1536; // OpenAI ada-002

// Validate before creating
if (embedding.length !== STANDARD_DIMENSION) {
  throw new Error("Invalid embedding dimension");
}

3. Memory Queries

// Good: Use appropriate thresholds
const memories = await adapter.searchMemories({
  tableName: 'memories',
  agentId,
  roomId,
  embedding: queryEmbedding,
  match_threshold: 0.8,  // High relevance only
  match_count: 10        // Limit results
});

// Bad: Over-fetching
const memories = await adapter.searchMemories({
  tableName: 'memories',
  agentId,
  roomId,
  embedding: queryEmbedding,
  match_threshold: 0.1,  // Too loose
  match_count: 1000      // Too many
});

4. Transaction Management

// Use transactions for consistency
await adapter.db.transaction(async (tx) => {
  await tx.insert(roomTable).values(room);
  await tx.insert(participantTable).values(participants);
});

Monitoring

Database Metrics

// Check adapter readiness
const isReady = await adapter.isReady();
console.log("Database ready:", isReady);

// Test connection (PostgreSQL)
if (adapter instanceof PgDatabaseAdapter) {
  const connection = await adapter.getConnection();
  console.log("Pool status:", {
    totalCount: connection.totalCount,
    idleCount: connection.idleCount,
    waitingCount: connection.waitingCount,
  });
}

// Check PGlite status
if (adapter instanceof PgliteDatabaseAdapter) {
  const connection = await adapter.getConnection();
  console.log("PGlite ready:", !connection.closed);
}

Query Performance

// Query logging is handled by the underlying Drizzle ORM
// Enable with environment variable or drizzle configuration
const adapter = createDatabaseAdapter(
  {
    postgresUrl: url,
  },
  agentId
);

// Monitor query performance in application logs
logger.info("Query executed:", { duration: "Xms", query: "SELECT..." });

Troubleshooting

Common Issues

  1. Vector Extension Missing

    CREATE EXTENSION IF NOT EXISTS vector;

  2. Dimension Mismatch

    Error: Embedding dimension mismatch
Solution: Use consistent dimensions (check VECTOR_DIMS)

  3. Connection Issues

    Error: Connection refused / timeout
Solution: Check connection string and network access

  4. Migration Failures

    # Reset and retry
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
# Run migrations again

Resources

Advanced Topics

Advanced concepts and features for experienced Eliza developers

Deployment

This guide covers various deployment options for Eliza agents, from local Docker containers to cloud platforms. Each method is optimized for different use cases and scale requirements.