---
title: "Database Schema"
description: "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:

```typescript
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:

```typescript
// 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:

```typescript
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:

```typescript
// 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, unknown>;
  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, unknown>,
  ): 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: Record<string, unknown>): 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

```typescript
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

```typescript
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:

```typescript
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

```typescript
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:

```typescript
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

```typescript
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:

```typescript
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

```typescript
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

```typescript
// 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

```typescript
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

```typescript
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

<CardGroup cols={2}>
  <Card title="Plugin Components" icon="cube" href="/plugins/components">
    Learn about Actions, Providers, Evaluators, and Services
  </Card>

<Card title="Development Guide" icon="code" href="/plugins/development">
  Build your first plugin step by step
</Card>

<Card title="Common Patterns" icon="lightbulb" href="/plugins/patterns">
  Learn proven plugin development patterns
</Card>

  <Card title="Plugin Reference" icon="book" href="/plugins/reference">
    Complete API reference for all interfaces
  </Card>
</CardGroup>