SingleStore MCP Server

Instructions

Implementation Reference

• src/index.ts:1237-1308 (registration)

  Registration of the 'create_table' tool in the ListToolsRequestSchema handler, including detailed input schema definition

  {
    name: 'create_table',
    description: 'Create a new table in the database with specified columns and constraints',
    inputSchema: {
      type: 'object',
      properties: {
        table_name: {
          type: 'string',
          description: 'Name of the table to create'
        },
        columns: {
          type: 'array',
          items: {
            type: 'object',
            properties: {
              name: {
                type: 'string',
                description: 'Column name'
              },
              type: {
                type: 'string',
                description: 'Data type (e.g., INT, VARCHAR(255), etc.)'
              },
              nullable: {
                type: 'boolean',
                description: 'Whether the column can be NULL'
              },
              default: {
                type: 'string',
                description: 'Default value for the column'
              },
              auto_increment: {
                type: 'boolean',
                description: 'Whether the column should auto increment'
              }
            },
            required: ['name', 'type']
          },
          description: 'List of columns to create'
        },
        table_options: {
          type: 'object',
          properties: {
            shard_key: {
              type: 'array',
              items: { type: 'string' },
              description: 'Columns to use as shard key'
            },
            sort_key: {
              type: 'array',
              items: { type: 'string' },
              description: 'Columns to use as sort key'
            },
            is_reference: {
              type: 'boolean',
              description: 'Whether this is a reference table'
            },
            compression: {
              type: 'string',
              enum: ['SPARSE'],
              description: 'Table compression type'
            },
            auto_increment_start: {
              type: 'number',
              description: 'Starting value for auto increment columns'
            }
          }
        }
      },
      required: ['table_name', 'columns']
    }
  },

• src/index.ts:1543-1617 (handler)

  The handler function for the 'create_table' tool that validates input arguments, constructs the SingleStore-specific CREATE TABLE SQL statement (including shard keys, sort keys, reference tables, etc.), executes it via mysql2 connection, and returns success message or throws MCP error.

  case 'create_table': {
    if (!request.params.arguments || !request.params.arguments.table_name || !Array.isArray(request.params.arguments.columns)) {
      throw new McpError(
        ErrorCode.InvalidParams,
        'Invalid parameters for create_table'
      );
    }
  
    try {
      // First convert to unknown, then to our expected type
      const args = request.params.arguments as unknown as CreateTableArguments;
      const { table_name, columns, table_options = {} as TableOptions } = args;
      
      // Start building the CREATE TABLE statement
      let sql = `CREATE ${(table_options as TableOptions).is_reference ? 'REFERENCE ' : ''}TABLE ${table_name} (\n`;
      
      // Add columns
      const columnDefs = columns.map(col => {
        let def = `  ${col.name} ${col.type}`;
        if (col.nullable === false) def += ' NOT NULL';
        if (col.default !== undefined) def += ` DEFAULT ${col.default}`;
        if (col.auto_increment) def += ' AUTO_INCREMENT';
        return def;
      });
  
      // Add primary key if auto_increment is used
      const autoIncrementCol = columns.find(col => col.auto_increment);
      if (autoIncrementCol) {
        columnDefs.push(`  PRIMARY KEY (${autoIncrementCol.name})`);
      }
  
      // Add shard key if specified
      if ((table_options as TableOptions).shard_key?.length) {
        columnDefs.push(`  SHARD KEY (${(table_options as TableOptions).shard_key.join(', ')})`);
      }
  
      // Add sort key if specified
      if ((table_options as TableOptions).sort_key?.length) {
        columnDefs.push(`  SORT KEY (${(table_options as TableOptions).sort_key.join(', ')})`);
      }
  
      sql += columnDefs.join(',\n');
      sql += '\n)';
  
      // Add table options
      const tableOptions = [];
      if ((table_options as TableOptions).compression === 'SPARSE') {
        tableOptions.push('COMPRESSION = SPARSE');
      }
      if ((table_options as TableOptions).auto_increment_start) {
        tableOptions.push(`AUTO_INCREMENT = ${(table_options as TableOptions).auto_increment_start}`);
      }
      if (tableOptions.length) {
        sql += ' ' + tableOptions.join(' ');
      }
  
      // Execute the CREATE TABLE statement
      await conn.query(sql);
  
      return {
        content: [
          {
            type: 'text',
            text: `Table ${table_name} created successfully`
          }
        ]
      };
    } catch (error: unknown) {
      const err = error as Error;
      throw new McpError(
        ErrorCode.InternalError,
        `Failed to create table: ${err.message}`
      );
    }
  }

• src/index.ts:64-68 (schema)

  TypeScript interface defining the expected arguments structure for the create_table tool, used for type safety in the handler.

  interface CreateTableArguments {
    table_name: string;
    columns: Column[];
    table_options?: TableOptions;
  }

• src/index.ts:39-45 (helper)

  TypeScript interface for table options used in create_table tool, supporting SingleStore-specific features like shard_key, sort_key, etc.

  interface TableOptions {
    is_reference?: boolean;
    shard_key?: string[];
    sort_key?: string[];
    compression?: string;
    auto_increment_start?: number;
  }

• src/index.ts:56-62 (helper)

  TypeScript interface defining column structure used in create_table tool arguments.

  interface Column {
    name: string;
    type: string;
    nullable?: boolean;
    default?: string | number | boolean;
    auto_increment?: boolean;
  }