SQL MCP Server

Instructions

Implementation Reference

• src/tools.ts:62-121 (handler)

  The async handler function that implements the get_columns tool. It connects to the database using Sequelize, queries the information_schema.columns (or equivalent for SQLite) based on db_type, maps the results to column objects, and returns them as JSON text content.

  async ({db_type, connection_string, table_name}) => {
    const sequelize = new Sequelize(connection_string, {
      dialect: db_type,
    })
  
    let result: any[] = []
    let columns: {
      name: string,
      type: string,
      nullable: string,
      default: string,
      key: string,
      extra: string,
    }[] = []
  
    switch (db_type) {
      case 'postgres':
        [result] = await sequelize.query(`SELECT * FROM information_schema.columns WHERE table_name = '${table_name}'`)
        columns = result.map((column: any) => ({
          name: column.column_name,
          type: column.data_type,
          nullable: column.is_nullable,
          default: column.column_default,
          key: column.column_key,
          extra: column.extra,
        }))
        break
      case 'mysql':
        [result] = await sequelize.query(`SELECT * FROM information_schema.columns WHERE table_name = '${table_name}'`)
        columns = result.map((column: any) => ({
          name: column.column_name,
          type: column.data_type,
          nullable: column.is_nullable,
          default: column.column_default,
          key: column.column_key,
          extra: column.extra,
        }))
        break
      case 'sqlite':
        [result] = await sequelize.query(`SELECT * FROM sqlite_master WHERE type = "table" AND name = '${table_name}'`)
        columns = result.map((column: any) => ({
          name: column.name,
          type: column.type,
          nullable: column.nullable,
          default: column.default,
          key: column.key,
          extra: column.extra,
        }))
        break
    }
  
    return {
      content: [
        {
          type: 'text',
          text: JSON.stringify(columns),
        }
      ],
    }
  },

• src/tools.ts:53-61 (schema)

  Input schema for the get_columns tool using Zod for validation of db_type (enum), connection_string, and table_name.

  {
    title: 'Get Columns',
    description: 'Get list of column in a table',
    inputSchema: {
      db_type: z.enum(['postgres', 'mysql', 'sqlite']),
      connection_string: z.string(),
      table_name: z.string(),
    },  
  },

• src/tools.ts:51-122 (registration)

  The server.registerTool call that registers the get_columns tool, specifying its name, input schema, and handler function.

  server.registerTool(
    'get_columns',
    {
      title: 'Get Columns',
      description: 'Get list of column in a table',
      inputSchema: {
        db_type: z.enum(['postgres', 'mysql', 'sqlite']),
        connection_string: z.string(),
        table_name: z.string(),
      },  
    },
    async ({db_type, connection_string, table_name}) => {
      const sequelize = new Sequelize(connection_string, {
        dialect: db_type,
      })
  
      let result: any[] = []
      let columns: {
        name: string,
        type: string,
        nullable: string,
        default: string,
        key: string,
        extra: string,
      }[] = []
  
      switch (db_type) {
        case 'postgres':
          [result] = await sequelize.query(`SELECT * FROM information_schema.columns WHERE table_name = '${table_name}'`)
          columns = result.map((column: any) => ({
            name: column.column_name,
            type: column.data_type,
            nullable: column.is_nullable,
            default: column.column_default,
            key: column.column_key,
            extra: column.extra,
          }))
          break
        case 'mysql':
          [result] = await sequelize.query(`SELECT * FROM information_schema.columns WHERE table_name = '${table_name}'`)
          columns = result.map((column: any) => ({
            name: column.column_name,
            type: column.data_type,
            nullable: column.is_nullable,
            default: column.column_default,
            key: column.column_key,
            extra: column.extra,
          }))
          break
        case 'sqlite':
          [result] = await sequelize.query(`SELECT * FROM sqlite_master WHERE type = "table" AND name = '${table_name}'`)
          columns = result.map((column: any) => ({
            name: column.name,
            type: column.type,
            nullable: column.nullable,
            default: column.default,
            key: column.key,
            extra: column.extra,
          }))
          break
      }
  
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify(columns),
          }
        ],
      }
    },
  )