[cogman10]

sqlx doesn't build queries, or at least it minimally builds them. Which I think is the thing the OP is complaining about.

And, IMO, making dynamic queries harder is preferable. Dynamic queries are inherently unsafe. Sometimes necessary, however you have to start considering things like sql injection attacks with dynamic queries.

This isn't to poo poo sea-orm. I'm just saying that sqlx's design choice to make dynamic queries hard is a logical choice from a safety standpoint.

[spoiler]

They didn't make them hard by design, I think, it's just the limitations of the current API and prioritisation. Dynamic queries are possible, just not trivial

[cogman10]

Nope, it really was part of the design [1]

[1] https://github.com/launchbadge/sqlx/issues/333#issuecomment-...

[spoiler]

Oh right, I know what you mean now. I was thinking more along the lines of the QueyBuilder API (and you can write extensions traits to make things more ergonomic). But yeah, some of their APIs work only/best with static strings.

There also sea-query, and sea-orm was already mentioned!

[Paul-E]

> And, IMO, making dynamic queries harder is preferable. Dynamic queries are inherently unsafe. Sometimes necessary, however you have to start considering things like sql injection attacks with dynamic queries.

Depends on what you mean by "dynamic query". You are dealing with injection attacks as soon as you start taking user input. Most useful user facing applications take user input.

In a simple case it might be "SELECT * FROM posts WHERE title LIKE '%hello world%', where "hello world" is a user specified string. This is easy with sqlx. Where things get more difficult is if you want to optionally add filters for things like date posted, score of the post, author, etc... That makes the query dynamic in a way that can't be solved by simply including a bind.

That's where sea-orm shines over sqlx IMO. sqlx will force you to do something like

```

let mut my_query = "SELECT * FROM posts WHERE title LIKE '%' + $1 + '%'";

let mut my_binds = vec![args.keyword];

if let Some(date) = args.date {

  my_query = format("{my_query} AND date = $2");

  my_binds.push(date);

}

...

```

Your building a string and tracking binds. It gets messy. A good query builder like seaorm has lets you do something this:

```

let mut query = Posts::find().filter(Column::title::like(args.keyword));

if let Some(date) = args.date {

  query = query.filter(column::Date::eq(date));

}

```

This pays off as your queries get more complicated. It pushes the string manipulation and bookkeeping into a library, which can be more thoroughly tested.

It also lets you pass around typed partial queries, eg in the example above query might be returned from a function, which helps you build more modular code.

[cogman10]

I agree with what you are saying, this is exactly what I was thinking when I said it was sometimes necessary. It's just not preferable IMO.

For this specific example, the better way is something like this

    let result = if let Some(date) = args.date {
      sqlx::query("SELECT * FROM posts WHERE title LIKE '%' + $1 + '%' AND date = $2")
        .bind(args.keyword)
        .bind(date)
        .fetch()
    } else {
      sqlx::query("SELECT * FROM posts WHERE title LIKE '%' + $1 + '%")
        .bind(args.keyword)
        .fetch()
    }

But I get how this would be untenable if as the number of query param combos goes up. In that case dynamic SQL really is the only sane way to handle something like that.