Help Needed with `sqlx::query!` Macro and `DATABASE_URL` in a Rust Discord Bot

[gmifflen]

Hello everyone,

I'm currently working on a Discord bot using Rust and have encountered an issue with the sqlx crate, specifically with the sqlx::query! macro. Despite setting the DATABASE_URL in my .env file, I'm still facing an error related to this macro. Here's a brief overview of my situation:

Issue:
When compiling my bot, I receive the following error:

error: set `DATABASE_URL` to use query macros online, or run `cargo sqlx prepare` to update the query cache
  --> src\main.rs:41:9
   |
41 | /         sqlx::query!(
42 | |             "INSERT INTO user_xp (user_id, xp, level) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE xp = xp + ?, level = ?",
43 | |             user_id,
44 | |             xp_gain,
...  |
47 | |             new_level,
48 | |         )
   | |_________^

What I've Tried:

1. Ensuring the .env file is correctly placed and formatted with the DATABASE_URL environment variable.
2. Directly embedding the database URL into the code (for troubleshooting purposes).
3. Running cargo sqlx prepare to update the query cache, but recieved the following warning:

error: no such command: `sqlx`

Did you mean `fix`?

View all installed commands with `cargo --list`

Despite these attempts, the error persists. The strange part is that another environment variable (DISCORD_TOKEN) is being read correctly from the same .env file.

Code Context:
Here's a snippet of the code where the error occurs:

sqlx::query!(
    "INSERT INTO user_xp (user_id, xp, level) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE xp = xp + ?, level = ?",
    user_id,
    xp_gain,
    new_level,
    xp_gain,
    new_level,
)
.execute(pool)
.await
.unwrap();

Environment Details:

• Rust version: rustc 1.74.0 (79e9716c9 2023-11-13)
• Database: MySQL
  Dependencies:
• serenity = { version = "0.12.0", features = ["full"] }
• tokio = { version = "1", features = ["full"] }
• rand = "0.8.4"
• sqlx = { version = "0.7", features = ["mysql", "runtime-tokio", "tls-rustls"] }
• dotenv = "0.15.0"

I would greatly appreciate any insights or suggestions on how to resolve this issue. It's puzzling why the DATABASE_URL isn't being recognized for the sqlx::query! macro, despite seemingly correct setup and other environment variables working fine, maybe I'm just overlooking something dumb.

My full code can be found on my GitHub page here.

Thank you in advance for your help!

[HJVT]

@gmifflen

Running cargo sqlx prepare to update the query cache, but recieved the following warning:

Have you installed the sqlx-cli tool?

error: set DATABASE_URL to use query macros online, or run cargo sqlx prepare to update the query cache

What this talks about is that this env var has to be set at compile time.

[behai]

Hi @gmifflen,

I did try to compile your code by copying down the content of main.rs and Cargo.toml.

I create the table user-xp as follows:

CREATE TABLE `user_xp` (
  `user_id` bigint unsigned NOT NULL,
  `xp` int unsigned DEFAULT NULL,
  `level` int unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

My Rust version is rustc 1.74.1 (a28077b28 2023-12-04) on Windows 10.

The only compile error I have is:

error[E0507]: cannot move out of a shared reference
  --> src\main.rs:65:20
   |
65 |         let pool = data_read.get::<MySqlPoolContainer>().unwrap().pool;
   |                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ move occurs because value has type `Pool<MySql>`, which does not implement the `Copy` trait
   |
help: consider borrowing here
   |
65 |         let pool = &data_read.get::<MySqlPoolContainer>().unwrap().pool;
   |                    +

For more information about this error, try `rustc --explain E0507`.
error: could not compile `learn-actix-web` (bin "learn-actix-web") due to previous error

Following the compiler suggestion, I compile it with no problem.

When I try to run, I got the error:

thread 'main' panicked at src\main.rs:124:21:
Could not access the bot id: Http(UnsuccessfulRequest(ErrorResponse { status_code: 401, url: "https://discord.com/api/v10/users/@me", method: GET, error: DiscordJsonError { code: 0, message: "401: Unauthorized", errors: [] } }))
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

My .env file is:

#-- MAX_CONNECTIONS=15
DATABASE_URL="mysql://root:pcb.2176310315865259@localhost:3306/employees"
DISCORD_TOKEN="bunch-o-numbers"
# DATABASE_URL=postgres://postgres:pcb.2176310315865259@localhost:5432/employees?schema=employees

# ALLOWED_ORIGIN=https://www.rust-lang.org
# ALLOWED_ORIGIN=http://kubernetes.docker.internal
#-- ALLOWED_ORIGIN=http://localhost:5000
#-- MAX_AGE=3600

( I created the user_xp inside the employees database. )

...
#[tokio::main]
async fn main() {
    dotenv::dotenv().ok();
    let database_url = env::var("DATABASE_URL").expect("Expected DATABASE_URL in the environment"); 
	
    println!("database_url {}", database_url);
...	

The application reads DATABASE_URL just fine, the correct value gets printed out.

Best regards,

...behai.

[behai]

@gmifflen

cargo sqlx prepare

Hi @gmifflen,

I do have SQLx CLI installed already.

Running cargo sqlx prepare:

F:\rust\actix_web>cargo sqlx prepare
    Checking learn-actix-web v0.1.0 (F:\rust\actix_web)
    Finished dev [unoptimized + debuginfo] target(s) in 0.95s
query data written to .sqlx in the current directory; please check this into version control

New directory .sqlx sits directly under where we ran cargo sqlx prepare.

Inside it, there is a file query-464543f359aa00eea7931926b5f358243ac7a3d083009c0d982f1ca8ec4c341a.json, whose content is:

{
  "db_name": "MySQL",
  "query": "INSERT INTO user_xp (user_id, xp, level) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE xp = xp + ?, level = ?",
  "describe": {
    "columns": [],
    "parameters": {
      "Right": 5
    },
    "nullable": []
  },
  "hash": "464543f359aa00eea7931926b5f358243ac7a3d083009c0d982f1ca8ec4c341a"
}

Best regards,

...behai.

[gmifflen]

Dear @behai and @HJVT,

I just wanted to express my gratitude for your assistance.

Behai, your detailed exploration of the problem, especially with the user_xp table, and the .env file setup, was extremely helpful. Also, I really appreciated the #-- MAX_CONNECTIONS=15 line in your .env file example – it's a neat addition that I plan to incorporate into my setup.

HJVT, your suggestion to check if I had installed the sqlx-cli tool was spot on. It turned out that I hadn't installed it, and that, combined with my goof of the .env file being in the wrong directory (~/src), was causing the issues.

Thanks to both of you, I was able to get everything working correctly.

Warm regards,
gmifflen