Diesel: A Safe, Extensible ORM and Query Builder for Rust
diesel.rs
diesel.rs
Post::belonging_to(user).load(&connection)
SELECT * FROM posts WHERE user_id = 1;
If you use this, it nails the database schema into your Rust code, because this needs to know the table formats. If someone adds a new field to a row, you have to rebuild all your Rust programs that use the database. Now your deployment process just got much more complicated.
It's a neat hack, but seems an overly complex way to do something that's not that complicated. Unless this has some huge performance gain over rust-postgres[1], it's probably too much.
If rust-postgres is too slow, it would be worth having an optional generic for rust-postgres to help with unmarshalling returned rows. Rust-postgres returns row objects from which fields can be extracted. Converting that to a Rust struct is the only operation which needs to go fast.
Additionally, SQL is extremely hard to compose. In my applications, I very rarely have some specific one off query that I want to run, I have multiple predicates that I want to be able to mix and match easily. String munging is fragile, while this actually lets me build reasonable domain abstractions.
To your final point "Unless this has some huge performance gain over rust-postgres"
Diesel performs 33% faster than rust-postgres, and will be competitive with if not faster than ideomatic C.
I've seen almost everyone who doesn't use SQL directly say this but I always feel it's the exact opposite. SQL is easy to write once you do it enough. I've gotten to the point where I compose zero SQL in any application's code and, instead, do it all inside of stored procedures (of which they do not generate dynamic SQL). Then my code just calls stored procedures, my database users for the web application never have more access than executing a specific subset of stored procedures, and if there is a bug found in a stored procedure I can deploy them separately from the web application.
I'm guessing it's just different philosophies but almost every time I've used something that abstracts an existing interface to another, completely different interface, I always end up running into major issues.
Ultimately an ORM can help you to build more maintainable and resilient (to changing market conditions) software than direct SQL. Diesel in particular can catch mistakes that SQL strings cannot.
I do understand why a lot of people have reservations about ORMs. I share a lot of them. I've maintained Active Record for about 2 years now, and learned a lot from doing so (note: This does not mean that I like AR or want this to be anything like it).
TL;DR: Your points aren't wrong. Diesel is different. Maybe give it a shot. ;)
I couldn't disagree more. Creating the separation between SQL DB and web application allows you to maintain, deploy, fix and upgrade things independently and it's easy (maybe not quite as easy as an ORM would make it but not really harder either; you're going to have to know how SQL works anyway if you want to effectively optimize and secure your data).
Not only that but you can provide far better security controls because you can limit certain users access to certain procedures, never direct access to tables and various other things and if you don't do any dynamic SQL in the procedures itself you not only never have to worry about SQL injection but unless there is a critical vulnerability in the SQL system itself it's going to be impossible to conduct an injection attack.
Yeah you can separate your code to make it easier to deploy separately to get a similar benefit and yeah your code can prevent SQL injections too but it's so much more work, more possible points of failure and the differences between databases can really bite you in the ass when you do everything generically. SQL is fun :)
> TL;DR: Your points aren't wrong. Diesel is different. Maybe give it a shot. ;)
Yeah I'm not saying NEVER use ORMs; they're great for creating things quickly that don't need to scale. But after my experience with them I'm highly skeptical using them outside of that type of use case. Of course you can scale ORMs but I've never seen it done well.
I'm curious what you mean by scale. Are you referring to performance, or code size? If you're referring to performance, you'll probably find it interesting that we out-perform or are competitive with ideomatic C.
Don't get me wrong ORMs can certainly be performant. I just haven't seen or know anyone who used it in a manner that scaled to thousands of concurrent users and, at the same time, didn't have a lot of pain trying to do that with the ORM.
A lot of systems lately are being built in a different way, that is, only one application connects to the database. This application presents an API (ReST, protobufs, thrift) for other apps that need that data. It handles access control and business logic, using the DB as a backing store. That means you don't have to worry about dababase user access control, because you don't have more than one user.
I don't have a strong opinion about either of these, but the latter way of working is how we do things at my current job (reasonably large payments software company), and it does work quite well. One advantage I appreciate is that there isn't arbitrary business logic sitting in the database where it can be changed on the fly - I wouldn't consider that a feature.
At least in Postgres they act as an optimization fence. In practice this means you can use them as an abstraction layer only in very limited ways where they happen to fit into a decent execution plan.
Don't get me wrong I see the simplicity in it from the ORM side I've just never had a good experience with ORM tools. Having worked directly against many RDMS and key value stores at least for me I don't see much value in them.
But anything that helps get a product out the door more power to ya, in my opinion.
I just implemented this recently in my job, but it was even tougher than that; I was joining dynamically created tables, and the number of joins depended on criteria. I ended up building my own AST to represent filters, and compiling to SQL. Every extra table join hurts performance - typically filters are implented as joins against a filtered sorted limited subquery returning ids, so fewer different table joins means less data to pull in when evaluating filters even when all the data is needed for the results page.
Point being, I had to compose complex dynamic queries and SQL was not a particularly pleasant target language to target directly. Abstractions (like asts) help with composition.
Sorting is easy. The stored procedure just accepts a sorting flag, add it into your query and your done. Filtering though, I could think of a dozen types of things you may want to filter on and I still may not hit your use case so it's hard to say how to do it based on what data is stored in the database, how it's stored and which RDMS you're using.
I've primarily done this type of work in MS SQL where I've built some pretty complex queries but I was always able to replace dynamically generated SQL with SQL that could generate a cacheable query plan.
Not saying there won't be edge cases and I don't know how your filtering worked so I don't have any type of satisfying answer for you. I would just be surprised if it wasn't doable somehow (I've seen some crazy shit in SQL queries :D)
Sorting has a couple of wrinkles. When Excel sorts rows, it changes the in-memory order; its sort is also stable. That means if you sort by column C, then B, then A, you're effectively doing "order by a, b, c". Seems simple enough. But that's not the only wrinkle in this problem.
Not all sortable columns are in the same table. If you do something naive like "select x.* , y.* from x join y [...] where [...] order by x.a, y.b limit 10 offset 20000", you're doing too much work - you're sorting a wide result set. It's normally better to do your pagination in a subquery and join against it, like this:
select x.*, y.* from x join y [...] join (
select x.id from x join y [...] where [...]
order by x.a, y.b limit 10 offset 20000
) f on f.id = x.id
order by x.a, y.b
The body of the where clause is another story, it's pretty much entirely arbitrary. The user may choose to filter on any column, just like Excel autofilter. Creating good indexes up front isn't feasible - you don't know how many columns to include in any one index, they take time to create and they're not free in disk storage either. And the filtered columns span multiple tables; the final result set may contain several hundred columns across all these tables.
So what I did is take the criteria - the filter clause, which might look something like this:
(and (or (eq (field 'x.f1') "foo")
(eq (field 'y.f2') "bar"))
(between (field 'm.timestamp') "date1" "date2"))
There are more wrinkles. To give a good experience, we need to populate a dropdown of potential filter values, like Excel autofilter. Potential filter values need to be filtered themselves - we only display filter values for that are potentially visible given the current filter. They also need to be searchable, because there may be millions of them. And they may include joins, like when you want to filter by tags or some other 1:N style relation, like row:tags. There are a bunch of different shapes to the different queries, but there are common composable underlyers to them.
All the above runs on mostly unindexed tables - we're relying on pure query compute performance within the designed data limits (less than 3 million rows). Sub-millisecond query response times aren't the goal here; we're supporting the user doing reasonably complex data manipulation operations with worst case performance measured in single-digit seconds.
(This is all in MySQL, because reasons that include business model constraints.)
-- Parameter :filter
--
AND CASE WHEN :filter IS NOT NULL THEN
table.column = :filter
ELSE TRUE END
That's where I usually get fed up with stored procedures, as pseudo-ADA ain't a nice language for list and string manipulation. I'd rather do that in a language better suited, and that quickly leads to query builders…
Diesel performs 33% faster than rust-postgres, and will be
competitive with if not faster than ideomatic C.
Long version: http://bikeshed.fm/49
Longer version: Planning on doing a really in depth write up for the website soon. I'll also be going into more detail during my talk at the SF rust meetup, which there's a link to the live stream towards the bottom of the thread
As far as I saw, Diesel is essentially shipping with its own PostgreSQL driver.
Please include in your larger talk!
(Sorry for the double post, I think I replied to my own comment and not yours!)
I don't think you're ragging on rust-postgress, although I'd probably attempt to change rust-postgres first :P
rusqlite has the same issues. libsqlite doesn't since it's just C bindings. Same as pq_sys. Ironically, rusqlite makes the same design choices as rust-postgres, but it's actually wrapping a heap allocated object anyway, so it gains nothing by doing so...
> I'd probably attempt to change rust-postgres first
I did, too. It turned out to be a pretty major rewrite.
In my experience outside of extension code the need for composable SQL is very limited. Most uses cases can be satisfied by Rails 2 scopes.
Now, if composability was free - sure it's great. But it's not free.
So, on one hand:
* it would be nice to have infinitely composable SQL
On the other hand:
* it would be nice to just have the actual SQL with all the features, well understood syntax and semantics that cost decades and billions of dollars to develop
* and no wrapper-on-top-of-SQL limitations and translation bugs, and need for training
With these language-on-top-of-SQL you don't get both. In fact you get severe compromises in the second group.
I'd love to see a solution with tradeoffs made the other way:
* syntax and semantics 99% like underlying SQL
* support for 99% of underlying DB functionality
* traditional areas where ORMs add value:
* deserialization into convenient data structures
* casting, quoting and escaping on the way in and out
* using stored association metadata for concise joins in the queryColumns may be sorted and filtered individually or in combination. Filters may be based on value set membership or range criteria, comparisons with fields in the data or via a join to other tables, or based on the presence of an entry in a join table (think: tags or labels, and you want to filter by presence or absence of tag).
(This is what I spent the past couple of months implementing. The simplest way of composing SQL is via union / except / intersect, but it's also the worst performing; doesn't work great past a few hundred thousand rows. And you still need to compose together the filter conditions into a where clause.)
Example:
var posts = connection.Query<Post>("select * from posts where user_id = @UserId", param: new { UserId = 1 });For example, in ActiveRecord (the Ruby library that this looks very much influenced by), given:
posts = Post.where(user_id: 1)
recent_posts = posts.order(created_at: :desc).limit(100)
tagged = recent_posts.where('tag in ?', ['hacker', 'news'])
tagged.group_by('tag')
tagged.update_attributes({author_id: 2})
tagged.destroy_all
If the query were just a string, this sort of thing becomes awkward, verbose, brittle, and generally not type-safe.
A "proper" solution to this problem requires language changes (as Microsoft did with C# 3.0).
Look at what Microsoft did with LINQ. You can't pull that off in Rust no matter how much you want to.
For example, ActiveRecord doesn't have support for window functions, but its underlying AST (a library called ARel) allows you to extend it, which at one point I did, allowing me to write something like this:
things = Arel::Table.new('things')
things.project(Arel::Nodes::NamedFunction.new(:sum, [things.count]).over('my_window')).
window('my_window').
order(things[:name]).
range(Arel::Nodes::Preceding.new)
select sum(count(*)) over my_window
from things
window my_window as (order by name rows unbounded preceding)
In other words, I could continue to express whatever I wanted of SQL, at the language level — no type mismatches.
The SQL builder code isn't pretty, to be sure, but it's something you typically encapsulate into a lower-level module rather than writing in application code. And as it's highly analogous to the SQL code it generates, it's not like it's that much more complicated. (ARel isn't a shining example of how to do it right, either. I expect Diesel implements the AST aspect more cleanly.)
Edit: I see what you're saying about typed record types. I was thinking more about the grammar, not the shape of the data.
I'd like to point out that strong static-typing does not preclude powerful metaprogramming! Nim's macro system is extremely powerful because of it's great interplay between it and the type system.
The main reasons I like this approach are:
* it makes way easier (and safer) to reuse query parts compared to string composition/interpolation
* the ORM takes care of differences between the backends, so it is easier if I have to port the application to another RDBMS.
1. http://www.sandimetz.com/blog/2016/1/20/the-wrong-abstractio...
Whereas as an applications programmer I'd rather deal with structures that make sense in my domain of interest, and let the marshalling code from SQL results be handled for me, and with compile-time checking for correctness. When that's coupled with a database schema reverse engineering tool (not sure if this project provides one), that way whenever the database changes, compile errors will result, which errors allow me to quickly hunt down places in my code that are affected and need changing.
Are you sure? Why do you say that?
On the contrary, the purpose of an ORM is to let your code be a schema for your database. This is my personal use case for an ORM; I want persistence but am not overly concerned with the database. There are plenty of use cases [1] where a schema update from outside is unlikely or impossible. In these cases, there is a very low cost for (by way of an ORM) tightly coupling your code to your schema.
Also, an ORM lets somebody else worry about sanitisation, and database specific-code. It can abstract away the choice of database, another very valuable feature. I think that the example you quote is actually a poor demonstration of the value of an ORM. The other ones on the linked page are far better :)
[1] A program running on only one machine, like an Android app or a music player on Linux desktops etc
Why is
Post::belonging_to(usre).load(&connection)
SELECT * FROM posts WHERE usre_id = 1;
... to which the answer is probably more obvious.
[1]: http://www.meetup.com/Rust-Bay-Area/events/219697075/
[2]: https://air.mozilla.org/bay-area-rust-meetup-february-2016/
It makes heavy usage of Rust's type system features, particularly zero-sized types, to provide a large degree of safety with no overhead. Sean has mentioned several times that he even believes that he is faster than literal SQL strings, due to pre-compiling them in some way, but that's a bit out of my league.
He also wants to have features like "type-check all of your queries at compile time and then query the database to make sure that the schemas all line up" and other such compile-time safety shenanigans.
Wouldn't waiting on the network eat up any possible performance gain you could expect from Rust vs something like the JVM or Go runtime? Before modern day JavaScript JITs, relational database systems were the epitome of dynamic runtime systems. There's so much performance variability in the query planner, network, disk caching, etc, to make garbage collection a rounding error. Zero-cost abstractions are just not a particularly useful thing in this context from what I can tell.
Even if you do have genuinely CPU-bound tasks, why wouldn't you use Rust for those modules + call it via FFI from whatever is doing most of the boring business logic? If it's just about static-type-check-all-the-things! I just can't get excited about it vs something like Go.
https://crates.io/crates/postgres/
https://crates.io/crates/mysql/
This is an experimental approach. It might or might not be useful, but you don't have to use it.
It seems like the intersection of good use case for Rust and need to use SQL, will also intersect with "minimal SQL needs", hence preference for simpler libraries.
And a lot of them really suck, too, because it's a hard problem. I'll take query builders or raw SQL over ORMs any day of the week.
Long version: http://bikeshed.fm/49
So if Rust is not substantially easier or harder than Go, when Rust catches up to Go in the library ecosystem, I can see why people might want to use Rust. Also, it's nice to have better language support for functional strategies.
I didn't pick up Rust as quickly as Go. However, that's not really surprising, because Go is approximately the lowest common dominator of modern statically typed languages minus generics. (In a good and bad way.)
At any rate, I started learning Rust ~2 weeks ago and I am already writing small libraries, programs, etc. for my daily work.
Although that took a bit more time than Go, it's also a much more pleasing language to write in (the lack or sum types or generics in Go is very annoying) and you get more safety guarantees.
Interesting anecdote: Last year, for a course I had to write the assignments in Go. As a Rustacean, I grumbled about the lack of sum types a lot, and made various hacks via interfaces writing unidiomatic Go code to simulate them. My code design was also very Rust-y and unidiomatic.
This year, I'm TAing the same course, and there's a similar assignment. One student's submission was eerily similar to my design (by now I knew that it was unidiomatic), down to the hacks used. It also contained a comment saying "missing Rust's enums" (my code contained a similar comment to explain the hacks). At first I thought it may have been copied from my code (which can be found if you're looking for it), but it was different enough in other areas. I dug a bit and found that the student was indeed a rustacean.
It's rather interesting that two rustaceans used the exact same hacks and same overall design when asked to code in Go.
Surprisingly, I don't miss generics too much in Go. interfaces used to make me cringe due to the extra runtime cost (since I knew Rust before learning Go, and such solutions would make me cringe in Rust), but once I got used to Go I learned to use them properly. Named interfaces are pretty easy to use and let you structure the code decently well, just like proper generics. They may have a runtime cost, but Go doesn't put as much focus on that as Rust.
On the other hand, easy-to-use non-hacky sum types are something I miss from Go all the time.
As someone belonging to that group, I agree. short programs are easy to write in Go. If I need to write some utility script I would do it in Go (or more likely python). However, if I need to write a whole new thing I find it very easy to design in Rust in a modular way.
Similarly, if I have a Go and a Rust codebase in front of me, and I need to make changes, it takes longer to work with the Go codebase for nontrivial changes. But this is less stark a difference than the above.
I will say that "modulo compiler perf" is an issue. I was tinkering with the Go compiler and the fact that I could try things out quickly was a big boon. However, I had to spend time figuring out which interface goes where (the documentation doesn't show interfaces implemented on types, you have to figure it out by looking at the source -- this is something that could be fixed though), so it might even out.
But yeah, fixing errors is not hard in Rust (nor do I often get borrow errors, usually it's other stuff), and in fact I find it easier to do than Go because Rust provides extensive errors with suggestions as well as showing the erroring thing inline, whereas Go just points out a file and line number (Again, this could be fixed).
My productivity has been greatly improved by restricting myself to `cargo check` when writing code. It only does type and borrow checking which is much faster than doing all of the LLVM passes (or whatever rustc does after those passes, on that subject I am ignorant).
I've found that when using Atom with linter/linter-rust installed and setup with cargo check I rarely context switch while waiting for errors/warnings to be highlighted (and this is on a 1.6Ghz Xeon machine at work). But maybe I'm just OK staring blankly for slightly longer than others :).
Anyway, I don't mean to dump on your work. It seems like it's actually quite a nice implementation. I'm just questioning 1) the size of the audience and 2) Whether or not the audience who thinks they want this should actually want this.
In fact, that's behind: strong memory safety, Option/no-null (probably lumps in with "strong memory safety") and of course generics and the type system at large.
The speed is honestly a nice benefit. (I'm not being sassy, I don't have speed intensive applications and still reach for Rust).
Think about JavaScript and Node.js. Who would have predicted that?
It's also a pain in the ass to register because you have to have a Serbian tax ID
There is an Italian clothing company "Diesel S.p.A." that tries to protect their brand by suing almost anyone using "Diesel".
For example a German video game called "DieselStormers": due to a successful trademark lawsuit by Diesel S.p.A., who owns the trademark "Diesel" in relation to video games, Black Forest Games had been required to drop the title DieselStormers. http://www.eurogamer.net/articles/2015-09-30-dieselstormers-...
Such greedy companies act very similar to patent trolls. Well, there are sadly several other common words that are tainted by such "brands".
Edit: In 2015/2016 the Volkswagen (VW) Diesel scandal tainted the "Diesel" name too: http://autoweek.com/car-news/vw-diesel-scandal
// application.css:529
body {
-webkit-font-feature-settings: "kern", "liga", "pnum";
-ms-font-feature-settings: "kern", "liga", "pnum";
font-feature-settings: "kern", "liga", "pnum";
// ...
}I'd rather use template string generated promises in node, tbh, than any ORM.
This is one of the shortcomings of ActiveRecord. The SQL building used to be extremely ad-hoc, and though ARel was eventually written to fix this problem, the integration between ARel and ActiveRecord is not very fluid (and ARel itself has some design issues; if I remember correctly from the last time I worked with it, it's not immutable, which can be very confusing when chaining expressions).
Also random fun story about Arel not being immutable. We had a bug in 4.1 where basically PG bind params are ordered, but Relation can't actually know the order ahead of time (e.g. because a Relation can be used in a from clause). Turned out actually fixing it was somewhat hard (the "real" fix was eventually https://github.com/rails/arel/commit/590c784a30b13153667f8db...). But as a hack, basically someone took advantage of the fact that `Arel::Nodes::BindParam < SqlLiteral` and `SqlLiteral < String`, and Strings are mutable. So they just ignored the lack of a proper map function, and fiddled with the strings. >_>
Anyway, yeah our AST is fun because Rust's compiler lets us set it up so that we can walk it as many times as we need basically for free (because it gets monomorphised and the walking ultimately gets inlined and optimized away)
I couldn't figure out from cursory reading how "PgConnection" works or how it would be compatible with other IO-related code.
I'm thinking of question such as :
-embedded http server vs using apache or nginx ( eg : node and go seem to favor not using third party servers at least for simple cases)
- prefered concurrency model ? ( async vs thread based vs coroutines vs...)
- deployment issues ? ( single binary with everything inside, vs parts that need to be predeployed on the server beforehand)
- third-party connectivity ? ( diesel seems like a really fantastic way to query a db, but what about other storage such as s3, mongo, redis, queue systems, etc)
- current frameworks status ?
I know it sounds like a lot, but as ORMs is usually the major pain point for me when looking at a new techno ( it's one of the main reason i'm not using go, for example) and now that diesel seems to answer all my needs, i'm REALLY looking forward to start using Rust. I just need those questions to be answered, and i think i'm not the only one.
> embedded http server vs using apache or nginx
> prefered concurrency model ?
> deployment issues ?
> third-party connectivity ?
> current frameworks status ?
IIRC, he hints at working on one (I think with Yehuda Katz?) in the podcast where he talked about Diesel:
It would be really cool if this had an easy integration with postgis that uses simple syntax to query location
When I'm using a node framework and using postgres I have to use a nosql db for playing with coordinates as postgis syntax is not something i am very fond of
If you're interested in adding postgis support, ping me in our Gitter room. I'd be happy to help.
Generally we're designed for the idea that you'll have a separate struct for read, create, and update. We also try and design for the latter two mapping roughly to web forms, as this is how most large applications end up wanting to be structured from my experience.
The main thing I'm trying to avoid is lumping all your logic in a single place, and ending up in many of the same rabbit holes that Active Record has led us to (which is at least partially my fault)
Also, what about upsert?
Correct. Sorry, I should have been clearer in my last comment. The reason you'd want a separate type for insert is because wrapping your id in an `Option` would be painful for anything except insert.
> but my first thought is of how much boiler plate will result.
Yeah, if you're coming from Active Record (which is the other project I maintain), we'll probably seem a little bit verbose. My goal here is to be 80% as nice to get started with, but nudge users towards significantly more maintainable code as their product grows.
> I would guess most record types will be very similar between create, update, and retrieve.
Early on, yes. 2 years in, not so much.
> Also, what about upsert?
This is something I'd like to add support for in the future. This comment made me realize I didn't have a tracking issue, so here it is: https://github.com/sgrif/diesel/issues/196
I guess it's because I'm coming from KDB ETL work when I think about database stuff that I don't share this perspective. But in KDB, the database has no notion of non-null columns or auto-ids or anything.
We were working in Scala, and I had a go at solving this using a higher-kinded type for the ID and other changing fields:
https://github.com/tim-group/higher-kinded-lifecycle/blob/ma...
The idea is that if you say that the ID is of type 'T of String, where T is some subtype of Option', then you can bind T to None for entities that haven't been saved, Some for ones which have, and Option where you have a mixture. You then have one type definition, with which you can write stage-agnostic code, but static guarantees about whether there's an ID.
You can go further and use type constraints to enforce the order of lifecycle stages: if your object can only be saved to the database once it's been given a timestamp, say, you can make sure that the ID type can only be Some if the timestamp type is also Some.
So maybe when Rust gets HKTs it'll be time for a new release!
Edit: let's paraphrase - how excessive safety nets for construction workers makes buildings more safe?
A strong-typed language without implicit coersions is not more "unsafe".
Baning heterogeneous containers and conditionals, maybe-wrapping of everything to be able to check some constraints at compile time and catch simple errors (which also would be caught in a strong-typed language at run time) does not quality for this "safe" meme.