Generate struct from query statement

[oOBoomberOo]

While FromRow and query!() have been a huge improvement for ensuring the type-correctness with the database. I started noticing a pattern emerging in my code that works in the opposite direction of FromRow, so I'd like to see what you think of this.

sqlx::data_source is a macro that generates a named struct that contains fields of what the specified query statement returns. You can think of it as the opposite of anonymous structs generated by the query!() macro.

While FromRow defines a way to map the database values into an existing struct, this macro defines a new struct that maps into the database values.

Here's some code example to express my idea:

/* schema */
create table users (
  id serial primary key,
  name text not null,
  age int,
  address text,
  created_at timestamp not null default now()
);

create table transactions (
  id bigserial primary key,
  sender int references users(id),
  receiver int references users(id),
  amount int not null,
  description text,
  created_at timestamp not null default now()
);

#[derive(sqlx::data_source("select * from users"))]
struct User;
// expanded to
struct User {
  id: i32,
  name: String,
  age: Option<i32>,
  address: Option<String>,
  created_at: DateTime
}

// you can also select specific columns
#[derive(sqlx::data_source("select id, address from users where address is not null"))
struct UserAddress;
// expanded to
struct UserAddress {
  id: i32,
  address: String
}

// or joins?
#[derive(sqlx::data_source("select users.id, name, receiver, amount, sum(amount) as total from users, transactions where users.id = transactions.sender and users.id = $1"))
struct UserTransaction;
// expanded to
struct UserTransaction {
  // from `users`
  id: i32,
  name: String,
  // from `transactions`
  receiver: i32,
  amount: i32,
  // aggregate
  total: i32
}

I think this makes it easy to keep the structs consistent with the table, allows you to reuse queries, and unlike anonymous structs, the type can be used in function parameters and return types.

You could even store the query in a trait and make the struct 'executable', e.g.

// query_with!() is a hypothetical macro that executes a query using the query string defined by the struct plus some predicate.
let user_above_30s = query_with!(Users, "where age >= 30").await;

// if the sqlx::data_source macro can statically determine how many parameters the query needs,
// then it can even generate a method to execute the query accordingly.
let specific_user_transactions = UserTransaction.execute(42).await;