postrust_core/
row_json.rs

1//! Conversion of PostgreSQL rows to JSON.
2//!
3//! Shared by every adapter that returns query results (the HTTP server, the
4//! Lambda adapter). Keeping one implementation matters: a per-adapter copy
5//! that reads every column as `serde_json::Value` silently yields `null` for
6//! anything that is not `json`/`jsonb`, because sqlx cannot decode an `int4`
7//! into a JSON value.
8
9/// Convert a sqlx row to a JSON object, decoding each column by its
10/// PostgreSQL type.
11///
12/// A column whose value cannot be decoded becomes `null` rather than failing
13/// the whole row.
14pub fn row_to_json(row: &sqlx::postgres::PgRow) -> serde_json::Value {
15    use sqlx::{Column, Row, TypeInfo};
16
17    let mut map = serde_json::Map::new();
18
19    for column in row.columns() {
20        let name = column.name();
21        let type_name = column.type_info().name();
22
23        let value = match type_name {
24            "INT2" | "SMALLINT" => row
25                .try_get::<i16, _>(name)
26                .ok()
27                .map(|v| serde_json::Value::Number(v.into())),
28            "INT4" | "INT" | "INTEGER" => row
29                .try_get::<i32, _>(name)
30                .ok()
31                .map(|v| serde_json::Value::Number(v.into())),
32            "INT8" | "BIGINT" => row
33                .try_get::<i64, _>(name)
34                .ok()
35                .map(|v| serde_json::Value::Number(v.into())),
36            "FLOAT4" | "REAL" => row
37                .try_get::<f32, _>(name)
38                .ok()
39                .and_then(|v| serde_json::Number::from_f64(v as f64))
40                .map(serde_json::Value::Number),
41            "FLOAT8" | "DOUBLE PRECISION" => row
42                .try_get::<f64, _>(name)
43                .ok()
44                .and_then(serde_json::Number::from_f64)
45                .map(serde_json::Value::Number),
46            // PostgreSQL's own row_to_json emits NUMERIC as a JSON number, and so
47            // does PostgREST, so returning a string here made every numeric column
48            // arrive at the client quoted.
49            //
50            // The value is parsed from its exact decimal text rather than through
51            // f64, so a NUMERIC(20, 6) keeps the digits it was stored with. NUMERIC
52            // also permits NaN, which JSON has no number for; that falls back to a
53            // string, which is what row_to_json does as well.
54            "NUMERIC" | "DECIMAL" => {
55                row.try_get::<sqlx::types::BigDecimal, _>(name)
56                    .ok()
57                    .map(|v| {
58                        let text = v.to_string();
59                        serde_json::from_str::<serde_json::Value>(&text)
60                            .ok()
61                            .filter(serde_json::Value::is_number)
62                            .unwrap_or(serde_json::Value::String(text))
63                    })
64            }
65            "BOOL" | "BOOLEAN" => row
66                .try_get::<bool, _>(name)
67                .ok()
68                .map(serde_json::Value::Bool),
69            "JSON" | "JSONB" => row.try_get::<serde_json::Value, _>(name).ok(),
70            "UUID" => row
71                .try_get::<sqlx::types::Uuid, _>(name)
72                .ok()
73                .map(|v| serde_json::Value::String(v.to_string())),
74            "TIMESTAMPTZ" | "TIMESTAMP WITH TIME ZONE" => row
75                .try_get::<chrono::DateTime<chrono::Utc>, _>(name)
76                .ok()
77                .map(|v| serde_json::Value::String(v.to_rfc3339())),
78            // ISO 8601, with `T` between the date and the time. `to_string`
79            // puts a space there, which is PostgreSQL's *display* format but
80            // not what it writes in JSON -- and a client parsing the result
81            // as a timestamp is parsing JSON.
82            "TIMESTAMP" | "TIMESTAMP WITHOUT TIME ZONE" => row
83                .try_get::<chrono::NaiveDateTime, _>(name)
84                .ok()
85                .map(|v| serde_json::Value::String(v.format("%Y-%m-%dT%H:%M:%S%.f").to_string())),
86            "DATE" => row
87                .try_get::<chrono::NaiveDate, _>(name)
88                .ok()
89                .map(|v| serde_json::Value::String(v.to_string())),
90            "TIME" | "TIME WITHOUT TIME ZONE" => row
91                .try_get::<chrono::NaiveTime, _>(name)
92                .ok()
93                .map(|v| serde_json::Value::String(v.to_string())),
94            _ => row
95                .try_get::<String, _>(name)
96                .ok()
97                .map(serde_json::Value::String),
98        };
99
100        map.insert(name.to_string(), value.unwrap_or(serde_json::Value::Null));
101    }
102
103    serde_json::Value::Object(map)
104}
105
106#[cfg(test)]
107mod tests {
108    /// The `T` is not decoration: `to_string` writes a space, which is
109    /// PostgreSQL's display format and not what it puts in JSON.
110    #[test]
111    fn a_timestamp_without_a_zone_is_written_in_iso_8601() {
112        let format = "%Y-%m-%dT%H:%M:%S%.f";
113
114        let with_fraction = chrono::NaiveDateTime::parse_from_str(
115            "2015-12-08 04:22:57.472738",
116            "%Y-%m-%d %H:%M:%S%.f",
117        )
118        .unwrap();
119        assert_eq!(
120            with_fraction.format(format).to_string(),
121            "2015-12-08T04:22:57.472738"
122        );
123
124        let whole_second =
125            chrono::NaiveDateTime::parse_from_str("2018-01-02 00:00:00", "%Y-%m-%d %H:%M:%S")
126                .unwrap();
127        assert_eq!(
128            whole_second.format(format).to_string(),
129            "2018-01-02T00:00:00"
130        );
131    }
132}
postrust_core/row_json.rs

postrust_core/
row_json.rs
