Computed column support by Shane32 · Pull Request #1896 · fluentmigrator/fluentmigrator

Shane32 · 2024-10-03T05:43:29Z

Adds computed column support

Todo:

Verify and/or test syntax for each provider
Determine how postgres alters generated/virtual columns
Verify infrastructure changes are okay
Verify various database syntaxes are okay
Add tests

Notes:

Throws exceptions as appropriate similar to other provider-specific features
Adds parentheses around the provided expression

Shane32 · 2024-10-03T06:00:58Z

        protected override string FormatNullable(ColumnDefinition column)
        {
-            if (column.IsNullable == true && column.Type == null && !string.IsNullOrEmpty(column.CustomType))
+            if (column.IsNullable == true && column.Type == null && !string.IsNullOrEmpty(column.CustomType) && column.Expression == null)


I really don't understand the purpose of this override to begin with. Only in the case of using AsCustom does it provide NULL for a nullable field. Why for AsCustom but not for any other data types? I can see that perhaps it was desirable to specify NULL or NOT NULL to override a non-default database option of ANSI_NULL_DFLT_OFF, but then why would it be only for AsCustom and no other data types? If this condition were removed, the entire overridden method could be removed.

Most likely it is because for all the other types, there are C# structs that map to them and the default is to be non-null. You have to wrap a struct as Nullable where T is a value type.

I agree, it is weird but would be a breaking change. If we break it, it would ideally be done in a separate PR and we rebase this PR - this way the PR auto-generates in the release notes as a breaking change and is a lot clearer to end-users.

Is SqlServer2008Column or later re-overriding this behavior though?

Is SqlServer2008Column or later re-overriding this behavior though?

No it isn't.

jzabroski · 2024-10-03T18:56:28Z

We could throw an exception or just assume the user intended to create a stored computed column.

Use CompatibilityMode.HandleCompatibility

fluentmigrator/src/FluentMigrator.Runner.Core/Generators/CompatibilityModeExtension.cs

Line 26 in 38530ea

public static class CompatibilityModeExtension

PhenX · 2024-10-05T15:11:55Z

+        /// <param name="expression">The expression to calculate</param>
+        /// <param name="stored">Whether the computed column is virtual or stored</param>
+        /// <returns>The next step</returns>
+        TNext AsExpression(string expression, bool stored = false);


Hello, just my two cents, but I think this naming may confuse people, because of C# Expression trees for example, while it is dedicated to computed columns. Why not name it simply AsComputed(string expression, bool stored = false) ?

Makes sense. Also it’s now treated as an option, so like Identity or Indexed, maybe it should be called Computed?

@PhenX Good question and your feedback serves as a "Keeper of the Flame."

I originally suggested AsExpression because the type is inferred by almost all database systems based on an expression's final type. In addition, I was thinking at some point specifying expressions from a POCO (similar to how third party package EasyMigrator works), so you could one day pass in an expression tree.

Incorporating expressions at some point in the future would also be a nice way to specify partition formulas. For a long time I didn't see a ton of value in the EasyMigrator approach but with features I want to do in the future like truly idempotent, offline Migrations and ability to test Migrations, having a current representation of what the schema should be described as entities is quite nice.

How confusing do you think it is? I guess the weird part is nobody in the sql world would call it an expression column.

the weird part is nobody in the sql world would call it an expression column

I think that’s important. I expect one of the goals is to make the library easy to use. If you were a user and were attempting to make a computed / calculated / virtual / generated column, what method name would you look for?

Expression certainly still makes sense for the argument name even if it’s not the name of the method.

I originally suggested AsExpression because the type is inferred by almost all database systems based on an expression's final type

My research indicated that only SQL Server was able to infer the data type; all other databases require an explicit data type. Hence moving the method to an option.

Ok. I'm out voted 2 to 1. AsComputed

lol I’m okay with whatever. Just my opinion.

Do you think it should be AsComputed or just Computed? We don’t have AsIdentity which is also a generated column…

I find Computed may be a better choice especially if we may still need to specify the column type with the "as" methods

Good point. Computed() reads better.

We could also add an IColumnTypeSyntax.AsComputed<TDbType>(string expression, bool stored = false) that wraps the long-form expression, e.g.

.AsComputed<int>("USER_ID()", true)

would be short-form for

.AsInt32().Computed("USER_ID(), true)

The relative advantage of the short-form is API discoverability.

ExpressionBuilderWithColumnTypesBase would then be something like:

/// <inheritdoc /> public TNext AsComputed<TDbType>(string expression, bool stored = false) { Column.Type = GetDbType<TDbType>(); this.Computed(expression, stored); return (TNext)(object)this; } private DbType GetDbType<TDbType>() { var typeCode = Type.GetTypeCode(typeof(TDbType); switch (typeCode) { case TypeCode.String: return DbType.String; case TypeCode.Int32: return DbType.Int32; case TypeCode.Int64: return DbType.Int64; case TypeCode.Decimal: return DbType.Decimal; // etc default: throw new InvalidOperationException("TDbType type code {typeCode} does not map to a FluentMigrator DbType."); } }

Thinking about my prototype above a bit more after lunch break - for Decimal and String, this interface is non-ideal since we wouldnt know the length of the string or whether it's an ANSI or UTF string, fixed or variable, or in the case of decimal, the scale and precision.

Okay, I'll work on the 'long-form' first (when I have time) and then we can always add the short-form as subsequent PR if desired.

PhenX · 2025-03-04T21:03:28Z

Hey @Shane32, any news about this pull request ? :)

jzabroski · 2025-03-06T16:09:44Z

@PhenX I'll take the blame for the momentum we lost on this PR. October was a busy time for me with the new born and I was probably too fussy with my feedback here.

I actually think we're not far from

So, relatively good news, @Shane32 changes only had one failing test so far:

FluentMigrator.Tests.Unit.Generators.Postgres.PostgresTableTests.CanCreateTableWithMultiColumnPrimaryKeyWithDefaultSchema(STRICT)

FluentMigrator.Exceptions.DatabaseOperationNotSupportedException : Virtual computed columns are not supported

jzabroski · 2025-03-06T16:29:30Z

+        public string Expression { get; set; }
+
+        /// <inheritdoc />
+        public bool ExpressionStored { get; set; }


@PhenX @Shane32 This is why the unit test is failing. Should it default to true?

I don't think it should be true by default

I designed as follows: Expression is a string containing the computed column SQL expression if it is a computed column; otherwise null. ExpressionStored is always false unless (a) it is a computed column, and (b) the column is persisted/stored (vs a virtual column).

PhenX · 2025-03-06T17:00:09Z

@jzabroski I think my proposal to rename the method should be applied before merging though. It will be much more clear imo

Shane32 · 2025-03-06T17:09:28Z

Sorry I forgot about this. I can’t remember where we were but I’m glad to pick it up again.

jzabroski · 2025-03-06T17:47:32Z

Agree. AsExpression should be renamed to AsComputed

Shane32 · 2025-03-11T02:53:26Z

+            var expression = GeneratorTestHelper.GetAlterColumnExpressionWithComputed();
+
+            var result = Generator.Generate(expression);
+            result.ShouldBe("ALTER TABLE \"public\".\"TestTable1\" ALTER \"TestColumn1\" TYPE ;");


@Shane32 @jzabroski While writing integration tests, I'm telling myself I'm not sure we need to support this, or only the possibility to change the expression of an already computed column. What do you think ?

I think line 292 is wrong.

All computed columns in Postgres are STORED.

You cannot change the type of the computed column.

You cannot replace a non-computed column with a computed column expression.

We could offer CompatibilityMode.Strict as a way to throw an error if someone tries to do this. If in non-Strict mode, drop the existing column and re-create it.

Yeah, that's why I asked this, the SQL is clearly wrong, hence @Shane32 's todo
I think we should simply remove theses tests, the SQL error a user might have if he tries that might be explicit enough not for us to throw a custom exception, no ?

Ideally every Generator follow the same Test naming conventions, so I am not in favor of removing tests. In the past this has caused us to miss regressions when making changes.

Shane32 · 2025-03-11T02:53:46Z

+            var expression = GeneratorTestHelper.GetAlterColumnExpressionWithComputed();
+
+            var result = Generator.Generate(expression);
+            result.ShouldBe("ALTER TABLE \"public\".\"TestTable1\" ALTER \"TestColumn1\" TYPE ;");


Shane32 · 2025-03-11T02:53:56Z

+            var expression = GeneratorTestHelper.GetAlterColumnExpressionWithComputed();
+
+            var result = _generator.Generate(expression);
+            result.ShouldBe("ALTER TABLE \"public\".\"TestTable1\" ALTER \"TestColumn1\" TYPE ;");


Shane32 · 2025-03-11T03:12:07Z

Anyone know where to find postgres SQL documentation for altering generated columns? This link here only mentions adding generated columns. Or with a sample syntax that we know works, I can update the code to produce expressions of the proper format.

Shane32 · 2025-03-11T03:16:40Z

Also, do CI checks automatically run here? Do they test all providers? I'm guessing that locally only SQLite runs by default. I could probably configure MS SQL localdb but I'm not really planning to test any other providers locally.

Shane32 · 2025-03-11T03:20:06Z

So, relatively good news, @Shane32 changes only had one failing test so far:

FluentMigrator.Tests.Unit.Generators.Postgres.PostgresTableTests.CanCreateTableWithMultiColumnPrimaryKeyWithDefaultSchema(STRICT)

I fixed the bug and checked all the similar conditions. Also wrapped all the expressions with parentheses, which is required by SQLite at minimum, and probably other providers too.

Shane32 · 2025-03-11T03:24:02Z

+            Create.Table("Products")
+                .WithColumn("Id").AsInt32().PrimaryKey().Identity()
+                .WithColumn("Price").AsDecimal(10, 2).NotNullable()
+                .WithColumn("Quantity").AsInt32().NotNullable()
+                .WithColumn("Total").AsDecimal(10, 2).AsExpression("Price * Quantity");
+
+            Alter.Table("Products")
+                .AddColumn("TotalWithTax").AsDecimal(10, 2).AsExpression("Total * 1.1", true);


This code will probably need a conditional to only run for providers that support virtual/generated columns. Since it only tests SQLite currently, the test passes. Also, one provider only supports stored/persisted columns, so might need to pass in , true for that provider. Could expand this migration to test altering the column as well, with additional conditionals for providers that support altering virtual/generated columns.

Shane32 · 2025-04-18T18:23:07Z

@jzabroski Had any time to look at this PR?

jzabroski · 2025-05-07T17:39:34Z

Also, do CI checks automatically run here?

Unfortunately it's broken. I think its because I enabled branch protection. I really dislike how GitHub security works.

jzabroski · 2025-05-07T17:40:52Z

@jzabroski Had any time to look at this PR?

I'm getting my energy back up again. I think this looks good but you have a few outstanding TODOs in your comments from March 10th that need resolving. I will run the pipeline for you now so you can see how the tests perform.

PhenX · 2025-08-18T08:30:12Z

@jzabroski I hope you get better now, what do we do about the last remaining issues ? Do we merge this PR as is and I make a pull request right after with the resolved TODOs ?

jzabroski · 2025-08-18T08:38:49Z

These two TODO?

Verify and/or test syntax for each provider
Determine how postgres alters generated/virtual columns

PhenX · 2025-08-18T09:08:36Z

Sorry, I meant the renaming of "AsExpression" to "Computed", but the todos are of course needed

PhenX · 2025-08-18T10:37:48Z

I prepared the changes here, along with test fixes https://github.com/PhenX/fluentmigrator/tree/computed_columns

jzabroski · 2025-08-18T20:49:42Z

Thanks.

Should it be .Computed() or .AsComputed()?
Regarding how Postgres handles generated columns, Grok says the following scenarios - and we can compress this response into another prompt that would guide Copilot with Claude Sonnet 4 to generate tests for Postgres iff we explain the unit testing architecture for testing generators:
You cannot directly modify the expression of an existing generated column in PostgreSQL (as of PostgreSQL 17). To change the expression, you must:

Drop the column and recreate it with the new expression.

Steps:
1. Use ALTER TABLE table_name DROP COLUMN column_name; to remove the existing generated column.
2. Use ALTER TABLE table_name ADD COLUMN column_name data_type GENERATED ALWAYS AS (new_expression) STORED; (or VIRTUAL in PostgreSQL 17) to recreate it with the new expression.
Example:
```
-- Original table with a generated column
CREATE TABLE products (
   id SERIAL PRIMARY KEY,
   price NUMERIC,
   tax NUMERIC GENERATED ALWAYS AS (price * 0.1) STORED
);

-- Drop the generated column
ALTER TABLE products DROP COLUMN tax;

-- Recreate with a new expression
ALTER TABLE products ADD COLUMN tax NUMERIC GENERATED ALWAYS AS (price * 0.15) STORED;
```
Changing the Data Type of a Generated Column

You can alter the data type of a generated column using ALTER TABLE ... ALTER COLUMN ... TYPE, but the new data type must be compatible with the expression’s output.

Syntax:
ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;

Example:
```
-- Original table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price NUMERIC,
    tax INTEGER GENERATED ALWAYS AS (price * 0.1) STORED
);

-- Change data type to NUMERIC
ALTER TABLE products ALTER COLUMN tax TYPE NUMERIC;
```
Converting Between Stored and Virtual (PostgreSQL 17+)

In PostgreSQL 17, with the introduction of virtual generated columns, you cannot directly convert a STORED generated column to a VIRTUAL one (or vice versa) without dropping and recreating the column.

Steps:
1. Drop the existing generated column.
2. Recreate it with the desired storage type (STORED or VIRTUAL).
Example:
```
-- Original table with a STORED generated column
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  price NUMERIC,
  tax NUMERIC GENERATED ALWAYS AS (price * 0.1) STORED
);

-- Drop the column
ALTER TABLE products DROP COLUMN tax;

-- Recreate as VIRTUAL
ALTER TABLE products ADD COLUMN tax NUMERIC GENERATED ALWAYS AS (price * 0.1) VIRTUAL;
```

PhenX · 2025-08-20T05:51:58Z

Computed, like Indexed, etc
OK, this feature will clearly need integration tests before beings released, but I think we will need another pull request.

Shane32 · 2025-08-21T03:35:11Z

I just saw some activity here, so I merged main in, fixed merge errors, then renamed AsExpression to Computed. All tests, such as they are, passed.

PhenX · 2025-08-28T19:54:05Z

@jzabroski let's merge this and after I'll make a PR with this what do you think?
https://github.com/PhenX/fluentmigrator/tree/computed_columns_integration_tests

jzabroski · 2025-08-28T20:27:55Z

It is claiming it is too complex for the web editor. Same issue with the DbFactory change. Won't let me do --no-ff either, and that is a local operation that GitHub has no control over.

Shane32 · 2025-09-01T02:21:47Z

I can rebase it manually, and then it will let you 'rebase and merge'. I normally use Squash and Merge myself, so I don't run into this issue.

Shane32 · 2025-09-01T02:25:41Z

Looking at the merge history, it looks like 'squash and merge' was used in the past, so not sure if it was an intentional change to use 'rebase and merge' now.

Shane32 · 2025-09-01T02:30:02Z

@PhenX most likely your branch is now going to have merge conflicts since I rebased this PR... (ugh) it would have been the same with squash and merge though probably ...

PhenX · 2025-09-01T06:09:43Z

@Shane32 no problem :)

jzabroski · 2025-09-01T12:39:51Z

@Shane32 Solved via #2131 - I still dont know what went wrong but I think it was a glitch in the GitHub UI. Today, the UI is different, but I am closing this PR. I merged @PhenX #2131 but preserved the commit history so that you get proper credit.

I sometimes squash - especially if it helps hide how dumb/rushed I can be :) Sometimes I only have 30 minutes to code on this project and I just commit stuff without running tests, etc. Not how I would code at my day job but somewhat low risk. Most of my deep thought on this project is reviewing PRs and issues vs. getting time to code. Hoping that will change going forward.

jzabroski · 2025-09-01T12:40:26Z

This is what the Conflict looks like today - way more descriptive than before.

Shane32 · 2025-09-01T13:51:35Z

Ok no problem. Interesting it didn't look that way on my pc. It said no conflicts... In any case glad it's merged in!

Shane32 · 2026-04-16T14:24:41Z

Note: This feature was merged in via:

Computed columns integration tests #2131

Shane32 commented Oct 3, 2024

View reviewed changes

PhenX reviewed Oct 5, 2024

View reviewed changes

jzabroski reviewed Mar 6, 2025

View reviewed changes

Shane32 force-pushed the computed_columns branch from 5b8d3d5 to 1ca1cd6 Compare March 11, 2025 02:42

Shane32 commented Mar 11, 2025

View reviewed changes

Comment thread src/FluentMigrator.Runner.Postgres/Generators/Postgres/PostgresColumn.cs Outdated

Shane32 commented Mar 11, 2025

View reviewed changes

PhenX approved these changes Aug 21, 2025

View reviewed changes

jzabroski approved these changes Aug 28, 2025

View reviewed changes

Add computed column support

752b1b3

Shane32 force-pushed the computed_columns branch from d198c9d to 752b1b3 Compare September 1, 2025 02:23

jzabroski closed this Sep 1, 2025

Shane32 deleted the computed_columns branch September 1, 2025 13:53

This was referenced Sep 4, 2025

Support Computed Columns #1152

Closed

1152 Issue Support Computed Columns #1155

Closed

This was referenced Apr 19, 2026

Bump FluentMigrator and FluentMigrator.Runner.Postgres reigithub/unity6-portfolio#155

Closed

Bump FluentMigrator and 2 others reigithub/unity6-portfolio#154

Merged

Bump FluentMigrator.Runner.SQLite from 3.3.2 to 8.0.1 Swartdraak/Bibliophilarr#57

Open

This was referenced Jun 8, 2026

Bump FluentMigrator from 6.2.0 to 8.0.1 klomkling/themia#61

Closed

Bump FluentMigrator.Runner from 6.2.0 to 8.0.1 klomkling/themia#62

Closed

This was referenced Jun 15, 2026

Bump FluentMigrator from 6.2.0 to 7.2.0 klomkling/themia#95

Closed

Bump FluentMigrator.Runner from 6.2.0 to 7.2.0 klomkling/themia#96

Closed

dependabot Bot mentioned this pull request Jul 19, 2026

chore: Bump the fluentmigrator group with 2 updates grezap/dataflow-studio#22

Merged

Uh oh!

Conversation

Shane32 commented Oct 3, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

jzabroski commented Oct 3, 2024

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Shane32 Oct 5, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

jzabroski Oct 7, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

PhenX commented Mar 4, 2025

Uh oh!

jzabroski commented Mar 6, 2025

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

PhenX commented Mar 6, 2025

Uh oh!

Shane32 commented Mar 6, 2025

Uh oh!

jzabroski commented Mar 6, 2025

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Shane32 commented Mar 11, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Shane32 commented Mar 11, 2025

Uh oh!

Shane32 commented Mar 11, 2025

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Shane32 commented Apr 18, 2025

Uh oh!

jzabroski commented May 7, 2025

Shane32 commented Oct 3, 2024 •

edited

Loading

Shane32 Oct 5, 2024 •

edited

Loading

jzabroski Oct 7, 2024 •

edited

Loading

Shane32 commented Mar 11, 2025 •

edited

Loading

PhenX commented Aug 18, 2025 •

edited

Loading

Shane32 commented Aug 21, 2025 •

edited

Loading