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.

[jplatte]

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.

Actually there is some movement towards integrating macro_rules! with regular import resolution; see rust-lang/rust#78166.

Wanted to link to that earlier but apparently it's virtually impossible to find with regular search engines, I only found it again now because I got a notification about a new comment.

[abonander]

It's been put to an RFC now so it's still going to take a while to get merged and then stabilized, but it's nice to see that it's possible. It sounds like they want to make it part of the 2021 Edition though.

As for this:

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.

Apparently it just works now so I'm not sure what funkiness I was remembering: https://play.rust-lang.org/?version=stable&mode=debug&edition=2018&gist=4de9983692e5894f174caf52b6c49191

[mehcode]

I think to reduce the syntax surface area I would be much more in favor of:

// all do the same, just bikeshedding
sqlx::macros!(foo, url = "env:DATABASE_URL");
sqlx::macros!(name: foo, url = "env:DATABASE_URL");
sqlx::macros!(prefix: foo, url = "env:DATABASE_URL");
sqlx::macros!(prefix = foo, url = "env:DATABASE_URL");

and used as:

foo_query!("SELECT ...");
foo_query_as!(Bar, "SELECT ...");

Yes this requires the macros to be in lib.rs or #[macro_use]'d near the top of the file, but I don't like a limitation like that will matter much to people that want this feature.

[Freyskeyd]

Hello

I'm facing the issue where I've multiple DB connections in the same crate. I would like to work on a solution, can we fixe one of the propose option as a base to iterate?

[abonander]

Sadly the pub_macro_rules feature was removed in rust-lang/rust#83713 so we're likely back to just supporting prefixes for the new macros as depicted in @mehcode's comment above.

[aaronmussig]

While this may not be a proper solution, it's a workaround that works in my case (as I have no duplicate table names, and my database schemas are not updated often), so maybe it will be of use to someone else.

Essentially I created a new database (C) that contains the schema of tables in databases A and B. The connection string to database C is then set in the .env file. Now all query! calls will check against the schema of C.

It's then up to me to make sure that the correct database worker pool (i.e. database A or B) is used when calling query!.

[nthnd]

I'm having this issue as well. wondering if there's any progress

[jssblck]

I'm working with multiple databases at the same time in a codebase, and this is a real issue- it's so unfortunate to have to give up 90% of what makes SQLx great to use (compile time queries) due to this.

Is this something you're looking for community help on?

[docwilco]

_with variants of the various query macros could be a thing. Would it be with name of the env var containing the URL? Or just the URL itself? Latter might be a bit more flexible for people who don't want to use a .env file.

[abonander]

The current plan is to use the sqlx.toml format I'm working on in #3383.

There will be a new proc-macro to define a new set of query!() and migrate!() macros at the call site:

sqlx::macros_with_config!("sqlx.foo.toml");

This will look for sqlx.foo.toml relative to the crate root and use it to configure the new macros, overriding any existing sqlx.toml config. The TOML file can be used to override the DATABASE_URL var, set default type overrides, change the table name for tracking migrations, etc.

Under the hood, this will just pass the file path to sqlx_macros::expand_query!(), resolved relative to the CARGO_MANIFEST_DIR for the crate that invoked macros_with_config!.

Additionally, it can support some control arguments:

sqlx::macros_with_config!(
    "sqlx.foo.toml",

    // Add a prefix to the macro names
    prefix = "foo_" // (`foo_query!()`, `foo_migrate!()`, etc.)

    // Add attributes to the generated macros 
    attr(macro_export), // (`#[macro_export]`)
    attr(cfg(feature = "foo")) // (`#[cfg(feature = "foo")]`),'

    // Any others we might want?
);

[AustinCase]

@abonander very excited about seeing some activity here. We were about to fork and see if we could get this working, obviously hoping to contribute back once we did -- great timing.

I was headed down the sqlx.toml path as well as I was documenting our plan -- that seemed to be the most straight forward and rust-analyzer friendly path.

Any help / insight myself or our team can provide to get this across the line, let us know!