Compatible with i64 and BigIntegerUnsigned

[boringplay]

Is your feature request related to a problem? Please describe.

A field type is changed from BigInteger to BigIntegerUnsigned in db schemas, but the Rust type (i64) isn't changed to u64. Then it will cause a ColumnDecode Error (Rust type core::option::Option<i64> (as SQL type BIGINT) is not compatible with SQL type BIGINT UNSIGNED).

Is it possible to be compatible with i64 and BigIntegerUnsigned so that these db schema changes won't cause online bugs?

Describe the solution you'd like

Make it compatible with signed types and unsigned types.

Describe alternatives you've considered

Additional context

[CommanderStorm]

Casting a u64 to a i64 would loose precision (it can contain values which are farther postive than i64) and is thus not okay to do for the library.

The error is correct as far as I see it. Am I missing something?

[kyku]

I don't know if it's the same issue but why can't I get "BIGINT UNSIGNED" as u64 while I can as i64:

"mismatched types; Rust type u64 (as SQL type BIGINT UNSIGNED) is not compatible with SQL type BIGINT"

sqlx version is 0.8.2

[boringplay]

For example, there is a BigInteger field in an online database and the Rust type in code is i64. Anyway, this field needs to be changed to BigIntegerUnsigned. Once this change is done, the online Rust code will cause this ColumnDecode error.

[CommanderStorm]

can't I get "BIGINT UNSIGNED" as u64

According to the error message you are trying to use a BIGINT as an u64.

Could you please share your code after verifying that your column is indeed unsigned?

[boringplay]

In my case, it's:

Rust type core::option::Option<i64> (as SQL type BIGINT) is not compatible with SQL type BIGINT UNSIGNED

Some code like:

#[derive(Debug, FromRow)]
pub struct User {
    id: i8,
    name: String,
}

async main() {
   // ...
   let users: Vec<User> = sqlx::query_as::<_, User>("SELECT id, name FROM users")
     .fetch_all(&pool)
     .await?;
}

db schema before:

CREATE TABLE
  `users` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'primary key',
    `name` VARCHAR(255) NOT NULL COMMENT 'name',
    PRIMARY KEY (`id`)
) CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

db schema after:

CREATE TABLE
  `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primary key',
    `name` VARCHAR(255) NOT NULL COMMENT 'name',
    PRIMARY KEY (`id`)
) CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

[boringplay]

Maybe it's better to make it compatible with unsigned and signed types. So that there is no need to change the Rust type when the database schema is changed from unsigned to signed or from signed to unsigned. As the type change in Rust code may cause a lot of changes.

It can throw an error in some cases that a u64 value can't be cast to an i64 value or a u64 value can't be cast to an i64 value.

[CommanderStorm]

no need to change the Rust type when the database schema is changed from unsigned to signed or from signed to unsigned

But there is a need to change the type

• u8 can hold integers in 0..=255
• i8 can hold integers in -128..=127

Mariadb/MySQL value ranges also differ between signedness.

[abonander]

Closing as wontfix. SQLx is specifically designed to encourage the use of the most appropriate type mappings. Weakening this would defeat the whole point of the type-checking in the first place.