create_table
Define and create a new database table with specific columns, constraints, and table options in SingleStore using the MCP Server for structured data management.
Instructions
Create a new table in the database with specified columns and constraints
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| columns | Yes | List of columns to create | |
| table_name | Yes | Name of the table to create | |
| table_options | No |
Input Schema (JSON Schema)
{
"properties": {
"columns": {
"description": "List of columns to create",
"items": {
"properties": {
"auto_increment": {
"description": "Whether the column should auto increment",
"type": "boolean"
},
"default": {
"description": "Default value for the column",
"type": "string"
},
"name": {
"description": "Column name",
"type": "string"
},
"nullable": {
"description": "Whether the column can be NULL",
"type": "boolean"
},
"type": {
"description": "Data type (e.g., INT, VARCHAR(255), etc.)",
"type": "string"
}
},
"required": [
"name",
"type"
],
"type": "object"
},
"type": "array"
},
"table_name": {
"description": "Name of the table to create",
"type": "string"
},
"table_options": {
"properties": {
"auto_increment_start": {
"description": "Starting value for auto increment columns",
"type": "number"
},
"compression": {
"description": "Table compression type",
"enum": [
"SPARSE"
],
"type": "string"
},
"is_reference": {
"description": "Whether this is a reference table",
"type": "boolean"
},
"shard_key": {
"description": "Columns to use as shard key",
"items": {
"type": "string"
},
"type": "array"
},
"sort_key": {
"description": "Columns to use as sort key",
"items": {
"type": "string"
},
"type": "array"
}
},
"type": "object"
}
},
"required": [
"table_name",
"columns"
],
"type": "object"
}
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; }