Easier integration with tests

[james-w]

Hi, thanks for building this library, it's important and often overlooked.

I'd like to discuss if there's a way to improve the integration with tests. Using #[sqlx::test] you can get a connection, a pool or connection info to build your own pool.

The flow will often be:

• Setup the database
• Put some test data into the database
• Make a request
• Check the response and/or the database state depending on the purpose of the test

This is possible with the current library, but I wonder if it could be improved.

• A pool is needed to setup Tx, even though only one connection will be used
• The pool is moved by setup so it's not so easy to acquire a connection to issue the queries
• I'd be interested in running the test inside a transaction, possibly with nested transactions, as it may make tests quicker or some tests easier, though I would have to experiment.

I wonder if there could be a couple of extra options for the state/layer:

• Provide a connection rather than a pool
• Provide a transaction and avoid the resolve at the end of the request

[connec]

Interesting ideas, and in general I'd be up for making improvements that aid testability.

Could you possibly provide some example test code using the API(s) you are thinking of? That would help me understand your use-case and quickstart an exploration if I find some time.

[james-w]

Sure thing, this is pretty rough, but hopefully gives you the idea. If not then please let me know and I'll flesh it out a bit more

#[sqlx::test]
async fn test_with_conn(conn: PoolConnection<DB>) {

  let (state, layer) = Tx<DB>::setup_with_conn(conn);
  let app = router.layer(layer)...
}

This would work the same, but not get a connection from the pool, just use the provided one.

This feels lighter, but I don't know if there's a practical difference really.

#[sqlx::test]
async fn test_with_tx(conn: PoolConnection<DB>) {

  let mut tx = conn.begin().await.expect("Failed to start transaction");

  create_user(&mut tx, ...);
  do_other_stuff_with_db(&mut tx, ...);

  let (state, layer) = Tx<DB>::setup_with_tx(tx);
  let app = router.layer(layer)...

  send_request();

  check_db_state(&mut tx, ...);
}

This would re-use the transaction for any Tx extractors. It would presumably not do any begin/commit calls.

This may allow for not resetting the db after each test (with a different setup function than sqlx::test).

I tried to hack something together as a PoC, but I couldn't make it work.

I don't know if all of this is worth the effort, but maybe with your experience you can see if this would be helpful in any way.

Thanks!

[connec]

Thanks, I think I understand the goal with the 2nd one, to allow the transaction to roll back when the test finishes. I do think that's a useful technique so I'll look into supporting it at some point.

I'm less sure about the 1st (as compared to, e.g., using a pool with a single connection – perhaps what sqlx::test already provides though I haven't checked). If it falls out as part of the above then I'd include it for completeness.

I likely won't have time to look into it for a couple of weeks though.

[james-w]

No worries, thanks. I’m not waiting on this as I have something that works.