sqlx

Written — Updated
  • Deserializing JSON

    • Custom Types

      • This is largely unintuitive if you don't want to go through a serde_json::Value. The trick is to rename the column in the query to include type information, which sqlx will use to
      • #[derive(Serialize, Deserialize)]
        pub struct SomeJsonField {
            // Whatever fields match the JSON structure
            pub name: String,
            pub some_param: Option<String>,
            pub count: i32,
        }
        
        sqlx_json_decode!(SomeJsonField);
        
        #[derive(sqlx::FromRow)]
        pub struct QueryResult {
            pub id: i32,
            pub name: String,
            pub params: sqlx::types::Json<SomeJsonField>,
        }
        
        async fn do_query(pool: &PgPool) {
          let result = sqlx::query_as!(
            QueryResult,
            r##"SELECT id,
                name,
                params as "params: SomeJsonField"
              FROM some_table"##,
        	).fetch_one(&pool).await?;
        }
        
      • I also have a crate sqlx-transparent-json-decode, that provides a macro to implement the Decode trait for JSON columns for any structure that implements Deserialize.
      • With this crate, you can import the sqlx_json_decode macro, then add sqlx_json_decode!(SomeJsonField) to the above code, and then you won't have to wrap the type in sqlx::types::Json anymore. This can be convenient when using one of these structures for both database results and other purposes.
    • Box<RawValue>

      • Sometimes you just want to get the raw JSON out of a field without needing to create a bunch of objects from it. For example, a web server that is passing the JSON straight down to the client without even looking at it. serde_json provides the RawValue type for this, but it is a reference type, and so needs to be wrapped in a Box or something to be usable once the database connection is released.
      • sqlx provides an implementation to decode a &RawValue, but not a Box<RawValue>, and so the sqlx-transparent-json-decode crate also offers a wrapper type around Box<RawValue> that implements Decode.
  • Migrations

    • To update the checksum of a migration when the file changed, you can recalculate the hash
      • cat migrations/FILENAME.sql | openssl dgst -sha384 | cut -d ' ' -f 2
    • Then change the checksum for that row in the database:
      • update _sqlx_migrations 
        set checksum='\x<the checksum>'
        where version ='<the version>';
        

Thanks for reading! If you have any questions or comments, please send me a note on Twitter.

Please also consider subscribing to my weekly-ish newsletter, where I write short essays, announce new articles, and share other interesting things I've found.