SQLx – Rust SQL Toolkit (github.com)

I have used this as well as many of the other lower-level db drivers (which don't check your SQL at compile time) and I can say I much prefer the latter.

My issues with SQLx when I first tried it were that it was really awkward (nigh impossible) to abstract away the underlying DB backend, I expect those issues are fixed now but for some simple apps it's nice to be able to start with SQLite and then switch out with postgres.

Then I wanted to dockerize an SQLx app at one point and it all becomes a hassle as you need postgres running at compile time and trying to integrate with docker compose was a real chore.

Now I don't use SQLx at all. I recommend other libraries like sqlite[1] or postgres[2] instead.

SQLx is a nice idea but too cumbersome in my experience.

[1]: https://docs.rs/sqlite/latest/sqlite/ [2]: https://docs.rs/postgres/latest/postgres/

I used SQLx with an SQLite database and ran into connection pool problems that would cause the database to be unexpectedly dropped.

The issues I saw seem to be related to these issues:

https://github.com/launchbadge/sqlx/issues/3080

https://github.com/launchbadge/sqlx/issues/2510

The problems did not manifest until the application was under load with multiple concurrent sessions.

Troubleshooting the issue by changing the connection pool parameters did not seem to help.

I ended up refactoring the application's data layer to use a NoSQL approach to work around the issue.

I really like the idea of SQLx and appreciate the efforts of the SQLx developers, but I would advise caution if you plan to use SQLx with SQLite.

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.

I have never used SQLx, but the best SQL integration I can think of is LINQ. How does this compare to that?

sqlx is my favorite way of working with databases in Rust hands down.

I've tried alternatives like Diesel and sea-orm. To be honest, I feel like full-blown ORMs really aren't a very good experience in Rust. They work great for dynamic languages in a lot of cases, but trying to tie in a DB schema into Rust's type system often creates a ton of issues once you try to do anything more than a basic query.

It's got a nice little migration system too with sqlx-cli which is solid.

Love SQLx for my Rust projects. I would like to figure out a great way to use the compile time checks in python or js projects, but haven't explored it yet.

I've read the GitHub page but I still have no idea what problems it solves or why I should care about this library?

SQLx and F# type-providers are probably the best developer experience for writing database access code. I wish more languages had something equivalent.

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-luddite but honestly, if sqlx had a non-async interface I'd be very happy to accept the "you don't need to use it" argument. its the only place where I don't feel like I really have a choice.

I find it kind of baffling that this toolkit is so popular when it makes handling database joins so difficult. After bashing my head against it for a while, I moved to Diesel, and while that has its own set of problems, I am generally able to get through them without resorting to horrible hacks or losing compile time checks.

Quite similar to manifold-sql[1], which is arguably better integrated into Java than SQLx is into Rust. Inline native SQL in Java is *inherently type-safe*, no mapping -- query types, query results, query parameters all projected types at compile-time.

    int year = 2019;
    . . .
    for(Film film: "[.sql/] select * from film where release_year > :rel_year".fetch(year)) {
        out.println(film.title);
    }

1. https://github.com/manifold-systems/manifold/blob/master/man...

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, the declarative nature is usually pleasant and satisfying to use), but whenever dynamic nature creeps in it gets messy. Conditional joins/selects/where clauses, etc

How do folks that go all in on sql-first approaches handle this? Home-grown dynamic builders is what I've seen various places I've work implement in the past, but it's usually not built out as a full API and kind of just cobbled together. Eventually they just swap to an ORM to solve the issue.

* [1] https://kysely.dev

I use a fork of sqlx in SQLPage [1]. I think my main complaint about it is runtime errors (or worse, values decoded as garbage) when decoding SQL values to the wrong rust type.

* [1] https://sql-page.com/

If anyone is looking for a similar thing in F#, complete with automatic type generation etc, have a look at SqlHydra.

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.

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.

I am not much into Rust ATM. I am quite comfortable with C++. So here it goes my question:

I use sqlpp11 in C++.

I generate code and I can use it with strong typing by including some headers. This Rust crate seems to provide compile-time checking.

But it will give me code-completion? It is very nice that by pressing '.' you know what you potentially have.

I first went to sqlx thinking it would be like JOOQ for Rust, but that wasn't the case. It's a pretty low-level library and didn't really abstract away the underlying DBs much, not to mention issues with type conversions. We've since just used rust-postgres.