Wow sqlite has CTE queries!
This improves readability a lot.
I've used CTE queries in clickhouse before and it was a charm. Especially in combination with our nasty python f-string formatting back then.
Sqlx does not seem to like the single row paradigm too much though.
But for now implementing the From trait into transfer objects isn't too bad. At least the query is precise and easy to understand.
for rust backend devs: try out clorinde (github)!
it's a maintained fork of cornucopia, and the main premise as opposed to e.g. sqlx is that you have your queries in separate .sql files that the tool then generates bindings for!
example: if you have this in queries/users.sql:
sebsch
Borup
Michał Fita
lina isidora--! get_followers_by_user_id
select f.follow_state,
f.follower_id,
u.user_display_name
from follower f
left join user u on f.follower_id = u.user_id
where f.followee_id = :id
(the --! part is important! it sets the query name, and is also used for specifying nullable result fields/args)
clorinde will generate a new crate in your project with a wrapper over every query that you can use e.g. like this:
// ...
let followers = users::get_followers_by_user_id()
.bind(&pg_client, user_id)
.all().await;
for fw in follower {
println!("{:?}", fw);
}
I built a little rust server that exposes a little website. Whenever I insert or update a task in the Postgres database, it automatically updates on the website. The whole thing is powered my pg_notify and SSE. This was very quick to build and works wonderfully!
TIL that the infamous N+1 queries problem does not apply to #SQLite. https://sqlite.org/np1queryprob.html
With other databases, it's a bad idea to, for example, look up 3 different configuration parameters by name using 3 queries. You need to batch them into a single query. That's because you have to wait for the database server to answer each query before sending the next one.
I wonder how this affects #Rust #sqlx, though. Running a query with sqlx does involve waiting on a worker thread…
#SQL interfaces could use a way to conditionally enable statement rows. If one writes query by hand you enable or disable statements with just adding "--" at the beginning of the line
SELECT * FROM example
WHERE 1=1
AND a = ?
AND b = ?
-- AND c = ?
AND d = ?
AND e = ?
Similarily programming interface could have option for this, I don't see many query builders doing this ergonomically.
In #TypeScript there is way with template literals, not with #Rust #SQLX
@khleedril To certain extent you can change the backend support, and your app shall work with other database.
#Diesel role is not to abstract what db you're using, but how you're using it, so your data is modelled in your application.
However, saying so I observe more and more people move away from such approach in favour of more data-centric approach and prefer #sqlx in result.
Architecting and building medium-sized web services in Rust with Axum, SQLx and PostgreSQL: https://kerkour.com/rust-web-services-axum-sqlx-postgresql
Jak2k 🏳️🌈
ARGVMI~1.PIF
Jari Pennanen
Tedi Heriyanto
Dave Warnock