Hey there!
I’m trying to get Ecto working with a virtual field that I have in a Postgres view, which is basically attributes->>"email" AS email and then field :email, :string, virtual: true in the corresponding Ecto schema. For some reason Ecto doesn’t set the value for that email field when data is fetched from the database.
Is this meant to work? If not, how can I achieve this?
Thanks!
a virtual field is something that will never come from the DB, it’s something that you set at the application side. if this is one of the fields of the view, it should be a normal field, doesn’t matter if it’s projected from a jsonb from the query that builds the view.
@LostKobrakai provides a really nice example in Github. See the “delete” attribute that is virtual. He also describes the project in this blog.
Ahh thank’s y’all. I think I got confused because in my brain “virtual” was needed because it’s not a field that we persist, it’s calculated based on another field. After removing virtual: true things work indeed. I just needed to make sure we don’t allow passing email values as input, as that would not work.
A good way to think of it is that an ecto schema is just a mapping of fields and types. when dealing with views, the idea is that you always gonna use it just to build queries. What I usually do on those cases is to nevere have a changeset/2 function on those schemas(and it’s good to add a module doc making it clear that it’s mapping a view and not a table).
oh we actually do write to our views so there are changesets involved 
TIL, i’ve always used views as a read thing 
In world where the only databases I interact with are Postgres and SQLite, it only works with Postgres and there are caveats to how complex the view can be (I don’t know those caveats off the top of my head).
Depending on the database engine, writable views can be very useful.
They can enable a more durable data access methodology to the application in cases where more complex information architecture needs require addressing the database table structure.
“Source-of-truth” level data validation can be handled via at a database level using updatable view mechanics.
Similarly, security boundaries can also be established using updatable views.
Naturally, not everyone needs to contemplate doing these things via the database. But in the enterprise/best-of-breed/integrated applications world that I tend to find myself in, the small performance cost is often times worth the benefits in data integrity and overall maintainability.
In fact, my current Elixir application currently only accesses the database using updatable views. In a very real sense the data and the database are considered more fundamental than the application; again in an enterprise context. The views allow me to only let the application connect to the database with just enough permissions to perform its required operations and to submit to source-of-truth validations, rather than allow the application to access the database with broad permissions which could allow it to do things which wouldn’t make sense: either because of developers getting too clever or in the case of a security compromise.
Anyway… long discussion possible on the trade-offs, but updatable views are definitely “a thing”.
From the Ecto perspective… just treat them as though they are regular, concrete tables. Everything else follows.
In PosgreSQL you can do some pretty complex things. Older versions of PostgreSQL required use of the rule system (PostgreSQL: Documentation: 16: Chapter 41. The Rule System), but in newer versions we have INSTEAD OF triggers (no great docs on these alone, they are discussed in the CREATE TRIGGER docs).
I’ve actually used PostgreSQL updatable views which weren’t really backed by tables, but as a table-like interface for making PL/pgSQL function calls; in this case I was doing FDW access from one database to another and that table-like access method allowed that to be handled transparently over the FDW interface. Not a common thing, but sometimes can be useful.
Oh cool, I was unaware of INSTEAD OF, thanks!
© Copyright Elixir Forum | Terms | Privacy & Cookies
