drizzle-team / drizzle-team/drizzle-orm
[FEATURE]: Simulate Virtual Generated Columns for Postgres
- Dominant language
- TypeScript
- Stars
- 35.8k
- Forks
- 1.6k
- Avg merge
- 2d 7h
- Merged PRs (30d)
- 4
Description
### Feature hasn't been suggested before.
- [x] I have verified this feature I'm about to request hasn't been suggested before.
### Describe the enhancement you want to request
Possibly a niche, nice-to-have feature, but worth considering.
The lack of Virtual generated table columns in Postgres is quite a frustration, especially coming from a database engine like MSSQL. Our use cases do not lend themselves to stored generated columns due to use of `CURRENT_TIMESTAMP` and other mutable functions; this and the [other limitations](https://www.postgresql.org/docs/current/ddl-generated-columns.html) of stored generated columns reduce their usefulness to some degree.
Hence, the proposal is to add a `generatedVirtualAs` function to the column generation that allows SQL to be generated during a SELECT statement. For example:
```ts
export const myTable = pgTable("MyTable", {
// other columns
dateCompleted: datetime().notNull(),
virtualColumn: boolean().generatedVirtualAs(sql`"dateCompleted" < CURRENT_TIMESTAMP`)
});
```
Which would generate the following SQL during a SELECT query:
```sql
select "MyTable"."dateCompleted","MyTable"."dateCompleted" < CURRENT_TIMESTAMP as "virtualColumn" FROM "MyTable"
```
Thus, the virtual column is not actually created in the postgres table, but is rather simulated using an inline SQL statement. Full type-safety is preserved, such that `$inferSelect` contains `boolean` for `virtualColumn`, and `$inferInsert` omits it.
This could be a very powerful feature, considering that mutable functions, subqueries and references to other generated columns could be used, which are the main limitations of stored generated columns.
Contributor guide
Assessment
This issue has not been assessed yet.