Database Schema

Learn how to add custom database schemas to elizaOS plugins for shared data access

Overview

elizaOS uses Drizzle ORM with PostgreSQL and automatically handles migrations from your schema definitions. This guide demonstrates how to add custom tables that can be shared across all agents (no agentId field), along with actions to write data and providers to read it.

Database Adapter Interface

Plugins can provide database adapters for custom storage backends. The IDatabaseAdapter interface is extensive, including methods for:

• Agents, Entities, Components
• Memories (with embeddings)
• Rooms, Participants
• Relationships
• Tasks
• Caching
• Logs

Example database adapter plugin:

export const plugin: Plugin = {
  name: '@elizaos/plugin-sql',
  description: 'A plugin for SQL database access with dynamic schema migrations',
  priority: 0,
  schema,
  init: async (_, runtime: IAgentRuntime) => {
    const dbAdapter = createDatabaseAdapter(config, runtime.agentId);
    runtime.registerDatabaseAdapter(dbAdapter);
  }
};

Step 1: Define Your Custom Schema

Creating a Shared Table

To create a table that’s accessible by all agents, define it without an agentId field. Here’s an example of a user preferences table:

// In your plugin's schema.ts file

import { pgTable, uuid, varchar, text, timestamp, jsonb, index } from 'drizzle-orm/pg-core';

export const userPreferencesTable = pgTable(
  'user_preferences',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id').notNull(), // Links to the user
    preferences: jsonb('preferences').default({}).notNull(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().notNull(),
  },
  (table) => [
    index('idx_user_preferences_user_id').on(table.userId),
  ]
);

// Export your schema
export const customSchema = {
  userPreferencesTable,
};

Key Points:

• No agentId field means data is shared across all agents
• elizaOS will automatically create migrations from this schema
• Use appropriate indexes for query performance

Creating Agent-Specific Tables

For data that should be scoped to individual agents:

export const agentDataTable = pgTable(
  'agent_data',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    agentId: uuid('agent_id').notNull(), // Scopes to specific agent
    key: varchar('key', { length: 255 }).notNull(),
    value: jsonb('value').notNull(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
  },
  (table) => [
    index('idx_agent_data_agent_key').on(table.agentId, table.key),
  ]
);

Step 2: Create a Repository for Database Access

Repository Pattern

Create a repository class to handle database operations. This follows the pattern used throughout elizaOS:

// In your plugin's repositories/user-preferences-repository.ts

import { eq } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/node-postgres';
import { UUID } from '@elizaos/core';
import { userPreferencesTable } from '../schema.ts';

export interface UserPreferences {
  id: UUID;
  userId: UUID;
  preferences: Record<string, any>;
  createdAt: Date;
  updatedAt: Date;
}

export class UserPreferencesRepository {
  constructor(private readonly db: ReturnType<typeof drizzle>) {}

  /**
   * Create or update user preferences
   */
  async upsert(userId: UUID, preferences: Record<string, any>): Promise<UserPreferences> {
    // Check if preferences exist
    const existing = await this.findByUserId(userId);
    
    if (existing) {
      // Update existing
      const [updated] = await this.db
        .update(userPreferencesTable)
        .set({
          preferences,
          updatedAt: new Date(),
        })
        .where(eq(userPreferencesTable.userId, userId))
        .returning();
      
      return this.mapToUserPreferences(updated);
    } else {
      // Create new
      const [created] = await this.db
        .insert(userPreferencesTable)
        .values({
          userId,
          preferences,
          createdAt: new Date(),
          updatedAt: new Date(),
        })
        .returning();
      
      return this.mapToUserPreferences(created);
    }
  }

  /**
   * Find preferences by user ID
   */
  async findByUserId(userId: UUID): Promise<UserPreferences | null> {
    const result = await this.db
      .select()
      .from(userPreferencesTable)
      .where(eq(userPreferencesTable.userId, userId))
      .limit(1);

    return result.length > 0 ? this.mapToUserPreferences(result[0]) : null;
  }

  /**
   * Delete preferences by user ID
   */
  async deleteByUserId(userId: UUID): Promise<boolean> {
    const result = await this.db
      .delete(userPreferencesTable)
      .where(eq(userPreferencesTable.userId, userId))
      .returning();

    return result.length > 0;
  }

  /**
   * Find all preferences (with pagination)
   */
  async findAll(offset = 0, limit = 100): Promise<UserPreferences[]> {
    const results = await this.db
      .select()
      .from(userPreferencesTable)
      .offset(offset)
      .limit(limit);

    return results.map(this.mapToUserPreferences);
  }

  /**
   * Map database row to domain type
   */
  private mapToUserPreferences(row: any): UserPreferences {
    return {
      id: row.id as UUID,
      userId: row.userId || row.user_id,
      preferences: row.preferences || {},
      createdAt: row.createdAt || row.created_at,
      updatedAt: row.updatedAt || row.updated_at,
    };
  }
}

Advanced Repository Patterns

Transactions

export class TransactionalRepository {
  async transferPoints(fromUserId: UUID, toUserId: UUID, points: number): Promise<void> {
    await this.db.transaction(async (tx) => {
      // Deduct from sender
      await tx
        .update(userPointsTable)
        .set({ 
          points: sql`${userPointsTable.points} - ${points}`,
          updatedAt: new Date()
        })
        .where(eq(userPointsTable.userId, fromUserId));

      // Add to receiver
      await tx
        .update(userPointsTable)
        .set({ 
          points: sql`${userPointsTable.points} + ${points}`,
          updatedAt: new Date()
        })
        .where(eq(userPointsTable.userId, toUserId));

      // Log transaction
      await tx.insert(transactionLogTable).values({
        fromUserId,
        toUserId,
        amount: points,
        createdAt: new Date()
      });
    });
  }
}

Complex Queries

export class AnalyticsRepository {
  async getUserActivityStats(userId: UUID, days = 30): Promise<ActivityStats> {
    const startDate = new Date();
    startDate.setDate(startDate.getDate() - days);

    const stats = await this.db
      .select({
        totalActions: count(userActionsTable.id),
        uniqueDays: countDistinct(
          sql`DATE(${userActionsTable.createdAt})`
        ),
        mostCommonAction: sql`
          MODE() WITHIN GROUP (ORDER BY ${userActionsTable.actionType})
        `,
      })
      .from(userActionsTable)
      .where(
        and(
          eq(userActionsTable.userId, userId),
          gte(userActionsTable.createdAt, startDate)
        )
      )
      .groupBy(userActionsTable.userId);

    return stats[0] || { totalActions: 0, uniqueDays: 0, mostCommonAction: null };
  }
}

Step 3: Create an Action to Write Data

Action Structure

Actions process user input and store data using the repository:

import type { Action, IAgentRuntime, Memory, ActionResult } from '@elizaos/core';
import { parseKeyValueXml } from '@elizaos/core';
import { UserPreferencesRepository } from '../repositories/user-preferences-repository.ts';

export const storeUserPreferencesAction: Action = {
  name: 'STORE_USER_PREFERENCES',
  description: 'Extract and store user preferences from messages',
  
  validate: async (runtime: IAgentRuntime, message: Memory) => {
    const text = message.content.text?.toLowerCase() || '';
    return text.includes('preference') || text.includes('prefer') || text.includes('like');
  },

  handler: async (runtime: IAgentRuntime, message: Memory) => {
    // 1. Create prompt for LLM to extract structured data
    const extractionPrompt = `
      Extract user preferences from the following message.
      Return in XML format:
      
      <preferences>
        <theme>light/dark/auto</theme>
        <language>en/es/fr/etc</language>
        <notifications>true/false</notifications>
        <customPreference>value</customPreference>
      </preferences>
      
      Message: "${message.content.text}"
    `;

    // 2. Use runtime's LLM
    const llmResponse = await runtime.completion({
      messages: [{ role: 'system', content: extractionPrompt }]
    });

    // 3. Parse the response
    const extractedPreferences = parseKeyValueXml(llmResponse.content);

    // 4. Get database and repository
    const db = runtime.databaseAdapter.db;
    const repository = new UserPreferencesRepository(db);
    
    // 5. Store preferences
    const userId = message.userId || message.entityId;
    const stored = await repository.upsert(userId, extractedPreferences);

    return {
      success: true,
      data: stored,
      text: 'Your preferences have been saved successfully.'
    };
  }
};

Batch Operations Action

export const batchImportAction: Action = {
  name: 'BATCH_IMPORT',
  description: 'Import multiple records at once',
  
  handler: async (runtime, message) => {
    const db = runtime.databaseAdapter.db;
    const repository = new DataRepository(db);
    
    // Parse batch data from message
    const records = JSON.parse(message.content.text);
    
    // Use batch insert for performance
    const results = await db
      .insert(dataTable)
      .values(records.map(r => ({
        ...r,
        createdAt: new Date(),
        updatedAt: new Date()
      })))
      .returning();
    
    return {
      success: true,
      text: `Imported ${results.length} records successfully`,
      data: { importedCount: results.length }
    };
  }
};

Step 4: Create a Provider to Read Data

Provider Structure

Providers make data available to agents during conversations:

import type { Provider, IAgentRuntime, Memory } from '@elizaos/core';
import { UserPreferencesRepository } from '../repositories/user-preferences-repository.ts';

export const userPreferencesProvider: Provider = {
  name: 'USER_PREFERENCES',
  description: 'Provides user preferences to customize agent behavior',
  dynamic: true, // Fetches fresh data on each request
  
  get: async (runtime: IAgentRuntime, message: Memory) => {
    // 1. Get user ID from message
    const userId = message.userId || message.entityId;
    
    // 2. Get database and repository
    const db = runtime.databaseAdapter.db;
    const repository = new UserPreferencesRepository(db);
    
    // 3. Fetch preferences
    const userPrefs = await repository.findByUserId(userId);
    
    if (!userPrefs) {
      return {
        data: { preferences: {} },
        values: { preferences: 'No preferences found' },
        text: ''
      };
    }
    
    // 4. Format data for agent context
    const preferencesText = `
# User Preferences
${Object.entries(userPrefs.preferences).map(([key, value]) => 
  `- ${key}: ${value}`
).join('\n')}
    `.trim();
    
    return {
      data: { preferences: userPrefs.preferences },
      values: userPrefs.preferences,
      text: preferencesText // This text is added to agent context
    };
  }
};

Caching Provider

export const cachedDataProvider: Provider = {
  name: 'CACHED_DATA',
  private: true,
  
  get: async (runtime, message) => {
    const cacheKey = `data_${message.roomId}`;
    const cached = runtime.cacheManager.get(cacheKey);
    
    if (cached && Date.now() - cached.timestamp < 60000) { // 1 minute cache
      return cached.data;
    }
    
    // Fetch fresh data
    const db = runtime.databaseAdapter.db;
    const repository = new DataRepository(db);
    const freshData = await repository.getRoomData(message.roomId);
    
    const result = {
      text: formatData(freshData),
      data: freshData,
      values: { roomData: freshData }
    };
    
    // Cache the result
    runtime.cacheManager.set(cacheKey, {
      data: result,
      timestamp: Date.now()
    });
    
    return result;
  }
};

Step 5: Register Your Components

Plugin Configuration

Register your schema, actions, and providers in your plugin:

import type { Plugin } from '@elizaos/core';

export const myPlugin: Plugin = {
  name: 'my-plugin',
  description: 'My custom plugin',
  actions: [storeUserPreferencesAction],
  providers: [userPreferencesProvider],
  schema: customSchema, // Your schema export
};

Important Considerations

1. Database Access Pattern

• Always access the database through runtime.databaseAdapter.db
• Use repository classes to encapsulate database operations
• The database type is already properly typed from the runtime adapter

2. Shared Data Pattern

Without agentId in your tables:

• All agents can read and write the same data
• Use userId or other identifiers to scope data appropriately
• Consider data consistency across multiple agents

3. Type Safety

• Define interfaces for your domain types
• Map database rows to domain types in repository methods
• Handle both camelCase and snake_case field names

4. Error Handling

try {
  const result = await repository.upsert(userId, preferences);
  return { success: true, data: result };
} catch (error) {
  console.error('Failed to store preferences:', error);
  return { 
    success: false, 
    error: error instanceof Error ? error.message : 'Unknown error' 
  };
}

5. Migration Strategy

// Schema versioning
export const schemaVersion = 2;

export const migrations = {
  1: async (db) => {
    // Initial schema
  },
  2: async (db) => {
    // Add new column
    await db.schema.alterTable('user_preferences', (table) => {
      table.addColumn('version', 'integer').defaultTo(1);
    });
  }
};

Example Flow

1. User sends message: “I prefer dark theme and Spanish language”
2. Action triggered:
   • LLM extracts: { theme: 'dark', language: 'es' }
   • Repository stores in database
3. Provider supplies data:
   • On next interaction, provider fetches preferences
   • Agent context includes: “User Preferences: theme: dark, language: es”
4. Multiple agents: Any agent can access this user’s preferences

Advanced Patterns

Embeddings and Vector Search

export const documentTable = pgTable('documents', {
  id: uuid('id').primaryKey().defaultRandom(),
  content: text('content').notNull(),
  embedding: vector('embedding', { dimensions: 1536 }),
  metadata: jsonb('metadata').default({})
});

export class DocumentRepository {
  async searchSimilar(embedding: number[], limit = 10): Promise<Document[]> {
    return await this.db
      .select()
      .from(documentTable)
      .orderBy(
        sql`${documentTable.embedding} <-> ${embedding}`
      )
      .limit(limit);
  }
}

Time-Series Data

export const metricsTable = pgTable('metrics', {
  id: uuid('id').primaryKey().defaultRandom(),
  metric: varchar('metric', { length: 255 }).notNull(),
  value: real('value').notNull(),
  timestamp: timestamp('timestamp').defaultNow().notNull(),
  tags: jsonb('tags').default({})
});

export class MetricsRepository {
  async getTimeSeries(metric: string, hours = 24): Promise<TimeSeries> {
    const since = new Date(Date.now() - hours * 60 * 60 * 1000);
    
    return await this.db
      .select({
        time: metricsTable.timestamp,
        value: avg(metricsTable.value),
      })
      .from(metricsTable)
      .where(
        and(
          eq(metricsTable.metric, metric),
          gte(metricsTable.timestamp, since)
        )
      )
      .groupBy(
        sql`DATE_TRUNC('hour', ${metricsTable.timestamp})`
      )
      .orderBy(metricsTable.timestamp);
  }
}

Summary

To add custom schema to an elizaOS plugin:

1. Define schema without agentId for shared data
2. Create repository classes following elizaOS’s pattern
3. Create actions to write data using parseKeyValueXml for structure
4. Create providers to read data and supply to agent context
5. Register everything in your plugin configuration

elizaOS handles the rest - migrations, database connections, and making your data available across all agents in the system.

See Also

Plugin Components

Learn about Actions, Providers, Evaluators, and Services

Development Guide

Build your first plugin step by step

Common Patterns

Learn proven plugin development patterns

Plugin Reference

Complete API reference for all interfaces