db_query

Instructions

Implementation Reference

• src/tools/db-query.ts:66-145 (handler)

  The async callback function that executes the db_query tool logic: validates SQL (only SELECT/EXPLAIN/WITH allowed), enforces LIMIT, queries SQLite (in-memory demo or file-based), and returns results as JSON.

    async ({ sql, mode, dbPath, limit }) => {
      // --- Validate SQL safety ---
      const normalised = sql.trim().toUpperCase();
      const allowedPrefixes = ["SELECT", "EXPLAIN", "WITH"];
      const startsAllowed = allowedPrefixes.some((p) =>
        normalised.startsWith(p)
      );
  
      if (!startsAllowed) {
        return {
          content: [
            {
              type: "text" as const,
              text: `Error: Only SELECT / EXPLAIN queries are allowed. Your statement starts with: ${normalised.slice(0, 20)}`,
            },
          ],
          isError: true,
        };
      }
  
      // Ensure a LIMIT clause to avoid massive result sets
      if (!/\bLIMIT\b/i.test(sql)) {
        sql += ` LIMIT ${limit}`;
      }
  
      try {
        let database: Database.Database;
  
        if (mode === "explore") {
          database = getDb(":memory:");
          seedDemoDatabase(database);
        } else {
          if (!dbPath) {
            return {
              content: [
                {
                  type: "text" as const,
                  text: "Error: 'dbPath' is required when mode is 'file'.",
                },
              ],
              isError: true,
            };
          }
          database = getDb(dbPath);
        }
  
        const stmt = database.prepare(sql);
        const rows = stmt.all() as Record<string, unknown>[];
        const columns =
          rows.length > 0 ? Object.keys(rows[0]) : [];
  
        return {
          content: [
            {
              type: "text" as const,
              text: JSON.stringify(
                {
                  rowCount: rows.length,
                  columns,
                  rows,
                },
                null,
                2
              ),
            },
          ],
        };
      } catch (err: any) {
        return {
          content: [
            {
              type: "text" as const,
              text: `SQL Error: ${err.message}`,
            },
          ],
          isError: true,
        };
      }
    }
  );

• src/tools/db-query.ts:44-65 (schema)

  Zod schema defining input parameters: sql (string), mode (enum 'explore'|'file' with default 'explore'), dbPath (optional string), limit (number 1-1000, default 100).

  {
    sql: z.string().describe("The SQL SELECT statement to execute."),
    mode: z
      .enum(["explore", "file"])
      .default("explore")
      .describe(
        'Use "explore" for the built-in demo DB, or "file" to query a specific SQLite file.'
      ),
    dbPath: z
      .string()
      .optional()
      .describe(
        "Path to a .db/.sqlite file (required when mode is 'file')."
      ),
    limit: z
      .number()
      .int()
      .min(1)
      .max(1000)
      .default(100)
      .describe("Maximum number of rows to return (1–1000)."),
  },

• src/tools/db-query.ts:25-146 (registration)

  The registerDbQueryTool function that registers 'db_query' on the McpServer via server.tool(), with description, input schema, and handler.

  export function registerDbQueryTool(server: McpServer): void {
    server.tool(
      "db_query",
      `Execute a SQL query against a SQLite database and return the results.
  
  Modes:
    - "explore" (default): Uses a built-in in-memory demo database pre-loaded
      with sample tables (users, products, orders).  Great for quick testing.
    - "file": Queries a user-specified SQLite file on disk.
  
  Security:
    - In "explore" mode only SELECT statements are allowed.
    - In "file" mode only SELECT, EXPLAIN, and WITH ... SELECT are allowed.
    - DML/DDL (INSERT, UPDATE, DELETE, DROP, etc.) will be rejected.
  
  Returns:
    - rows: array of objects
    - rowCount: number of rows returned
    - columns: list of column names`,
      {
        sql: z.string().describe("The SQL SELECT statement to execute."),
        mode: z
          .enum(["explore", "file"])
          .default("explore")
          .describe(
            'Use "explore" for the built-in demo DB, or "file" to query a specific SQLite file.'
          ),
        dbPath: z
          .string()
          .optional()
          .describe(
            "Path to a .db/.sqlite file (required when mode is 'file')."
          ),
        limit: z
          .number()
          .int()
          .min(1)
          .max(1000)
          .default(100)
          .describe("Maximum number of rows to return (1–1000)."),
      },
      async ({ sql, mode, dbPath, limit }) => {
        // --- Validate SQL safety ---
        const normalised = sql.trim().toUpperCase();
        const allowedPrefixes = ["SELECT", "EXPLAIN", "WITH"];
        const startsAllowed = allowedPrefixes.some((p) =>
          normalised.startsWith(p)
        );
  
        if (!startsAllowed) {
          return {
            content: [
              {
                type: "text" as const,
                text: `Error: Only SELECT / EXPLAIN queries are allowed. Your statement starts with: ${normalised.slice(0, 20)}`,
              },
            ],
            isError: true,
          };
        }
  
        // Ensure a LIMIT clause to avoid massive result sets
        if (!/\bLIMIT\b/i.test(sql)) {
          sql += ` LIMIT ${limit}`;
        }
  
        try {
          let database: Database.Database;
  
          if (mode === "explore") {
            database = getDb(":memory:");
            seedDemoDatabase(database);
          } else {
            if (!dbPath) {
              return {
                content: [
                  {
                    type: "text" as const,
                    text: "Error: 'dbPath' is required when mode is 'file'.",
                  },
                ],
                isError: true,
              };
            }
            database = getDb(dbPath);
          }
  
          const stmt = database.prepare(sql);
          const rows = stmt.all() as Record<string, unknown>[];
          const columns =
            rows.length > 0 ? Object.keys(rows[0]) : [];
  
          return {
            content: [
              {
                type: "text" as const,
                text: JSON.stringify(
                  {
                    rowCount: rows.length,
                    columns,
                    rows,
                  },
                  null,
                  2
                ),
              },
            ],
          };
        } catch (err: any) {
          return {
            content: [
              {
                type: "text" as const,
                text: `SQL Error: ${err.message}`,
              },
            ],
            isError: true,
          };
        }
      }
    );
  }

• src/index.ts:40-41 (registration)

  The call to registerDbQueryTool(this.server) within the McpToolkitServer class that wires the tool into the MCP server.

  // --- Database Tool ---
  registerDbQueryTool(this.server);

• src/tools/db-query.ts:152-217 (helper)

  seedDemoDatabase helper function that populates an in-memory SQLite database with sample users, products, and orders tables for the 'explore' mode.

  let seeded = false;
  
  function seedDemoDatabase(database: Database.Database): void {
    if (seeded) return;
    seeded = true;
  
    database.exec(`
      CREATE TABLE IF NOT EXISTS users (
        id      INTEGER PRIMARY KEY AUTOINCREMENT,
        name    TEXT NOT NULL,
        email   TEXT UNIQUE NOT NULL,
        role    TEXT DEFAULT 'user',
        created TEXT DEFAULT (datetime('now'))
      );
  
      CREATE TABLE IF NOT EXISTS products (
        id        INTEGER PRIMARY KEY AUTOINCREMENT,
        name      TEXT NOT NULL,
        category  TEXT NOT NULL,
        price     REAL NOT NULL,
        stock     INTEGER DEFAULT 0
      );
  
      CREATE TABLE IF NOT EXISTS orders (
        id         INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id    INTEGER REFERENCES users(id),
        product_id INTEGER REFERENCES products(id),
        quantity   INTEGER NOT NULL,
        total      REAL NOT NULL,
        status     TEXT DEFAULT 'pending',
        ordered_at TEXT DEFAULT (datetime('now'))
      );
  
      -- Seed users
      INSERT OR IGNORE INTO users (name, email, role) VALUES
        ('Alice Johnson',   'alice@example.com',    'admin'),
        ('Bob Smith',       'bob@example.com',      'user'),
        ('Charlie Davis',   'charlie@example.com',  'user'),
        ('Diana Prince',    'diana@example.com',    'editor'),
        ('Eve Williams',    'eve@example.com',      'user');
  
      -- Seed products
      INSERT OR IGNORE INTO products (name, category, price, stock) VALUES
        ('Mechanical Keyboard', 'Electronics',  149.99, 45),
        ('Wireless Mouse',     'Electronics',   59.99, 120),
        ('USB-C Hub',           'Electronics',   39.99, 200),
        ('Standing Desk Mat',   'Office',        29.99, 350),
        ('Ergonomic Chair',     'Furniture',    499.99, 15),
        ('Monitor Arm',         'Office',        79.99, 80),
        ('Webcam 4K',           'Electronics',  129.99, 60),
        ('Desk Lamp',           'Office',        49.99, 90);
  
      -- Seed orders
      INSERT OR IGNORE INTO orders (user_id, product_id, quantity, total, status) VALUES
        (1, 1, 1, 149.99, 'delivered'),
        (1, 2, 2, 119.98, 'delivered'),
        (2, 3, 1, 39.99,  'shipped'),
        (2, 5, 1, 499.99, 'pending'),
        (3, 4, 3, 89.97,  'delivered'),
        (3, 7, 1, 129.99, 'shipped'),
        (4, 6, 2, 159.98, 'delivered'),
        (4, 8, 1, 49.99,  'pending'),
        (5, 1, 1, 149.99, 'shipped'),
        (5, 2, 1, 59.99,  'delivered');
    `);
  }