Computed Columns

[leopascual]

Hi I didn't find a way to make a computed column out of previous columns defined.

Table LOA_AMT_AmortizationTable{ AMT_LoanID bigint MonthPeriod int SaldoInitCapital_PrincipalInitialBalance double Pago_Payment double PagoCapital_PrincipalPayment double PagoInteres_InterestPayment double PagosCapitalAcumulado_CumulativePrincipalPayments double PagosInteresesAcumulados_CumulativeInterestPayments double SaldoFinalCapital_PrincipalEndingPeriodBalance double [cc: SaldoInitCapital_PrincipalInitialBalance - PagoCapital_PrincipalPayment ] }

[tuanmonn]

Hi @leopascual I'm Tuan, maker of DBML. Thanks so much for the request.

We design DBML mainly to define the database structure, so such function (used for data modeling) has not been supported yet.

However, I have noted this down for the team to consider and discuss. Will let you know if there's any news!

[chishaku]

Hi Tuan, thank you for this great software.

It's been very helpful for us to visualize our ideas and quickly generate the basis for migrations.

It would be great to see support for computed/generated columns.

As generated columns are first class citizens within postgres and mysql create table statements, I think there is a strong argument that generated columns are part of defining database structure and within scope for DBML.

Here's a contrived example building off the existing syntax:

Table person {
    id int [pk, increment]
    name varchar [not null]
    height_in numeric
    height_cm numeric [generated: height_in / 2.54]
}

Thanks for any feedback or update on this potential feature.

[JacobHorwath]

Hi @tuanmonn and @chishaku. I would argue that computed columns (in MSSQL) are not simply for data modeling. These are persisted objects in the schema.

Consider this simple scenario:

CREATE TABLE [dbo].[test1](
	[foo] [INT] NULL,
	[bar] [INT] NULL,
	[foobar]  AS ([foo]+[bar])
);

sql2dbml will error on [foobar] line, even though this is a column in the table.

I am less familiar with mysql and postgres, but it does sound like the "generated" column concept is nearly equivalent.

In either case, they are objects in the schema.

Thanks for considering this!

[chishaku]

Hi @tuanmonn!

One approach to this problem could be to enable appending arbitrary clauses to the column definition. That could enable more flexibility for different SQL flavors

Table person {
    id int [pk, increment]
    name varchar [not null]
    height_cm numeric  
    height_in numeric [append: "GENERATED ALWAYS AS (height_cm / 2.54) STORED"]
}

[pozsa]

Hi @tuanmonn, do you have any news on this? Did the team manage to discuss the feature request? For the time being my workaround is to put "GENERATED ALWAYS AS () STORED" as a note on the column.

[nihaux]

Ping would love to see this happening too. Append thing from @chishaku seems a good workaround. Especially when using dbml2sql

[eagan-martin]

Hi @tuanmonn , I hope you and the team are doing well, just wanna ask if there are any news regarding this feature? Looking forward for this awesome feature on dbml! Both generated and append concept from chishaku looks great for this :D