The @elizaos/plugin-sql provides comprehensive database management for elizaOS agents, featuring automatic schema migrations, multi-database support, and a sophisticated plugin architecture.

​
Key Features

​
🗄️ Dual Database Support

  • PGLite - Embedded PostgreSQL for development
  • PostgreSQL - Full PostgreSQL for production
  • Automatic adapter selection based on environment

​
🔄 Dynamic Migration System

  • Automatic schema discovery from plugins
  • Intelligent table creation and updates
  • Dependency resolution for foreign keys
  • No manual migration files needed

​
🏗️ Schema Introspection

  • Analyzes existing database structure
  • Detects missing columns and indexes
  • Handles composite primary keys
  • Preserves existing data

​
🔌 Plugin Integration

  • Plugins can define their own schemas
  • Automatic table creation at startup
  • Isolated namespaces prevent conflicts
  • Shared core tables for common data

​
Architecture Overview

The plugin consists of several key components:

​
Core Components

​
1. Database Adapters

  • BaseDrizzleAdapter - Shared functionality
  • PgliteDatabaseAdapter - Development database
  • PgDatabaseAdapter - Production database

​
2. Migration Service

  • DatabaseMigrationService - Orchestrates migrations
  • DrizzleSchemaIntrospector - Analyzes schemas
  • Custom Migrator - Executes schema changes

​
3. Connection Management

  • PGliteClientManager - Singleton PGLite instance
  • PostgresConnectionManager - Connection pooling
  • Global Singletons - Prevents multiple connections

​
4. Schema Definitions

Core tables for agent functionality:
  • agents - Agent identities
  • memories - Knowledge storage
  • entities - People and objects
  • relationships - Entity connections
  • messages - Communication history
  • embeddings - Vector search
  • cache - Key-value storage
  • logs - System events

​
Installation

elizaos plugins add @elizaos/plugin-sql

​
Configuration

The SQL plugin is automatically included by the elizaOS runtime and configured via environment variables.

​
Environment Setup

Create a .env file in your project root: 
# For PostgreSQL (production)
POSTGRES_URL=postgresql://user:password@host:5432/database

# For custom PGLite directory (development)
# Optional - defaults to ./.eliza/.elizadb if not set
PGLITE_DATA_DIR=/path/to/custom/db

​
Adapter Selection

The plugin automatically chooses the appropriate adapter:
  • With POSTGRES_URL → PostgreSQL adapter (production)
  • Without POSTGRES_URL → PGLite adapter (development)
No code changes needed - just set your environment variables.

​
Custom Plugin with Schema

import { Plugin } from '@elizaos/core';
import { pgTable, uuid, text, timestamp } from 'drizzle-orm/pg-core';

// Define your schema
const customTable = pgTable('custom_data', {
  id: uuid('id').primaryKey().defaultRandom(),
  agentId: uuid('agent_id').notNull(),
  data: text('data').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

// Create plugin
export const customPlugin: Plugin = {
  name: 'custom-plugin',
  schema: {
    customTable,
  },
  // Plugin will have access to database via runtime
};

​
How It Works

​
1. Initialization

When the agent starts:
  1. SQL plugin initializes the appropriate database adapter
  2. Migration service discovers all plugin schemas
  3. Schemas are analyzed and dependencies resolved
  4. Tables are created or updated as needed

​
2. Schema Discovery

// Plugins export their schemas
export const myPlugin: Plugin = {
  name: 'my-plugin',
  schema: { /* Drizzle tables */ },
};

// Migration service finds and registers them
discoverAndRegisterPluginSchemas(plugins);

​
3. Dynamic Migration

The system:
  • Introspects existing database structure
  • Compares with plugin schema definitions
  • Generates and executes necessary DDL
  • Handles errors gracefully

​
4. Runtime Access

Plugins access the database through the runtime: 
const adapter = runtime.databaseAdapter;
await adapter.getMemories({ agentId });

​
Advanced Features

​
Composite Primary Keys

const cacheTable = pgTable('cache', {
  key: text('key').notNull(),
  agentId: uuid('agent_id').notNull(),
  value: jsonb('value'),
}, (table) => ({
  pk: primaryKey(table.key, table.agentId),
}));

​
Foreign Key Dependencies

Tables with foreign keys are automatically created in the correct order.

​
Schema Introspection

The system can analyze and adapt to existing database structures.

​
Error Recovery

  • Automatic retries with exponential backoff
  • Detailed error logging
  • Graceful degradation

​
Best Practices

  1. Define Clear Schemas - Use TypeScript for type safety
  2. Use UUIDs - For distributed compatibility
  3. Include Timestamps - Track data changes
  4. Index Strategically - For query performance
  5. Test Migrations - Verify schema changes locally

​
Limitations

  • No automatic downgrades or rollbacks
  • Column type changes require manual intervention
  • Data migrations must be handled separately
  • Schema changes should be tested thoroughly

​
Next Steps