Virtual Columns and Query Interception

[DenisBessa]

I have a specific question regarding the handling of virtual columns and query interception:

Consider a files table with an id column and a virtual data column that fetches file contents from an external service (e.g., S3).

While the "JS runtime computed" feature enables async functions to retrieve data for the virtual data column, I'd like to implement the reverse operation: intercepting insert queries to execute an async function that uploads files to S3.

I attempted this using beforeHooks, but couldn't access the data of the row being inserted. The documentation indicates beforeHooks should return a function accepting a q parameter (presumably the executing query), but extracting data from it isn't clear.

The ability to define JavaScript functions for virtual columns excellently reduces complexity by keeping business rules at the table level, making select and insert operations behave like standard SQL columns. This functionality would be valuable.

Great work on the project.

[romeerez]

This is technically achievable to add such a new feature, but I'm having some doubts regarding if it is worthy.

In general, I believe in separating business logic from the querying/persistance layer, and that's what Orchid ORM is - abstraction for querying and persisting. A little hint for this: "real" ORMs call their table abstraction classes as entities or models, while I'm calling them tables.

Why you'd prefer uploading in the beforeHook or inside the query rather than before the query?

[DenisBessa]

Thank you for the quick response!

In this case, I believe the functionality would be useful because it allows a column to be completely external to the database while still working "transparently" in all queries.

In my scenario, I have some tables that store binary files, which I would like to send to S3. There are few tables, but many queries that use them, and I would have to do a major system refactoring.

I like to think of database tables as a "single source of truth," and I wouldn't want to give up this concept just because a small part of the data isn't stored in the database.

[romeerez]

After working for ~5 years on node.js backends, my instincts are screaming "gosh, this is insane! why'd you ever would want to do that? magic bad!".

But, prior to node.js I worked with Ruby on Rails where magic is praised, things are designed to be transparent as you're saying.
And yeah, I briefly checked how they're doing that, how I was doing that years ago, and it was just like you're saying:

# carrierwave library example

u = User.new
u.avatar = params[:file]
u.save! 

The file is stored in db just in dev environment for simplicity, but it's configured to upload to S3 or other places in a different env, and this works, it simplifies lives, is configurable and extendable. From this perspective this is totally worthy.

Yes, it is worth adding virtual columns with async handles, technically it's achievable, with a tough part of designing it in a usable way, and it would potentially unlock incredible libraries like the one in RoR.