How should one use `sqlx` with `Newtype` pattern?

[teenjuna]

Hello there!

So, here is the problem: I'm trying to use sqlx crate, but having a hard type with Newtype pattern. Consider this types:

/// Text is a `String` with additional invariants
/// (which are not shown in this example).
#[derive(sqlx::Type)]
#[repr(transparent)]
pub struct Text(String);

/// Texts is a `Vec<Text>` with additional invariants
/// (which are also not shown in this example).
#[derive(sqlx::Type)]
#[repr(transparent)]
pub struct Texts(Vec<Text>);

/// This is the type I want to store in a database.
#[derive(sqlx::FromRow)]
pub struct Article {
    pub id: i16,
    pub body: Text,
    pub notes: Texts,
}

So, now I want to add an Article to a postgres database with the following schema:

CREATE TABLE IF NOT EXISTS articles (
    id    smallserial PRIMARY KEY,
    body  text        NOT NULL,
    notes text[]      NOT NULL
);

But when I execute this line:

let article: Article = sqlx::query_as(
    "
     INSERT INTO articles (body, notes)
     VALUES ($1, $2)
     RETURNING *
    ",
)
.bind(body /* Text */)
.bind(notes /* Texts */)
.fetch_one(&pool)
.await?;

I get this error:

TypeNotFound { type_name: "Texts" }

To avoid this, I need to change .bind(notes) to this:

.bind(
    &notes
        .0
        .iter()
        .map(|text| text.0.clone())
        .collect::<Vec<String>>()
)

When I want to get the values from the database, I can't just do this:

let article: Article = sqlx::query_as("SELECT * FROM articles")
    .fetch_one(&pool)
    .unwrap();

Because I get error:

ColumnDecode { index: "\"notes\"", source: "mismatched types; Rust type `Texts` (as SQL type `Texts`) is not compatible with SQL type `TEXT[]`" }'

For this to work, I need to replace #[derive(sqlx::FromRow)] on the Article struct to a manual implementation:

impl<'r> sqlx::FromRow<'r, PgRow> for Article {
    fn from_row(row: &'r PgRow) -> Result<Self, sqlx::Error> {
        Ok(Article {
            id: row.try_get("id")?,
            body: row.try_get("body")?,
            notes: Texts(
                row
                .try_get::<Vec<String>, _>("notes")?
                .into_iter()
                .map(|s| Text(s))
                .collect::<Vec<Text>>()
            ),
        })
    }
}

Am I doing something wrong or is it not possible right now to do such things automatically?

PS: after writing this post and reading it from top to bottom, I guess I understand that I should create type Texts in my database schema, like this:

CREATE DOMAIN texts AS text[];

CREATE TABLE IF NOT EXISTS articles (
    id    smallserial PRIMARY KEY,
    body  text        NOT NULL,
    notes texts       NOT NULL
);

And modify the Rust code:

#[derive(sqlx::Type)]
#[repr(transparent)]
#[sqlx(type_name = "texts")]
pub struct Texts(Vec<Text>);

#[derive(sqlx::FromRow)]
pub struct Article {
    pub id: i16,
    pub body: Text,
    pub notes: Texts,
}

But it doesn't really solve the problem. When I'm trying to run this code:

let article: Article = sqlx::query_as(
    "
     INSERT INTO articles (body, notes)
     VALUES ($1, $2)
     RETURNING *
    ",
)
.bind(body /* Text */)
.bind(notes /* Texts */)
.fetch_one(&pool)
.await?;

I get his error:

ColumnDecode { index: "\"notes\"", source: "mismatched types; Rust type `Texts` (as SQL type `texts`) is not compatible with SQL type `TEXT[]`" }

However, even if it did solve this problem, I'm really just interested in encoding and decoding my newtypes into native postgres types. Can I do this?

[teenjuna]

I solved my problem with manual trait implementations:

#[derive(sqlx::Decode, sqlx::Encode)]
pub struct Text(String);

impl sqlx::Type<sqlx::Postgres> for Text {
    fn type_info() -> <sqlx::Postgres as sqlx::Database>::TypeInfo {
        <String as sqlx::Type<sqlx::Postgres>>::type_info()
    }
}

impl sqlx::PgHasArrayType for Text {
    fn array_type_info() -> sqlx::postgres::PgTypeInfo {
       <String as sqlx::PgHasArrayType>::array_type_info()
    }
}

#[derive(sqlx::Decode, sqlx::Encode)]
pub struct Texts(Vec<Text>);

impl sqlx::Type<sqlx::Postgres> for Texts {
    fn type_info() -> <sqlx::Postgres as sqlx::Database>::TypeInfo {
        <Vec<Text> as sqlx::Type<sqlx::Postgres>>::type_info()
    }
}

Now Text is the same as String, and Texts is the same as Vec<String>.