io.github.daedalus/mcp-sqlite3

Get the SQLite library version. Returns: The SQLite library version string. Example: >>> get_sqlite_version() "3.44.0"

Get the sqlite3 module version. Returns: The sqlite3 module version string. Example: >>> get_sqlite3_version() "3.44.0"

Check if a SQL statement is syntactically complete. Args: sql: The SQL statement to check. Returns: True if the statement is complete, False otherwise. Example: >>> complete_sql_statement("SELECT * FROM users;") True >>> complete_sql_statement("SELECT * FROM") False

Open a database connection. Args: database: Path to the database file or ':memory:' for in-memory database. timeout: Connection timeout in seconds. detect_types: Parse types for columns (PARSE_DECLTYPES, PARSE_COLNAMES). isolation_level: Transaction isolation level or None for autocommit. check_same_thread: Ensure thread safety. cached_statements: Number of cached statements. uri: Interpret database as URI. Returns: Connection result with conn_id or error. Example: >>> connect_database(":memory:") {"success": true, "conn_id": "abc-123"} >>> connect_database("/path/to/db.sqlite") {"success": true, "conn_id": "def-456"}

Close a database connection. Args: conn_id: The connection ID to close. Returns: Success status. Example: >>> close_connection("abc-123") {"success": true}

Commit any pending transaction to the database. Args: conn_id: The connection ID. Returns: Success status. Example: >>> commit("abc-123") {"success": true}

Rollback any pending transaction. Args: conn_id: The connection ID. Returns: Success status. Example: >>> rollback("abc-123") {"success": true}

Execute a SQL query and return results. Args: conn_id: The connection ID. sql: The SQL query to execute. params: Query parameters for parameterized queries. Returns: Query results with rows, columns, and metadata. Example: >>> execute_query("abc-123", "SELECT * FROM users WHERE id = ?", [1]) {"success": true, "data": [{"id": 1, "name": "John"}], "columns": ["id", "name"]}

Execute a SQL statement with multiple parameter sets. Args: conn_id: The connection ID. sql: The SQL statement to execute. params_list: List of parameter sets. Returns: Execution result with total rows affected. Example: >>> execute_many("abc-123", "INSERT INTO users (name) VALUES (?)", [["Alice"], ["Bob"]]) {"success": true, "rowcount": 2}

Execute a SQL script with multiple statements. Args: conn_id: The connection ID. sql_script: The SQL script containing multiple statements. Returns: Execution result. Example: >>> execute_script("abc-123", "CREATE TABLE t (x); INSERT INTO t VALUES (1); SELECT * FROM t;") {"success": true, "rowcount": -1}

Fetch results from a cursor. Args: conn_id: The connection ID. cursor_id: The cursor ID from a previous execute_query call. fetch_size: Number of rows to fetch (None for all remaining). Returns: Fetched rows. Example: >>> fetch_results("abc-123", "cursor-xyz", 10) {"success": true, "data": [{"x": 1}, {"x": 2}]}

List all tables in the database. Args: conn_id: The connection ID. Returns: List of table names. Example: >>> list_tables("abc-123") {"success": true, "data": ["users", "products", "orders"]}

Get detailed information about a table. Args: conn_id: The connection ID. table_name: The name of the table. Returns: Table schema information including columns, types, defaults, and nullability. Example: >>> get_table_info("abc-123", "users") {"success": true, "data": [{"cid": 0, "name": "id", "type": "INTEGER", "notnull": 1, "dflt_value": null, "pk": 1}]}

Get column information for a table. Args: conn_id: The connection ID. table_name: The name of the table. Returns: List of column definitions. Example: >>> get_columns("abc-123", "users") {"success": true, "data": [{"name": "id", "type": "INTEGER"}, {"name": "name", "type": "TEXT"}]}

Get index information for a table or all indexes. Args: conn_id: The connection ID. table_name: Optional table name to filter indexes. Returns: List of index information. Example: >>> get_indexes("abc-123", "users") {"success": true, "data": [{"name": "idx_name", "table": "users", "unique": 0}]}

Get primary key columns for a table. Args: conn_id: The connection ID. table_name: The name of the table. Returns: List of primary key column names. Example: >>> get_primary_keys("abc-123", "users") {"success": true, "data": ["id"]}

Get foreign key information for a table. Args: conn_id: The connection ID. table_name: The name of the table. Returns: List of foreign key definitions. Example: >>> get_foreign_keys("abc-123", "orders") {"success": true, "data": [{"from": "user_id", "to": "users.id", "table": "users"}]}

Create a new table. Args: conn_id: The connection ID. table_name: The name of the table to create. columns: List of column definitions with 'name' and 'type' (and optional 'pk', 'notnull', 'default'). if_not_exists: Add IF NOT EXISTS clause. Returns: Success status. Example: >>> create_table("abc-123", "users", [{"name": "id", "type": "INTEGER", "pk": True}, {"name": "name", "type": "TEXT"}]) {"success": true}

Drop a table. Args: conn_id: The connection ID. table_name: The name of the table to drop. if_exists: Add IF EXISTS clause. Returns: Success status. Example: >>> drop_table("abc-123", "users") {"success": true}

Rename a table. Args: conn_id: The connection ID. old_name: Current table name. new_name: New table name. Returns: Success status. Example: >>> rename_table("abc-123", "users", "accounts") {"success": true}

Add a column to a table. Args: conn_id: The connection ID. table_name: The name of the table. column_def: Column definition (e.g., 'email TEXT'). Returns: Success status. Example: >>> alter_table_add_column("abc-123", "users", "email TEXT NOT NULL") {"success": true}

Vacuum the database to reclaim space and optimize. Args: conn_id: The connection ID. Returns: Success status. Example: >>> vacuum("abc-123") {"success": true}

Insert a row into a table. Args: conn_id: The connection ID. table_name: The name of the table. data: Dictionary of column names to values. Returns: Success status with lastrowid. Example: >>> insert_row("abc-123", "users", {"name": "John", "email": "john@example.com"}) {"success": true, "lastrowid": 1}

Update rows in a table. Args: conn_id: The connection ID. table_name: The name of the table. data: Dictionary of column names to new values. where: WHERE clause condition. where_params: Parameters for the WHERE clause. Returns: Success status with rowcount. Example: >>> update_rows("abc-123", "users", {"name": "Jane"}, "id = ?", [1]) {"success": true, "rowcount": 1}

Delete rows from a table. Args: conn_id: The connection ID. table_name: The name of the table. where: WHERE clause condition. where_params: Parameters for the WHERE clause. Returns: Success status with rowcount. Example: >>> delete_rows("abc-123", "users", "id = ?", [1]) {"success": true, "rowcount": 1}

Select rows from a table. Args: conn_id: The connection ID. table_name: The name of the table. columns: Comma-separated column names or * for all. where: WHERE clause condition. where_params: Parameters for the WHERE clause. order_by: ORDER BY clause. limit: Maximum number of rows. offset: Number of rows to skip. Returns: Query results. Example: >>> select_rows("abc-123", "users", "id, name", "active = ?", [1], "name", 10) {"success": true, "data": [{"id": 1, "name": "John"}], "columns": ["id", "name"]}

Register a Python function as a SQLite function. Args: conn_id: The connection ID. name: Name of the SQL function. func_code: Python code that defines a function named 'func'. n_arg: Number of arguments (-1 for any). deterministic: Whether the function is deterministic. Returns: Success status. Example: >>> create_python_function("abc-123", "my_upper", "def func(x): return x.upper() if x else None") {"success": true}

Register a Python class as a SQLite aggregate function. Args: conn_id: The connection ID. name: Name of the SQL aggregate function. step_code: Python code defining a Step class with a step() method. finalize_code: Python code defining a Final class with a finalize() method. n_arg: Number of arguments (-1 for any). Returns: Success status. Example: >>> create_python_aggregate("abc-123", "my_sum", "class Step: total = 0; def step(self, v): self.total += v", ... "class Final: def finalize(self): return self.total") {"success": true}

Drop a user-defined function. Note: SQLite doesn't have DROP FUNCTION; this just confirms the function name. Args: conn_id: The connection ID. _name: Name of the function (unused, SQLite limitation). Returns: Success status.

Backup the database to a file. Args: conn_id: The connection ID. target_path: Path to the backup file. pages: Number of pages to copy per iteration (-1 for all). name: Database name ('main' or 'temp' or attached database name). Returns: Success status. Example: >>> backup_database("abc-123", "/path/to/backup.db") {"success": true}

Restore the database from a backup file. Args: conn_id: The connection ID. source_path: Path to the backup file. Returns: Success status. Example: >>> restore_database("abc-123", "/path/to/backup.db") {"success": true}

Serialize a database to base64-encoded bytes. Args: conn_id: The connection ID. name: Database name to serialize. Returns: Base64-encoded database. Example: >>> serialize_database("abc-123") {"success": true, "data": "U29tZURhdGFiYXNl..."}

Deserialize a base64-encoded database. Args: conn_id: The connection ID. data: Base64-encoded database data. name: Database name to deserialize to. database: Optional new database path. Returns: Success status. Example: >>> deserialize_database("abc-123", "U29tZURhdGFiYXNl...") {"success": true}

Get the CREATE TABLE SQL statement for a table. Args: conn_id: The connection ID. table_name: The name of the table. Returns: CREATE TABLE SQL statement. Example: >>> get_table_sql("abc-123", "users") {"success": true, "data": "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)"}

Export the database as a SQL dump. Args: conn_id: The connection ID. Returns: SQL dump as string. Example: >>> export_sql_dump("abc-123") {"success": true, "data": "BEGIN TRANSACTION;\nCREATE TABLE..."}

Register an adapter for a Python type to SQL type. Args: py_type: Python type name (e.g., 'datetime.date'). sql_type: SQL type name. Returns: Success status. Example: >>> register_adapter("datetime.date", "TEXT") {"success": true}

Register a converter for a SQL type to Python type. Args: typename: SQL type name. converter_code: Python code defining a converter function named 'convert'. Returns: Success status. Example: >>> register_converter("MYINT", "def convert(data): return int(data)") {"success": true}