sqlx

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.

• cat migrations/FILENAME.sql | openssl dgst -sha384 | cut -d ' ' -f 2

• update _sqlx_migrations 
  set checksum='\x<the checksum>'
  where version ='<the version>';