Database Views Support (followup on Issue #43)

[Zizico2]

I want to start working on this. But I really don't know where to start. What could we consider a proof of concept? I was thinking a version where we completely disallow insert/update/delete, even in views that some DB (postgres for example) would allow it and consider all columns as nullable, to normalize any nullability inconsistencies (since we are only allowing reads, this wouldn't cause any errors on the DB side). Otherwise the DSL would look like the tables DSL.

Initially this could even be implemented with runtime checks, that panic or throw an error if one tries to manipulate a view. This would make it possible (?) to share the DSL with tables in it's entirety. This could be implemented as an experimental feature gate. unchecked-views or experimental-views or something along those lines.

And about the point you made about primary keys: why does Diesel require primary keys on tables in the first place? What would be the technical difficulties of implementing views, without said primary keys?

The features described above would already be useful for a lot of people. From my experience views are mostly used as read only anyway.

Once we have a proof of concept working I'm sure new questions will arise and we can continue discussion from there.

[weiznich]

Thanks for opening up this thread. To add the most important point upfront: We do not have any design yet how database views should be supported or not, therefore I cannot provide any details on how something should be implemented.

That written, let me try to address a few of your suggestions:

I want to start working on this. But I really don't know where to start. What could we consider a proof of concept? I was thinking a version where we completely disallow insert/update/delete, even in views that some DB (postgres for example) would allow it and consider all columns as nullable, to normalize any nullability inconsistencies (since we are only allowing reads, this wouldn't cause any errors on the DB side).

That would be reasonable.

Otherwise the DSL would look like the tables DSL.

There is no need to design a complete new DSL for that, at least not for querying …. That should reuse the existing query dsl. For defining such a view I would imaging a similar setup than the table! macro. (As that's a proc macro now, you likely can reuse quite large amounts of that code)

Initially this could even be implemented with runtime checks, that panic or throw an error if one tries to manipulate a view.

Runtime errors are unfortunately not a solution for something like that if that should be part of diesel itself. If you want to experiment with such an implementation I suggest you to try that out as a third party crate. For the inclusion in diesel itself the requirements for such a feature are that it cannot cause runtime issues for such things like mutations.

And about the point you made about primary keys: why does Diesel require primary keys on tables in the first place? What would be the technical difficulties of implementing views, without said primary keys?

Diesel requires primary keys for tables because it's best practice to have a primary key on your database table. As for views: views shouldn't implement Table, but only QuerySource. Notably does the second trait not require a primary key at all.

[coderPaddyS]

Here are some thoughts I have regarding the further implementation of views:

== Null-ability
All columns on a view are marked as null-able. Usually a view is simply returning the values according to the query written by the user. This is weird in the first sense, as a user may not expect any null-ability without using a left join or comparable.
Introducing some way to remove the null-ability on a column. My first thoughts are:

Use a annotation which will be parsed by diesel @diesel non-null.
Add a config field to diesel.toml to remove null-able on specified views and columns.
Parse the SQL-Query and link each column to the source-table and take that type.
Find a heuristic which will sometimes mark a non-null-able column as null-able, but not otherwise.

This is something I may or may not take on, it's a desired feature but most likely more time-consuming that I currently be able to invest.

== Updating
Postgres allows for updating or inserting into a view. This could be represented in the future, requiring to modify or build a new DSL for views and possibly implement view not as a supertrait to a table.

== Automatic joins
Most views are simply common queries joining multiple tables. Those views may have multiple columns being a candidate for a primary key. Identifying those and using those as primary key in a diesel::joinable! call would allow users to integrate the DB more and move logic into SQL.