database_mcp_sqlite/
operations.rs

1//! `SQLite` database query operations.
2
3use database_mcp_backend::error::AppError;
4use serde_json::Value;
5use sqlx::sqlite::SqliteRow;
6use sqlx_to_json::RowExt;
7
8use super::SqliteBackend;
9
10impl SqliteBackend {
11    /// Lists all tables in a database.
12    ///
13    /// # Errors
14    ///
15    /// Returns [`AppError`] if the identifier is invalid or the query fails.
16    pub async fn list_tables(&self, _database: &str) -> Result<Vec<String>, AppError> {
17        let rows: Vec<(String,)> = sqlx::query_as(
18            "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name",
19        )
20        .fetch_all(&self.pool)
21        .await
22        .map_err(|e| AppError::Query(e.to_string()))?;
23        Ok(rows.into_iter().map(|r| r.0).collect())
24    }
25
26    /// Executes a SQL query and returns rows as JSON.
27    ///
28    /// # Errors
29    ///
30    /// Returns [`AppError`] if the query fails.
31    pub async fn execute_query(&self, sql: &str, _database: Option<&str>) -> Result<Value, AppError> {
32        let rows: Vec<SqliteRow> = sqlx::query(sql)
33            .fetch_all(&self.pool)
34            .await
35            .map_err(|e| AppError::Query(e.to_string()))?;
36        Ok(Value::Array(rows.iter().map(RowExt::to_json).collect()))
37    }
38}
39
40#[cfg(test)]
41mod tests {
42    use super::*;
43    use sqlx::SqlitePool;
44    use sqlx::sqlite::SqlitePoolOptions;
45
46    // Row-to-JSON conversion tests live in crates/sqlx_to_json.
47    // These tests cover the array-level wrapping done by execute_query.
48
49    /// Helper: creates an in-memory `SQLite` pool for unit tests.
50    async fn mem_pool() -> SqlitePool {
51        SqlitePoolOptions::new()
52            .max_connections(1)
53            .connect("sqlite::memory:")
54            .await
55            .expect("in-memory SQLite")
56    }
57
58    /// Helper: runs a query and converts all rows via [`RowExt::to_json`].
59    async fn query_json(pool: &SqlitePool, sql: &str) -> Value {
60        let rows: Vec<SqliteRow> = sqlx::query(sql).fetch_all(pool).await.expect("query failed");
61        Value::Array(rows.iter().map(RowExt::to_json).collect())
62    }
63
64    #[tokio::test]
65    async fn execute_query_empty_result() {
66        let pool = mem_pool().await;
67        sqlx::query("CREATE TABLE t (v INTEGER)").execute(&pool).await.unwrap();
68
69        let rows = query_json(&pool, "SELECT v FROM t").await;
70        assert_eq!(rows, Value::Array(vec![]));
71    }
72
73    #[tokio::test]
74    async fn execute_query_multiple_rows() {
75        let pool = mem_pool().await;
76        sqlx::query("CREATE TABLE t (id INTEGER, name TEXT, score REAL)")
77            .execute(&pool)
78            .await
79            .unwrap();
80        sqlx::query("INSERT INTO t VALUES (1, 'alice', 9.5), (2, 'bob', 8.0)")
81            .execute(&pool)
82            .await
83            .unwrap();
84
85        let rows = query_json(&pool, "SELECT id, name, score FROM t ORDER BY id").await;
86        assert_eq!(rows.as_array().expect("should be array").len(), 2);
87
88        assert_eq!(rows[0]["id"], Value::Number(1.into()));
89        assert_eq!(rows[0]["name"], Value::String("alice".into()));
90        assert!(rows[0]["score"].is_number());
91
92        assert_eq!(rows[1]["id"], Value::Number(2.into()));
93        assert_eq!(rows[1]["name"], Value::String("bob".into()));
94    }
95}
database_mcp_sqlite/operations.rs

database_mcp_sqlite/
operations.rs
