Support multiple types of DBs at once

[Snapstromegon]

This PR is meant as a starting point and baseline for discussions.

As of right now, this implementation does not work. The query!() macros don't select the correct DB to expand to. I'm looking into this, but I have no experience working on this crate, so support is very welcome.

It implements support for multiple types of DBs by using mutliple "DATABASEURL*" environment variables, falling back to the current "DATABASE_URL" variable.

This doesn't allow using multiple DBs of the same type, but two different DBs as long as they don't share a driver (so e.g. Postgres and SQLite).

fixes #121

[Snapstromegon]

This PR right now only holds a rough draft. I don't have much experience workling with rust macros, so I chose the easy way for some first discussions.

Right now I only implmented the query! macro, but the others should work the same way. It also uses a stringly typing for the selection of the DB driver - I don't like that, but wasn't able to get the procedural macros to work with actual types.

How this currently looks like:

query!(
    "SQLite",
    "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)"
).execute(&sqlite).await.unwrap();
let row = query!("PostgreSQL", "SELECT 1 as ID").fetch_one(&pg).await.unwrap();

If you now set the environment variables DATABASE_URL_SQLITE and DATABASE_URL_POSTGRES, both queries will get checked at compile time and work at once.

If you have ideas for improving this, please let me know.

[esmevane]

This is cool to see!

I'm wondering two things about the DATABASE_URL_DRIVER format. I don't want to make it sound like I'm suggesting you do anything differently, I'm mostly just curious and wondered if maybe you've considered these approaches to the design already:

• Is it possible to get any DATABASE_URL_* string from the environment? Say by pulling up the environment as a whole and just plucking matches?
• Is it possible to infer the driver via the protocol? mysql:// or postgres:// or sqlite:// etc. In other words, let the interior of the env var describe the kind of DB, vs. the var name? (I think this might already be something sqlx does?)

The reason I ask these is because I bet you if these things are possible, it would then be possible to allow the caller define any variables they like, even having multiple databases with the same driver. I.E., DATABASE_URL_MEMORY and DATABASE_URL_CACHE, both as sqlite.

[Snapstromegon]

• Is it possible to get any DATABASE_URL_* string from the environment? Say by pulling up the environment as a whole and just plucking matches?

Yes, this is totally possible and I want to implement this in the future. It should even be possible to infer the actual expected varaible at compiletime via the type of the database driver (via Database::NAME). Finding some wildcard solution is also important to allow for supporting third party implemented DB drivers (e.g. for DuckDB or something similar).

• Is it possible to infer the driver via the protocol? mysql:// or postgres:// or sqlite:// etc. In other words, let the interior of the env var describe the kind of DB, vs. the var name? (I think this might already be something sqlx does?)

Also this is possible and I thought about this too. Right now this PR already kind of does this, because the names are only for differntiating and the code doesn't check that a DATABASE_URL_POSTGRES actually holds a postgres URL. You could just as well (using the patternmatching from point 1) use DATABASE_URL_FILESYSTEM and DATABASE_URL_SERVER for e.g. sqlite and postgres. When resolving the type to an URL it does exactly this check to test if any of the vars contains a URL that matches the DB supported schemas.

The reason I ask these is because I bet you if these things are possible, it would then be possible to allow the caller define any variables they like, even having multiple databases with the same driver. I.E., DATABASE_URL_MEMORY and DATABASE_URL_CACHE, both as sqlite.

Sadly IMO this will not be possible (at least as I implemented it right now), because it will always use the first URL that matches the scheme for the DB when resolving the URL, so you can't have two sqlite DBs.

IMO the most urgent usecase is supporting multiple types of DBs (as this is my biggest painpoint). Maybe we can find a way to support multiple DBs of the same type at a later point in time.

[Snapstromegon]

I'm working on a version that uses types instead of strings for selecting the DB backend here: https://github.com/Snapstromegon/sqlx/pull/1

I'm still running into an issue when multiple backends are compatible with a query and any help is welcome.

[Snapstromegon]

Hi all, I just merged my implementation for a type driven query provider selection.

With this change you can use the query! macro like this:

query!(
  Sqlite,
  "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)"
).execute(&sqlite).await.unwrap();
query!(
  Postgres,
  "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)"
).execute(&pg).await.unwrap();

I only implemented the declarative macro rule changes for query!, but it should work basically the same for all other macros.

I'd love to have some feedback from someone related to the project about what would need to be added to this PR so a feature like this can be merged.