I ran into this myself. I'm not sure about bit(1), but for tinyint(1) It looks like this crate can convert it to a rust bool, but by default wants to use an i8. You can coerce it to a bool by altering your query like this (if has_subscript was a tinyint(1):
SELECT
info_id,
open_id,
has_subscript as `has_subscript: _`, -- or `has_subscript: bool`
sub_time
FROM wx_user_info where info_id = ?
There are details on the docs for query!: https://docs.rs/sqlx/0.4.1/sqlx/macro.query.html#force-a-differentcustom-type
For bit(1) you might have implement Decode for a custom type
If possible, I think it would make sense if you could instruct sqlx to use a custom type when defining a struct deriving FromRow (maybe using a field attribute?).
Allowing that would mean you wouldn't need to duplicate the type override in every single query returning that struct, and you also wouldn't need to enumerate every column in queries fetching all columns (mentioned by the previous comment).
Just ran into this problem and I have to say - it's really frustrating.
The "accepted" workaround becomes a big problem when you start to have a lot of bool fields:
use serde::{Deserialize, Serialize};
use sqlx::{query_as, MySqlPool};
#[derive(Serialize, Deserialize, Debug)]
pub(crate) struct DbAccessRole {
id: u8,
name: String,
title: String,
// General
can_log_in: bool,
can_queue_for_matchmaking: bool,
// User Moderation
can_view_users: bool,
can_manage_users: bool,
can_ban_users: bool,
// Match Outcome Disputes
can_view_disputes: bool,
can_assist_disputes: bool,
can_resolve_disputes: bool,
// System Administration
can_view_system: bool,
can_modify_system: bool,
impl DbAccessRole {
pub async fn all(db: &MySqlPool) -> sqlx::Result<Vec<DbAccessRole>> {
query_as!(DbAccessRole, "SELECT * FROM `access_role`",)
.fetch_all(db)
.await
pub async fn by_name(db: &MySqlPool, name: &String) -> sqlx::Result<Option<DbAccessRole>> {
query_as!(
DbAccessRole,
"SELECT * FROM `access_role` WHERE `name` = ?",
.fetch_optional(db)
.await
Each one of these bool fields is mapped as a tinyint(1) in the MySQL database schema, and I get compile errors for query_as!(...):
error[E0277]: the trait bound `bool: From<i8>` is not satisfied
--> src\db\access_role.rs:31:9
31 | query_as!(DbAccessRole, "SELECT * FROM `access_role`",)
| ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ the trait `From<i8>` is not implemented for `bool`
= help: the trait `From<subtle::Choice>` is implemented for `bool`
= note: required for `i8` to implement `Into<bool>`
Ideally, sqlx should know that the MySQL engine converts all bit, bool, and boolean types to tinyint and provide a seamless conversion if the type is specified as bool in the Rust code.
If the sqlx team doesn't want to break the explicit type safety, then there should be a field attribute to override/specify the database type that it should type-check against and convert from:
pub(crate) struct DbAccessRole {
id: u8,
name: String,
#[sqlx(type = u8)]
can_log_in: bool,
Is that an acceptable solution for the library given that it excludes support for the SELECT * syntax? It's cumbersome to have to duplicate the entire list of columns during decode.
Off the top of your head, would it be a significant amount of work to allow a field attribute to specify the type override in the struct definition like I suggested?
