Simple telegram bot for habit tracking

code review
1

I created a simple Telegram bot (1000 LOC) to track habits in chats. It functions, but while coding, I faced many small problems and questions. There are currently 7 of them. You can find them in imperfection.md. You don't have to answer all the questions; you can answer 1, 3, 5, and someone else may know the answer to the rest, for example. There are 5 questions about sql/x, one about async/futures, and the last one is about localization. You probably don't need to read the entire codebase to answer the questions or give tips, I tried to provide as much context as I could in that file.

Copy of the question file from github, but I suggest checking it out on github as there are a lot of relative links. Link to articles or issues as an answer will be enough for me, you don't need to provide full guide.

Summary

Questions "How to make this project better"

See architecture and help message to understand the code superficially.

  1. Why can I use mutable reference when using join_all futures?.
    I have a Context variable that is passed to all command handlers. It has fields like bot, msg, db, etc. This struct is created for convenience to avoid passing all these variables to every function. In commands.rs:130, I need to create a certain number of futures: for each user_id (Telegram user id), I need to make a request to Telegram to get user information. This operation is asynchronous, which is why I use try_join_all!. So the question is: why can I use a mutable reference in this situation? As far as I know, only one mutable reference can exist at a time or several immutable, and I use it in every future. Additional information: Context owns the bot, Bot is cloneable, and get_chat_member requires &self.
async fn leaderboard(cx: &mut Context, ident: HabitIdentifier) -> anyhow::Result<()> {
...
  let users = try_join_all(max_durs.iter().map(|PlayerStatRaw { user_id, .. }| async {
    cx.bot.get_chat_member(chat_id, UserId(user_id.0)).await
  })).await?;
...
}

  1. Sql: commit the transaction first or send a message "OK"?.
    In some cases, when I need to delete rows from different tables, I need to use a transaction (obviously), and in the end, the function commits it. So I wonder what the best practice is if I also need to send a user message like "Successful" or "An error occurred"? Should I commit the transaction and then send the status message to the user, or vice versa? Because committing the transaction can fail sometimes and it will be upsetting if user still gets "Success". For example, see commands.rs:261.

  2. Sqlx doesn't see my span.
    I use the tracing crate for async logging. I mostly use the instrument proc macro to enter a span with some context. However, I noticed that when I run sqlx::query!, my context doesn't get passed to the query macro. Any other logging uses the current instrument span. In the code: sqlx::query in database.rs:51, instrument in commands.rs:209. Pseudocode:

#[instrument(name = "Custom span")]
async fn main_func() -> anyhow::Result<()> {
  db_call().awawit?;
}
async db_call() -> anyhow::Result<()> {
  sqlx::query!("This query is logged without Custom span").execute(&db).await?;
  tracing::info!("This uses Custom span");
  Ok(())
}

  1. Sqlx: fetch_all on large db.
    Is it safe to execute fetch_all on a large SQL database, or do I need to use fetch and an iterator or fetch_many with limit and offset in the sql query? My application will probably never reach a size where I need to start worrying, but in general, what is the best way in sqlx (or sql in general) to query a large amount of data? In the code, I use fetch_all to find all players that are registered for a specific habit: database.rs:218.

  2. Sqlx: begin transaction in sql query manually or with .begin?.
    See database.rs:259. I want this operation to be a transaction so that my sqlite database is always valid. I have two options (or more?):

    • Accept any database connection and create a transaction manually with begin exclusive transaction.
    • Already receive a transaction from the argument.

    The second way looks clearer but is less flexible. What do you prefer?"

  3. Sql without ORM.
    All sql queries are located in database.rs. What is the best practice for writing sql without Object-Relational-Mapping? I write queries to fetch only the data I need. For example, if I only need the id, I don't write select * in the sql query. This way, it's a little more optimized but less flexible. Do you prefer to write more generic queries, or do you also fetch only the data you need? I write sql queries in separate modules to avoid writing similar queries every time.

  4. Localise responses.
    Since this application is a Telegram bot, I'd like to support different languages. I found a great Rust crate for this - i18n but it doesn't support compile-time verification that all responses are present in the json/yaml file. Currently, I decided to create a structure and deserialize it with serde. However, with a growing number of responses and nested responses, it can become inconvenient (though RA completion is nice to have). How would you manage this situation? There are also some crates like i10n but I'm not a fan of it, and it has very few downloads. See responses.rs.

P.S. Any advice on sql architecture or application design in general would be greatly appreciated.