How to create Postgres table with Identity as GENERATED ALWAYS using Fluent Migrator?

Prasanna Kumar J 0
Viewed 18

My Fluent migrator code :

Create.Table("Players")
    .WithColumn("Id"       ).AsInt32().NotNullable().PrimaryKey().Identity()
    .WithColumn("FirstName").AsString();

Generated SQL is:

CREATE TABLE public.Players (
    id     serial4      NOT NULL,
    "name" varchar(100) NOT NULL,

    CONSTRAINT PK_Players PRIMARY KEY (id)
);

But I want the id as GENERATED ALWAYS AS IDENTITY instead of serial4.

Expected table creation SQL is :

CREATE TABLE public.Players (
    id     int4         NOT NULL GENERATED ALWAYS AS IDENTITY,
    "name" varchar(100) NOT NULL,

    CONSTRAINT "PK_Players" PRIMARY KEY (id)
);

I don't know how to achieve this using fluent migrator.

Could you please provide solutions or suggestions?

Configurations

private static IServiceProvider CreateServices( IConfiguration configuration )
{
    return new ServiceCollection()
        .AddSingleton<IConventionSet>( new DefaultConventionSet( configuration.GetSection("TenantName").Value, null )
        )
        .AddFluentMigratorCore()
            .ConfigureRunner( r => r
                .AddPostgres()
                .WithGlobalConnectionString(  configuration.GetConnectionString("DefaultConnection" ) )
                                                    
                .WithRunnerConventions( new MigrationRunnerConventions() )
                .ScanIn( typeof( Migrations.CoreInitialMigration ).Assembly ).For.Migrations()
            )
            .BuildServiceProvider(false);
}

private static void UpdateDatabase(IServiceProvider serviceProvider)
{
    // Instantiate the runner
    var runner = serviceProvider.GetRequiredService<IMigrationRunner>();
    
    // Execute the migrations
    runner.MigrateUp();
}

In StartUp.cs

```csharp
var serviceProvider = CreateServices(Configuration);
using (var scope = serviceProvider.CreateScope())
{
    UpdateDatabase(scope.ServiceProvider);
}
fluent-migrator c# sql
1 Answers

FluentMigrator does seems to support GENERATED ... AS IDENTITY - so the problem is caused by your (lack of) configuration.

Your ConfigureServices method currently has this:

.AddFluentMigratorCore()
.ConfigureRunner( r => r
    .AddPostgres()
    .WithGlobalConnectionString( configuration.GetConnectionString( "DefaultConnection" ) )
    .WithRunnerConventions( new MigrationRunnerConventions() )
    .ScanIn( typeof(Migrations.CoreInitialMigration).Assembly ).For.Migrations()
)

Your use of .AddPostgres() will configure FluentMigrator to assume you're using an old version of Postgres - I'm unsure which version exactly but it looks like some version older than Postgres 9.2.

...but GENERATED AS IDENTITY was introduced in Postgres 10 (observe how AS IDENTITY isn't in the 9.x docs), so you need to change AddPostgres() to AddPostgres10_0() or higher, such as AddPostgres11_0().

You can see these extension methods here.

So change your code to this:

.AddFluentMigratorCore()
.ConfigureRunner( r => r
    .AddPostgres11_0()
    .WithGlobalConnectionString( configuration.GetConnectionString( "DefaultConnection" ) )
    .WithRunnerConventions( new MigrationRunnerConventions() )
    .ScanIn( typeof(Migrations.CoreInitialMigration).Assembly ).For.Migrations()
)

...and it should just-work.

usertqF0
Related