Diesel.rs Trick: Treat View as Table

Diesel is a type-safe query builder for Rust. Its goal is to give you an idiomatic interface for interacting with your database so it's easy to write highly performant and correct queries. But SQL is quite a complex beast, and sadly, this also makes Diesel an inherently complex tool.

On the implementation side, one of the most complex parts of Diesel is the association handling. We take extra care to only allow you to query fields from tables that are part of the query (either as FROM or as JOIN). But currently, this is limited in some ways. E.g., we don't have a way to join the same table twice (as there is no automatic type-level aliasing for the types that represent the database tables).

To work around that, and similar problems, and as an alternative to using the raw SQL escape hatch, you can use this trick: Create a view in a migration, and query this view like a table (by writing a table! macro call for your view).

Example

Let's imagine we have a simple schema with two tables users and follows (this is SQLite syntax):

Here is a SQL query to get the content of the follows table including the names of the follower/followed user:

This is a query you can't currently express in Diesel without resorting to weird tricks. But if this is a query you need, you can easily save it as a view:

Then tell Diesel about it:

Voilà! You can now query this like a table. Let's describe their structures:

As you can see, two records are associated with #[derive(Associations)] and #[belongs_to]. After that you can load the followers for the user using the belonging_to:

Postgres even allows you to call insert, update, and delete on simple views like this. (You can accomplish similar functionality by using INSTEAD OF triggers on SQLite.)

This also works great for aggregate queries, or to abstract over database-specific operations your application doesn't need to care about.

Thanks to @keyridan for adding the associations example to this post!