dbcli 0.1.0

Convert SQL query results to JSON without struct mapping, supporting MySQL/PostgreSQL/SQLite/Odbc
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90

//! SQLite implementation of `execute_raw_sql`.
//!
//! Executes one or more SQL statements against a SQLite database and returns
//! a [`Vec<SqlResult>`] — one entry per statement.

use futures::TryStreamExt;
use sqlx::sqlite::{SqlitePool, SqliteRow};
use sqlx::Either;
use crate::SqlResult;

/// Execute one or more SQL statements against a SQLite database.
///
/// Supports multiple statements separated by `;`.
/// Each statement that produces rows (SELECT, PRAGMA with output, etc.) is automatically
/// converted to JSON via [`crate::to_json::sqlite::to_json`] and returned as a
/// [`SqlResult::Query`]. Statements that do not produce rows return a
/// [`SqlResult::Execute`] with the count of affected rows.
///
/// # Arguments
///
/// * `pool` - SQLite connection pool
/// * `sql`  - SQL string; may contain multiple statements separated by `;`
///
/// # Returns
///
/// A [`Vec<SqlResult>`] with one entry per statement result, in execution order.
///
/// # SQLite Multi-statement Limitations
///
/// The `sqlx` SQLite driver has limited support for multi-statement execution via
/// `raw_sql`. In practice, only a subset of multi-statement batches works reliably.
/// For best results, prefer sending one statement at a time when targeting SQLite,
/// or split the SQL string by `;` manually before calling this function.
///
/// # Example
///
/// ```rust,no_run
/// # use sqlx::sqlite::SqlitePool;
/// # async fn run(pool: &SqlitePool) -> anyhow::Result<()> {
/// // Single statement — most reliable usage with SQLite
/// let results = dbcli::execute::sqlite::execute_raw_sql(
///     pool,
///     "SELECT * FROM users;",
/// ).await?;
///
/// for result in &results {
///     println!("{}", serde_json::to_string_pretty(result)?);
/// }
/// # Ok(())
/// # }
/// ```
pub async fn execute_raw_sql(
    pool: &SqlitePool,
    sql: &str,
) -> anyhow::Result<Vec<SqlResult>> {
    let mut stream = sqlx::raw_sql(sql).fetch_many(pool);
    let mut results: Vec<SqlResult> = Vec::new();
    let mut current_rows: Vec<SqliteRow> = Vec::new();

    while let Some(item) = stream.try_next().await? {
        match item {
            Either::Left(result) => {
                if !current_rows.is_empty() {
                    // Accumulated rows belong to the preceding SELECT statement.
                    let (data, columns) = crate::to_json::sqlite::to_json(
                        std::mem::take(&mut current_rows),
                    )?;
                    results.push(SqlResult::Query { data, columns });
                } else {
                    // No rows accumulated — this is a DML/DDL result.
                    results.push(SqlResult::Execute {
                        rows_affected: result.rows_affected(),
                    });
                }
            }
            Either::Right(row) => {
                current_rows.push(row);
            }
        }
    }

    // Flush any remaining rows (e.g. the last statement was a SELECT with no
    // trailing Either::Left summary emitted by the driver).
    if !current_rows.is_empty() {
        let (data, columns) = crate::to_json::sqlite::to_json(current_rows)?;
        results.push(SqlResult::Query { data, columns });
    }

    Ok(results)
}