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    // By name, not by position: where two columns share a name, both read the
20    // *last* one's value -- sqlx's name lookup is a map the later column
21    // overwrote -- and the object holds that one value under the name. That
22    // is how this has always behaved, and the socket driver matches it.
23    for column in row.columns() {
24        let name = column.name();
25        let value = decode(row, name, column.type_info().name());
26        map.insert(name.to_string(), value);
27    }
28
29    serde_json::Value::Object(map)
30}
31
32/// A row's columns in order, decoded by the rules [`row_to_json`] uses.
33pub fn row_to_values(row: &sqlx::postgres::PgRow) -> Vec<serde_json::Value> {
34    use sqlx::{Column, Row, TypeInfo};
35
36    row.columns()
37        .iter()
38        .map(|column| decode(row, column.ordinal(), column.type_info().name()))
39        .collect()
40}
41
42/// One column, by name or by position, decoded by its PostgreSQL type.
43fn decode<I>(row: &sqlx::postgres::PgRow, index: I, type_name: &str) -> serde_json::Value
44where
45    I: sqlx::ColumnIndex<sqlx::postgres::PgRow> + Copy,
46{
47    use sqlx::Row;
48    // `name` reads as it always has; it may be a position.
49    let name = index;
50
51    let value = match type_name {
52        "INT2" | "SMALLINT" => row
53            .try_get::<i16, _>(name)
54            .ok()
55            .map(|v| serde_json::Value::Number(v.into())),
56        "INT4" | "INT" | "INTEGER" => row
57            .try_get::<i32, _>(name)
58            .ok()
59            .map(|v| serde_json::Value::Number(v.into())),
60        "INT8" | "BIGINT" => row
61            .try_get::<i64, _>(name)
62            .ok()
63            .map(|v| serde_json::Value::Number(v.into())),
64        "FLOAT4" | "REAL" => row
65            .try_get::<f32, _>(name)
66            .ok()
67            .and_then(|v| serde_json::Number::from_f64(v as f64))
68            .map(serde_json::Value::Number),
69        "FLOAT8" | "DOUBLE PRECISION" => row
70            .try_get::<f64, _>(name)
71            .ok()
72            .and_then(serde_json::Number::from_f64)
73            .map(serde_json::Value::Number),
74        // PostgreSQL's own row_to_json emits NUMERIC as a JSON number, and so
75        // does PostgREST, so returning a string here made every numeric column
76        // arrive at the client quoted.
77        //
78        // The value is parsed from its exact decimal text rather than through
79        // f64, so a NUMERIC(20, 6) keeps the digits it was stored with. NUMERIC
80        // also permits NaN, which JSON has no number for; that falls back to a
81        // string, which is what row_to_json does as well.
82        "NUMERIC" | "DECIMAL" => row
83            .try_get::<sqlx::types::BigDecimal, _>(name)
84            .ok()
85            .map(|v| {
86                let text = v.to_string();
87                serde_json::from_str::<serde_json::Value>(&text)
88                    .ok()
89                    .filter(serde_json::Value::is_number)
90                    .unwrap_or(serde_json::Value::String(text))
91            }),
92        "BOOL" | "BOOLEAN" => row
93            .try_get::<bool, _>(name)
94            .ok()
95            .map(serde_json::Value::Bool),
96        "JSON" | "JSONB" => row.try_get::<serde_json::Value, _>(name).ok(),
97        "UUID" => row
98            .try_get::<sqlx::types::Uuid, _>(name)
99            .ok()
100            .map(|v| serde_json::Value::String(v.to_string())),
101        "TIMESTAMPTZ" | "TIMESTAMP WITH TIME ZONE" => row
102            .try_get::<chrono::DateTime<chrono::Utc>, _>(name)
103            .ok()
104            .map(|v| serde_json::Value::String(v.to_rfc3339())),
105        // ISO 8601, with `T` between the date and the time. `to_string`
106        // puts a space there, which is PostgreSQL's *display* format but
107        // not what it writes in JSON -- and a client parsing the result
108        // as a timestamp is parsing JSON.
109        "TIMESTAMP" | "TIMESTAMP WITHOUT TIME ZONE" => row
110            .try_get::<chrono::NaiveDateTime, _>(name)
111            .ok()
112            .map(|v| serde_json::Value::String(v.format("%Y-%m-%dT%H:%M:%S%.f").to_string())),
113        "DATE" => row
114            .try_get::<chrono::NaiveDate, _>(name)
115            .ok()
116            .map(|v| serde_json::Value::String(v.to_string())),
117        "TIME" | "TIME WITHOUT TIME ZONE" => row
118            .try_get::<chrono::NaiveTime, _>(name)
119            .ok()
120            .map(|v| serde_json::Value::String(v.to_string())),
121        _ => row
122            .try_get::<String, _>(name)
123            .ok()
124            .map(serde_json::Value::String),
125    };
126
127    value.unwrap_or(serde_json::Value::Null)
128}
129
130#[cfg(test)]
131mod tests {
132    /// The `T` is not decoration: `to_string` writes a space, which is
133    /// PostgreSQL's display format and not what it puts in JSON.
134    #[test]
135    fn a_timestamp_without_a_zone_is_written_in_iso_8601() {
136        let format = "%Y-%m-%dT%H:%M:%S%.f";
137
138        let with_fraction = chrono::NaiveDateTime::parse_from_str(
139            "2015-12-08 04:22:57.472738",
140            "%Y-%m-%d %H:%M:%S%.f",
141        )
142        .unwrap();
143        assert_eq!(
144            with_fraction.format(format).to_string(),
145            "2015-12-08T04:22:57.472738"
146        );
147
148        let whole_second =
149            chrono::NaiveDateTime::parse_from_str("2018-01-02 00:00:00", "%Y-%m-%d %H:%M:%S")
150                .unwrap();
151        assert_eq!(
152            whole_second.format(format).to_string(),
153            "2018-01-02T00:00:00"
154        );
155    }
156}
postrust_core/row_json.rs

postrust_core/
row_json.rs
