Rust and Postgres

Introductin

Quick into to Rust and Postgres

Rust and Postgres Types

PostgreSQL Type	Description	Rust Type	Notes
smallint, int2	2-byte signed integer	i16
integer, int4	4-byte signed integer	i32	Most common integer type
bigint, int8	8-byte signed integer	i64
serial, serial4	Auto-incrementing int4	i32	Often used for primary keys
bigserial, serial8	Auto-incrementing int8	i64
real, float4	4-byte float	f32
double precision, float8	8-byte float	f64
numeric, decimal	Arbitrary precision	rust_decimal::Decimal	Requires `postgres-decimal` crate
money	Currency amount	i64 or custom type	Often wrapped for precision
boolean	True/false	bool
character varying(n), varchar(n)	Variable-length string	String
character(n), char(n)	Fixed-length string	String
text	Unbounded string	String
bytea	Binary data	Vec<u8>
date	Calendar date	chrono::NaiveDate
timestamp	Date and time (no timezone)	chrono::NaiveDateTime
timestamptz	Date and time with timezone	chrono::DateTime<Utc> or <FixedOffset>
time	Time of day	chrono::NaiveTime
timetz	Time with timezone	chrono::DateTime<FixedOffset>
interval	Time span	postgres_types::Interval or custom
uuid	Universally unique ID	uuid::Uuid
json, jsonb	JSON data	serde_json::Value
enum	Custom enum	Rust enum with #[derive(ToSql, FromSql)]
composite	Structured type	Rust struct with #[derive(ToSql, FromSql)]

Macro to do the Mapping

Here is a macro to enable getting of Postgres type to Rust type

#[macro_export]
macro_rules! get_field {
    ($row:expr, $col:expr, $type:ty) => {{
        $row.try_get::<_, $type>($col).map_err(|e| {
            crate::types::DatabaseError::Deserialization {
                column: $col.to_string(),
                source: e,
            }
        })?
    }};
}

And for Optional fields

#[macro_export]
macro_rules! get_optional_field {
    ($row:expr, $col:expr, $type:ty) => {{
        $row.try_get::<_, Option<$type>>($col).map_err(|e| {
            crate::types::DatabaseError::Deserialization {
                column: $col.to_string(),
                source: e,
            }
        })?
    }};
}

Entity Example

fn parse_film_row(row: &Row) -> Result<FilmEntity, DatabaseError> {
    Ok(FilmEntity {
        film_id: get_field!(row, "film_id", i32),
        title: get_field!(row, "title", String),
        description: get_optional_field!(row, "description", String),
        release_year: get_field!(row, "release_year", i32),
        language_id: get_field!(row, "language_id", i16),
        rental_duration: get_field!(row, "rental_duration", i16),
        rental_rate: get_field!(row, "rental_rate", Decimal),
        length: get_field!(row, "length", i32),
        replacement_cost: get_field!(row, "replacement_cost", Decimal),
        rating: get_field!(row, "rating", MpaaRating),
        last_update: get_field!(row, "last_update", chrono::NaiveDateTime),
        special_features: get_field!(row, "special_features", String),
        full_text: get_field!(row, "fulltext", TsVector),
    })
}

You will see a custom type. This can be parse by making an enum to support this

use postgres_types::{ToSql, FromSql};

#[derive(Debug, Clone, Copy, PartialEq, Eq, ToSql, FromSql)]
#[postgres(name = "mpaa_rating")]
pub enum MpaaRating {
    #[postgres(name = "G")]
    G,
    #[postgres(name = "PG")]
    PG,
    #[postgres(name = "PG-13")]
    PG13,
    #[postgres(name = "R")]
    R,
    #[postgres(name = "NC-17")]
    NC17,
}

An example of the tsvector is in my grpc project for rust which shows an example of implementing FromSql and ToSql here