---
title: "Schema Management"
description: "Dynamic schema management and migrations in the SQL plugin"
---


The SQL plugin provides a sophisticated dynamic migration system that automatically manages database schemas for plugins. This guide covers how the system works and how to define schemas for your plugins.

## Dynamic Migration System

The SQL plugin uses a **dynamic migration service** that automatically creates and updates database tables based on plugin schemas. This eliminates the need for traditional migration files.

### How It Works

1. **Plugin Registration** - Plugins export their schema definitions
2. **Schema Discovery** - The migration service discovers all plugin schemas at startup
3. **Schema Introspection** - The system analyzes existing database tables
4. **Dynamic Migration** - Tables are created or updated as needed
5. **Dependency Resolution** - Tables are created in the correct order based on foreign key dependencies

### Key Components

```typescript
// DatabaseMigrationService - Manages all plugin migrations
export class DatabaseMigrationService {
  private registeredSchemas = new Map<string, any>();

  discoverAndRegisterPluginSchemas(plugins: Plugin[]): void {
    for (const plugin of plugins) {
      if (plugin.schema) {
        this.registeredSchemas.set(plugin.name, plugin.schema);
      }
    }
  }

  async runAllPluginMigrations(): Promise<void> {
    for (const [pluginName, schema] of this.registeredSchemas) {
      await runPluginMigrations(this.db!, pluginName, schema);
    }
  }
}
```

## Defining Plugin Schemas

To enable automatic schema management, plugins must export their Drizzle schema definitions:

### Plugin Structure

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

// Define your schema
export const myTable = pgTable('my_table', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  metadata: jsonb('metadata').default(sql`'{}'::jsonb`),
  createdAt: timestamp('created_at').defaultNow(),
});

// Export as part of plugin
export const myPlugin: Plugin = {
  name: 'my-plugin',
  schema: {
    myTable,
    // Add other tables here
  },
  // ... other plugin properties
};
```

## Core Schema Tables

The SQL plugin provides these core tables that all agents use:

### Agent Tables
- `agents` - Core agent identity
- `memories` - Agent memory storage
- `entities` - People, objects, and concepts
- `relationships` - Connections between entities

### Communication Tables
- `rooms` - Conversation contexts
- `participants` - Room membership
- `messages` - Message history

### System Tables
- `logs` - System event logging
- `cache` - Key-value cache with composite primary key
- `tasks` - Background task management
- `embeddings` - Vector embeddings for similarity search

## Schema Introspection

The system uses `DrizzleSchemaIntrospector` to analyze database schemas:

```typescript
export class DrizzleSchemaIntrospector {
  parseTableDefinition(table: any, exportKey?: string): TableDefinition {
    const columns = this.parseColumns(table);
    const foreignKeys = this.parseForeignKeys(table);
    const indexes = this.parseIndexes(table);
    const checkConstraints = this.parseCheckConstraints(table);
    const compositePrimaryKey = this.parseCompositePrimaryKey(table);
    
    return {
      name: tableName,
      columns,
      indexes,
      foreignKeys,
      checkConstraints,
      compositePrimaryKey,
      dependencies, // Tables this table depends on
    };
  }
}
```

## Migration Process

The dynamic migrator handles various scenarios:

### Table Creation
```typescript
// Automatically generates CREATE TABLE statements
await createTable(db, tableDefinition);
```

### Column Addition
```typescript
// Detects and adds missing columns
if (!existingColumns.has(column.name)) {
  await addColumn(db, tableName, column);
}
```

### Index Management
```typescript
// Creates missing indexes
for (const index of tableDefinition.indexes) {
  if (!existingIndexes.has(index.name)) {
    await createIndex(db, tableName, index);
  }
}
```

### Foreign Key Constraints
```typescript
// Adds foreign keys after all tables exist
for (const fk of tableDefinition.foreignKeys) {
  await addForeignKey(db, tableName, fk);
}
```

## Best Practices

### 1. Schema Design
- Use UUIDs for primary keys
- Include timestamps (created_at, updated_at)
- Use JSONB for flexible metadata
- Define proper indexes for query performance

### 2. Foreign Keys
- Always reference existing tables
- Consider cascade options carefully
- Be aware of circular dependencies

### 3. Composite Keys
```typescript
// Example: Cache table with composite primary key
export const cacheTable = pgTable('cache', {
  key: text('key').notNull(),
  agentId: uuid('agent_id').notNull(),
  value: jsonb('value').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
}, (table) => {
  return {
    pk: primaryKey(table.key, table.agentId),
  };
});
```

### 4. Plugin Schema Organization
```typescript
// Organize related tables together
export const schema = {
  // Core tables
  users: userTable,
  profiles: profileTable,
  
  // Feature tables
  posts: postTable,
  comments: commentTable,
  
  // Junction tables
  userFollows: userFollowsTable,
};
```

## Error Handling

The migration system includes robust error handling:

- **Duplicate Tables** - Silently skipped
- **Missing Dependencies** - Tables created in dependency order
- **Failed Migrations** - Detailed error logging with rollback
- **Schema Conflicts** - Clear error messages for debugging

## Limitations and Considerations

1. **No Downgrades** - The system only adds, never removes
2. **Column Type Changes** - Not automatically handled
3. **Data Migrations** - Must be handled separately
4. **Production Use** - Test thoroughly before deploying schema changes

## Example: Complete Plugin Schema

```typescript
import { Plugin } from '@elizaos/core';
import { pgTable, uuid, text, timestamp, jsonb, boolean, integer } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';

// Define tables
export const projectTable = pgTable('projects', {
  id: uuid('id').primaryKey().defaultRandom(),
  agentId: uuid('agent_id').notNull(),
  name: text('name').notNull(),
  description: text('description'),
  status: text('status').default('active'),
  metadata: jsonb('metadata').default(sql`'{}'::jsonb`),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

export const taskTable = pgTable('project_tasks', {
  id: uuid('id').primaryKey().defaultRandom(),
  projectId: uuid('project_id').notNull().references(() => projectTable.id),
  title: text('title').notNull(),
  completed: boolean('completed').default(false),
  priority: integer('priority').default(0),
  dueDate: timestamp('due_date'),
  createdAt: timestamp('created_at').defaultNow(),
});

// Create indexes
export const projectIndexes = {
  agentIdIdx: index('project_agent_id_idx').on(projectTable.agentId),
  statusIdx: index('project_status_idx').on(projectTable.status),
};

// Export plugin
export const projectPlugin: Plugin = {
  name: 'project-management',
  schema: {
    projectTable,
    taskTable,
    ...projectIndexes,
  },
  // ... other plugin properties
};
```

This schema will be automatically created when the agent starts, with all tables, columns, indexes, and foreign keys properly configured.