database_mcp_sqlite/
handler.rs

1//! `SQLite` handler: connection pool, MCP tool router, and `ServerHandler` impl.
2//!
3//! Uses [`SqlitePoolOptions::connect_lazy_with`] so no file I/O happens
4//! until the first tool invocation.
5
6use std::time::Duration;
7
8use database_mcp_config::DatabaseConfig;
9use database_mcp_server::server_info;
10use rmcp::RoleServer;
11use rmcp::handler::server::router::tool::ToolRouter;
12use rmcp::handler::server::tool::ToolCallContext;
13use rmcp::model::{CallToolRequestParams, CallToolResult, ListToolsResult, PaginatedRequestParams, ServerInfo, Tool};
14use rmcp::service::RequestContext;
15use rmcp::{ErrorData, ServerHandler};
16use sqlx::SqlitePool;
17use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};
18
19use crate::tools::{
20    DropTableTool, ExplainQueryTool, GetTableSchemaTool, ListTablesTool, ReadQueryTool, WriteQueryTool,
21};
22
23/// Backend-specific description for `SQLite`.
24const DESCRIPTION: &str = "Database MCP Server for SQLite";
25
26/// Backend-specific instructions for `SQLite`.
27const INSTRUCTIONS: &str = r"## Workflow
28
291. Call `list_tables` to discover tables in the connected database.
302. Call `get_table_schema` with a `table_name` to inspect columns, types, and foreign keys before writing queries.
313. Use `read_query` for read-only SQL (SELECT, EXPLAIN).
324. Use `write_query` for data changes (INSERT, UPDATE, DELETE, CREATE, ALTER, DROP).
33
34## Constraints
35
36- The `write_query` tool is hidden when read-only mode is active.
37- Multi-statement queries are not supported. Send one statement per request.";
38
39/// `SQLite` file-based database handler.
40///
41/// The connection pool is created with [`SqlitePoolOptions::connect_lazy_with`],
42/// which defers all file I/O until the first query. Connection errors
43/// surface as tool-level errors returned to the MCP client.
44#[derive(Clone)]
45pub struct SqliteHandler {
46    pub(crate) config: DatabaseConfig,
47    pub(crate) pool: SqlitePool,
48    tool_router: ToolRouter<Self>,
49}
50
51impl std::fmt::Debug for SqliteHandler {
52    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
53        f.debug_struct("SqliteHandler")
54            .field("read_only", &self.config.read_only)
55            .finish_non_exhaustive()
56    }
57}
58
59impl SqliteHandler {
60    /// Creates a new `SQLite` handler with a lazy connection pool.
61    ///
62    /// Does **not** open the database file. The pool connects on-demand
63    /// when the first query is executed. The MCP tool router is built once
64    /// here and reused for every request.
65    #[must_use]
66    pub fn new(config: &DatabaseConfig) -> Self {
67        Self {
68            config: config.clone(),
69            pool: pool_options(config).connect_lazy_with(connect_options(config)),
70            tool_router: build_tool_router(config.read_only),
71        }
72    }
73
74    /// Wraps `name` in double quotes for safe use in `SQLite` SQL statements.
75    pub(crate) fn quote_identifier(name: &str) -> String {
76        database_mcp_sql::identifier::quote_identifier(name, '"')
77    }
78}
79
80/// Builds [`SqlitePoolOptions`] with lifecycle defaults from a [`DatabaseConfig`].
81fn pool_options(config: &DatabaseConfig) -> SqlitePoolOptions {
82    let mut opts = SqlitePoolOptions::new()
83        .max_connections(1) // SQLite is a single-writer
84        .min_connections(DatabaseConfig::DEFAULT_MIN_CONNECTIONS)
85        .idle_timeout(Duration::from_secs(DatabaseConfig::DEFAULT_IDLE_TIMEOUT_SECS))
86        .max_lifetime(Duration::from_secs(DatabaseConfig::DEFAULT_MAX_LIFETIME_SECS));
87
88    if let Some(timeout) = config.connection_timeout {
89        opts = opts.acquire_timeout(Duration::from_secs(timeout));
90    }
91
92    opts
93}
94
95/// Builds [`SqliteConnectOptions`] from a [`DatabaseConfig`].
96fn connect_options(config: &DatabaseConfig) -> SqliteConnectOptions {
97    SqliteConnectOptions::new().filename(config.name.as_deref().unwrap_or_default())
98}
99
100/// Builds the tool router, including write tools only when not in read-only mode.
101fn build_tool_router(read_only: bool) -> ToolRouter<SqliteHandler> {
102    let mut router = ToolRouter::new()
103        .with_async_tool::<ListTablesTool>()
104        .with_async_tool::<GetTableSchemaTool>()
105        .with_async_tool::<ReadQueryTool>()
106        .with_async_tool::<ExplainQueryTool>();
107
108    if !read_only {
109        router = router
110            .with_async_tool::<WriteQueryTool>()
111            .with_async_tool::<DropTableTool>();
112    }
113    router
114}
115
116impl ServerHandler for SqliteHandler {
117    fn get_info(&self) -> ServerInfo {
118        let mut info = server_info();
119        info.server_info.description = Some(DESCRIPTION.into());
120        info.instructions = Some(INSTRUCTIONS.into());
121        info
122    }
123
124    async fn call_tool(
125        &self,
126        request: CallToolRequestParams,
127        context: RequestContext<RoleServer>,
128    ) -> Result<CallToolResult, ErrorData> {
129        let tcc = ToolCallContext::new(self, request, context);
130        self.tool_router.call(tcc).await
131    }
132
133    async fn list_tools(
134        &self,
135        _request: Option<PaginatedRequestParams>,
136        _context: RequestContext<RoleServer>,
137    ) -> Result<ListToolsResult, ErrorData> {
138        Ok(ListToolsResult {
139            tools: self.tool_router.list_all(),
140            next_cursor: None,
141            meta: None,
142        })
143    }
144
145    fn get_tool(&self, name: &str) -> Option<Tool> {
146        self.tool_router.get(name).cloned()
147    }
148}
149
150#[cfg(test)]
151mod tests {
152    use super::*;
153    use database_mcp_config::DatabaseBackend;
154
155    fn base_config() -> DatabaseConfig {
156        DatabaseConfig {
157            backend: DatabaseBackend::Sqlite,
158            name: Some("test.db".into()),
159            ..DatabaseConfig::default()
160        }
161    }
162
163    fn handler(read_only: bool) -> SqliteHandler {
164        SqliteHandler::new(&DatabaseConfig {
165            backend: DatabaseBackend::Sqlite,
166            name: Some(":memory:".into()),
167            read_only,
168            ..DatabaseConfig::default()
169        })
170    }
171
172    #[test]
173    fn pool_options_applies_defaults() {
174        let config = base_config();
175        let opts = pool_options(&config);
176
177        assert_eq!(opts.get_max_connections(), 1, "SQLite must be single-writer");
178        assert_eq!(opts.get_min_connections(), DatabaseConfig::DEFAULT_MIN_CONNECTIONS);
179        assert_eq!(
180            opts.get_idle_timeout(),
181            Some(Duration::from_secs(DatabaseConfig::DEFAULT_IDLE_TIMEOUT_SECS))
182        );
183        assert_eq!(
184            opts.get_max_lifetime(),
185            Some(Duration::from_secs(DatabaseConfig::DEFAULT_MAX_LIFETIME_SECS))
186        );
187    }
188
189    #[test]
190    fn pool_options_applies_connection_timeout() {
191        let config = DatabaseConfig {
192            connection_timeout: Some(7),
193            ..base_config()
194        };
195        let opts = pool_options(&config);
196
197        assert_eq!(opts.get_acquire_timeout(), Duration::from_secs(7));
198    }
199
200    #[test]
201    fn pool_options_without_connection_timeout_uses_sqlx_default() {
202        let config = base_config();
203        let opts = pool_options(&config);
204
205        assert_eq!(opts.get_acquire_timeout(), Duration::from_secs(30));
206    }
207
208    #[test]
209    fn pool_options_ignores_max_pool_size() {
210        let config = DatabaseConfig {
211            max_pool_size: 20,
212            ..base_config()
213        };
214        let opts = pool_options(&config);
215
216        assert_eq!(opts.get_max_connections(), 1, "SQLite must always be single-writer");
217    }
218
219    #[test]
220    fn try_from_sets_filename() {
221        let opts = connect_options(&base_config());
222
223        assert_eq!(opts.get_filename().to_str().expect("valid path"), "test.db");
224    }
225
226    #[test]
227    fn try_from_empty_name_defaults() {
228        let config = DatabaseConfig {
229            name: None,
230            ..base_config()
231        };
232        let opts = connect_options(&config);
233
234        // Empty string filename — validated elsewhere by Config::validate()
235        assert_eq!(opts.get_filename().to_str().expect("valid path"), "");
236    }
237
238    #[tokio::test]
239    async fn new_creates_lazy_pool() {
240        let config = base_config();
241        let handler = SqliteHandler::new(&config);
242        // Pool exists but has no active connections (lazy).
243        assert_eq!(handler.pool.size(), 0);
244    }
245
246    #[tokio::test]
247    async fn router_exposes_all_six_tools_in_read_write_mode() {
248        let router = handler(false).tool_router;
249        for name in [
250            "list_tables",
251            "get_table_schema",
252            "drop_table",
253            "read_query",
254            "write_query",
255            "explain_query",
256        ] {
257            assert!(router.has_route(name), "missing tool: {name}");
258        }
259    }
260
261    #[tokio::test]
262    async fn router_hides_write_tools_in_read_only_mode() {
263        let router = handler(true).tool_router;
264        assert!(router.has_route("list_tables"));
265        assert!(router.has_route("get_table_schema"));
266        assert!(router.has_route("read_query"));
267        assert!(router.has_route("explain_query"));
268        assert!(!router.has_route("write_query"));
269        assert!(!router.has_route("drop_table"));
270    }
271
272    #[tokio::test]
273    async fn list_tables_metadata_matches_macro_parity() {
274        let router = handler(false).tool_router;
275        let tool = router.get("list_tables").expect("list_tables registered");
276
277        assert_eq!(tool.name, "list_tables");
278        assert_eq!(
279            tool.description.as_deref(),
280            Some("List all tables in the connected `SQLite` database.")
281        );
282
283        let annotations = tool.annotations.as_ref().expect("annotations present");
284        assert_eq!(annotations.read_only_hint, Some(true));
285        assert_eq!(annotations.destructive_hint, Some(false));
286        assert_eq!(annotations.idempotent_hint, Some(true));
287        assert_eq!(annotations.open_world_hint, Some(false));
288    }
289}
database_mcp_sqlite/handler.rs

database_mcp_sqlite/
handler.rs
