sqlx

Executor Trait

• For functions that just make a single call, you can use the Executor trait (here, PgExecutor which is an alias for the Postgres version)

• async fn do_query(db: impl PgExecutor<'_>) -> Result<Vec<Stuff>, sqlx::Error> {
  	sqlx::query_as!(Stuff, "SELECT * FROM stuff")
        .fetch_all(db)
        .await
  }
  

• The main issue here is that Rust doesn't easily have a way to pass a reference to that impl PgExecutor object that it also thinks will implement PgExecutor. Passing it once passes ownership and you can't use it again. Still this works fine for any function that only makes one database query, and provides the simples experience to the caller.

Dereferencing to Connection

• This method requires you to manually acquire a connection from a pool first, but it does allow you to call a function with either a plain connection or a transaction.
• Here, you need to use &mut *conn any time you use the connection. This looks a bit weird, but takes advantage of the fact that all the relevant objects implement DerefMut to allow getting a reference to the Connection.

• async fn do_query(db: &mut PgConnection) -> Result<Vec<Stuff>, sqlx::Error> {
      sqlx::query!("DELETE FROM stuff WHERE NOT active")
    		.execute(&mut *db)
    		.await?;
  	
  	sqlx::query_as!(Stuff, "SELECT * FROM stuff")
        .fetch_all(&mut *db)
        .await
  }
  
  async fn call_it(db: sqlx::PgPool) {
    let tx = db.begin().await?;
    // Get the underlying connection from the transaction and pass it in.
    do_query(&mut *tx).await?;
    tx.commit().await
  }
  

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>';
  

• The prepare command handles this, mostly. You need to run cargo clean -p DEP_CRATE before every time your run cargo sqlx prepare for it to properly find all the queries.
• With the default settings, the build process will not find the offline data when building the dependency. To fix that, set the SQLX_OFFLINE_DIR environment variable to the location of the .sqlx directory.