sqlx-json 0.10.4

Database-agnostic row-to-JSON conversion for sqlx
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

//! [`RowExt`](crate::RowExt) implementation for `MySQL` rows.
//!
//! Uses `column.type_info().name()` to pick the right Rust type for each column.
//! `MySQL` 9 reports `information_schema` text columns as `VARBINARY`; these
//! are decoded as UTF-8 strings rather than base64. Temporal types (`DATE`,
//! `TIME`, `DATETIME`, `TIMESTAMP`) are decoded via sqlx's `chrono` integration
//! and serialized as naive ISO 8601 strings (no timezone offset). `DECIMAL`
//! is decoded via `BigDecimal` to preserve precision; `FLOAT` is decoded as
//! `f32` (sqlx-mysql strict-checks the column type), `DOUBLE` as `f64`.

use base64::Engine as _;
use base64::engine::general_purpose::STANDARD as BASE64;
use bigdecimal::BigDecimal;
use serde_json::{Map, Value};
use sqlx::mysql::MySqlRow;
use sqlx::types::chrono::{DateTime, NaiveDate, NaiveDateTime, NaiveTime, Utc};
use sqlx::{Column, Row, TypeInfo, ValueRef};

use crate::RowExt;
use crate::numeric::bigdecimal_to_json;

impl RowExt for MySqlRow {
    fn to_json(&self) -> Value {
        let columns = self.columns();
        let mut map = Map::with_capacity(columns.len());

        for column in columns {
            let idx = column.ordinal();
            let type_name = column.type_info().name();

            let value = if self.try_get_raw(idx).is_ok_and(|v| v.is_null()) {
                Value::Null
            } else {
                match type_name {
                    "BOOLEAN" => self.try_get::<bool, _>(idx).map_or(Value::Null, Value::Bool),

                    "TINYINT" | "SMALLINT" | "INT" | "MEDIUMINT" | "BIGINT" | "TINYINT UNSIGNED"
                    | "SMALLINT UNSIGNED" | "INT UNSIGNED" | "MEDIUMINT UNSIGNED" | "YEAR" => self
                        .try_get::<i64, _>(idx)
                        .map_or(Value::Null, |v| Value::Number(v.into())),

                    "BIGINT UNSIGNED" => self.try_get::<u64, _>(idx).map_or(Value::Null, |v| {
                        i64::try_from(v)
                            .map_or_else(|_| Value::String(v.to_string()), |signed| Value::Number(signed.into()))
                    }),

                    "DECIMAL" => self
                        .try_get::<BigDecimal, _>(idx)
                        .map_or(Value::Null, |v| bigdecimal_to_json(&v)),

                    "FLOAT" => self.try_get::<f32, _>(idx).map_or(Value::Null, Value::from),

                    "DOUBLE" => self.try_get::<f64, _>(idx).map_or(Value::Null, Value::from),

                    "JSON" => self.try_get::<Value, _>(idx).unwrap_or(Value::Null),

                    // MySQL 9 returns information_schema columns as BINARY/VARBINARY
                    // even when they contain valid UTF-8. Try String first, then bytes.
                    "BINARY" | "VARBINARY" => self
                        .try_get::<String, _>(idx)
                        .map_or_else(|_| bytes_to_json(self, idx), Value::String),

                    "BLOB" | "TINYBLOB" | "MEDIUMBLOB" | "LONGBLOB" | "BIT" | "GEOMETRY" => bytes_to_json(self, idx),

                    "DATE" => self
                        .try_get::<NaiveDate, _>(idx)
                        .map_or(Value::Null, |v| Value::String(v.to_string())),

                    "TIME" => self
                        .try_get::<NaiveTime, _>(idx)
                        .map_or(Value::Null, |v| Value::String(v.to_string())),

                    "DATETIME" => self
                        .try_get::<NaiveDateTime, _>(idx)
                        .map_or(Value::Null, |v| Value::String(format!("{}T{}", v.date(), v.time()))),

                    // sqlx-mysql's NaiveDateTime decoder only accepts ColumnType::Datetime,
                    // not Timestamp. DateTime<Utc> accepts both; we then strip the zone
                    // via naive_utc() so the wire shape matches the naive ISO 8601 form.
                    "TIMESTAMP" => self.try_get::<DateTime<Utc>, _>(idx).map_or(Value::Null, |v| {
                        let n = v.naive_utc();
                        Value::String(format!("{}T{}", n.date(), n.time()))
                    }),

                    // All other types (VARCHAR, TEXT, ENUM, etc.) → String
                    _ => self
                        .try_get::<String, _>(idx)
                        .map_or_else(|_| bytes_to_json(self, idx), Value::String),
                }
            };

            map.insert(column.name().to_string(), value);
        }

        Value::Object(map)
    }
}

/// Extracts a `MySQL` binary column as UTF-8 string, falling back to base64.
fn bytes_to_json(row: &MySqlRow, idx: usize) -> Value {
    row.try_get::<Vec<u8>, _>(idx).map_or(Value::Null, |bytes| {
        String::from_utf8(bytes.clone()).map_or_else(|_| Value::String(BASE64.encode(&bytes)), Value::String)
    })
}