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

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

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:

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

// Automatically generates CREATE TABLE statements
await createTable(db, tableDefinition);

​
Column Addition

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

​
Index Management

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

​
Foreign Key Constraints

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

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

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

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.