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

11–20 of 101 posts

Re: SQLx – Rust SQL Toolkit

#11

Post by zbentley »

thesuperbigfrog wrote: post #7

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…

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.

Re: SQLx – Rust SQL Toolkit

#12

Post by jchw »

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.

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/

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.)

Re: SQLx – Rust SQL Toolkit

#13

Post by Ameo »

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.

Re: SQLx – Rust SQL Toolkit

#14

Post by no_circuit »

tmpfs wrote: post #2

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 po…

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 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/47f3d77e599043bc2...

[2] https://crates.io/crates/rusqlite

[3] https://www.sea-ql.org/SeaORM/

[4] https://github.com/diesel-rs/diesel/issues/2078

[5] https://github.com/diesel-rs/diesel/issues/1728

[6] https://github.com/diesel-rs/diesel/discussions/4160

[7] https://en.wikipedia.org/wiki/Database_normalization

Re: SQLx – Rust SQL Toolkit

#15

Post by bitbasher »

jchw wrote: post #12
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.

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/ 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 mac…

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.

Re: SQLx – Rust SQL Toolkit

#16

Post by written-beyond »

jchw wrote: post #12
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.

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/ 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 mac…

I spent 2 weeks trying to build a very basic rest crud API with SQLc and it was not better. I had to shift to SQLx because of how unintuitive SQLc was.

Re: SQLx – Rust SQL Toolkit

#17

Post by devjab »

written-beyond wrote: post #16
jchw wrote: post #12

Earlier quoted context omitted.

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/ 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 mac…

I spent 2 weeks trying to build a very basic rest crud API with SQLc and it was not better. I had to shift to SQLx because of how unintuitive SQLc was.

We've been running SQLC in production for a while now and I'm curious which part of it you found unintuitive? We run ours as a container service within the development environment that will compile your code from a postgres dump file. We've had no issues with it at all after the initial configuration guidelines for SQLC, though the documentation certainly isn't exactly great. Hell, I'm not sure I've ever worked with a better SQL to language tool in my decades so I'm surprised that it isn't working out for you.

That being said, as I understand it, SQLx does something very different. If you want dynamic queries, you'll basically have to build that module yourself. The power of SQLC is that anyone who can write SQL can work on the CRUD part of your Go backend, even if they don't know Go. Hell, we've even had some success with business domain experts who added CRUD functionality by using LLM's to generate SQL. (We do have a lot of safeguards around that, to make it less crazy than it sounds).

If you want fancy Linq, grapQL, Odata or even a lot of REST frameworks, you're not getting any of that with SQLC though, but that's typically not what you'd want from a Go backend in my experience. Might as well build it with C# or Java then.

Re: SQLx – Rust SQL Toolkit

#18

Post by happens »

written-beyond wrote: post #16
jchw wrote: post #12

Earlier quoted context omitted.

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/ 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 mac…

I spent 2 weeks trying to build a very basic rest crud API with SQLc and it was not better. I had to shift to SQLx because of how unintuitive SQLc was.

Interesting - I've had the opposite experience. I usually prefer rust for personal projects, but when I recently tried to use SQLx with sqlite, lots of very basic patterns presented problems, and I wished I had sqlc back.

Re: SQLx – Rust SQL Toolkit

#19

Post by p4ul »

Ameo wrote: post #13

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…

This has been exactly my experience! I've found SQLx to be a joy to work with in Rust!

Re: SQLx – Rust SQL Toolkit

#20

Post by maxbond »

RonanSoleste wrote: post #10
hyperbrainer wrote: post #9

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

Different products. I would not compare them. LINQ is more like Diesel ( https://diesel.rs/ )

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-...

Post reply on HN