define sql query strings in .sql files

[mclark]

👋 Hi folks,

While I've been programming for a looong time I'm relatively new to Rust and very new to sqlx.

In larger apps I've found it's valuable to have queries defined in dedicated .sql files separate from the code so we can track all the unique queries that get executed against our database. In my current experimental app, I've got a user_queries.sql file defined, with each query named like this:

-- name: insert_user
INSERT INTO users (name, email) VALUES (?, ?);

Then in my build.rs file I have a function that parses this sql file to produce a Rust file like this:

pub const INSERT_USER: &str = r#"INSERT INTO users (name, email) VALUES (?, ?);"#;

Finally, in my actual model code I include! that generated file and have all the query constants available to me with autocomplete within that module:

// in user_store.rs
mod queries {
    include!(concat!(env!("OUT_DIR"), "/user_queries.rs"));
}

// ...

sqlx::query(queries::INSERT_USER)
            .bind(name)
            .bind(email)
            .execute(&*self.pool)

This seems pretty simple but I thought I'd check if there was any interest in me putting a pull request together and polishing it up. No point in everyone writing their own parser/generator. Would this be of value to sqlx? Are others doing something similar, or did I miss a common utility for this already?

Thanks everyone!

[joeydewaal]

I think a lot of people tend to reach for query_file (and friends) because it has the compile time checks.

[mclark]

Cool, I didn't know about that!

It looks like it's probably very helpful for large queries but I'm more interested in aggregating all my queries for a given entity into a single file and just referencing the generated constants. For large queries I'll keep it in mind though!

What do you mean by compile time checks?

[joeydewaal]

Right, you can't have multiple sql queries in one file.

I'd recommend reading the query! docs. The macro's can connect to your db at compile time and type check your query (and some other fancy stuff), which is very useful.