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

61–70 of 101 posts

Re: SQLx – Rust SQL Toolkit

#61

Post by koito17 »

solidsnack9000 wrote: post #55
bitbasher wrote: post #15

Earlier quoted context omitted.

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.

This is not quite the same thing, because it requires `sqlx prepare` to be run first; and that talks to the database to get type information. In SQLC, on the other hand, query parsing and type inference is implemented from first principles, in pure Go.

sqlc's approach has its limitations. Its SQLite query parser is generated from an ANTLR grammar, and I've encountered situations where valid SQLite syntax was rejected by sqlc due to their parser failing.

Type inference was okay, since SQLite barely has any types. The bigger issue I had was dealing with migration files. The nice part about SQLx is that `cargo sqlx database setup` will run all necessary migrations, and no special tooling is necessary to manage migration files. sqlc, on the other hand, hard codes support for specific Go migration tools; each of the supported tools were either too opinionated for my use case or seemed unmaintained. SQLx has built-in tooling for migrations; it requires zero extra dependencies and satisfies my needs. Additionally, inferring types inside the actual database has its benefits: (1) no situations where subsets of valid query syntax are rejected, and (2) the DB may be used for actual schema validation.

For an example of why (2) may be better than sqlc's approach: databases like SQLite sometimes allow NULL primary keys; this gets reflected in SQLx when it validates inferred types against actual database schemas. When I last used sqlc, this potential footgun was never represented in the generated types. In SQLx, this footgun is documented in the type system whenever it can detect that SQLite allows silly things (like NULL primary keys when the PK satisfies certain conditions).

Re: SQLx – Rust SQL Toolkit

#62

Post by inbx0 »

zackangelo wrote: post #56
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…

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

Re: SQLx – Rust SQL Toolkit

#63

Post by selfmodruntime »

Thaxll wrote: post #38
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.

imo sqlc from Go is supperior to sqlx from Rust. The other thing is that sqlx is somehow slow, when I did some test, pgx ( Go ) was faster than sqlx.

sqlx pulls in `syn`. Syn is really slow to compile.

Re: SQLx – Rust SQL Toolkit

#64

Post by seivan »

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…

[dead]

Re: SQLx – Rust SQL Toolkit

#65

Post by fstephany »

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…

I agree with you that dynamic query building can be tedious with a pure SQL approach. The use case you are describing can be solved with something alone the lines of:

  WHERE organization = $1
     AND ($2 IS NULL OR starts_with(first_name, $2)
     AND ($3 IS NULL OR birth_date > $3)
With SQLx you would have all the params to be Options and fill them according the parameters that were sent to your API.

Does that make sense?

Re: SQLx – Rust SQL Toolkit

#68

Post by rzmmm »

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.

Re: SQLx – Rust SQL Toolkit

#69

Post by yahoozoo »

fstephany wrote: post #65
inbx0 wrote: post #62

Earlier quoted context omitted.

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…

I agree with you that dynamic query building can be tedious with a pure SQL approach. The use case you are describing can be solved with something alone the lines of: WHERE organization = $1 AND ($2 IS NULL OR starts_with(first_name, $2) AND ($3 IS NULL OR birth_date > $3) With SQLx you would have all the params to be Options and fill them according the parameters that were sent to your API. Does that make sense?

I think the dynamic part is where the clauses themselves are optional. For example, say you have a data table that a user can filter rows using multiple columns. They can filter by just `first_name` or by `birth_date` or both at the same time using AND / OR, and so on. So you’re dynamically needing to add more or less “WHERE” clauses and then it gets tricky when you have to include placeholders like `$1` since you have to keep track of how many parameters your dynamic query is actually including.

Re: SQLx – Rust SQL Toolkit

#70

Post by dathinab »

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…

> dynamic query building

it's not (really) addressed by sqlx (intentionally), in the same way most ORM features are not addressed

but to some degree this is what is so nice about sqlx it mainly(1) provides the basic SQL functionality and then let you decide what to use on top of it (or if to use anything on top).

If you need more e.g. the sea-* ecosystem (sea-query, sea-orm) might fulfill you needs.

(1): It can compile time check "static" queries (i.e. only placeholders) which is a bit more then "basic" features, but some projects have to 99+% only static queries in which case this feature can move SQLx from "a building block for other sql libs" to "all you need" to keep dependencies thinner.

Post reply on HN