mssqlrust 1.0.0

Lightweight Rust library for Microsoft SQL Server using dataset and datatable
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
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136

# mssqlrust

mssqlrust is a lightweight Rust library for Microsoft SQL Server built on top of [Tiberius](https://github.com/prisma/tiberius). It executes queries, stored procedures and views and returns results in a typed hierarchy `DataSet → DataTable → DataRow → DataValue` with column metadata, nullable handling and streaming support.

## Installation

Add the dependency to your `Cargo.toml`:

```toml
[dependencies]
mssqlrust = "1.0.0"
```

Adjust the URL or version depending on the source you use.

## How it works

The crate wraps the SQL Server connection using Tiberius and exposes an async `execute` function that receives a [`MssqlConfig`](src/infrastructure/mssql/config.rs) and a [`Command`](src/repositories/command.rs). The returned data is built into the hierarchy `DataSet → DataTable → DataRow → DataValue` for typed access to results.

```mermaid
flowchart LR
    A[Create MssqlConfig] --> B[Build Command]
    B --> C[execute]
    C --> D[SQL Server]
    D --> E[DataSet]
    E --> F[DataTable]
    F --> G[DataRow]
```

```mermaid
sequenceDiagram
    actor Client
    participant mssqlrust
    participant SQLServer
    Client->>mssqlrust: execute(config, cmd)
    mssqlrust->>SQLServer: run query
    SQLServer-->>mssqlrust: result rows
    mssqlrust-->>Client: DataSet
```

## Usage

```rust
use mssqlrust::{execute, Command, Parameter};
use mssqlrust::dataset::DataValue;
use mssqlrust::infrastructure::mssql::MssqlConfig;
use chrono::NaiveDate;
use rust_decimal::Decimal;

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    // Connection configuration
    let config = MssqlConfig::new(
        "localhost",
        1433,
        "sa",
        "YourStrong!Passw0rd",
        "master",
        true,
    );

    // Query returning different SQL types
    let cmd = Command::query(
        "SELECT \
         CAST(42 AS INT)            AS id, \
         N'hello'                   AS name, \
         CAST(1 AS BIT)             AS active, \
         CAST('2024-01-01' AS DATE) AS start_date, \
         CAST(123.45 AS DECIMAL(5,2)) AS price"
    );
    let ds = execute(config.clone(), cmd).await?;
    let row = &ds.tables["table0"][0];
    println!("id: {:?}", row["id"]);
    println!("name: {:?}", row["name"]);
    println!("active: {:?}", row["active"]);
    println!("start_date: {:?}", row["start_date"]);
    println!("price: {:?}", row["price"]);

    // Parameterized query
    let cmd = Command::query("SELECT @id AS id, @flag AS flag, @amount AS amount, @when AS when_date")
        .with_param(Parameter::new("id", DataValue::Int(7)))
        .with_param(Parameter::new("flag", DataValue::Bool(false)))
        .with_param(Parameter::new("amount", DataValue::Decimal(Decimal::new(1999, 2))))
        .with_param(Parameter::new("when", DataValue::Date(NaiveDate::from_ymd_opt(2024, 6, 1).unwrap())));
    let ds = execute(config.clone(), cmd).await?;
    let row = &ds.tables["table0"][0];
    println!("id: {:?}, flag: {:?}, amount: {:?}, when: {:?}", row["id"], row["flag"], row["amount"], row["when_date"]);

    // Stored procedure with parameter
    let cmd = Command::stored_procedure("sp_get_user")
        .with_param(Parameter::new("id", DataValue::Int(42)));
    let ds = execute(config, cmd).await?;
    println!("name: {:?}", ds.tables["table0"][0]["name"]);

    Ok(())
}
```

## DataSet structure

```mermaid
classDiagram
    class DataSet {
        tables: HashMap<String, DataTable>
    }
    class DataTable {
        name: String
        columns: Vec<DataColumn>
        rows: Vec<DataRow>
    }
    class DataColumn {
        name: String
        sql_type: String
        size: Option<u32>
        nullable: bool
    }
    class DataRow {
        cells: HashMap<String, DataCell>
    }
    class DataCell {
        value: DataValue
    }
    DataSet --> DataTable
    DataTable --> DataColumn
    DataTable --> DataRow
    DataRow --> DataCell
    DataCell --> DataValue
```

## Examples

The [`tests`](tests) directory contains additional examples that show parameterized queries, stored procedures and mapping of various SQL types.

## License

This project is distributed under the terms of the MIT license.