15 ms·

SQLx – Rust SQL Toolkit

180 points by stmw 1y ago

tmpfs 1y ago
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/ https://docs.rs/sqlite/latest/sqlite/ [2]: https://docs.rs/postgres/latest/postgres/ https://docs.rs/postgres/latest/postgres/
- vegizombie 1y ago
  It's definitely not perfect, but I think both of those issues are better now, if not fully solved. For needing a DB at compile time, there's an option to have it produce artefacts on demand that replace the DB, although you'll need to connect to a DB again each time your queries change. Even that is all optional though, if you want it to compile time check your queries.
- stmw 1y ago
  Thanks, interesting experience - so much depends on getting developer ergonomics right. There is something to be said for checking the SQL at compile-time, though - esp. if trying to ORM to a typesafe language.
- belak 1y ago
  I'm have no experience with abstracting away the backend, but Dockerizing is actually pretty easy now - there's an offline mode[1] where you can have sqlx generate some files which let it work when there's no DB running. [1]: https://docs.rs/sqlx/latest/sqlx/macro.query.html#offline-mode https://docs.rs/sqlx/latest/sqlx/macro.query.html#offline-mo...
- 0xCMP 1y ago
  I know it's annoying (and apparently there is a solution for generating the required files before the build), but in these kinds of situations Go and Rust are great for doing a static build on the system and then copying into a scratch image. Versus Python and Node often needing to properly link with the system they'll actually be running in.
- no_circuit 1y ago
  How long ago did you try SQLx? Not necessarily promoting SQLX, but the `query_as` which lets one make queries without the live database macro has been around for 5 years [1]. For lower level libraries there is also the more downloaded SQLite library, rusqlite [2] who is also the maintainer of libsqlite3-sys which is what the sqlite library wraps. The most pleasant ORM experience, when you want one, IMO is the SeaQl ecosystem [3] (which also has a nice migrations library), since it uses derive macros. Even with an ORM I don't try to make databases swappable via the ORM so I can support database-specific enhancements. The most Rust-like in an idealist sense is Diesel, but its well-defined path is to use a live database to generate Rust code that uses macros to then define the schema-defining types which are used in the row structs type/member checking. If the auto-detect does not work, then you have to use its patch_file system that can't be maintained automatically just through Cargo [4] (I wrote a Makefile scheme for myself). You most likely will have to use the patch_file if you want to use the chrono::DateTime<chrono::Utc> for timestamps with time zones, e.g., Timestamp -> Timestamptz for postgres. And if you do anything advanced like multiple schemas, you may be out of luck [5]. And it may not be the best library for you if want large denormalized tables [6] because compile times, and because a database that is not normalized [7], is considered an anti-pattern by project. If you are just starting out with Rust, I'd recommend checking out SeaQl. And then if you can benchmark that you need faster performance, swap out for one of the lower level libraries for the affected methods/services. [1] https://github.com/launchbadge/sqlx/commit/47f3d77e599043bc25f78765ec42189e529dc76c https://github.com/launchbadge/sqlx/commit/47f3d77e599043bc2... [2] https://crates.io/crates/rusqlite https://crates.io/crates/rusqlite [3] https://www.sea-ql.org/SeaORM/ https://www.sea-ql.org/SeaORM/ [4] https://github.com/diesel-rs/diesel/issues/2078 https://github.com/diesel-rs/diesel/issues/2078 [5] https://github.com/diesel-rs/diesel/issues/1728 https://github.com/diesel-rs/diesel/issues/1728 [6] https://github.com/diesel-rs/diesel/discussions/4160 https://github.com/diesel-rs/diesel/discussions/4160 [7] https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization
- adelmotsjr 1y ago
  Why would you want to abstract away the underlying database? Wouldn't it better to already use the target DB to cattch potential issues earlier? Also to avoid creating another layer of indirection, potentially complecting the codebase and reducing performance?
  - TrueDuality 1y ago
    Primarily for libraries and deployment environments that aren't fully in your control which is still pretty common once you get to B2B interactions, SaaS is not something you can easily sell to certain environments. Depending on the assurance you need, you might even need to mock out the database entirely to test certain classes of database errors being recoverable or fail in a consistent state. Even in SaaS systems, once you get large enough with a large enough test suite you'll be wanting to tier those tests starting with a lowest common denominator (sqlite) that doesn't incur network latency before getting into the serious integration tests.
  - small_scombrus 1y ago
    > Wouldn't it better to already use the target DB to cattch potential issues earlier? The target DB can change as a project goes from something mildly fun to tinker with to something you think might actually be useful. Also I personally find that SQLite is just nice to work with. No containers or extra programs, it just does what you ask it to, when you ask it to
thesuperbigfrog 1y ago
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/3080 https://github.com/launchbadge/sqlx/issues/2510 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.
- zbentley 1y ago
  As a total outsider to sqlx, those issues don’t surprise me: any application on any platform that uses a SQLite in-memory DB concurrently is likely to violate many assumptions made by client-side connection pooling tools. In-memory SQLite is a great tool, but using it indirectly behind a connection pooler that assumes the database is external to the current process is bound to cause problems.
  - stmw 1y ago
    Agree with zbentley, I actually wouldn't expect this to work well - perhaps a good thing for sqlx team to warn against.
- cortesi 1y ago
  SQLx is great, but I had a long laundry list of issues with its SQLite support so I forked it into a focused SQLite-specific library. It has now diverged very far from SQLx, and the number of small inaccuracies and issues we fixed in the low-level SQLite bindings is well into the dozens. The library is unannounced, but is already being used in some high-throughput scenarios. https://github.com/cortesi/musq https://github.com/cortesi/musq
  - thesuperbigfrog 1y ago
bitbasher 1y ago
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.
- jchw 1y ago
  Speaking of Go, if you want compile-time type checking like what SQLx offers, the Go ecosystem has an option that is arguably even better at it: https://sqlc.dev/ https://sqlc.dev/ It has the advantage that it implements the parsing and type checking logic in pure Go, allowing it to import your migrations and infer the schema for type checking. With SQLx you need to have your database engine running at compile time during the proc macro execution with the schema already available. This makes SQLx kind of a non-starter for me, though I understand why nobody wants to do what sqlc does (it involves a lot of duplication that essentially reimplements database features.) (Somewhat ironically it's less useful for sqlc to do this since it runs as code generation outside the normal compilation and thus even if it did need a live database connection to do the code generation it would be less of an impact... But it's still nice for simplicity.)
  - bitbasher 1y ago
    It's possible to run sqlx in 'offline' mode that uses your schema to do the checks so you don't need a live database. That's a popular option in CI/CD scenarios.
    - echelon 1y ago
      Offline query caching is great. The team has made it work fantastically for workspace oriented monorepos too. I ran sqlx / mysql on a 6M MAU Actix-Web website with 100kqps at peak with relatively complex transactions and queries. It was rock solid. I'm currently using sqlx on the backend and on the desktop (Tauri with sqlite). In my humble opinion, sqlx is the best, safest, most performant, and most Rustful way of writing SQL. The ORMs just aren't quite there. I wish other Rust client libraries were as nice as sqlx. I consider sqlx to be one of Rust's essential crates.
hyperbrainer 1y ago
I have never used SQLx, but the best SQL integration I can think of is LINQ. How does this compare to that?
- RonanSoleste 1y ago
  Different products. I would not compare them. LINQ is more like Diesel (https://diesel.rs/ https://diesel.rs/)
  - maxbond 1y ago
    To expand, SQLx isn't an ORM or query builder, what it does is allow you to write raw SQL with compile-time guarantees of type safety. It does this by connecting to a dev database at compile time & uses SQL's introspection features (specifically, by preparing a statement[1]) to analyze your queries. (It can also cache this information to check without a database available, and has a basic migration facility.) [1] https://github.com/launchbadge/sqlx/blob/main/FAQ.md#how-do-the-query-macros-work-under-the-hood https://github.com/launchbadge/sqlx/blob/main/FAQ.md#how-do-...
- S04dKHzrKT 1y ago
  In the dotnet world, SQLx is more analogous to F# type providers like FSharp.Data.SqlClient , SQLProvider or Rezoom.SQL.
Ameo 1y ago
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.
- p4ul 1y ago
  This has been exactly my experience! I've found SQLx to be a joy to work with in Rust!
- WD-42 1y ago
  Same. Never again diesel. The type system just turns it into madness. Sqlx is a much more natural fit.
- deleted 1y ago
  [deleted]
- theOGognf 1y ago
  I’ve used Diesel for a bit now but haven’t had issues wrangling the type system. Can you give an example of an issue you’ve encountered?
chuckhend 1y ago
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.
OoooooooO 1y ago
I've read the GitHub page but I still have no idea what problems it solves or why I should care about this library?
- nbf_1995 1y ago
  It's a rust library that you can use to run sql queries against a database. It also inspects the database at compile* time to figure out the type of each column in your query so that your code is type-safe. * Or in your editor as you're writing code.
nbf_1995 1y ago
SQLx and F# type-providers are probably the best developer experience for writing database access code. I wish more languages had something equivalent.
- stmw 1y ago
  I think this sort of stuff only comes after a LOT of experience with building SQL db backed systems - it resonated with me immediately. (I'm the OP but not affiliated with this Rust project at all).
moggers123 1y ago
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.
- pie_flavor 1y ago
  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.
  - moggers123 1y ago
    I just chucked something together to try and demonstrate. I don't see the massive 20x slowdown, only about a 3x slowdown (5x on release build). Still enough to be painful for the use case in question. https://github.com/Moggers/rusqlitebenchmark https://github.com/Moggers/rusqlitebenchmark Do you think you could look through it and point out what you think the reason is? I think they've both got the same WAL and mutex settings. Its a very contrived and synthetic example but actually somewhat representative of what the original code wanted to do.
karlmdavis 1y ago
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.
- rellfy 1y ago
  What problems have you had with joins? I have this comment in one of my projects: ``` It is required to mark left-joined columns in the query as nullable, otherwise SQLx expects them to not be null even though it is a left join. For more information, see the link below: https://github.com/launchbadge/sqlx/issues/367#issuecomment-799829096 https://github.com/launchbadge/sqlx/issues/367#issuecomment-... ``` Did you have other problems beyond this, or are you referring to something different? The issue above is a bit annoying but not enough that I'd switch to an ORM over it. I think SQLx overall is great.
- cryptonector 1y ago
  What do you mean? It takes SQL queries. You use the `JOIN` keyword in the SQL to do joins.
owlstuffing 1y ago
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/manifold-deps-parent/manifold-sql/readme.md https://github.com/manifold-systems/manifold/blob/master/man...
banashark 1y ago
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 https://kysely.dev
- zackangelo 1y ago
  Is something like SeaQuery[0] what you're talking about? [0] https://github.com/SeaQL/sea-query/ https://github.com/SeaQL/sea-query/
  - inbx0 1y ago
    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 you have multiple possible where-conditions, let's say "WHERE organization = ?", "WHERE starts_with(first_name, ?)", "WHERE birth_date > ?", and you need to some combination of those (possibly also none of those) based on query parameters to the API. I think that's a pretty common use case.
    - seivan 1y ago
      [dead]
    - fstephany 1y ago
lovasoa 1y ago
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/ https://sql-page.com/
aryonoco 1y ago
If anyone is looking for a similar thing in F#, complete with automatic type generation etc, have a look at SqlHydra.
valenterry 1y ago
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.
- lazypenguin 1y ago
  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.
  - valenterry 1y ago
    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/ https://typelevel.org/doobie/] which is basically the same in green.
- acjohnson55 1y ago
  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.
mcflubbins 1y ago
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.
germandiago 1y ago
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.
- LtdJorge 1y ago
  It depends. On RustRover you do, because the query text can be language-injected as SQL, and it uses your configured schema.
lsuresh 1y ago
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.

180 points by stmw 1y ago

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/ https://docs.rs/sqlite/latest/sqlite/ [2]: https://docs.rs/postgres/latest/postgres/ https://docs.rs/postgres/latest/postgres/

vegizombie 1y ago
It's definitely not perfect, but I think both of those issues are better now, if not fully solved. For needing a DB at compile time, there's an option to have it produce artefacts on demand that replace the DB, although you'll need to connect to a DB again each time your queries change. Even that is all optional though, if you want it to compile time check your queries.
stmw 1y ago
Thanks, interesting experience - so much depends on getting developer ergonomics right. There is something to be said for checking the SQL at compile-time, though - esp. if trying to ORM to a typesafe language.
belak 1y ago
I'm have no experience with abstracting away the backend, but Dockerizing is actually pretty easy now - there's an offline mode[1] where you can have sqlx generate some files which let it work when there's no DB running. [1]: https://docs.rs/sqlx/latest/sqlx/macro.query.html#offline-mode https://docs.rs/sqlx/latest/sqlx/macro.query.html#offline-mo...
0xCMP 1y ago
I know it's annoying (and apparently there is a solution for generating the required files before the build), but in these kinds of situations Go and Rust are great for doing a static build on the system and then copying into a scratch image. Versus Python and Node often needing to properly link with the system they'll actually be running in.
no_circuit 1y ago
How long ago did you try SQLx? Not necessarily promoting SQLX, but the `query_as` which lets one make queries without the live database macro has been around for 5 years [1]. For lower level libraries there is also the more downloaded SQLite library, rusqlite [2] who is also the maintainer of libsqlite3-sys which is what the sqlite library wraps. The most pleasant ORM experience, when you want one, IMO is the SeaQl ecosystem [3] (which also has a nice migrations library), since it uses derive macros. Even with an ORM I don't try to make databases swappable via the ORM so I can support database-specific enhancements. The most Rust-like in an idealist sense is Diesel, but its well-defined path is to use a live database to generate Rust code that uses macros to then define the schema-defining types which are used in the row structs type/member checking. If the auto-detect does not work, then you have to use its patch_file system that can't be maintained automatically just through Cargo [4] (I wrote a Makefile scheme for myself). You most likely will have to use the patch_file if you want to use the chrono::DateTime<chrono::Utc> for timestamps with time zones, e.g., Timestamp -> Timestamptz for postgres. And if you do anything advanced like multiple schemas, you may be out of luck [5]. And it may not be the best library for you if want large denormalized tables [6] because compile times, and because a database that is not normalized [7], is considered an anti-pattern by project. If you are just starting out with Rust, I'd recommend checking out SeaQl. And then if you can benchmark that you need faster performance, swap out for one of the lower level libraries for the affected methods/services. [1] https://github.com/launchbadge/sqlx/commit/47f3d77e599043bc25f78765ec42189e529dc76c https://github.com/launchbadge/sqlx/commit/47f3d77e599043bc2... [2] https://crates.io/crates/rusqlite https://crates.io/crates/rusqlite [3] https://www.sea-ql.org/SeaORM/ https://www.sea-ql.org/SeaORM/ [4] https://github.com/diesel-rs/diesel/issues/2078 https://github.com/diesel-rs/diesel/issues/2078 [5] https://github.com/diesel-rs/diesel/issues/1728 https://github.com/diesel-rs/diesel/issues/1728 [6] https://github.com/diesel-rs/diesel/discussions/4160 https://github.com/diesel-rs/diesel/discussions/4160 [7] https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization
adelmotsjr 1y ago
Why would you want to abstract away the underlying database? Wouldn't it better to already use the target DB to cattch potential issues earlier? Also to avoid creating another layer of indirection, potentially complecting the codebase and reducing performance?
- TrueDuality 1y ago
  Primarily for libraries and deployment environments that aren't fully in your control which is still pretty common once you get to B2B interactions, SaaS is not something you can easily sell to certain environments. Depending on the assurance you need, you might even need to mock out the database entirely to test certain classes of database errors being recoverable or fail in a consistent state. Even in SaaS systems, once you get large enough with a large enough test suite you'll be wanting to tier those tests starting with a lowest common denominator (sqlite) that doesn't incur network latency before getting into the serious integration tests.
- small_scombrus 1y ago
  > Wouldn't it better to already use the target DB to cattch potential issues earlier? The target DB can change as a project goes from something mildly fun to tinker with to something you think might actually be useful. Also I personally find that SQLite is just nice to work with. No containers or extra programs, it just does what you ask it to, when you ask it to

thesuperbigfrog 1y ago

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/3080 https://github.com/launchbadge/sqlx/issues/2510 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.

zbentley 1y ago
As a total outsider to sqlx, those issues don’t surprise me: any application on any platform that uses a SQLite in-memory DB concurrently is likely to violate many assumptions made by client-side connection pooling tools. In-memory SQLite is a great tool, but using it indirectly behind a connection pooler that assumes the database is external to the current process is bound to cause problems.
- stmw 1y ago
  Agree with zbentley, I actually wouldn't expect this to work well - perhaps a good thing for sqlx team to warn against.
cortesi 1y ago
SQLx is great, but I had a long laundry list of issues with its SQLite support so I forked it into a focused SQLite-specific library. It has now diverged very far from SQLx, and the number of small inaccuracies and issues we fixed in the low-level SQLite bindings is well into the dozens. The library is unannounced, but is already being used in some high-throughput scenarios. https://github.com/cortesi/musq https://github.com/cortesi/musq
- thesuperbigfrog 1y ago

bitbasher 1y ago

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.

jchw 1y ago
Speaking of Go, if you want compile-time type checking like what SQLx offers, the Go ecosystem has an option that is arguably even better at it: https://sqlc.dev/ https://sqlc.dev/ It has the advantage that it implements the parsing and type checking logic in pure Go, allowing it to import your migrations and infer the schema for type checking. With SQLx you need to have your database engine running at compile time during the proc macro execution with the schema already available. This makes SQLx kind of a non-starter for me, though I understand why nobody wants to do what sqlc does (it involves a lot of duplication that essentially reimplements database features.) (Somewhat ironically it's less useful for sqlc to do this since it runs as code generation outside the normal compilation and thus even if it did need a live database connection to do the code generation it would be less of an impact... But it's still nice for simplicity.)
- bitbasher 1y ago
  It's possible to run sqlx in 'offline' mode that uses your schema to do the checks so you don't need a live database. That's a popular option in CI/CD scenarios.
  - echelon 1y ago
    Offline query caching is great. The team has made it work fantastically for workspace oriented monorepos too. I ran sqlx / mysql on a 6M MAU Actix-Web website with 100kqps at peak with relatively complex transactions and queries. It was rock solid. I'm currently using sqlx on the backend and on the desktop (Tauri with sqlite). In my humble opinion, sqlx is the best, safest, most performant, and most Rustful way of writing SQL. The ORMs just aren't quite there. I wish other Rust client libraries were as nice as sqlx. I consider sqlx to be one of Rust's essential crates.

hyperbrainer 1y ago

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

RonanSoleste 1y ago
Different products. I would not compare them. LINQ is more like Diesel (https://diesel.rs/ https://diesel.rs/)
- maxbond 1y ago
  To expand, SQLx isn't an ORM or query builder, what it does is allow you to write raw SQL with compile-time guarantees of type safety. It does this by connecting to a dev database at compile time & uses SQL's introspection features (specifically, by preparing a statement[1]) to analyze your queries. (It can also cache this information to check without a database available, and has a basic migration facility.) [1] https://github.com/launchbadge/sqlx/blob/main/FAQ.md#how-do-the-query-macros-work-under-the-hood https://github.com/launchbadge/sqlx/blob/main/FAQ.md#how-do-...
S04dKHzrKT 1y ago
In the dotnet world, SQLx is more analogous to F# type providers like FSharp.Data.SqlClient , SQLProvider or Rezoom.SQL.

Ameo 1y ago

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.

p4ul 1y ago
This has been exactly my experience! I've found SQLx to be a joy to work with in Rust!
WD-42 1y ago
Same. Never again diesel. The type system just turns it into madness. Sqlx is a much more natural fit.
deleted 1y ago
[deleted]
theOGognf 1y ago
I’ve used Diesel for a bit now but haven’t had issues wrangling the type system. Can you give an example of an issue you’ve encountered?

chuckhend 1y ago

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.

OoooooooO 1y ago

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

nbf_1995 1y ago
It's a rust library that you can use to run sql queries against a database. It also inspects the database at compile* time to figure out the type of each column in your query so that your code is type-safe. * Or in your editor as you're writing code.

nbf_1995 1y ago

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

stmw 1y ago
I think this sort of stuff only comes after a LOT of experience with building SQL db backed systems - it resonated with me immediately. (I'm the OP but not affiliated with this Rust project at all).

moggers123 1y ago

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.

pie_flavor 1y ago
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.
- moggers123 1y ago
  I just chucked something together to try and demonstrate. I don't see the massive 20x slowdown, only about a 3x slowdown (5x on release build). Still enough to be painful for the use case in question. https://github.com/Moggers/rusqlitebenchmark https://github.com/Moggers/rusqlitebenchmark Do you think you could look through it and point out what you think the reason is? I think they've both got the same WAL and mutex settings. Its a very contrived and synthetic example but actually somewhat representative of what the original code wanted to do.

karlmdavis 1y ago

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.

rellfy 1y ago
What problems have you had with joins? I have this comment in one of my projects: ``` It is required to mark left-joined columns in the query as nullable, otherwise SQLx expects them to not be null even though it is a left join. For more information, see the link below: https://github.com/launchbadge/sqlx/issues/367#issuecomment-799829096 https://github.com/launchbadge/sqlx/issues/367#issuecomment-... ``` Did you have other problems beyond this, or are you referring to something different? The issue above is a bit annoying but not enough that I'd switch to an ORM over it. I think SQLx overall is great.
cryptonector 1y ago
What do you mean? It takes SQL queries. You use the `JOIN` keyword in the SQL to do joins.

owlstuffing 1y ago

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/manifold-deps-parent/manifold-sql/readme.md https://github.com/manifold-systems/manifold/blob/master/man...

banashark 1y ago

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 https://kysely.dev

zackangelo 1y ago
Is something like SeaQuery[0] what you're talking about? [0] https://github.com/SeaQL/sea-query/ https://github.com/SeaQL/sea-query/
- inbx0 1y ago
  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 you have multiple possible where-conditions, let's say "WHERE organization = ?", "WHERE starts_with(first_name, ?)", "WHERE birth_date > ?", and you need to some combination of those (possibly also none of those) based on query parameters to the API. I think that's a pretty common use case.
  - seivan 1y ago
    [dead]
  - fstephany 1y ago

lovasoa 1y ago

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/ https://sql-page.com/

aryonoco 1y ago

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

valenterry 1y ago

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.

lazypenguin 1y ago
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.
- valenterry 1y ago
  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/ https://typelevel.org/doobie/] which is basically the same in green.
acjohnson55 1y ago
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.

mcflubbins 1y ago

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.

germandiago 1y ago

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.

LtdJorge 1y ago
It depends. On RustRover you do, because the query text can be language-injected as SQL, and it uses your configured schema.

lsuresh 1y ago

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.