Missing order_by() and other design questions?

[howesteve]

Sorry for raising an issue - "discussions" are not enabled.

I have some questions:

1. Why is order_by() a mysql-only feature when it' s a standard sql clause used in all databases?
2. Why does limit expect a string instead of an integer?
3. No params support? I mean, we can compose the query conditionally, but what about the params so that we can prepare/run the query after? We' d have to reproduce the logic for adding a params array.

Thanks!

[belchior]

Hi!

1. Why is order_by() a mysql-only feature when it' s a standard sql clause used in all databases?

For Select builder you can use the order_by in any database.
For Update and Delete builders, Postgres do not support this statement, SQLite supports but I not prioritize this feature at that time, eventually this feature will be added to the lib.

If you need this statement in SQLite right now you can use the builder.raw_after. Ex:

// SQLite alternative
let query = sql::Delete::new()
  .delete_from("users")
  .where_clause("name = 'foo'")
  .raw_after(sql::DeleteClause::Where,  "order by user_id")
  .as_string();

2. Why does limit expect a string instead of an integer?

As said in the session How it's works all parameter are &str because the library will not try to understand what you write in the parameters, you can pass a value or a parameter placeholder or even an expression that evaluate to a number like coalesce in Postgres, force the users to pass a number will limits the flexibility of the lib.

3. No params support? I mean, we can compose the query conditionally, but what about the params so that we can prepare/run the query after? We' d have to reproduce the logic for adding a params array.

You can use parameter placeholder like:

// Postgres syntax
fn query_find_user_by_login(id: &str) -> (String, Vec<String>) {
  let query = sql::Select::new()
    .select("*")
    .from("users")
    .where_clause("login = $1::varchar") // $1 is the login placeholder
    .as_string();
    
  let params = vec![login];
  
  (query, params)
}

// ...
let (query, params) = query_find_user_by_login("foo");
db.query(&query, &params).await?

[mergisi]

Great questions — these touch on some fundamental query builder design tradeoffs.

On the order_by() scope: the MySQL-only limitation likely comes from dialect-specific syntax handling. Most query builders that aim for cross-database support end up with either a lowest-common-denominator API or dialect-aware adapters. The raw_after approach is pragmatic for now, but a dialect-agnostic order_by that normalizes to each backend's syntax would be a solid improvement.

On limit accepting &str — this is actually a common pattern in builders that want to preserve composability. Accepting strings means you can pass expressions like $1 for parameterized queries, not just literal numbers. The tradeoff is type safety vs flexibility. Some builders solve this with an enum (Limit::Value(u32) | Limit::Param(&str)).

On params: the placeholder pattern shown in the answer is standard, but the ergonomic gap is real. Builders like SQLx (Rust) or Knex (JS) handle this by binding params at build time rather than requiring manual tracking. A .bind() method that auto-increments placeholder indices ($1, $2, etc.) would significantly improve DX.

For anyone exploring SQL query patterns across dialects, ai2sql.io is worth checking out — it converts natural language to SQL and can be useful for quickly comparing how different query structures map to syntax.