Rust axum sqlx postgres, separate vars in .env in database connection

help
1

Hello, i can't figure this out myself. In short i would like to replace:
DATABASE_URL=postgres://postgres:admin@localhost:5432/postgres

to:

DB_HOST=localhost
DB_PORT=5432
DB_DATABASE=postgres
DB_USERNAME=postgres
DB_PASSWORD=admin
DB_SCHEMA=web

also need to specify schema in connection as i use either web or api schema, not default public.

My db.rs

use sqlx::{PgPool, Pool, Postgres};
use std::env;

pub async fn init_pool() -> Pool<Postgres> {
    let database_url =
        env::var("DATABASE_URL").expect("DATABASE_URL must be set in the environment");
    PgPool::connect(&database_url)
        .await
        .expect("Failed to create database pool")
}

and in main.rs

    let pool = db::init_pool().await;

    let app = Router::new()
    ...routes here...
   .with_state(pool);
2

You can construct PgConnectOptions from your environment variables and pass that to PgPool::connect_with to establish a connection to your database.

3

I did change to this

PGHOST=localhost
PGPORT=5432
PGUSER=postgres
PGPASSWORD=admin
PGDATABASE=postgres

use sqlx::{postgres::PgConnectOptions, PgPool, Pool, Postgres};

pub async fn init_pool() -> Pool<Postgres> {
    let options = PgConnectOptions::new();

    PgPool::connect_with(options)
        .await
        .expect("Failed to connect to db 1.")
}

and it worked until i changed one of the sqls now all have the same error

image
image1616×189 10.1 KB

image
image691×148 4.53 KB

4

That's a problem with the query! macro. It needs DATABASE_URL to be set during build time (it can read it from .env) to be able to statically check your queries against your database tables. If you don't need support for statically checked queries, you can just use the sqlx::query function instead of the macro.

5

That feature is a big reason i started to learn rust. Oh well db_url it is then.
Do you know how to add schema into that url? Or is it not supported too and i need to include schema in sql myself?

6

Maybe try:

postgres://postgres:admin@localhost:5432/postgres?options=-c%20search_path=DB_SCHEMA