Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLx – Rust SQL Toolkit

github.com

Post reply on HN
Print view View original

71–80 of 101 posts

Re: SQLx – Rust SQL Toolkit

#71

Post by dathinab »

inbx0 wrote: post #62
zackangelo wrote: post #56

Earlier quoted context omitted.

Is something like SeaQuery[0] what you're talking about? [0] https://github.com/SeaQL/sea-query/

SeaQuery looks like a similar dynamic query builder for Rust as Kysely is for JS/TS, so yeah, that'd probably solve the dynamic query problem. But I think parent wasn't so much asking for another library but for patterns. How do people who choose to use a no-dsl SQL library, like SQLx, handle dynamic queries? Especially with compile-time checking. The readme has this example: ... WHERE organization = ? But what if yo…

> Especially with compile-time checking.

no compile time checking and integration tests

in general sqlx only provides the most minimal string based query building so you can easily run into annoying edge cases you forgot to test, so if your project needs that, libraries like sea-query or sea-orm are the way to go (through it's still viable, without just a bit annoying).

in general SQLx "compile time query checking" still needs a concrete query and a running db to check if the query is valid. It is not doing a rem-implementation of every dialects syntax, semantics and subtle edge cases etc. that just isn't practical as sql is too inconsistent in the edge cases, non standard extensions and even the theoretical standardized parts due to it costing money to read the standard and its updates being highly biased for MS/Oracle databases).

This means compile time query checking doesn't scale that well to dynamic queries, you basically would need to build and check every query you might dynamically create (or the subset you want to test) at which point you are in integration test territory (and you can do it with integration tests just fine).

besides the sqlx specific stuff AFIK some of the "tweaked sql syntax for better composeability" experiments are heading for SQL standardization which might make this way less of a pain in the long run but I don't remember the details at all, so uh, maybe not???

---

EDIT: Yes there is an sqlx "offline" mode which doesn't need a live db, it works by basically caching results from the online mode. It is very useful, but still no "independent/standalone" query analysis.

Re: SQLx – Rust SQL Toolkit

#72

Post by valenterry »

Just coming here to make a prediction: using raw SQL is not great for anything but very simple cases. You can make it type-safe, but that becomes tricky once things become dynamic.

But the real problem is ergonomy. The better solution in almost any language is to leverage the syntax of your language to allow for as much (non-macro) type-safety and auto-completion as possible.

For example instead of:

   SELECT country, COUNT(*) as count
   FROM users
   GROUP BY country
   WHERE organization = ?
That should be

   select("country", count("\*").as("count))
   .from("users")
   .groupBy("country")
   .where("organization".=(yourVariable))
[note that it matches SQL, not the language's collections function's names.]

As you see, that's also nice because now you can actually use variables easy - and even use pure rust to decide dynamically on things.

You can further increase typesafety if you want by doing things like `.from(table("users"))` and running extra checks on that table() part, similar to what the lib probably does. Also, sometimes you might have to make a compromise on your syntax and things like `"organization".=(yourVariable)` might have to be slightly rewritten.

Still, I think that people will rather end up with a library like I described, unless the SQL is very basic/static.

Re: SQLx – Rust SQL Toolkit

#73

Post by lazypenguin »

valenterry wrote: post #72

Just coming here to make a prediction: using raw SQL is not great for anything but very simple cases. You can make it type-safe, but that becomes tricky once things become dynamic. But the real problem is ergonomy. The better solution in almost any language is to leverage the syntax of your language to allow for as much (non-macro) type-safety and auto-completion as possible. For example instead of: SELECT country, C…

No my experience is the inverse. The type of library you describe is nice for the basic queries but once you start needing CTE, subquery, postgres json query, etc. it just because easier to manage it all in SQL directly.

Re: SQLx – Rust SQL Toolkit

#74

Post by bitbasher »

frollogaston wrote: post #27
bitbasher wrote: post #8

I've been using sqlx with postgres for several months now on a production server with decent query volume all day long. It has been rock solid. I find writing sql in rust with sqlx to be far fewer lines of code than the same in Go. This server was ported from Go and the end result was ~40% fewer lines of code, less memory usage and stable cpu/memory usage over time.

How is it more LoC in Go, just cause of the "if err" stuff?

Go's verbose error handling certainly impacted the vertical height of files (lots of early returns), but wasn't a big contributor to overall LoC.

The more serious LoC offenders in Go were:

1. Marshalling/Unmarshalling code (for API responses, to/from external services, etc). In general, working with JSON in Go was painful and error prone. Rust's serde made this a complete non-issue.

2. Repetitive sql query code (query, scan for results, custom driver code for jsonb column marshalling/unmarshalling). Rust's sqlx made this a non-issue.

3. Go's use of context to share data through handlers was a real pain and error prone (type casting, nil checks, etc). Rust's actix-web made this a real beautiful thing to work with. Need a "User" in your handler? Just put it as an argument to the handler and it's only called if it's available. Need a db connection? Just put it as an argument to the handler.

4. Go's HTML/Text templates required more data to be passed in and also required more safety checks. Rust's askama was overall more pleasant to use and provided more confidence when changing templates. In Rust, I'd catch errors at compile time. In Go, I'd catch them at runtime (or, a user would).

I must admit I was surprised. I thought Rust would have been more lines of code because it's a lower level language, but it ended up being ~40% less code. My general sentiment around working with the code is very different as well.

In the Rust codebase I have no hesitation to change things. I am confident the compiler will tell me when I'm breaking something. I never had that confidence in Go.

Re: SQLx – Rust SQL Toolkit

#75

Post by pie_flavor »

moggers123 wrote: post #41

SQLx is great, but I really wish they had a non-async interface. I had to switch a project from sqlx to rusqlite seemingly just due to the overhead of the async machinery. Saw a 20x latency reduction that I narrowed down to "probably async" (sort of hard to tell, I find it very difficult to do perf analysis of async code). I try to avoid discussing async so as to not come off as a frothing-at-the-mouth-chest-thumping…

Async does not incur 20x slowdowns when you're I/O bound. It would be ridiculous for copying a few bytes to be slower than a syscall. This sounds like mutex issues, or WAL config, or something like that.

Re: SQLx – Rust SQL Toolkit

#76

Post by valenterry »

lazypenguin wrote: post #73
valenterry wrote: post #72

Just coming here to make a prediction: using raw SQL is not great for anything but very simple cases. You can make it type-safe, but that becomes tricky once things become dynamic. But the real problem is ergonomy. The better solution in almost any language is to leverage the syntax of your language to allow for as much (non-macro) type-safety and auto-completion as possible. For example instead of: SELECT country, C…

No my experience is the inverse. The type of library you describe is nice for the basic queries but once you start needing CTE, subquery, postgres json query, etc. it just because easier to manage it all in SQL directly.

I'm using JOOQ. Not saying that JOOQ is the greatest library, but all of what you just mentioned works in there without problem. Including CTEs and json stuff.

With a library such as SQLx, you can never really factor anything out. Or at least it's very hard and you lose the actual typesafety. I've been there and done that with doobie [https://typelevel.org/doobie/] which is basically the same in green.

Re: SQLx – Rust SQL Toolkit

#77

Post by acjohnson55 »

valenterry wrote: post #72

Just coming here to make a prediction: using raw SQL is not great for anything but very simple cases. You can make it type-safe, but that becomes tricky once things become dynamic. But the real problem is ergonomy. The better solution in almost any language is to leverage the syntax of your language to allow for as much (non-macro) type-safety and auto-completion as possible. For example instead of: SELECT country, C…

I've been using SQL from backend languages for years, and I totally disagree. Using raw SQL is the only way for me. It's much easier to develop, tune, and debug in a SQL IDE. There's no need to translate back and forth between SQL and the language-specific DSL. With SQLx, I get all the type safety I really need.

Dynamically constructing queries is awkward, but most of mine ha very limited dynamic variation.

Re: SQLx – Rust SQL Toolkit

#78

Post by mcflubbins »

I've used SQLx for a couple of projects (MariaDB and SQLite) its good, it does the thing though it takes a little bit of getting used to. The fact that it can check queries at compile time is its biggest strength.

Re: SQLx – Rust SQL Toolkit

#79

Post by valenterry »

acjohnson55 wrote: post #77
valenterry wrote: post #72

Just coming here to make a prediction: using raw SQL is not great for anything but very simple cases. You can make it type-safe, but that becomes tricky once things become dynamic. But the real problem is ergonomy. The better solution in almost any language is to leverage the syntax of your language to allow for as much (non-macro) type-safety and auto-completion as possible. For example instead of: SELECT country, C…

I've been using SQL from backend languages for years, and I totally disagree. Using raw SQL is the only way for me. It's much easier to develop, tune, and debug in a SQL IDE. There's no need to translate back and forth between SQL and the language-specific DSL. With SQLx, I get all the type safety I really need. Dynamically constructing queries is awkward, but most of mine ha very limited dynamic variation.

I get where you are coming from. But it's very easy to generate the SQL from the code. Then I take that, tune it in my SQL IDE against the DB and then adjust the code. Since the code is basically a 1:1 mapping to SQL (just with slightly different syntax) there isn't really a problem with that.

Once you have any kind of dynamic stuff (like a dynamic filter) you don't have any 100% pure SQL anymore anyways. If you don't have that, okay, this lib will be more convenient.

Re: SQLx – Rust SQL Toolkit

#80

Post by hu3 »

rzmmm wrote: post #68
banashark wrote: post #52

One thing I don't usually see addressed with the pure-sql approaches is how to handle dynamic query building. The most common example being large configurable forms that display a data grid. Kysely[1] does a good job of starting from this angle, but allowing something like specifying the concrete deserialization type similar to the libraries here. I'm a big fan of sql in general (even if the syntax can be verbose, th…

One approach is to create views for the required data and then just select the columns which are needed. The joins will be pruned by the query planner if they are not needed, so there is no need for conditional joins.

> The joins will be pruned by the query planner if they are not needed, so there is no need for conditional joins.

I always wondered about this. How reliable is that in your experience? Thank you in advance.

Post reply on HN