sqlx-json 0.9.0

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

//! [`RowExt`](crate::RowExt) implementation for `PostgreSQL` rows.
//!
//! Type names are normalized to uppercase because sqlx may return either case
//! depending on the query context. Integer types use size-specific Rust types
//! (`i16`, `i32`, `i64`) because sqlx enforces strict type matching for
//! `PostgreSQL`. Temporal types (`DATE`, `TIME`, `TIMESTAMP`, `TIMESTAMPTZ`)
//! are decoded via sqlx's `chrono` integration and serialized as RFC 3339
//! strings; `TIMESTAMPTZ` is normalized to UTC and emitted with a `Z` suffix.

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

use crate::RowExt;

impl RowExt for PgRow {
    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().to_ascii_uppercase();

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

                    "INT8" => self
                        .try_get::<i64, _>(idx)
                        .map_or(Value::Null, |v| Value::Number(v.into())),

                    "INT4" | "OID" => self
                        .try_get::<i32, _>(idx)
                        .map_or(Value::Null, |v| Value::Number(i64::from(v).into())),

                    "INT2" => self
                        .try_get::<i16, _>(idx)
                        .map_or(Value::Null, |v| Value::Number(i64::from(v).into())),

                    "FLOAT4" | "FLOAT8" | "NUMERIC" | "MONEY" => self
                        .try_get::<f64, _>(idx)
                        .ok()
                        .and_then(serde_json::Number::from_f64)
                        .map_or(Value::Null, Value::Number),

                    "BYTEA" => self
                        .try_get::<Vec<u8>, _>(idx)
                        .map_or(Value::Null, |bytes| Value::String(BASE64.encode(&bytes))),

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

                    "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())),

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

                    "TIMESTAMPTZ" => self.try_get::<DateTime<Utc>, _>(idx).map_or(Value::Null, |v| {
                        let n = v.naive_utc();
                        Value::String(format!("{}T{}Z", n.date(), n.time()))
                    }),

                    _ => self.try_get::<String, _>(idx).map_or(Value::Null, Value::String),
                }
            };

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

        Value::Object(map)
    }
}