What's the best way of getting DB connection in tests?

[bubelov]

I'm working on a simple API as an exercise in Rust and Rocket, and I have some issues with figuring out the best way to test my endpoints. Here is my setup and intentions:

1. The server uses SQLite for storing data (rusqlite via fairing)
2. I want to initialize an empty DB for every test and run all migrations in order to get to the latest schema every time (attaching migration code to AdHoc::on_ignite seem to do the trick)
3. I want to fill this database with some data which is going to be used by endpoints under test (here is my main problem)
4. I want to call those endpoints and make sure the resulting JSON and HTTP codes match my expectations (the documentation is pretty clear on that, so no problems here)

Nothing fancy, just the usual test workflow. Here are my problems so far:

1. Tests are synchronous (at least by default, and it seem to be a recommended mode). I found no interfaces to get a DB connection synchronously from a Rocket instance. I need a DB connection in order to bring the database to the desired state before calling endpoints under tests
2. It would be nice to use an in-memory database for tests and real database for debug and release builds. It looks like configuration only supports debug and release profiles. What's the best way to implement a test profile or to make sure tests always use in-memory database?

[SergioBenitez]

See the todo example, which effectively serializes tests that deal with the database. While unfortunate, in practice tests still run plenty fast. The alternative would be to support transactional testing. That's being tracked in #1197.

2. I want to initialize an empty DB for every test and run all migrations in order to get to the latest schema every time (attaching migration code to `AdHoc`::`on_ignite` seem to do the trick)

That's the best approach. See the databases example for the most idiomatic way to accomplish this.

1. Tests are synchronous (at least by default, and it seem to be a recommended mode). I found no interfaces to get a DB connection synchronously from a Rocket instance. I need a DB connection in order to bring the database to the desired state before calling endpoints under tests

Do you mean Rust's #[test]? If so, yes, they're synchronous by default. Rocket provides a client that hides async and one that doesn't. If you need to do "visible" I/O, like accessing the database, you must use the async Client. There's no way around it. Again, see the todo example.

2. It would be nice to use an in-memory database for tests and real database for debug and release builds. It looks like configuration only supports `debug` and `release` profiles. What's the best way to implement a `test` profile or to make sure tests always use in-memory database?

Where did you get the idea that only two profiles are supported? Have you read through the configuration guide? You can have any number of profiles with any name.

[jebrosen]

When I introduced db variable in that test, rocket started to ignore migrations AdHoc and my query started to fail with table not found error.

Can you try adding some printlns to your AdHoc fairing to help confirm that this is definitely the issue, or confirm from the log output that the fairing is no longer being attached or run? Many, many other examples and applications do exactly this without any problems.

[bubelov]

@jebrosen thank you for your suggestions, it looks like Rocket applies migrations with no problems. I started looking for alternative explanations and pinned it down to a database URL. This test passes if I use file-based URL but it fails if I use an in-memory database (url = ":memory:"). I double-checked the syntax and I'm pretty sure :memory: is a correct way to request an in-memory database. I suspect that somehow it creates more than one database. That would explain why migrations are passing but the other connection handle can't find the table at the later stage. Any ideas?

Here is the full source code:
https://github.com/bubelov/rocket-rates

Running cargo test fails but if you change Rocket.toml to use file-based database it will pass

[jebrosen]

I double-checked the syntax and I'm pretty sure :memory: is a correct way to request an in-memory database. I suspect that somehow it creates more than one database. That would explain why migrations are passing but the other connection handle can't find the table at the later stage. Any ideas?

Yes, this is essentially "how SQLite works". Each connection to :memory: is a new database by default. there is no reasonable way to use the :memory: database in combination with connection pooling. (see the next answer; apparently there is an alternative to make this work.)

You're not the first person to have this problem, though. Is there anywhere in the documentation where we could have placed a warning about this? Where did you learn about using :memory: in the first place?

[SergioBenitez]

Please read the SQLite docs on in-memory databases: https://www.sqlite.org/inmemorydb.html.

In-memory databases are allowed to use shared cache if they are opened using a URI filename. If the unadorned ":memory:" name is used to specify the in-memory database, then that database always has a private cache and is this only visible to the database connection that originally opened it. However, the same in-memory database can be opened by two or more database connections as follows:

rc = sqlite3_open("file::memory:?cache=shared", &db).

[bubelov]

@jebrosen @SergioBenitez TIL, thanks for your feedback. This behavior is surprising but it makes total sense from a low-level SQLite point of view since it can't really make any assumptions about what we're trying to achieve. I'm not sure if this argument holds if we're talking about a DB pool. The whole idea of pooling connections is to speed up a single DB, isn't it? Are there sane real-world use cases where we need a pool with one to one connection to database ratio, filled with a bunch of databases?

I'm willing to contribute to documentation if changing defaults is not an option, but IMO it's worth considering since higher-level things may be expected to have different defaults.

[bubelov]

Answering my own question:

The cleanest way of getting a DB connection is to do it directly, before even touching Rocket. Getting a database via Rocket involves async code and anonymous functions, which complicates things quite a bit for no good reason. It's better to connect to your DB directly via the base crate under your DB pool (e.g. rusqlite), and then let Rocket do what it usually does with no extra tweaks. That way, tests can keep using blocking API and avoid anonymous functions and their side effects.

Here is an example:

#[test]
fn exchange_rates_controller_get() {
    let (client, mut db) = setup();

    let rate = ExchangeRate {
        base: "USD".to_string(),
        quote: "EUR".to_string(),
        rate: 1.25,
    };

    exchange_rates::insert(&mut db, &rate);

    let res = client.get("/exchange_rates?base=USD&quote=EUR").dispatch();

    assert_eq!(res.status(), Status::Ok);
    let body = res.into_json::<ExchangeRate>().unwrap();
    assert_eq!(rate, body);
}

PS. Beware that it's also desirable to clean the DB after every test somehow, so they won't interfere with each other. I'm not sure what's the best way of doing that. Simply dropping the DB after each test won't work either, because cargo is very pushy on executing tests in parallel. I'm yet to find a satisfying way of making all tests run in sequence. Passing the number of threads on every cargo test invocation is a deal breaker. There are some crates which force serial execution via per-test annotations but it adds a lot of boilerplate. An alternative option would be to generate an unique in-memory database per test, which may be the best option for SQLite, but I didn't try it yet (added: tried it, works like a charm, so there is no need to worry about concurrency and cleaning the database, it gets cleaned up automatically).