Support multiple databases in the same crate (compile-time macros)

[mehcode]

# .env
FOO_DATABASE_URL="postgres://..."

Option 1

Inspired from the log crate and log!(target: _).

let rows = sqlx::query!(database: "foo", "SELECT * FROM bar WHERE x = ?val", val = 10)
    .fetch_all(&mut conn)?;

Option 2

#[sqlx::database(url = "env:FOO_DATABASE_URL")]
mod foo;

// [...]

let rows = foo::query!("SELECT * FROM bar WHERE x = ?val", val = 10)
    .fetch_all(&mut conn)?;

Option 3

mod foo {
    sqlx::database!(url = "env:FOO_DATABASE_URL");
}

// [...]

let rows = foo::query!("SELECT * FROM bar WHERE x = ?val", val = 10)
    .fetch_all(&mut conn)?;

---

That's all I can think of at the moment. I prefer Option 2.

This is a solution to #121 and #224.

Thoughts?

[abonander]

From a UX perspective, 3 is the friendliest to IDEs because it can be done with macro_rules! macros (can even be built on top of 1). Number 2 would not work at all with code-completion in IDEA. It's also non-local to the module file which I don't really like, although it could be the inner form #![sqlx::database(...)] but that would get lost above the imports in the kind of module that's typical of the projects we've been working on.

[mehcode]

Option 4

sqlx::database!(name = "foo", url = "env:FOO_DATABASE_URL");

// [...]

let rows = foo::query!("SELECT * FROM bar WHERE x = ?val", val = 10)
    .fetch_all(&mut conn)?;

---

To keep it 1-line (if that's important), we could inline the module creation within "Option 3".

[abonander]

I would think name = should be to say "use all the parameters from DATABASE_URL but change the database name to this".

What about

sqlx::database!(mod foo, name = "bar_test_db")

which declares a module foo and redefines the query macros to talk to bar_test_db with the same authority and params as DATABASE_URL.

[mehcode]

I like the idea of name being "use the same URL but change the name to this".

I don't like !(mod foo, as syntax.

I'd probably prefer the 3-line mod foo { ... } version over that.

# .env
DATABASE_URL="postgres://postgres@localhost/foo"
OTHER_DATABASE_URL="postgres://postgres@other/foo"

mod bar { sqlx::database!(name = "bar"); } // url is "postgres://postgres@localhost/bar"
mod other_foo { sqlx::database!(url = "env:OTHER_DATABASE_URL"); } // url is "postgres://postgres@other/foo"

[abonander]

name is really ambiguous anyway.

What about

sqlx::database!(mod = "foo", db = "bar")

[andrewwhitehead]

What else is it going to produce besides a mod, presumably pub(crate)?

sqlx::database!(foo, url = "env:FOO_DATABASE_URL");

Although if the user wants to put additional items in that module or adjust the visibility, the above macro could delegate to another one with both exposed:

mod foo { sqlx::database_impl!(url = "env:FOO_DATABASE_URL"); }

[mehcode]

What else is it going to produce besides a mod, presumably pub(crate)?

That's a really good reason for me to not generate the rust module at all. I hadn't considered visibility.

[abonander]

Yeah, that could get messy.

[abonander]

@mehcode I just realized we were already talking about doing this a long time ago, potentially with allowing other configuration to the macros such as specifying type overrides #121

We also have talked about a sqlx.toml but I'm still somewhat of the persuasion that we shouldn't have an external config file that changes the behavior of the macros as that's a non-local effect.

[abonander]

In talking about it, @mehcode and I settled on sqlx::macros!(), short and sweet.

[abonander]

Unfortunately, the whole idea with using sqlx::macros!() in two different modules so you could do, e.g. foo::query!() and bar::query!() isn't gonna work with macro_rules! as it exists in this day and age.

Even in the 2018 edition you still can't invoke a macro in the same crate via its fully-qualified path; you have to use #[macro_use] to import all macros from a submodule into the parent module and all child modules following the annotated submodule in declaration order. Presumably declarative macros 2.0 would let this work as expected as it actually participates in normal item resolution but I have no idea when that's going to be stabilized.

There's still some utility with sqlx::macros!() but using it with two separate databases at once would require either invoking it in separate crates or making sqlx::macros!() a proc-macro so it can append a user-defined prefix to the macro names, which as mentioned above would kill autocomplete for the macros at least with IntelliJ-Rust.

We could make it work like this, though:

sqlx::macros!(
    db_url[foo] = "env::DATABASE_FOO",
    db_url[bar] = "env::DATABASE_BAR",
    types[bar] = {
        "BAZ": crate::model::Baz,
    },
    // top-level option which applies to both `foo` and `bar`:
    types = {
        "CITEXT": String,
    }
);

// this matches how arguments are passed to the macros internally but can be changed
query!(db = foo, "select * from ...")

I'm thinking any configuration options we plan to add, we can have them optionally accept a database name as subscript, and then they only apply to that database. Otherwise, they apply to all databases.

It could also look like this, maybe (again, with top-level options applying to all databases):

sqlx::macros! (
    db.foo = {
        url: "env::DATABASE_FOO",
    },
    db.bar = {
        url: "env::DATABASE_BAR",
        types: {
            "BAZ": crate::model::Baz,
        }
    },
    types = {
        "CITEXT": String,

    }
);

Or combine both and make the subscript db[foo] = { ... }.

[abonander]

However, I'm also recalling some issues with macros-defining-macros where you want to use parameters from the outer macro invocation in the inner macro... things get weird.

Maybe it's time to dust off the sqlx.toml proposal.