sqlx_serde/
lib.rs

1use sqlx::postgres::{PgRow, PgValueRef, Postgres};
2use sqlx::{Column, Decode, Row, TypeInfo, ValueRef};
3use serde_json::{Value};
4
5use serde::{Serialize, Serializer};
6use serde::ser::{SerializeMap, SerializeSeq};
7
8pub fn read_header(row: &PgRow) -> Vec<String> {
9    let columns = row.columns();
10    let mut headers = vec![];
11    for c in columns {
12        headers.push(c.name().to_string());
13    }
14    headers
15}
16
17pub fn read_row(row: &PgRow) -> Vec<Value> {
18    let columns = row.columns();
19    let mut result: Vec<Value> = Vec::with_capacity(columns.len());
20    for c in columns {
21        let value = row.try_get_raw(c.ordinal()).unwrap();
22        let value = SerPgValueRef(value);
23        let value = serde_json::to_value(&value).unwrap();
24        result.push(value);
25    }
26    result
27}
28
29/// Can be used with serialize_with
30pub fn serialize_pgvalueref<S>(value: &PgValueRef, s: S) -> Result<S::Ok, S::Error>
31    where
32        S: Serializer,
33{
34    if value.is_null() {
35        return s.serialize_none();
36    }
37    let value = value.clone();
38    let info = value.type_info();
39    let name = info.name();
40    match name {
41        "BOOL" => {
42            let v: bool = Decode::<Postgres>::decode(value).unwrap();
43            s.serialize_bool(v)
44        }
45        "INT2" => {
46            let v: i16 = Decode::<Postgres>::decode(value).unwrap();
47            s.serialize_i16(v)
48        }
49        "INT4" => {
50            let v: i32 = Decode::<Postgres>::decode(value).unwrap();
51            s.serialize_i32(v)
52        }
53        "INT8" => {
54            let v: i64 = Decode::<Postgres>::decode(value).unwrap();
55            s.serialize_i64(v)
56        }
57        "FLOAT4" => {
58            let v: f32 = Decode::<Postgres>::decode(value).unwrap();
59            s.serialize_f32(v)
60        }
61        "FLOAT8" => {
62            let v: f64 = Decode::<Postgres>::decode(value).unwrap();
63            s.serialize_f64(v)
64        }
65        #[cfg(feature = "decimal")]
66        "NUMERIC" => {
67            let v: sqlx::types::Decimal = Decode::<Postgres>::decode(value).unwrap();
68            s.serialize_str(&v.to_string())
69        }
70        "CHAR" | "VARCHAR" | "TEXT" | "\"CHAR\"" => {
71            let v: String = Decode::<Postgres>::decode(value).unwrap();
72            s.serialize_str(&v)
73        }
74        "BYTEA" => {
75            let v: Vec<u8> = Decode::<Postgres>::decode(value).unwrap();
76            s.serialize_some(&v)
77        }
78        "JSON" | "JSONB" => {
79            let v: Value = Decode::<Postgres>::decode(value).unwrap();
80            s.serialize_some(&v)
81        }
82        #[cfg(feature = "chrono")]
83        "TIMESTAMP" => {
84            let v: sqlx::types::chrono::NaiveDateTime = Decode::<Postgres>::decode(value).unwrap();
85            let v = v.format("%Y-%m-%dT%H:%M:%S.%f").to_string();
86            s.serialize_str(&v)
87        }
88        #[cfg(feature = "chrono")]
89        "TIMESTAMPTZ" => {
90            use sqlx::types::chrono;
91            let v: chrono::DateTime::<chrono::Utc> = Decode::<Postgres>::decode(value).unwrap();
92            s.serialize_str(&v.to_rfc3339())
93        }
94        #[cfg(feature = "chrono")]
95        "DATE" => {
96            use sqlx::types::chrono;
97            let v: chrono::NaiveDate = Decode::<Postgres>::decode(value).unwrap();
98            s.serialize_str(&v.to_string())
99        }
100        #[cfg(feature = "chrono")]
101        "TIME" => {
102            use sqlx::types::chrono;
103            let v: chrono::NaiveTime = Decode::<Postgres>::decode(value).unwrap();
104            s.serialize_str(&v.to_string())
105        }
106        #[cfg(feature = "uuid")]
107        "UUID" => {
108            let v: sqlx::types::Uuid = Decode::<Postgres>::decode(value).unwrap();
109            let v = v.to_string();
110            s.serialize_str(&v)
111        }
112        #[cfg(not(feature = "uuid"))]
113        "UUID" => {
114            panic!("UUID type is not supported. Please enable the `uuid` feature on crate sqlx_serde.")
115        }
116        _ => {
117            let error_message = format!("Failed to deserialize postgres type {} as string", name);
118            let v: String = Decode::<Postgres>::decode(value).expect(&error_message);
119            s.serialize_str(&v)
120        }
121        // PgType::Name => "NAME",
122        // PgType::Oid => "OID",
123        // PgType::JsonArray => "JSON[]",
124        // PgType::Point => "POINT",
125        // PgType::Lseg => "LSEG",
126        // PgType::Path => "PATH",
127        // PgType::Box => "BOX",
128        // PgType::Polygon => "POLYGON",
129        // PgType::Line => "LINE",
130        // PgType::LineArray => "LINE[]",
131        // PgType::Cidr => "CIDR",
132        // PgType::CidrArray => "CIDR[]",
133        // PgType::Unknown => "UNKNOWN",
134        // PgType::Circle => "CIRCLE",
135        // PgType::CircleArray => "CIRCLE[]",
136        // PgType::Macaddr8 => "MACADDR8",
137        // PgType::Macaddr8Array => "MACADDR8[]",
138        // PgType::Macaddr => "MACADDR",
139        // PgType::Inet => "INET",
140        // PgType::BoolArray => "BOOL[]",
141        // PgType::ByteaArray => "BYTEA[]",
142        // PgType::CharArray => "\"CHAR\"[]",
143        // PgType::NameArray => "NAME[]",
144        // PgType::Int2Array => "INT2[]",
145        // PgType::Int4Array => "INT4[]",
146        // PgType::TextArray => "TEXT[]",
147        // PgType::BpcharArray => "CHAR[]",
148        // PgType::VarcharArray => "VARCHAR[]",
149        // PgType::Int8Array => "INT8[]",
150        // PgType::PointArray => "POINT[]",
151        // PgType::LsegArray => "LSEG[]",
152        // PgType::PathArray => "PATH[]",
153        // PgType::BoxArray => "BOX[]",
154        // PgType::Float4Array => "FLOAT4[]",
155        // PgType::Float8Array => "FLOAT8[]",
156        // PgType::PolygonArray => "POLYGON[]",
157        // PgType::OidArray => "OID[]",
158        // PgType::MacaddrArray => "MACADDR[]",
159        // PgType::InetArray => "INET[]",
160        // PgType::Date => "DATE",
161        // PgType::Time => "TIME",
162        // PgType::Timestamp => "TIMESTAMP",
163        // PgType::TimestampArray => "TIMESTAMP[]",
164        // PgType::DateArray => "DATE[]",
165        // PgType::TimeArray => "TIME[]",
166        // PgType::Timestamptz => "TIMESTAMPTZ",
167        // PgType::TimestamptzArray => "TIMESTAMPTZ[]",
168        // PgType::Interval => "INTERVAL",
169        // PgType::IntervalArray => "INTERVAL[]",
170        // PgType::NumericArray => "NUMERIC[]",
171        // PgType::Timetz => "TIMETZ",
172        // PgType::TimetzArray => "TIMETZ[]",
173        // PgType::Bit => "BIT",
174        // PgType::BitArray => "BIT[]",
175        // PgType::Varbit => "VARBIT",
176        // PgType::VarbitArray => "VARBIT[]",
177        // PgType::Numeric => "NUMERIC",
178        // PgType::Record => "RECORD",
179        // PgType::RecordArray => "RECORD[]",
180        // PgType::UuidArray => "UUID[]",
181        // PgType::JsonbArray => "JSONB[]",
182        // PgType::Int4Range => "INT4RANGE",
183        // PgType::Int4RangeArray => "INT4RANGE[]",
184        // PgType::NumRange => "NUMRANGE",
185        // PgType::NumRangeArray => "NUMRANGE[]",
186        // PgType::TsRange => "TSRANGE",
187        // PgType::TsRangeArray => "TSRANGE[]",
188        // PgType::TstzRange => "TSTZRANGE",
189        // PgType::TstzRangeArray => "TSTZRANGE[]",
190        // PgType::DateRange => "DATERANGE",
191        // PgType::DateRangeArray => "DATERANGE[]",
192        // PgType::Int8Range => "INT8RANGE",
193        // PgType::Int8RangeArray => "INT8RANGE[]",
194        // PgType::Jsonpath => "JSONPATH",
195        // PgType::JsonpathArray => "JSONPATH[]",
196        // PgType::Money => "MONEY",
197        // PgType::MoneyArray => "MONEY[]",
198        // PgType::Void => "VOID",
199    }
200}
201
202/// Can be used with serialize_with
203pub fn serialize_pgrow_as_vec<S>(x: &PgRow, s: S) -> Result<S::Ok, S::Error>
204    where
205        S: Serializer,
206{
207    let cols = x.columns();
208    let mut seq = s.serialize_seq(Some(cols.len()))?;
209    for c in cols {
210        let c: PgValueRef = x.try_get_raw(c.ordinal()).unwrap();
211        let c = SerPgValueRef(c);
212        seq.serialize_element(&c)?;
213    }
214    seq.end()
215}
216
217/// Can be used with serialize_with
218pub fn serialize_pgrow_as_map<S>(x: &PgRow, s: S) -> Result<S::Ok, S::Error>
219    where
220        S: Serializer,
221{
222    let cols = x.columns();
223    let mut map = s.serialize_map(Some(cols.len()))?;
224    for col in cols {
225        let c: PgValueRef = x.try_get_raw(col.ordinal()).unwrap();
226        let c = SerPgValueRef(c);
227        map.serialize_entry(col.name(), &c)?;
228    }
229    map.end()
230}
231
232/// SerVecPgRow::from(pg_row) will make your row serialize as a vector.
233#[derive(Serialize)]
234pub struct SerVecPgRow(
235    #[serde(serialize_with = "serialize_pgrow_as_vec")]
236    PgRow
237);
238
239/// SerMapPgRow::from(pg_row) will make your row serialize as a map.
240/// If you have multiple columns with the same name, the last one will win.
241#[derive(Serialize)]
242pub struct SerMapPgRow(
243    #[serde(serialize_with = "serialize_pgrow_as_map")]
244    PgRow
245);
246
247impl From<PgRow> for SerMapPgRow {
248    fn from(row: PgRow) -> Self {
249        SerMapPgRow(row)
250    }
251}
252
253impl std::ops::Deref for SerMapPgRow {
254    type Target = PgRow;
255
256    fn deref(&self) -> &Self::Target {
257        &self.0
258    }
259}
260
261impl std::ops::DerefMut for SerMapPgRow {
262    fn deref_mut(&mut self) -> &mut Self::Target {
263        &mut self.0
264    }
265}
266
267impl Into<PgRow> for SerMapPgRow {
268    fn into(self) -> PgRow {
269        self.0
270    }
271}
272
273/// SerPgValueRef::from(pg_value_ref) will make your value serialize as its closest serde type.
274#[derive(Serialize)]
275pub struct SerPgValueRef<'r>(
276    #[serde(serialize_with = "serialize_pgvalueref")]
277    PgValueRef<'r>,
278);
279
280impl From<PgRow> for SerVecPgRow {
281    fn from(row: PgRow) -> Self {
282        SerVecPgRow(row)
283    }
284}
285
286impl std::ops::Deref for SerVecPgRow {
287    type Target = PgRow;
288
289    fn deref(&self) -> &Self::Target {
290        &self.0
291    }
292}
293
294impl std::ops::DerefMut for SerVecPgRow {
295    fn deref_mut(&mut self) -> &mut Self::Target {
296        &mut self.0
297    }
298}
299
300impl Into<PgRow> for SerVecPgRow {
301    fn into(self) -> PgRow {
302        self.0
303    }
304}
305
306
307#[cfg(test)]
308mod tests {
309    use sqlx::{Connection, Executor, PgConnection};
310    use super::*;
311
312    #[tokio::test]
313    async fn it_works() {
314        use sqlx::types::chrono;
315        let mut conn = PgConnection::connect("postgres://localhost:5432/postgres").await.unwrap();
316        let row = conn.fetch_one("SELECT NOW()").await.unwrap();
317        let row = read_row(&row);
318        chrono::DateTime::parse_from_rfc3339(row[0].as_str().unwrap()).unwrap();
319
320        let row = conn.fetch_one("select '00000000-0000-0000-0000-000000000000'::uuid").await.unwrap();
321        let row = read_row(&row);
322        assert_eq!(row[0].as_str().unwrap(), "00000000-0000-0000-0000-000000000000");
323
324        let row = conn.fetch_one("select 3.3").await.unwrap();
325        let row = read_row(&row);
326        assert_eq!(row[0].as_str().unwrap(), "3.3");
327
328        let row = conn.fetch_one("select 3.3::numeric(19,4)").await.unwrap();
329        let row = read_row(&row);
330        assert_eq!(row[0].as_str().unwrap(), "3.3000");
331
332        let row = conn.fetch_one("select 'null'::jsonb").await.unwrap();
333        let row = read_row(&row);
334        assert_eq!(row[0], Value::Null);
335
336        let row = conn.fetch_one("select 1 as foo, 'hello' as bar").await.unwrap();
337        let row = SerMapPgRow::from(row);
338        let row = serde_json::to_string(&row).unwrap();
339        assert_eq!(row, r#"{"foo":1,"bar":"hello"}"#);
340
341        let row = conn.fetch_one("select 1 as foo, 'hello' as bar").await.unwrap();
342        let row = SerVecPgRow::from(row);
343        let row = serde_json::to_string(&row).unwrap();
344        assert_eq!(row, r#"[1,"hello"]"#);
345
346        let row = conn.fetch_one("select null::text as foo ").await.unwrap();
347        let row = SerVecPgRow::from(row);
348        let row = serde_json::to_string(&row).unwrap();
349        assert_eq!(row, r#"[null]"#);
350    }
351}
sqlx_serde/lib.rs

sqlx_serde/
lib.rs
